/*
The Design Patterns Java Companion
Copyright (C) 1998, by James W. Cooper
IBM Thomas J. Watson Research Center
*/
import java.awt.BorderLayout;
import java.awt.Button;
import java.awt.Color;
import java.awt.Component;
import java.awt.Dialog;
import java.awt.Frame;
import java.awt.Graphics;
import java.awt.GridLayout;
import java.awt.Label;
import java.awt.List;
import java.awt.Panel;
import java.awt.TextArea;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Vector;
public class DatabaseFrame extends Frame implements ActionListener,
ItemListener {
Database db;
List Tables, Columns, Data;
TextArea query;
Button Search, Quit;
public DatabaseFrame() {
super("Database demonstration");
setGUI();
db = new Database("sun.jdbc.odbc.JdbcOdbcDriver");
db.Open("jdbc:odbc:Grocery prices", null);
String tnames[] = db.getTableNames();
loadList(Tables, tnames);
String queryText = "SELECT * from yourTableName";
query.setText(queryText);
}
//------------------------------------
private void setGUI() {
setBackground(Color.lightGray);
setLayout(new BorderLayout());
Panel pn = new Panel();
add("North", pn);
pn.setLayout(new GridLayout(1, 3));
pn.add(new Label("Tables"));
pn.add(new Label("Columns"));
pn.add(new Label("Data"));
Panel pc = new Panel();
add("Center", pc);
pc.setLayout(new GridLayout(1, 3));
pc.add(Tables = new List(15));
pc.add(Columns = new List(15));
pc.add(Data = new List(15));
Tables.addItemListener(this);
Columns.addItemListener(this);
Panel ps = new Panel();
add("South", ps);
ps.add(query = new TextArea("", 3, 40));
addPanel(ps, Search = new Button("Run Query"));
addPanel(ps, Quit = new Button("Quit"));
Search.addActionListener(this);
Quit.addActionListener(this);
setBounds(100, 100, 500, 300);
setVisible(true);
}
//------------------------------------
private void addPanel(Panel ps, Component c) {
Panel p = new Panel();
ps.add(p);
p.add(c);
}
//------------------------------------
private void loadList(List list, String[] s) {
list.removeAll();
for (int i = 0; i < s.length; i++)
list.add(s[i]);
}
//------------------------------------
public void actionPerformed(ActionEvent e) {
Object obj = e.getSource();
if (obj == Quit)
System.exit(0);
if (obj == Search)
clickedSearch();
}
//------------------------------------
public void itemStateChanged(ItemEvent e) {
Object obj = e.getSource();
if (obj == Tables)
showColumns();
if (obj == Columns)
showData();
}
//------------------------------------
private void showColumns() {
String cnames[] = db.getColumnNames(Tables.getSelectedItem());
loadList(Columns, cnames);
}
//------------------------------------
private void showData() {
String colname = Columns.getSelectedItem();
String colval = db.getColumnValue(Tables.getSelectedItem(), colname);
Data.setVisible(false);
Data.removeAll();
Data.setVisible(true);
colval = db.getNextValue(Columns.getSelectedItem());
while (colval.length() > 0) {
Data.add(colval);
colval = db.getNextValue(Columns.getSelectedItem());
}
}
//------------------------------------
private void clickedSearch() {
resultSet rs = db.Execute(query.getText());
String cnames[] = rs.getMetaData();
Columns.removeAll();
queryDialog q = new queryDialog(this, rs);
q.show();
}
//------------------------------------
static public void main(String argv[]) {
new DatabaseFrame();
}
}
class queryDialog extends Dialog implements ActionListener {
resultSet results;
Button OK;
textPanel pc;
Vector tables;
public queryDialog(Frame f, resultSet r) {
super(f, "Query Result");
results = r;
setLayout(new BorderLayout());
OK = new Button("OK");
Panel p = new Panel();
add("South", p);
p.add(OK);
OK.addActionListener(this);
pc = new textPanel();
pc.setBackground(Color.white);
add("Center", pc);
makeTables();
setBounds(100, 100, 500, 300);
setVisible(true);
repaint();
}
//-------------------------------------
private void makeTables() {
tables = new Vector();
String t[] = results.getMetaData();
tables.addElement(t);
while (results.hasMoreElements()) {
tables.addElement(results.nextElement());
}
}
//-------------------------------------
public void actionPerformed(ActionEvent e) {
setVisible(false);
}
//-------------------------------------
class textPanel extends Panel {
public void paint(Graphics g) {
String s[];
int x = 0;
int y = g.getFontMetrics().getHeight();
int deltaX = (int) 1.5f
* (g.getFontMetrics().stringWidth("wwwwwwwwwwwwww"));
for (int i = 0; i < tables.size(); i++) {
s = (String[]) tables.elementAt(i);
for (int j = 0; j < s.length; j++) {
String st = s[j];
g.drawString(st, x, y);
x += deltaX;
}
x = 0;
y += g.getFontMetrics().getHeight();
if (i == 0)
y += g.getFontMetrics().getHeight();
}
}
}
}
class Database {
Connection con;
resultSet results;
ResultSetMetaData rsmd;
DatabaseMetaData dma;
String catalog;
String types[];
String database_url;
public Database(String driver) {
types = new String[1];
types[0] = "TABLES"; //initialize type array
try {
Class.forName(driver);
} //load the Bridge driver
catch (Exception e) {
System.out.println(e.getMessage());
}
}
//-----------------------------------
public void close() {
try {
con.close();
} catch (Exception e) {
System.out.println("close error");
}
}
//-----------------------------------
public void Open(String url, String cat) {
catalog = cat;
database_url = url;
try {
con = DriverManager.getConnection(url);
dma = con.getMetaData(); //get the meta data
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
//-----------------------------------
public void reOpen() {
try {
con = DriverManager.getConnection(database_url);
dma = con.getMetaData(); //get the meta data
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
//-----------------------------------
public String[] getTableNames() {
String[] tbnames = null;
Vector tname = new Vector();
//add the table names to a Vector
//since we don't know how many there are
try {
results = new resultSet(dma.getTables(catalog, null, "%", types));
} catch (Exception e) {
System.out.println(e);
}
while (results.hasMoreElements())
tname.addElement(results.getColumnValue("TABLE_NAME"));
//copy the table names into a String array
tbnames = new String[tname.size()];
for (int i = 0; i < tname.size(); i++)
tbnames[i] = (String) tname.elementAt(i);
return tbnames;
}
//-----------------------------------
public String[] getTableMetaData() {
// return the table type information
results = null;
try {
results = new resultSet(dma.getTables(catalog, null, "%", types));
} catch (Exception e) {
System.out.println(e.getMessage());
}
return results.getMetaData();
}
//-----------------------------------
public String[] getColumnMetaData(String tablename) {
//return the data on a column
results = null;
try {
results = new resultSet(dma.getColumns(catalog, null, tablename,
null));
} catch (Exception e) {
System.out.println(e.getMessage());
}
return results.getMetaData();
}
//-----------------------------------
public String[] getColumnNames(String table) {
//return an array of Column names
String[] tbnames = null;
Vector tname = new Vector();
try {
results = new resultSet(dma.getColumns(catalog, null, table, null));
while (results.hasMoreElements())
tname.addElement(results.getColumnValue("COLUMN_NAME"));
} catch (Exception e) {
System.out.println(e);
}
tbnames = new String[tname.size()];
for (int i = 0; i < tname.size(); i++)
tbnames[i] = (String) tname.elementAt(i);
return tbnames;
}
//-----------------------------------
public String getColumnValue(String table, String columnName) {
//return the value of a given column
String res = null;
try {
if (table.length() > 0)
results = Execute("Select " + columnName + " from " + table
+ " order by " + columnName);
if (results.hasMoreElements())
res = results.getColumnValue(columnName);
} catch (Exception e) {
System.out.println("Column value error" + columnName
+ e.getMessage());
}
return res;
}
//-----------------------------------
public String getNextValue(String columnName) {
// return the next value in that column
//using the remembered resultSet
String res = "";
try {
if (results.hasMoreElements())
res = results.getColumnValue(columnName);
} catch (Exception e) {
System.out
.println("next value error" + columnName + e.getMessage());
}
return res;
}
//-----------------------------------
public resultSet Execute(String sql) {
//execute an SQL query on this database
results = null;
try {
Statement stmt = con.createStatement();
results = new resultSet(stmt.executeQuery(sql));
} catch (Exception e) {
System.out.println("execute error: " + e.getMessage());
}
return results;
}
}
class resultSet {
//this class is a higher level abstraction
//of the JDBC ResultSet object
ResultSet rs;
ResultSetMetaData rsmd;
int numCols;
public resultSet(ResultSet rset) {
rs = rset;
try {
//get the meta data and column count at once
rsmd = rs.getMetaData();
numCols = rsmd.getColumnCount();
} catch (Exception e) {
System.out.println("resultset error" + e.getMessage());
}
}
//-----------------------------------
public String[] getMetaData() {
//returns an array of all the column names
//or other meta data
String md[] = new String[numCols];
try {
for (int i = 1; i <= numCols; i++)
md[i - 1] = rsmd.getColumnName(i);
} catch (Exception e) {
System.out.println("meta data error" + e.getMessage());
}
return md;
}
//-----------------------------------
public boolean hasMoreElements() {
try {
return rs.next();
} catch (Exception e) {
System.out.println("next error " + e.getMessage());
return false;
}
}
//-----------------------------------
public String[] nextElement() {
//copies contents of row into string array
String[] row = new String[numCols];
try {
for (int i = 1; i <= numCols; i++)
row[i - 1] = rs.getString(i);
} catch (Exception e) {
System.out.println("next element error" + e.getMessage());
}
return row;
}
//-------------------------------------
public String getColumnValue(String columnName) {
String res = "";
try {
res = rs.getString(columnName);
} catch (Exception e) {
System.out.println("Column value error:" + columnName
+ e.getMessage());
}
return res;
}
//-------------------------------------
public String getColumnValue(int i) {
String res = "";
try {
res = rs.getString(i);
} catch (Exception e) {
System.out.println("Column value error: " + i + " "
+ e.getMessage());
}
return res;
}
//----------------------------------------------
public void finalize() {
try {
rs.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}