SELECT statement in JAVA

Question

    public void search() throws Exception{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String url = "jdbc:odbc:******";
                String user = "*****";
                String pass = "*****";
                Connection con =  DriverManager.getConnection(url, user, pass);
                Statement state = con.createStatement();
                ResultSet rs = state.executeQuery("");
                ResultSetMetaData rsmetadata = rs.getMetaData();
                int columns = rsmetadata.getColumnCount();
                DefaultTableModel dtm = new DefaultTableModel();
                Vector column_name = new Vector();
                Vector data_rows = new Vector();

                for (int i=1; i<columns;i++){
                    column_name.addElement(rsmetadata.getColumnName(i));
                }
                dtm.setColumnIdentifiers(column_name);

                while(rs.next()){
                    data_rows = new Vector();
                    for (int j=1; j<columns; j++){
                    data_rows.addElement(rs.getString(j));
                    }
                    dtm.addRow(data_rows);
                }
                tblPatient.setModel(dtm);
        }

On my ResultSet rs = state.executeQuery() I used this SQL

                              "SELECT "
                            + "pIDNo AS 'Patient ID',"
                            + "pLName AS 'Last Name'," 
                            + "pFName AS 'First Name',"
                            + "pMI AS 'M.I.',"
                            + "pSex AS 'Sex',"
                            + "pStatus AS 'Status',"
                            + "pTelNo AS 'Contact No.',"
                            + "pDocID AS 'Doctor ID',"
                            + "pAddr AS 'St. No.',"
                            + "pStreet AS 'St. Name',"
                            + "pBarangay AS 'Barangay',"
                            + "pCity AS 'City',"
                            + " pProvince AS 'Province',"
                            + " pLNameKIN AS 'Last Name',"
                            + "pFNameKIN AS 'First Name',"
                            + "pMIKIN AS 'M.I.',"
                            + "pRelationKIN AS 'Relation',"
                            + "pTotalDue AS 'Total Due'"
                            + " FROM dbo.Patients"); 

First I run this line (pTotalDue didn't come up to jTable.)

And on my second attempt to display it I do this:

"SELECT pTotalDue AS 'Total Due' FROM dbo.Patients"

Now I tried this one, and I think something's really wrong about my codes. BTW this column has MONEY DATA TYPE

why does it didn't show to my JTable? could anyone tell me what is the problem with my codes?

(Problem in the answer that has given to me) public class QueryOnWorkerThread extends SwingWorker{ private final JTable tableToUpdate;

  public QueryOnWorkerThread( JTable aTableToUpdate ) {
    tableToUpdate = aTableToUpdate;
  }

  @Override
  protected TableModel doInBackground() throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String url = "jdbc:odbc:OJT_dsn";
    String user = "sa";
    String pass = "";
    Connection con =  DriverManager.getConnection( url, user, pass );
    Statement state = con.createStatement();
    ResultSet rs = state.executeQuery("");
    ResultSetMetaData rsmetadata = rs.getMetaData();
    int columns = rsmetadata.getColumnCount();
    DefaultTableModel dtm = new DefaultTableModel();
    Vector column_name = new Vector();
    Vector data_rows;

    //note the <= check iso the < check (as the count starts at index 1)
    for (int i=1; i<=columns;i++){
      column_name.addElement(rsmetadata.getColumnName(i));
    }
    dtm.setColumnIdentifiers(column_name);

    while(rs.next()){
      data_rows = new Vector();
      //note the <= check iso the < check (as the count starts at index 1)
      for (int j=1; j<=columns; j++){
        data_rows.addElement(rs.getString(j));
      }
      dtm.addRow(data_rows);
    }
    return dtm;
  }


        `@Override <<<<<<<<<<<<<<<<<<<<< I have a problem here it says : done() in javaapplication25.SearchPatient.QueryWorkerThread cannot override done() in javax.swing.SwingWorker overriden method does not throw java.lang.Exception , what does it mean sir?` 
  protected void done() throws Exception{
    //this method runs on the EDT, so it is safe to update our table here
    try {
      tableToUpdate.setModel( get() );
    } catch ( InterruptedException e ) {
      throw new RuntimeException( e );
    } catch ( ExecutionException e ) {
      throw new RuntimeException( e );
    }
  }

Answer 1

try this

DefaultTableModel dtm=(DefaultTableModel)table.getModel();
for (int i = dtm.getRowCount() - 1; i > -1; i--) {
dtm.removeRow(i);
}

Connection con =  DriverManager.getConnection(url, user, pass);
Statement state = con.createStatement();
ResultSet rs = state.executeQuery("Your SQL Query");

while(rs.next())
{
String str1=rs.getString(1);
String str2=rs.getString(2);
String str3=rs.getString(3);
String str4=rs.getString(4);
String str5=rs.getString(5);
:
:
:
dtm.addRow(new Object[]{str1,str2,str3,str4,str5});
}

Answer 2

In you loops, your exit condition is

j<columns 

this means thant the last column will never be recovered. try this insted:

for (int j=1; j<=columns; j++)

Answer 3

The fact that your last column does not appear is probably related to your loop statements, as already indicated by @Joan.

There are however more issues with this code. You should only update Swing components on the Event Dispatch Thread, and on that Thread you should not perform long running operations. In short, mixing SQL queries and updates of the JTable should not happen on the same thread. Consult the Concurrency in Swing guide for more info.

Using a SwingWorker could solve this issue:

public class QueryOnWorkerThread extends SwingWorker<TableModel, Void>{
  private final JTable tableToUpdate;

  public QueryOnWorkerThread( JTable aTableToUpdate ) {
    tableToUpdate = aTableToUpdate;
  }

  @Override
  protected TableModel doInBackground() throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String url = "jdbc:odbc:******";
    String user = "*****";
    String pass = "*****";
    Connection con =  DriverManager.getConnection( url, user, pass );
    Statement state = con.createStatement();
    ResultSet rs = state.executeQuery("");
    ResultSetMetaData rsmetadata = rs.getMetaData();
    int columns = rsmetadata.getColumnCount();
    DefaultTableModel dtm = new DefaultTableModel();
    Vector column_name = new Vector();
    Vector data_rows;

    //note the <= check iso the < check (as the count starts at index 1)
    for (int i=1; i<=columns;i++){
      column_name.addElement(rsmetadata.getColumnName(i));
    }
    dtm.setColumnIdentifiers(column_name);

    while(rs.next()){
      data_rows = new Vector();
      //note the <= check iso the < check (as the count starts at index 1)
      for (int j=1; j<=columns; j++){
        data_rows.addElement(rs.getString(j));
      }
      dtm.addRow(data_rows);
    }
    return dtm;
  }

  @Override
  protected void done() {
    //this method runs on the EDT, so it is safe to update our table here
    try {
      tableToUpdate.setModel( get() );
    } catch ( InterruptedException e ) {
      throw new RuntimeException( e );
    } catch ( ExecutionException e ) {
      throw new RuntimeException( e );
    }
  }
}

The SwingWorker can be started by calling

QueryOnWorkerThread worker = new QueryOnWorkerThread( tblPatient );
worker.execute();

Note how I changed the loops in your code

Answer 4

Try getting that column via ResultSet.getBigDecimal() rather than via ResultSet.getString(). Then put your retrieved BigDecimal.toPlainString() into your table cell.

Example:

data_rows.addElement(rs.getBigDecimal("pTotalDue").toPlainString());//Assuming your select returns a pTotalDue Column (e.g. SELECT pTotalDue,... FROM ...)

Answer 5

Try to Use an TableCellRenderer.

Implement the Renderer and render the Column with the Money Type in the form you wish.

Regards, HL