Y08.java

import java.io.*;
import javax.swing.*;
import javax.swing.table.*;
import javax.swing.event.*;
import java.util.*; // for Enumeration
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

/**
Demonstration of a custom table model in a SQL query tool.

Reads database connection information from a property file named 
"Y08.properties".  Any database drivers used must be on 
the classpath!
*/
public class Y08
{
    public static void main(String argv[])
    {
        JFrame f = new QFrame();
        f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        f.show();
    }
}

class QFrame extends JFrame
{
    public QFrame()
    {
        Container cp = getContentPane();

        setTitle("CISC370-011 -- JTable demo with SQL query");
        setSize(500,300);
        setLocation(100,100);

        String query = 
            "SELECT EmpID,EmpName\n" +
            "FROM Employee\n" +
            "WHERE active<>0\n" +
            "ORDER BY nLast,nFirst\n";

        JPanel p1 = new JPanel();
        qfield = new JTextArea(8,32);
        qfield.setText(query);
        JScrollPane sq = new JScrollPane(qfield);
        p1.add(sq);
        JButton xbutton = new JButton("Execute");
        p1.add(xbutton);
        cp.add(p1,BorderLayout.NORTH);

        table = new JTable();
        JScrollPane sp = new JScrollPane(table);
        cp.add(sp,BorderLayout.CENTER);
        pack();

        model = new QueryModel(query);
        table.setModel(model);
        xbutton.addActionListener(
            new ActionListener()
            {
                public void actionPerformed(ActionEvent e)
                {
                    if (model != null)
                        model.close();
                    String query = qfield.getText();
                    model = new QueryModel(query);
                    table.setModel(model);
                }
            });

        ListSelectionModel sm = table.getSelectionModel();
        sm.addListSelectionListener(
            new ListSelectionListener()
            {
                public void valueChanged(ListSelectionEvent e)
                {
                    if (!e.getValueIsAdjusting()) {
                        System.out.println("Sel from " +
                            e.getFirstIndex() + " to " +
                            e.getLastIndex());
                        }
                }
            });
    }

    private JTextArea qfield;
    private QueryModel model;
    private JTable table;
}

/**
Demonstration of an AbstractTableModel based on a SQL query.
*/
class QueryModel extends AbstractTableModel
{
    public QueryModel(String query)
    {
        try {
            Properties props = new Properties();
            FileInputStream in = 
                new FileInputStream("db.properties");
            props.load(in);
            in.close();

            String driver = props.getProperty("jdbc.driver");
            String url = props.getProperty("jdbc.url");
            String uid = props.getProperty("jdbc.username");
            String pwd = props.getProperty("jdbc.password");

            Class.forName(driver);
            conn = DriverManager.getConnection(url,uid,pwd);

            stat = conn.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
            boolean hasResultSet = stat.execute(query);
            if (hasResultSet) {
                rs = stat.getResultSet();
                md = rs.getMetaData();
                }
            else {
                close();
                }
            }
        catch (SQLException e) {
            System.err.println("SQL problem: " + e);
            stat = null;
            }
        catch (IOException e) {
            System.err.println("IO problem: " + e);
            stat = null;
            }
        catch (ClassNotFoundException e) {
            System.err.println("DB driver loading problem: " + e);
            stat = null;
            }
    }

    public int getRowCount()
    {
        if (stat == null)
            return 0;
        try {
            rs.last();
            return rs.getRow();
            }
        catch (SQLException e) {
            return 0;
            }
    }

    public int getColumnCount()
    {
        if (stat == null)
            return 0;
        try {
            return md.getColumnCount();
            }
        catch (SQLException e) { 
            return 0;
            }
    }

    public String getColumnName(int colnum)
    {
        try {
            return md.getColumnName(colnum+1);
            }
        catch (SQLException e) {
            return super.getColumnName(colnum);
            }
    }

    public Object getValueAt(int row,int col)
    {
        if (stat == null)
            return null;
        try {
            rs.absolute(row+1);
            return rs.getObject(col+1);
            }
        catch (SQLException e) {
            return null;
            }
    }

    public void close()
    {
        if (stat == null)
            return;
        try {
            stat.close();
            conn.close();
            }
        catch (SQLException e) {
            }
        finally {
            stat = null;
            }
    }

    Connection conn;
    Statement stat;
    ResultSet rs;
    ResultSetMetaData md;
}