Database Programming with Swing JTable question
Hello guys. i have a JComboBox with 4 items and a button by the name of execute. once a user select an item from a JComboxBox and click on execute i would like to show my results in JTable with the columnname and the appropriate results. i am new to JTable. i have a question.
If a person selects an item from my combobox how can i display the results in JTable? i could have done it easily with the textArea by using its append method or settext but to look it more attractive i wanted to use JTable. so how to display the results in the table?
Secondly where do i need to put my connection status for database like all those statements which we use to connect to database in try block and finally?
I am done with everything except the part to display results in JTable. any help will be really appreciated.
import java.awt.FlowLayout;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.table.TableModel;
import java.awt.event.ActionEvent;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
publicclass DatabaseProgrammingextends JFrameimplements ActionListener{
staticfinal String JDBC_DRIVER ="com.mysql.jdbc.Driver";
staticfinal String DATABASE_URL ="jdbc:mysql://localhost/books";
staticfinal String USERNAME ="jhtp6";
staticfinal String PASSWORD ="jhtp6";
privatestaticfinal String QUERY1 ="SELECT * FROM employees WHERE DEPARTMENTNAME = 'SALES'";
privatestaticfinal String QUERY2 ="SELECT * FROM hourlyEmployees WHERE hours >= '30'";
privatestaticfinal String QUERY3 ="SELECT * FROM commissionEmployees ORDER BY commissionRate DESC";
private String names[] ={"QUERY1","QUERY2","QUERY3","QUERY4"};
private Connection connection;
private ResultSet resultSet;
private Statement statement;
private ResultSetMetaData metaData;
private JTextArea result;
private JTable resultTable;
private JComboBox queryBox;
private JButton button;
privateint number;
privateboolean connectedDatabase =false;
publicstaticvoid main(String[] args){
DatabaseProgramming frame =new DatabaseProgramming();
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public DatabaseProgramming(){
super("Testing Database");
setLayout(new FlowLayout());
queryBox =new JComboBox(names);
result =new JTextArea(4,50);
button =new JButton("EXECUTE");
resultTable =new JTable();
button.addActionListener(this);
result.setEditable(false);
try{
Class.forName(JDBC_DRIVER);
connection = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
System.out.println("Yay Database Connected");
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
connectedDatabase =true;
}
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
System.exit( 1 );
}
catch ( ClassNotFoundException classNotFound )
{
classNotFound.printStackTrace();
System.exit( 1 );
}
finally
{
try
{
statement.close();
connection.close();
}
catch ( Exception exception )
{
exception.printStackTrace();
System.exit( 1 );
}
}
add(queryBox);
add(button);
add(resultTable);
//add(result);
setSize(400,400);
}
publicvoid actionPerformed(ActionEvent e){
if(true){
}
}
}
here's an old thread about using jtable and resultset
http://forum.java.sun.com/thread.jspa?threadID=765575&messageID=4365359
Yannix thanks for the help. But the output is not displayed in my JTable any ideas whats going on? i am watching the output in my console that its connected to the database. but i dont see the results in my JTabe.
import java.awt.FlowLayout;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import java.awt.event.ActionEvent;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;
public class DatabaseProgramming extends JFrame implements ActionListener{
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/books";
static final String USERNAME = "jhtp6";
static final String PASSWORD = "jhtp6";
private static final String QUERY1 = "SELECT * FROM employees WHERE DEPARTMENTNAME = 'SALES'";
private static final String QUERY2 = "SELECT * FROM hourlyEmployees WHERE hours >= '30'";
private static final String QUERY3 = "SELECT * FROM commissionEmployees ORDER BY commissionRate DESC";
private static final TableModel DatabaseProgramming = null;
private String names[] = {"QUERY1", "QUERY2", "QUERY3", "QUERY4"};
private Connection connection;
private ResultSet resultSet;
private Statement statement;
private ResultSetMetaData metaData;
private JTextArea result;
private JTable resultTable;
private JComboBox queryBox;
private JButton button;
private int number;
private boolean connectedDatabase = false;
private DefaultTableModel dtm;
public static void main(String[] args) {
DatabaseProgramming frame = new DatabaseProgramming();
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public DatabaseProgramming() {
super("Testing Database");
setLayout(new FlowLayout());
//dtm = new DefaultTableModel(getColumnName(number),0);
try {
Class.forName(JDBC_DRIVER);
connection = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
System.out.println("Yay Database Connected");
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
connectedDatabase = true;
queryBox = new JComboBox(names);
result = new JTextArea(4,50);
button = new JButton("EXECUTE");
resultTable = new JTable();
button.addActionListener(this);
add(queryBox);
add(button);
add(resultTable);
//add(result);
setSize(400,400);
}
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
System.exit( 1 );
}
catch ( ClassNotFoundException classNotFound )
{
classNotFound.printStackTrace();
System.exit( 1 );
}
}
public void actionPerformed(ActionEvent e) {
String selection = (String) queryBox.getSelectedItem();
if(selection.equals(QUERY1))
validateQuery(QUERY1);
if(selection.equals(QUERY2))
validateQuery(QUERY2);
if(selection.equals(QUERY3))
validateQuery(QUERY3);
}
public void validateQuery(String query) {
try {
Vector col = new Vector();
Vector data = new Vector();
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
metaData = resultSet.getMetaData();
resultSet.last();
number = metaData.getColumnCount();
for(int i=1; i<=number; i++)
col.addElement(metaData.getColumnName(i));
while(resultSet.next()) {
Vector row = new Vector();
for(int i=0; i<=number; i++)
row.addElement(resultSet.getObject(i));
data.addElement(row);
}
resultTable.setModel(new DefaultTableModel(data,col));
resultTable.revalidate();
}
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
System.exit( 1 );
}
finally
{
try
{
statement.close();
connection.close();
}
catch ( SQLException sqlException )
{
JOptionPane.showMessageDialog( null,
sqlException.getMessage(), "Database error",
JOptionPane.ERROR_MESSAGE );
disconnectFromDatabase();
System.exit( 1 );
}
}
}
public void disconnectFromDatabase()
{
if ( !connectedDatabase )
return;
try
{
statement.close();
connection.close();
}
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
}
finally
{
connectedDatabase = false;
}
}
}
Message was edited by:
schumacher
you can try this approach. this how i do it.
ResultSetTable myModel = new ResultSetTable();
try{
sqlString = "";
ResultSet rs = con.createStatement().executeQuery(sqlString);
myModel.setResultSet(rs);
myTable.setModel(myModel); // set your table model
}catch(Exception e){
}
class ResultSetTable extends AbstractTableModel {
private ResultSetMetaData rsMeta;
private int columnCount;
private final Vector<String> columnNames = new Vector<String>();
private final Vector<Object[]> cache = new Vector<Object[]>();
/** Set new result set */
public void setResultSet(ResultSet rs) throws SQLException {
cache.clear();
columnNames.clear();
rsMeta = rs.getMetaData();
columnCount = rsMeta.getColumnCount();
// get columnNames
for(int col = 0; col < columnCount; col++) {
columnNames.add(rsMeta.getColumnName(col+1));
}
// get data
while(rs.next()) {
Object rowData[] = new Object[columnCount];
for(int col = 0; col < columnCount; col++){
rowData[col] = rs.getObject(col+1);
}
cache.add(rowData);
}
fireTableStructureChanged();
}
public void close() throws SQLException {
rs.close();
}
public String getColumnName(int column) {
return columnNames.get(column);
}
public Object getValueAt(int rowIndex, int columnIndex) {
Object[] row = cache.get(rowIndex);
return row[columnIndex];
}
public int getRowCount() {
return cache.size();
}
public int getColumnCount() {
return columnCount;
}
public boolean isCellEditable(int row, int col){
return false;
}
}
Oh Lord spent 1.5 hour in writing this program and now had to redo everything again :(
you don't have to redo just create a class that i have posted
you already have a resultset just pass it to the setResultSet() method and set your jtable model.
e.g.
/*
ResultSetTable model = new ResultSetTable();
resultSet // your result set
model.setResultSet(resultSet);
resultTable // your jtable
resultTable.setModel(model);
*/
no need to change codes
Message was edited by:
Yannix
i did no success
private ResultSetTable re;
public static void main(String[] args) {
DatabaseProgramming frame = new DatabaseProgramming();
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public DatabaseProgramming() {
super("Testing Database");
setLayout(new FlowLayout());
re = new ResultSetTable();
.
.
.
.
.
.
public void actionPerformed(ActionEvent e) {
String selection = (String) queryBox.getSelectedItem();
if(selection.equals(QUERY1))
validateQuery(QUERY1);
if(selection.equals(QUERY2))
validateQuery(QUERY2);
if(selection.equals(QUERY3))
validateQuery(QUERY3);
}
public void validateQuery(String query) {
try {
Vector col = new Vector();
Vector data = new Vector();
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
re.setResultSet(resultSet);
metaData = resultSet.getMetaData();
resultSet.last();
number = metaData.getColumnCount();
for(int i=1; i<=number; i++)
col.addElement(metaData.getColumnName(i));
while(resultSet.next()) {
Vector row = new Vector();
for(int i=1; i<=number; i++)
row.addElement(resultSet.getObject(i));
data.addElement(row);
}
resultTable.setModel(re);
resultTable.revalidate();
}
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
System.exit( 1 );
}
/* finally
{
try
{
statement.close();
connection.close();
}
catch ( SQLException sqlException )
{
JOptionPane.showMessageDialog( null,
sqlException.getMessage(), "Database error",
JOptionPane.ERROR_MESSAGE );
System.exit( 1 );
}
} */
}
Same thing no output displayed
Message was edited by:
schumacher
now if i do this still no success
public void validateQuery(String query) {
try {
Vector col = new Vector();
Vector data = new Vector();
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
re.setResultSet(resultSet);
/* metaData = resultSet.getMetaData();
resultSet.last();
number = metaData.getColumnCount();
for(int i=1; i<=number; i++)
col.addElement(metaData.getColumnName(i));
while(resultSet.next()) {
Vector row = new Vector();
for(int i=1; i<=number; i++)
row.addElement(resultSet.getObject(i));
data.addElement(row);
}*/
resultTable.setModel(re);
resultTable.revalidate();
}
In here you called you database programming method
public static void main(String[] args) {
DatabaseProgramming frame = new DatabaseProgramming();
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
In here i don't see when did you call your validate query method
public DatabaseProgramming() {
super("Testing Database");
setLayout(new FlowLayout());
re = new ResultSetTable();
.
.
.
.
.
.
if you are having a hard time try this code it is fully functional
just change the database name and query statement.
import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.util.Vector;
import javax.swing.table.AbstractTableModel;
public class sample1{
private static JFrame frm;
private static JTable jt;
private static JScrollPane pane;
private static ResultSetTable model = new ResultSetTable();
public static void main(String args[]){
String url = "jdbc:mysql://192.168.1.1:3306/ersdb";
Connection con;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url,"webdev","test");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from t_custtype");
// display the data in jtable
jt = new JTable();
model.setResultSet(rs);
jt.setModel(model);
pane = new JScrollPane(jt);
frm = new JFrame("Sample");
frm.setSize(400,400);
frm.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frm.setLayout(new BorderLayout());
frm.add(pane,BorderLayout.PAGE_START);
frm.setVisible(true);
}catch(ClassNotFoundException sqle){
System.out.println(sqle);
}catch(SQLException sqle){
System.out.println(sqle);
}
}
}
class ResultSetTable extends AbstractTableModel {
private ResultSetMetaData rsMeta;
private int columnCount;
private final Vector<String> columnNames = new Vector<String>();
private final Vector<Object[]> cache = new Vector<Object[]>();
/** Set new result set */
public void setResultSet(ResultSet rs) throws SQLException {
cache.clear();
columnNames.clear();
rsMeta = rs.getMetaData();
columnCount = rsMeta.getColumnCount();
// get columnNames
for(int col = 0; col < columnCount; col++) {
columnNames.add(rsMeta.getColumnName(col+1));
}
// get data
while(rs.next()) {
Object rowData[] = new Object[columnCount];
for(int col = 0; col < columnCount; col++){
rowData[col] = rs.getObject(col+1);
}
cache.add(rowData);
}
fireTableStructureChanged();
}
public String getColumnName(int column) {
return columnNames.get(column);
}
public Object getValueAt(int rowIndex, int columnIndex) {
Object[] row = cache.get(rowIndex);
return row[columnIndex];
}
public int getRowCount() {
return cache.size();
}
public int getColumnCount() {
return columnCount;
}
public boolean isCellEditable(int row, int col){
return false;
}
}
Thanks Yanimk. I dont think my problem is my program. I mean there is a connection and everything is working fine. its just not displaying it on my GUI.
Thanks for helping me out. i guess i will post it in the swing forum may be i can get more replies.
see i knew nothing was wrong with my program. But too bad i cannot see it for myself just one simple problem and took me 3 hours almost. Sucks :(((
if(selection.equals(QUERY1))should be replaced with
if(selection.equals(names[0])) works perfect now.
How cani display columnNames ? for each query
public class DatabaseProgramming extends JFrame implements ActionListener{
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/employees";
static final String USERNAME = "jhtp6";
static final String PASSWORD = "jhtp6";
private static final String QUERY1 = "SELECT * FROM employees WHERE DEPARTMENTNAME = 'SALES'";
private static final String QUERY2 = "SELECT * FROM hourlyEmployees WHERE hours >= '30'";
private static final String QUERY3 = "SELECT * FROM commissionEmployees ORDER BY commissionRate DESC";
private String names[] = {"QUERY1", "QUERY2", "QUERY3"};
private Connection connection;
private ResultSet resultSet;
private Statement statement;
private ResultSetMetaData metaData;
private JTable resultTable;
private JComboBox queryBox;
private JButton button;
private int number;
private boolean connectedDatabase = false;
private DefaultTableModel dtm;
public static void main(String[] args) {
DatabaseProgramming frame = new DatabaseProgramming();
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public DatabaseProgramming() {
super("Testing Database");
setLayout(new FlowLayout());
//dtm = new DefaultTableModel(getColumnName(number),0);
//statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
// ResultSet.CONCUR_READ_ONLY);
queryBox = new JComboBox(names);
queryBox.setEditable(false);
//queryBox.addActionListener(this);
button = new JButton("EXECUTE");
resultTable = new JTable();
button.addActionListener(this);
add(queryBox);
add(button);
add(resultTable);
//add(result);
setSize(400,400);
}
public void actionPerformed(ActionEvent e) {
String selection = (String)queryBox.getSelectedItem();
if(selection.equals(names[0]))
validateQuery(QUERY1);
if(selection.equals(names[1]))
validateQuery(QUERY2);
if(selection.equals(names[2]))
validateQuery(QUERY3);
}
public void validateQuery(String query) {
try {
Class.forName(JDBC_DRIVER);
connection = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
System.out.println("Yay Database Connected");
Vector col = new Vector();
Vector data = new Vector();
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
metaData = resultSet.getMetaData();
number = metaData.getColumnCount();
for(int i=1; i<=number; i++)
col.addElement(metaData.getColumnName(i));
while(resultSet.next()) {
Vector row = new Vector();
for(int i=1; i<=number; i++)
row.addElement(resultSet.getObject(i));
data.addElement(row);
}
resultTable.setModel(new DefaultTableModel(data,col));
resultTable.revalidate();
}
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
System.exit( 1 );
}
catch(ClassNotFoundException e) {
e.printStackTrace();
}
finally
{
try
{
statement.close();
connection.close();
}
catch ( SQLException sqlException )
{
JOptionPane.showMessageDialog( null,
sqlException.getMessage(), "Database error",
JOptionPane.ERROR_MESSAGE );
System.exit( 1 );
}
}
}
}
Message was edited by:
schumacher
>How cani display columnNames ? for each query
your metadata, metaData.getColumnName(i).
Yannix you helped me alot. let me try that columnName and thanks alot for your program. next time may be i can write with a class extending abstractDatamodel. Dukes awarded bro.
but yannix i have already defined the metadata.getcolumnname. how can i display it in the JTable? the setModel is already taking 2 parameters.
> but yannix i have already defined the
> metadata.getcolumnname. how can i display it in the
> JTable? the setModel is already taking 2 parameters.
you can always create a new ResultSetMetaData.
if you already extracted the column names and set it to the table header use this.
jtable.getColumnModel().getColumn(index).setHeaderValue("column name");
something like this?
for(int i=1; i<=number; i++) {
col.addElement(metaData.getColumnName(i));
resultTable.getColumnModel().getColumn(i).setHeaderValue("column name");
}
But gives me error
i tried this also but gives me run time queuedispatcher exception
resultTable.setModel((TableModel) col);
resultTable.setModel(new DefaultTableModel(data, col));
resultTable.revalidate();
resultSet = statement.executeQuery(query);
metaData = resultSet.getMetaData();
number = metaData.getColumnCount();
for(int i=1; i<=number; i++)
col.addElement(metaData.getColumnName(i));
// don't put it here you are still extracting the column name
while(resultSet.next()) {
Vector row = new Vector();
for(int i=1; i<=number; i++)
row.addElement(resultSet.getObject(i));
data.addElement(row);
}
resultTable.setModel(new DefaultTableModel(data,col));
resultTable.revalidate();
i don't understand why get the column name when the default table model automatically
display the column names in the table header.
i know Yanix but for some reasons my table is displaying the records only. No Column names. like if i extract the first query1. i have columnName like
firstnamelastnameSSn
but for some reason my table is just displaying the records with no column name. I dont know why.? any clues?
you should specify your problem in the first place... :) That column header won't display.
try this:
resultTable = new JTable(); // your declaration
JScrollPane myPane = new JScrollPane();
myPane.setViewportView(resultTable);
that should solve your problem... ^_^ (I guess)
> you should specify your problem in the first place...
> :) That column header won't display.
>
> try this:
> resultTable = new JTable(); // your
> declaration
> JScrollPane myPane = new JScrollPane();
> myPane.setViewportView(resultTable);
>
> that should solve your problem... ^_^ (I guess)
Same thing Yaniks. sorry for being a brat. I am new to JTable and soon i have a project to do "A custom GUI database" build in swing. so i am just trying to make myself comfortable before i go and work on this big project.
public DatabaseProgramming() {
super("Testing Database");
setLayout(new FlowLayout());
queryBox = new JComboBox(names);
queryBox.setEditable(false);
//queryBox.addActionListener(this);
button = new JButton("EXECUTE");
resultTable = new JTable();
JScrollPane myPane = new JScrollPane();
myPane.setViewportView(resultTable);
button.addActionListener(this);
add(queryBox);
add(button);
add(resultTable);
//add(result);
setSize(400,400);
}
Stil the same thing the output in my GUI is like this
222-22-2222SueJones1961-02-03commissionEmployeeSALES
333-33-3333BobLowis1958-10-05basePlusCommissionEmployeeSALES
666-66-6666malcojohn1999-10-23commissionEmployeeSALES
Instead i want my output with my columnHeaderName also which is not displaying
SSN FirstNameLastNameDOBABCDept
222-22-2222SueJones1961-02-03commissionEmployeeSALES
These columnHeading comes directly from my database metaData.getColumnName();
Message was edited by:
schumacher
resultTable = new JTable();
JScrollPane myPane = new JScrollPane();
myPane.setViewportView(resultTable);
button.addActionListener(this);
add(queryBox);
add(button);
add(resultTable); // replace resultTable with myPane
now i am getting
SSN SSNSSNSSNSSNSSN
222-22-2222SueJones1961-02-03commissionEmployeeSALES
Yes i forgot to add(myPane). works fine but now the headings are same for every column.
hahah Never Mind got it Yaniks. Thanks so Much for helping me out. If you're a girl then a big Kiss :(): if you're a guy then nothing for you :). You know alot in Swing and i have just started learning it. lost my 4 hours for just this program. You know about programming alot. Nice man.
Thansk for your great assistance.
Message was edited by:
schumacher
no problem
>Thanks so Much for helping me out.
>If you're a girl then a big Kiss :(): if you're a guy then
>nothing for you :).
to bad because i'm a HE!!! ^_^
but i got a she in our team you can have her she's hot.
> no problem
>
> >Thanks so Much for helping me out.
> >If you're a girl then a big Kiss :(): if you're a
> guy then
> >nothing for you :).
>
> to bad because i'm a HE!!! ^_^
> but i got a she in our team you can have her she's
> hot.
LOL. Iwsh i can have a girl who knows programming in our team also hehe.
