public DatabaseConnect()
public DatabaseConnect(String urlin,String userin,String pwdin)
public static List getResults(Vector outgoing)
public static int getRowCount(String sqlQuery)
public static Vector getContents(String query)
public void openConn()
public void exeSql(String sql)
public void closeConn()
public void createStmt()
public void closeStmt()
public void createResultSet(String query)
public Vector getDisplayableResults(String query)
public Vector getResults(String query)
public Vector getNamedAttributes(String query)
public Hashtable getPrimaryKeys(String query)
public Hashtable getColumnNames(String query)
public Hashtable getColumnTypes(String query)
public Hashtable getColumnSizes(String query)
public void displayResults(Vector incoming)
public void displayResults()
public void closeAll()
// DatabaseConnect.java
// encapsulating connection details in a class
// appears to work
// Java packages used.
import java.sql.*;
import java.util.*;
import java.awt.*;
import java.lang.*;
// Class declaration
public class DatabaseConnect
{
// Instance variables.
protected String url;
protected String user;
protected String password;
public Connection conn;
public Statement stmt;
public ResultSet rst;
public ResultSetMetaData
rmeta;
protected int numcol;
//******************************************************************
// Default class Constructor
// Access7 refers to a System
Data Source Name which I have set up on my
// computer. This is the 32 bit ODBC driver which enables
communications
// with an existing Access95
database.
public DatabaseConnect()
{
//System.out.println("Default Constructor called");
this.url="jdbc:odbc:Access7";
this.user="admin";
this.password="
";
this.conn=null;
stmt=null;
rst=null;
}
//******************************************************************
// Class Constructor
// User can specify an
existing Data Source to which a connection can
// be opened.
public DatabaseConnect(String
urlin,String userin,String pwdin){
//System.out.println("Constructor with parameters called");
url ="jdbc:odbc:"+urlin;
user=userin;
password=pwdin;
this.conn=null;
rst=null;
}
// Class/static Method
//******************************************************************
// Test +++++ to return a
list to be added to the Gui
// Static method, accepts a
Vector and returns a List
// For display purposes with
a Gui
public static List
getResults(Vector outgoing){
int rows=outgoing.size();
List theList=new
List(rows,false);
String
tempString="";
for(int i=0;i<rows;i++){
Hashtable
temp=(Hashtable)outgoing.elementAt(i);
int j=temp.size();
for(int
k=1;k<=j;k++){
if(k==j){
Integer t=new
Integer(k);
tempString+=temp.get(t.toString());
}
else if(k==1){
Integer t=new
Integer(k);
tempString+=temp.get(t.toString())+" "+"\t"+"\t";
}
else{
Integer t=new
Integer(k);
tempString+=temp.get(t.toString())+"\t";
}
theList.add(tempString);
}
}
return theList;
}
//******************************************************************
// Returns the number of rows
in a database table
public static int
getRowCount(String sqlQuery){
Vector
table=getContents(sqlQuery);
int theSize =
table.size();
return theSize;
}
//******************************************************************
// Contents of a ResultSet
placed in a Vector.
// Differs from getResults()
as it is a static or class method and must
// be called via the class
name i.e. DatabaseConnect.getResults();
public static Vector
getContents(String query){
Vector rows =new Vector();
try{
//
System.out.println("getResults -query="+query);
DatabaseConnect db =
new DatabaseConnect();
db.openConn();
db.createStmt();
db.createResultSet(query);
db.rst=db.stmt.executeQuery(query);
db.rmeta=db.rst.getMetaData();
db.numcol=db.rmeta.getColumnCount(); //set numcol to number of columns
in
//Resultset mlocal.
Hashtable columns =
new Hashtable();
while(db.rst.next()){
Hashtable
anotherColumn =new Hashtable();
for(int
j=1;j<=db.numcol;j++){
Integer t=new
Integer(j);
System.out.print(db.rst.getString(j));
anotherColumn.put(t.toString(),db.rst.getString(j));
}
rows.addElement(anotherColumn);
}
db.closeAll();
}
catch(Exception e){
System.out.println(e.getMessage());
}
return rows;
}
// Instance Methods
//******************************************************************
// Opens a connection with a
database as specified in the url variable.
// Uses the JDBC/ODBC bridge
driver.
public void openConn(){
//System.out.println("Checking if connection is open");
try{
if(conn != null
&& !conn.isClosed()){
//System.err.println("The Specified connection is already
open");
}
else{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection(url,"user","password");
//System.out.println("Connection has been opened");
}
}
catch(Exception e){
System.err.println(e.getMessage());
}
}
//******************************************************************
// Executes a SQL
statement. Connection and Statement
objects are
// required.
public void exeSql(String
sql){
try{
if(conn!= null
&&!conn.isClosed())
{
//System.out.println("executeUpdate :"+sql);
stmt.executeUpdate(sql);
}
else{
this.openConn();
stmt.executeUpdate(sql);
}
}
catch(SQLException e){
System.err.println(e.getMessage());
}
}
//******************************************************************
// Closes the connection with
the database if it is open.
// Applies to the instance
variable "conn". It also closes the Statement
// object "stmt"
public void closeConn(){
try
{
if(conn!= null
&&!conn.isClosed()){ //if conn
is open
//System.out.println("Closing Connection");
conn.close();
conn = null;
//System.out.println("Connection Closed");
}
else
System.out.println("Connection is already Closed");
}
catch(SQLException e){
System.err.println(e.getMessage());
}
}
//******************************************************************
// Creates a Statemetn object
"stmt", which belongs to an instance of
// the class.
public void createStmt(){
try
{
if(stmt == null)
{
stmt=conn.createStatement();
//System.out.println("Statement Created");
}
else{
//System.out.println("The specified Statement already
exists");
}
}
catch(SQLException e){
System.err.println(e.getMessage());
}
}
//******************************************************************
// Closes the stmt Statement
public void closeStmt(){
try{
if(stmt != null){
stmt.close();
stmt=null;
//System.out.println("Statement closed");
}
else
System.out.println("Statement is already closed");
}
catch(SQLException e){
System.err.println(e.getMessage());
}
}
//******************************************************************
// Creates a ResultSet
object, which belongs to an instance of the class
// DatabaseConnect. Uses a ResultSetMetaData object to
dynamically
// discover the number of
columns in the ResultSet object.
public void
createResultSet(String query){
try
{
//if stmt is not a
statement then make it a statement else
if(stmt != null){
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount();
//System.out.println("Ready to start work with myresultset");
}
else
{
this.createStmt();
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount();
}
}
catch(SQLException e){
System.err.println(e.getMessage());
}
}
//******************************************************************
// Creates ResultSet object rst, initialises it using a
SQL statement
// and the executeQuery
method. It then takes the values from
each row
// in rst and places them in
a Hashtable, columns. Each Hashtable is
then
// placed in the vector
object rows, which is returned. When
the values are
// placed in a hashtable they
are identified by a String. Thus when
accessing
// the data held in each
Hashtable, it will be done via colunm number which.
// is first converted to a
string. The first Hashtable in the
vector stores
// the names of the tables.
public Vector
getDisplayableResults(String query){
Vector rows =new Vector();
try{
// test
this.openConn();
this.createStmt();
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount(); //set numcol to number of columns in
//Resultset mlocal.
Hashtable columns =
new Hashtable();
for(int j=1;j<=numcol;j++){
Integer t=new
Integer(j);
columns.put(t.toString(),rmeta.getColumnName(j));
}
rows.addElement(columns);
while(rst.next()){
Hashtable
anotherColumn =new Hashtable();
for(int
j=1;j<=numcol;j++){
Integer t=new
Integer(j);
anotherColumn.put(t.toString(),rst.getString(j));
}
rows.addElement(anotherColumn);
}
}
catch(Exception e){
System.out.println(e.getMessage());
}
return rows;
}
//******************************************************************
// Contents of a ResultSet
placed in a Vector.
// Differs from
getDisplayableResults() as it does not contain the names
// of each column.
public Vector
getResults(String query){
Vector rows =new Vector();
try{
//
System.out.println("getResults -query="+query);
this.openConn();
this.createStmt();
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount(); //set numcol to number of columns in
//Resultset mlocal.
Hashtable columns =
new Hashtable();
while(rst.next()){
Hashtable
anotherColumn =new Hashtable();
for(int
j=1;j<=numcol;j++){
Integer t=new
Integer(j);
// System.out.print(rst.getString(j));!!!!!
// This can
not be done as the resultset can only access
// a
particular value once ???
anotherColumn.put(t.toString(),rst.getString(j));
}
rows.addElement(anotherColumn);
}
}
catch(Exception e){
System.out.println(e.getMessage());
}
return rows;
}
//
****************************************************************
// Creates a ResultSet and
places the contents in a Vector.
// Each row in the ResultSet
table is placed in a Hashtable using the column name
// as the identifier of each
field.
// Used by Framework class to
initialise an existing object.
public Vector
getNamedAttributes(String query){
Vector rows =new Vector();
try{
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount(); //set numcol to number of columns in
//Resultset mlocal.
while(rst.next()){
Hashtable columns =
new Hashtable();
for(int
j=1;j<=numcol;j++){
columns.put(rmeta.getColumnName(j),rst.getString(j));
}
rows.addElement(columns);
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return rows;
}
//**************************************************************
// Accepts a query specifying
the name of a column to be returned as a
// Hashtable. Used to get all the names of Lotto objects
for TheDemo
// application.
public Hashtable
getPrimaryKeys(String query){
this.openConn();
this.createStmt();
this.createResultSet(query);
int i=1;
Hashtable theValues=new
Hashtable();
try{
while(rst.next()){
String
name=rst.getString(1);
Integer no = new
Integer(i);
theValues.put(no.toString(),name);
i++;
}
}
catch(Exception e){
System.err.println(e.getMessage());
}
return theValues;
}
//******************************************************************
// Accepts SQL query, creates
a ResultSet, ResultsetMetaData and Hashtable
// objects. Puts the column names in the Hashtable,
identified by column
// number. Finally returns the Hashtable containing the
column names.
public Hashtable
getColumnNames(String query){
Hashtable columns = new
Hashtable();
try{
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount();
for(int
j=1;j<=numcol;j++){
Integer t=new
Integer(j);
columns.put(t.toString(),rmeta.getColumnName(j));
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return columns;
}
//******************************************************************
// Gets the types of each
column in a database table and places them in a
// Hashtable. The contents of the Hashtable are identified
by column
// number
public Hashtable
getColumnTypes(String query){
Hashtable columns = new
Hashtable();
try{
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount(); //set numcol to number of columns in
//Resultset mlocal.
for(int
j=1;j<=numcol;j++){
Integer t=new
Integer(j);
columns.put(t.toString(),rmeta.getColumnTypeName(j));
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return columns;
}
//******************************************************************
//Gets the maximum size of
each column in a database table and places them in a
// Hashtable. The contents of the Hashtable are identified
by column
public Hashtable getColumnSizes(String
query){
Hashtable columns = new
Hashtable();
try{
rst=stmt.executeQuery(query);
rmeta=rst.getMetaData();
numcol=rmeta.getColumnCount(); //set numcol to number of columns in
//Resultset mlocal.
for(int
j=1;j<=numcol;j++){
Integer t=new
Integer(j);
int
theInt=rmeta.getPrecision(j);
Integer u=new
Integer(theInt);
columns.put(t.toString(),u.toString());
}
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return columns;
}
//******************************************************************
// Accepts a Vector object as
input. Prints the contents of the
database
// contained within the
Vector to the Standard output stream in tabular
// format.
public void
displayResults(Vector incoming){
for(int
i=0;i<incoming.size();i++){
Hashtable
temp=(Hashtable)incoming.elementAt(i);
int j=temp.size();
for(int
k=1;k<=j;k++){
if(k==j){
Integer t=new
Integer(k);
System.out.println(temp.get(t.toString()));
}
else if(k==1){
Integer t=new
Integer(k);
System.out.print(temp.get(t.toString())+" "+"\t"+"\t");
}
else{
Integer t=new
Integer(k);
System.out.print(temp.get(t.toString())+"\t");
}
}
}
}
//******************************************************************
// Displays the results
contained within the ResultSet rst
// Uses ResultSetMetaData
rmeta to identify the column names
// Displays the results to
the standard output stream.
public void displayResults(){
try{
for(int
i=1;i<=numcol;i++){
if(i<numcol){
String row
=rmeta.getColumnName(i)+" | ";
System.out.print(row);
}
else{
String row
=rmeta.getColumnName(i);
System.out.println(row);
}
}
while(rst.next()){
for(int
i=1;i<=numcol;i++){
if(i<numcol){
String
stringpart=rst.getString(i)+" | ";
System.out.print(stringpart);
}
else{
String stringpart
=rst.getString(i).trim();
System.out.println(stringpart);
}
}
}
}
catch(SQLException e){
System.err.println(e.getMessage());
}
}
//******************************************************************
// Closes the Connection, ResultSet,
Statement and ResultSetMetaData
// associated with an
instance of the class DatabaseConnect, after first
// checking to see if they
are active and open.
public void closeAll(){
try{
if(stmt != null)
this.closeStmt();
if(rst != null) rst.close();
if(conn!= null
&&!conn.isClosed())this.closeConn();
}
catch(SQLException e){
System.err.println(e.getMessage());
}
}
}
//********************************************************************