[MitulP91]

Hello all,

I have been given an assignment to create a database with eight different tables and then create Java code that allows me to access the tables and the data within them in various ways in a JFrame. I had no issue creating the database and accessing it, however I am having issues getting the data to appear.

The way I have been asked to do this is to have a JComboBox that contains all of the Table Names, and then the names of the columns within that table should appear within a JList. I don't know how to get access to the names of the columns once I have the table selected in the JComboBox.

Here is a portion of my code:

        ResultSet rsTables = dbMetaData.getTables(null, null, null,
                new String[] {"TABLE"});

        // Store table names into a string array
        int i = 0;

        String[] tblNames = new String[8];

        while (rsTables.next()) {
            tblNames[i] = rsTables.getString("TABLE_NAME");
            i += 1;
        }

        jcbTblNames = new JComboBox(tblNames);

        jcbTblNames.addItemListener(new ItemListener() {
            public void itemStateChanged(ItemEvent e) {
                String selectedTbl = (String) (jcbTblNames.getSelectedItem());
            }
        });

This is the part of my code where I call upon the database to get the names of the tables and then have them put into a JComboBox. After that I added the ItemListener that changes based on which Table the user selects.

I have no idea how to take this selected Table and then create a JList of the columns. I'm assuming it has something to do with the following:

        ResultSet resultSet = stmt.executeQuery("select * from " + selectedTbl);

        String[] colNames = new String[10];

        ResultSetMetaData rsMetaData = resultSet.getMetaData();

        for (int k = 0; k < rsMetaData.getColumnCount(); k++) {
            colNames[k] = rsMetaData.getColumnName(k);
        }

Can anyone give me a push in the right direction? If I was unclear on anything or if you require my full code, please ask.

[g00se]

That's more or less it. I would use defaultListModel.addElement(columnName) to build your ListModel

[MitulP91]

The whole point is for the program to be able to get the column names based on the name of the table without the need for manually entering the table values.

I've been messing around with this for a while now and can't seem to find a way to do it.

Any ideas?

MitulP91, on 17 December 2012 - 07:54 AM, said:

The whole point is for the program to be able to get the column names based on the name of the table without the need for manually entering the table values.

I've been messing around with this for a while now and can't seem to find a way to do it.

Any ideas?

Oh and in case I wasn't clear, the MetaData bit doesn't seem to be working. Not sure if I've done something wrong.

[baavgai]

While counter intuitive, Java starts counting columns at 1. So getColumnName(0) will chuck an error at you. You should have gotten an exception, no?

Another problem is all those arrays with arbitrary lengths. Use a List. e.g.

List<String> getTableMetaData(ResultSetMetaData meta) throws SQLException {
	List<String> list = new ArrayList<String>();
	for(int i=0; i<meta.getColumnCount(); i++) {
		list.add(meta.getColumnName(i+1));
	}
	return list;
}

[MitulP91]

baavgai, on 17 December 2012 - 08:43 AM, said:

While counter intuitive, Java starts counting columns at 1. So getColumnName(0) will chuck an error at you. You should have gotten an exception, no?

Another problem is all those arrays with arbitrary lengths. Use a List. e.g.

List<String> getTableMetaData(ResultSetMetaData meta) throws SQLException {
	List<String> list = new ArrayList<String>();
	for(int i=0; i<meta.getColumnCount(); i++) {
		list.add(meta.getColumnName(i+1));
	}
	return list;
}

I'm sorry, I'm fairly new to JDBC so it might take me a while to catch on.

How would I implement that code utilizing the data taken from the JComboBox to pick a specific table?

[g00se]

Quote

The whole point is for the program to be able to get the column names based on the name of the table without the need for manually entering the table values.

I'm aware of that

[MitulP91]

g00se, on 17 December 2012 - 09:09 AM, said:

Quote

The whole point is for the program to be able to get the column names based on the name of the table without the need for manually entering the table values.

I'm aware of that />

Then I'm unsure of how I would implement that using what I have without manually entering each column.

Here is my full current code:

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

public class TableViewer2 extends JFrame {
    // Create the necessary GUI components
    private JLabel jlblTblNames = new JLabel("Table Names");
    private JLabel jlblColNames = new JLabel("Column Names");
    private JLabel jlblSelCol = new JLabel("Selected Columns");
    private JComboBox jcbTblNames;
    private JList jlstColNames;
    private JCheckBox jchSortDesc = new JCheckBox("DESC");
    private JButton jbtShowData = new JButton("Show Data");
    private JButton jbtSelDB = new JButton("Select Another Database");

    // Statement for executing queries
    private Statement stmt;

    // Initializes the JFrame and throws necessary exceptions
    public TableViewer2() throws SQLException, ClassNotFoundException {
        // Load the driver
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        System.out.println("Driver Loaded");

        // Establish connection
        Connection connection = DriverManager.getConnection
            ("jdbc:derby://localhost:1527/LiangBookDB", "user", "password");

        // Access Database MetaData
        DatabaseMetaData dbMetaData = connection.getMetaData();
        System.out.println("Database Connected");

        // Create the query statement
        stmt = connection.createStatement();

        // Obtain the names of each table
        ResultSet rsTables = dbMetaData.getTables(null, null, null,
                new String[] {"TABLE"});

        // Store table names into a string array
        int i = 0;
        String[] tblNames = new String[8];
        while (rsTables.next()) {
            tblNames[i] = rsTables.getString("TABLE_NAME");
            i += 1;
        }

        jcbTblNames = new JComboBox(tblNames);

        JPanel panel = new JPanel();
        panel.add(jlblTblNames);
        panel.add(jcbTblNames);
        panel.add(jlstColNames);
        panel.add(jchSortDesc);
        panel.add(jbtShowData);
        panel.add(jbtSelDB);

        this.add(panel, BorderLayout.NORTH);

        jcbTblNames.addItemListener(new ItemListener() {
            public void itemStateChanged(ItemEvent e) {
                try {
                    String selectedTbl = (String) (jcbTblNames.getSelectedItem());
                    fetchColumns(selectedTbl);
                } catch (SQLException ex) {
                }
            }
        });
    }

    public static void main(String args[])
            throws SQLException, ClassNotFoundException {
        TableViewer2 frame = new TableViewer2();
        frame.setTitle("Database Table Viewer2");
        frame.setSize(500,500);
        frame.setLocationRelativeTo(null);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setVisible(true);
    }

    private void fetchColumns(String tableName) throws SQLException {
        ResultSet resultSet = stmt.executeQuery("select * from "+tableName);
        ResultSetMetaData rsMetaData = resultSet.getMetaData();
        String[] colNames = new String[rsMetaData.getColumnCount()];
        for (int k = 0; k < rsMetaData.getColumnCount(); k++) {
            colNames[k] = rsMetaData.getColumnName(k+1);
        }
        jlstColNames = new JList();
        jlstColNames.setListData(colNames);
    }
}

I thought this would work, but I keep getting the following error.

Exception in thread "main" java.lang.NullPointerException
at java.awt.Container.addImpl(Container.java:1031)
at java.awt.Container.add(Container.java:352)
at TableViewer2.<init>(TableViewer2.java:54)
at TableViewer2.main(TableViewer2.java:75)

where Line 54:

panel.add(jlstColNames);

and Line 75:

TableViewer2 frame = new TableViewer2();

I'm not sure why it's saying either of them are null. Any ideas?

[g00se]

Try creating your JList first. Your fetch method should simply set the ListModel on the JList

Quote

I'm not sure why it's saying either of them are null. Any ideas?

Because your JList is added before it's created:

Quote

panel.add(jlstColNames);

[MitulP91]

g00se, on 17 December 2012 - 09:28 AM, said:

Try creating your JList first. Your fetch method should simply set the ListModel on the JList

Quote

I'm not sure why it's saying either of them are null. Any ideas?

Because your JList is added before it's created:

Quote

panel.add(jlstColNames);

When I do this, the error stops, but for whatever reason the JList itself does not appear in the frame.

[pbl]

Simply Select * from the table from the comboBox
Pass the ResultSet to that folk

http://www.dreaminco...snippet6437.htm

that will generate the TableModel to use with your JTable

[g00se]

Quote

When I do this, the error stops, but for whatever reason the JList itself does not appear in the frame.

When you do what? Please post your new code

[MitulP91]

pbl, on 17 December 2012 - 09:50 AM, said:

Simply Select * from the table from the comboBox
Pass the ResultSet to that folk

http://www.dreaminco...snippet6437.htm

that will generate the TableModel to use with your JTable

Unless I'm missing something, I'm fairly sure I did almost precisely that. The only difference is I'm creating a GUI which seems to be causing issues. I'm not sure why in my code (posted above) colNames does not seem to be holding any values. Also for whatever reason my JList is not appearing on the frame.

g00se, on 17 December 2012 - 09:53 AM, said:

Quote

When I do this, the error stops, but for whatever reason the JList itself does not appear in the frame.

When you do what? Please post your new code

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;

public class TableViewer2 extends JFrame {
    // Create the necessary GUI components
    private JLabel jlblTblNames = new JLabel("Table Names");
    private JLabel jlblColNames = new JLabel("Column Names");
    private JLabel jlblSelCol = new JLabel("Selected Columns");
    private JComboBox jcbTblNames;
    private JList jlstColNames = new JList();
    private JCheckBox jchSortDesc = new JCheckBox("DESC");
    private JButton jbtShowData = new JButton("Show Data");
    private JButton jbtSelDB = new JButton("Select Another Database");

    // Statement for executing queries
    private Statement stmt;

    // Initializes the JFrame and throws necessary exceptions
    public TableViewer2() throws SQLException, ClassNotFoundException {
        // Load the driver
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        System.out.println("Driver Loaded");

        // Establish connection
        Connection connection = DriverManager.getConnection
            ("jdbc:derby://localhost:1527/LiangBookDB", "user", "password");

        // Access Database MetaData
        DatabaseMetaData dbMetaData = connection.getMetaData();
        System.out.println("Database Connected");

        // Create the query statement
        stmt = connection.createStatement();

        // Obtain the names of each table
        ResultSet rsTables = dbMetaData.getTables(null, null, null,
                new String[] {"TABLE"});

        // Store table names into a string array
        int i = 0;
        String[] tblNames = new String[8];
        while (rsTables.next()) {
            tblNames[i] = rsTables.getString("TABLE_NAME");
            i += 1;
        }

        jcbTblNames = new JComboBox(tblNames);

        JPanel panel = new JPanel();
        panel.add(jlblTblNames);
        panel.add(jcbTblNames);
        panel.add(jlstColNames);
        panel.add(jchSortDesc);
        panel.add(jbtShowData);
        panel.add(jbtSelDB);

        this.add(panel, BorderLayout.NORTH);

        jcbTblNames.addItemListener(new ItemListener() {
            public void itemStateChanged(ItemEvent e) {
                    String selectedTbl = (String) (jcbTblNames.getSelectedItem());
                try {
                    fetchColumns(selectedTbl);
                } catch (SQLException ex) {
                }
            }
        });
    }

    public static void main(String args[])
            throws SQLException, ClassNotFoundException {
        TableViewer2 frame = new TableViewer2();
        frame.setTitle("Database Table Viewer2");
        frame.setSize(1000,700);
        frame.setLocationRelativeTo(null);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setVisible(true);
    }

    private void fetchColumns(String tableName) throws SQLException {
        ResultSet resultSet = stmt.executeQuery("select * from "+tableName);
        ResultSetMetaData rsMetaData = resultSet.getMetaData();
        int count = rsMetaData.getColumnCount();
        System.out.println(count);
        String[] colNames = new String[count];
        for (int k = 0; k < count; k++) {
            colNames[k] = rsMetaData.getColumnName(k+1);
        }
        jlstColNames.setListData(colNames);
    }
}

[g00se]

See ABOVE

[MitulP91]

g00se, on 17 December 2012 - 09:58 AM, said:

See ABOVE

If you're asking what I changed, then I created the new JList before calling it to the frame, and then used the setListData method to transfer a String array to the list.

[g00se]

You're saying that you don't see ANY JList - whether filled or empty?