Uploading a Clob field from Derby Db to a JTABLE within Netbeans 12.5 and GUI Form

[Bill Melendez]

Guys: I have written a program for tracking support calls but now need to load the results from the Derby DB. The fields are CLOBs and I have been able to save them to Derby and display them from Derby to a TextArea entry. However, I now need to display the CLOBs to a JTable entry. The below is a method I have so far done and it does load the Clobs but not as string data.  Instead I get "org.apache.derby.impl.jdbc.EmbedClob@55443010 , org.apache.derby.impl.jdbc.EmbedClob@3e784536, and so on" for each field entry which, I understand to be the definitions of the Clobs as stored in the database. Loading directly from the database as a ResultSet is pretty straight forward-- but loading it using DbUtils doesn't allow for converting to a string text. I need to be able to display the text of each CLOB within the JTable. Any help would be appreciated. I fully understand that outside of Netbeans one can write great code --however, I need to continue using the GUI Form Displays. The below method is run from a jbutton (external to the method) and it displays the results to a table    jtbl_CategoryList.

 private void TaskLoadDB() throws SQLException {                                            
        Connection conn;
        Statement stmt;
        ResultSet rst;
         //* Load the data from the database  
         
         SimpleDateFormat format = new SimpleDateFormat("MM-dd-yyyy");
           // DATETIME = String.format(DATETIME);   
            //String date = format.format(DATETIME);
        try {
           
            conn = DriverManager.getConnection("jdbc:derby:ticketLogDB");
            String sql5 = "SELECT PROBLEM,TROUBLESHOOT, RESOLUTION,TASKCODE  FROM TASKLOG";
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sql5);
            
                   // Iterate through the result set and retrieve the CLOB fields.
       
                   // check for selected row first --remove empty rows 
           if(jtbl_CategoryList.getSelectedRow() != -1) {
               //remove empty rows from the model
             modelC.removeRow(jtbl_CategoryList.getSelectedRow()); }    
             
            //* place the data from the database into the JTable
           
             String[] clobFields = new String[rst.getMetaData().getColumnCount()];
            while (rst.next()) {
                 for (int i = 0; i < clobFields.length; i++) {
                  clobFields[i] = rst.getClob(i + 1).toString();
                  
                     //* place the data from the database into the JTable
           
                }
                   modelC = (DefaultTableModel) DbUtils.resultSetToTableModel(rst);         
               }
            
            jtbl_CategoryList.setModel (modelC) ;     
            jtbl_CategoryList.setRowHeight(20);
            jtbl_CategoryList.setCellSelectionEnabled(true);
            jtbl_CategoryList.setAutoCreateRowSorter(true);
            jtbl_CategoryList.setDragEnabled(true);
            jtbl_CategoryList.setColumnSelectionAllowed(true);
            jtbl_CategoryList.setRowSelectionAllowed(false);
           
          


            TableColumnModel columnModel = jtbl_CategoryList.getColumnModel();
            for(int col=0;col<jtbl_CategoryList.getColumnCount();col++){
                int maxwidth = 0;
                for (int row=0;row<jtbl_CategoryList.getRowCount();row++){
                    TableCellRenderer rend = jtbl_CategoryList.getCellRenderer(row, col);
                    Object value = jtbl_CategoryList.getValueAt(row, col);
                    Component comp = rend.getTableCellRendererComponent(jtbl_CategoryList, value, false, false,row, col);
                     SimpleDateFormat f = new SimpleDateFormat("MM/dd/yy");
                    if( value instanceof Date) {
                    value = f.format(value);
        }
                    
                    
                    
                    
                    maxwidth = Math.max(comp.getPreferredSize().width,maxwidth);
                }// for row
                TableColumn column = columnModel.getColumn(col);
                column.setPreferredWidth(maxwidth);
            }
              
           jtbl_CategoryList.setDefaultRenderer(Object.class, new TableCellRenderer(){
                private final DefaultTableCellRenderer DEFAULT_RENDERER =  new DefaultTableCellRenderer();
                @Override
                public Component getTableCellRendererComponent(JTable jtbl_TaskDisplay, Object value, boolean isSelected, boolean hasFocus, int row, int column) {
                Component c = DEFAULT_RENDERER.getTableCellRendererComponent(jtbl_TaskDisplay, value, isSelected, hasFocus, row, column);
                jtbl_TaskDisplay.setFont(new Font("Tahoma", Font.BOLD, 15));
              
                
                c.setForeground(Color.BLACK);
                if (row%2 == 0){
                    c.setBackground(Color.WHITE);
                }
                else {
                    c.setBackground(Color.getHSBColor(200,100,245));
                }                    
                             
                return c;
              }
           });
        
         
           // Set the headers on the JTable to replace the ones from the database table
           String header[] = {"PROBLEM","TROUBESHOOT ", "RESOLUTION ", "TASKCODE"};
            for(int i=1;i<(jtbl_CategoryList.getColumnCount()-1);i++)
            {
              // jtbl_CategoryList.getTableHeader().setFont( new Font( "Arial" , Font.BOLD, 14 ));
               TableColumn column1 = jtbl_CategoryList.getTableHeader().getColumnModel().getColumn(i);
                column1.setHeaderValue(header[i]);
              }
              
    }      catch (SQLException ex) {
            Logger.getLogger(PhoneLog.class.getName()).log(Level.SEVERE, null, ex); 
      }
        
    } 

[Paul Clapham]

Let me first suggest that wrapping your test code in a Swing wrapper will just add a layer of confusion. Start by writing some ordinary code which reads data from a database and writes it to the console.

And second, the Clob interface has various methods like length() and getSubstring() and getCharacterStream() which enable you to extract data from a Clob. You've got the getClob() method in your ResultSet. So use those methods to get ahold of the String data.

[Bill Melendez]

@Paul: Thanks for the great advice! I relooked at the whole setup and decided that I could use LONG VARCHAR instead of Clob. LONG VARCHAR allows up to 35K length --which is more than ample storing space for the different entries within the Phone Log program. It also made retrieving the data from the Derby DB much easier since JTABLE uses STRING and the method I coded really is critically tied to a String ResultSet. I didn't have to do any conversions from Clob to String.  

[Paul Clapham]

Bill Melendez wrote:I relooked at the whole setup and decided that I could use LONG VARCHAR instead of Clob.

Excellent idea! I've never used Clob myself -- I imagine it could be useful if you're storing a chapter of a novel in a database cell, or something like that, but just plain text has always been fine for my requirements.

[Tim Holloway]

I've used CLOB for a system that stored a lot of legal stuff in Rich Text Format.

But where possible, a String is preferable.

[Bill Melendez]

@All:  Aft,er solving my issue with Clob into Derby by using the LONG VARCHAR, and then loading this LONG VARCHAR to a JTable, I looked at adding the line wrap (setLineWrap(true)) to the rows but found it somewhat too complicated. For the time being, I instead used textareas to display the slected row data. Below is the simple Table_Click method I used by adding the mouse click event to the table. I hope someone out there can benefit from my own trial and error experience. NOTE: I added a public String to problemSearch, troubleshootSearch, resolutionSearch, and codeSearch. The parameter variable rowrec took a public Integer.

     private void jtbl_TaskListMouseClicked(java.awt.event.MouseEvent evt){

              int rowrec = jtbl_TaskList.getSelectedRow();
              int colrec = jtbl_TaskList.getSelectedColumn();
              
              //post record number to display within a JLabel
              String rec2 =String.valueOf(rowrec);
              jlbl_Record.setText(rec2);
              
              //implement table click 
              try {
                  Table_click = PhoneLog.this.jtbl_TaskList.getModel().getValueAt(rowrec, 0).toString();
             
 }catch(Exception ex){} 
                
              try{
              if (Table_click != null ){
                      
                 
                        int[] selection = jtbl_TaskList.getSelectedRows();
                        int q = 0;
                        rowrec = selection[q] ;
                      
                   if (rowrec >= 0 && colrec >= 0) {
                         try { 
		//setup the derby database search parameters (for Problem, Troubleshoot, Resolution, and TaskCode)

                          problemSearch = jtbl_TaskList.getValueAt(rowrec,0).toString();//Extracts the LONG VARCHAR Problem
                          troubleShootSearch = jtbl_TaskList.getValueAt(rowrec,1).toString();//Extracts the LONG VARCHAR Troubleshoot
                          resolutionSearch = jtbl_TaskList.getValueAt(rowrec,2).toString();//Extracts the LONG VARCHAR Resolution
                          codeSearch= jtbl_TaskList.getValueAt(rowrec,3).toString();//Extracts the VARCHAR random number (for duplicate entries to the same property issues)
                         }catch(Exception et){}
                
   	 try{
	      //run the different methods needed to post to textareas

                         doLoadTextareas(); //SQL search with "problemSearch" parameter. I could also use the other parameters to further refine the search
                        doAddVariables();// adds the ResultSet to the textareas entries
                         }catch (Exception et){}
                      }
                      
                                         
                      
                      
                  }
              }  catch(Exception ex){}  
    }