jtable using sql

johnnyc

i want to be able to display information from my database using sql. The jtable should have 3 columns this information will be retrieved from the access database..if anybody has code that will perform the task please post a reply.. When a person has been added the database should uploaded..the table should contain a scrollbar,,and clicking on the tabe should be disabled

hussey

yeah I do.

well sort of .. i'll post it soon and you can edit as nesseccary

hussey

What this does is creates a JFrame which allows the user to input a sql command, and it will create a JTable based on results

Please Edit the XXXXXXXXXX etc

not 100% what you were looking for but hey

you could use the SQL command to input the user
and this will update the table

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;


public class DisplayQueryResults extends JFrame {

  // java.sql types needed for database processing
  private Connection connection;
  private Statement statement;
  private ResultSet resultSet;
  private ResultSetMetaData rsMetaData;

  //javax.swing types needed for GUI
  private JTable table;
  private JTextArea inputQuery;
  private JButton submitQuery;

  public DisplayQueryResults() {
    //call super class constructor
    super( "Enter Query. Click Submit to See Results." );
    //The url specifying the GMS database to which this program connects using
    //JDBC to connect to a Microsoft ODBC database
    String url = "jdbc: odbc:XXXX";
    String username = "user";
    String password = "password";

    //Load the driver to allow connection to the database
    try {
      Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
      connection = DriverManager.getConnection(
          url, username, password );
    }
    catch ( ClassNotFoundException cnfex ) {
      System.err.println(
          "Failed to load JDBC/ODBC driver." );
      cnfex.printStackTrace();
      System.exit( 1 ); //terminate the program
    }
    catch ( SQLException sqlex ) {
      System.err.println( "Unable to Connect" );
      sqlex.printStackTrace();
      System.exit( 1 );
    }
    // If connected to database, set up GUI
    inputQuery = new JTextArea( "SELECT XXXXX from YYYYY where ZZZZZZZZZZZZZ", 4, 30 );
    submitQuery = new JButton( "Submit query" );
    submitQuery.addActionListener(
        new ActionListener() {
           public void actionPerformed( ActionEvent e )
           {
             if ( e.getSource() == submitQuery )
               getTable();
           }
        }
    );

    JPanel topPanel = new JPanel();
    topPanel.setLayout( new BorderLayout() );
    topPanel.add( new JScrollPane( inputQuery ), BorderLayout.CENTER );
    topPanel.add( submitQuery, BorderLayout.SOUTH );

    table = new JTable( 4, 4 );

    Container c = getContentPane();
    c.setLayout( new BorderLayout() );
    c.add( topPanel, BorderLayout.NORTH );
    c.add( table, BorderLayout.CENTER );

    //getTable();

    setSize( 500, 500 );
    show();
  }

  private void getTable() {

    try {
      String query = inputQuery.getText();

      statement = connection.createStatement();
      resultSet = statement.executeQuery( query );
      displayResultSet( resultSet );
    }
    catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
    }
  }

  private void displayResultSet( ResultSet rs ) throws SQLException {

    //position to first record
    boolean moreRecords = rs.next();
    // If there are no more records, display a message
    if ( ! moreRecords ) {
      JOptionPane.showMessageDialog( this, "ResultSet contained no records" );
      setTitle( "No records to display" );
      return;
    }

    Vector columnHeads = new Vector();
    Vector rows = new Vector();

    try {
      // get column heads
      ResultSetMetaData rsmd = rs.getMetaData();
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
        columnHeads.addElement( rsmd.getColumnName( i ));
      //get row data
      do {
        rows.addElement( getNextRow( rs, rsmd ) );
      } while ( rs.next() );

      //display table with ResultSet contents
      table = new JTable( rows, columnHeads );
      JScrollPane scroller = new JScrollPane( table );
      Container c = getContentPane();
      c.remove( 1 );
      c.add( scroller, BorderLayout.CENTER );
      c.validate();
    }catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
    }

  }// end displayResultSet

  private Vector getNextRow( ResultSet rs, ResultSetMetaData rsmd )
      throws SQLException {

    Vector  currentRow = new Vector();

    for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
      switch( rsmd.getColumnType( i ) ) {
          case Types.VARCHAR:
          case Types.LONGVARCHAR:
            currentRow.addElement( rs.getString( i ) );
            break;
          case Types.INTEGER:
            currentRow.addElement(
                new Long( rs.getLong( i ) ) );
            break;
          case Types.DOUBLE:
            currentRow.addElement(
                new Double( rs.getDouble( i ) ) );
          default:
            //System.out.println( "Type was: " + rsmd.getColumnTypeName( i ) );
      }

    return currentRow;
  }

  public void shutDown(){

    try {
      connection.close();
    }catch ( SQLException sqlex ) {
      System.err.println( "Unable to disconnect" );
      sqlex.printStackTrace();
    }
  }

  public static void main( String args[] ) {

    final DisplayQueryResults app = new DisplayQueryResults();

    app.addWindowListener(
        new WindowAdapter() {
           public void windowClosing( WindowEvent e ) {
             app.shutDown();
             System.exit( 0 );
           }
        }
    );
  }
}

johnnyc

The layout of the table is show the white sqaure box is my table. The following code displays the layout i have a sql statement to retrieve the info from the staff table. The information in this table is an integer, string and date. I want to place this info into the jtable which will be update when a person is added to the database.. The following is my code if you have any suggestions please reply. Please click the link to preview my layout

JTable scheduletable= new JTable();
scheduletable.setSize(200,190);
scheduletable.setLocation(180,110);
scheduletable.setEnabled(true);
container.add(scheduletable);
#############################################
//Reads the Information from the Schedule table
//and displays the informationon in a jtable

try
{
ResultSet rs;
ResultSetMetaData rsmd;
if (objDA == null)
{
objDA = new DataAccessObject();
}
rs = objDA.SelectDatabaseRecords("SELECT * From Staff");
rsmd = rs.getMetaData();
String num;
if ( rs == null )
{
System.out.println("rs is null");
}
else
{
System.out.println("rs is not null");
int max;
if (rs.next())
{
max = Integer.parseInt(rs.getString(1));

for (int numEmps = 0; numEmps < max; numEmps ++)
{
num = Integer.toString(numEmps + 1);
staff1.addItem((Object)num);
}
}
}

hussey

what you could do is attach an action associated with the add button

somthing like

JButton addButton = new JButton("add");
addButton.addActionListener(
        new ActionListener() {
           public void actionPerformed( ActionEvent e )
           {
             if ( e.getSource() == addButton )
               refreshTable();
           }
        }
    );

where refresh table just refresh's the table

ie when you click this .. do this

let the action call a function called I dunno 'updateTable'
and this will refresh the view on the table

johnnyc

theinfo on the table does'nt display anything thats the main problem

hussey

and how do you define update the table function??

I presume staff1 is a vector??

johnnyc

yes the staff1 is a vector

hussey

would it be possibel to put the java files in a .zip file and have attach it

I know you'l have a different database etc.

but just so I can have a look see

I'm not busy in work and it will give me something to do