| SqlBean.java |
package jdbc;
import java.sql.*;
import java.util.Vector;
/**
* This class provides an implementation some of the methods in the java.sql package. *
* This init method creates the
* connection to the database and retrieves additional database information. The information is retrieved and * contained in the class variables for further accessibility. *
* @author Douglas Lyon
* @version 1.00
*/
public class SqlBean {
private String url = null;
private String driver = null;
private String userId = null;
private String password = null;
private Connection c = null;
private Statement statement = null;
private DatabaseMetaData dbmd = null;
private ResultSet rs = null;
private ResultSetMetaData rsmd = null;
private ResultSet rs_tabletypes = null;
private boolean isReadOnly = false;
private boolean usesLocalFiles = false;
private String driverName = null;
private String catalogName = null;
private String productName = null;
private String keyWords = null;
private String tablename = null;
private String currentquery = null;
private String tableFields[] = null;
private String tableNames[] = null;
private String nonMSysTables[] = null;
/**
* Gets whether or not this database stores tables in a local file. *
* The value is set using the DatabaseMetaData.usesLocalFiles() method * contained in the java.sql package. This method is called and this value is * set in this classes init() method.
*
* @return true if it does, false if not
*/
public boolean getUsesLocalFiles() {
return usesLocalFiles;
}
/**
* Gets whether or not this connection to this database is read only. *
* The value is set using the Connection.isReadOnly() method * contained in the java.sql package. This method is called and this value is * set in this classes init() method.
*
* @return true if it is, false if not
*/
public boolean getReadOnly() {
return isReadOnly;
}
/**
* Gets this Connection's current catalog name. *
* The value is set using the Connection.getCatalog() method * contained in the java.sql package. This method is called and this value is * set in this classes init() method.
*
* @return the current catalog name or null */
public String getCatalogName() {
return catalogName;
}
/**
* Gets the name of this JDBC driver.
*
* The value is set using the DatabaseMetaData.getDriverName() method * contained in the java.sql package. This method is called and this value is * set in this classes init() method.
*
* @return the JDBC Driver name
*/
public String getDriverName() {
return driverName;
}
/**
* Gets the name of this database product. *
* The value is set using the DatabaseMetaData.getDatabaseProductName() method * contained in the java.sql package. This method is called and this value is * set in this classes init() method.
*
* @return the database product name
*/
public String getProductName() {
return productName;
}
/**
* Gets the ResultSet contained in this instance variable rs. *
* @return this classes ResultSet
*/
public ResultSet getResultSet() {
return rs;
}
/**
* Gets the value of this current table name. *
* The value is set using the SqlBean.setTableName() method *
* @return the current table name
*/
public String getTableName() {
return tablename;
}
/**
* Gets the value of this current SQL.
*
* The value is set using the SqlBean.query() method *
* @return the current SQL query
*/
public String getCurrentQuery() {
return currentquery;
}
/**
* Gets the table names contained in this current database. *
* The table names are placed in a ResultSet using the DatabaseMetaData.getTables() method. * From the ResultSet, the tables are added to a vector and then * converted into a String array. This method can be used at anytime after the init() * method is called to set the DataBaseMetaData. *
* @return the table names
*/
public String[] getTableNames() {
Vector tableVector = new Vector();
try {
rs = dbmd.getTables(null, null, null, null);
rsmd = rs.getMetaData();
while (rs.next())
tableVector.addElement(rs.getString("TABLE_NAME"));
} catch (SQLException e) {
print(e);
}
int n = tableVector.size();
tableNames = new String[n];
for (int i = 0; i < n; i++)
tableNames[i] = (String) tableVector.elementAt(i);
return tableNames;
}
/**
* Gets the names of all the Non-System Tables in this Database. *
* Retrieves all the tables using the getTableNames() method. Then uses the * getNumberOfMSysTables() to determine the number of SystemTables contained in the * database and places those names in a String array. This method can be used at anytime * after the init() method is called to set the DataBaseMetaData. *
* @return the names of the non-system tables */
public String[] getNonMSysTables() {
String tn[] = getTableNames();
int n = tableNames.length - getNumberOfMSysTables();
nonMSysTables = new String[n];
for (int i = 0; i < n; i++) {
nonMSysTables[i] = tn[i + getNumberOfMSysTables()];
}
return nonMSysTables;
}
/**
* Gets the number of the System Tables in this Database. *
* Used to help determine the table names in the getNonMSysTables() method. * Determine the number of SystemTables contained in the database my searching for * names starting with MSys.
*
* @return the number of system tables
*/
private int getNumberOfMSysTables() {
int k = 0;
for (int i = 0; i < tableNames.length; i++) if (tableNames[i].startsWith("MSys")) k++;
return k;
}
/**
* Gets the table types available in this database connection. The results are ordered by table type. *
* The results are ordered by table type. Typical types are: * <br>"TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL", "TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM". *
* @return the current SQL query
*/
public ResultSet getTableTypes() {
return rs_tabletypes;
}
/**
* Sets this classes resultset instance variable, rs, based on a provided SQL query. *
* @param myquery the SQL query
*/
public void setResultSet(String myquery) {
rs = query(myquery);
}
/**
* Sets this classes instance variable, userId, for this database connection. *
* This is the database user on whose behalf the Connection is being made for *
* @param _userID the database UserId
*/
public void setUserId(String _userId) {
userId = _userId;
}
/**
* Sets this classes instance variable, password, for this database connection. *
* The password associated with the database user on whose behalf the * Connection is being made for.
*
* @param _password the database Password */
public void setPassword(String _password) {
password = _password;
}
/**
* Sets this classes instance variable, url, for this database url. *
* The url is in the form of jdbc:subprotocol:subname *
* @param _url the database url
*/
public void setUrl(String _url) {
url = _url;
}
/**
* Sets the name of the instance variable, driver, which this class is loadeding. *
* This is the string representation of the driver being loaded to make the connection to the database. *
* @param _driver the driver name
*/
public void setDriver(String _driver) {
driver = _driver;
}
/**
* Sets this classes instance variable, tablename, for the current database tablename. *
* @param _tablename the database tablename */
public void setTableName(String _tablename) {
tablename = _tablename;
}
/**
* Constructor.
*/
public SqlBean() {
}
/**
* Loads specified driver and initializes the Connection to this Database. *
* <p>Loads the driver and Connects to the Database * <br>Retrieves and sets Database/Connection Information as follows: * <br>   MetaData of this Database * <br>   Read Only Property * <br>   Uses Local Files Property * <br>   Driver Name Used for the Connection * <br>   Database Product Name * <br>   Table Types in this Database */
public void init() {
try {
Class.forName(driver);
c = DriverManager.getConnection(url, userId, password);
dbmd = c.getMetaData();
catalogName = c.getCatalog();
isReadOnly = c.isReadOnly();
usesLocalFiles = dbmd.usesLocalFiles();
driverName = dbmd.getDriverName();
productName = dbmd.getDatabaseProductName();
rs_tabletypes = dbmd.getTableTypes();
} catch (ClassNotFoundException e) {
println(e);
} catch (SQLException e) {
print(e);
}
System.out.println("Opened Connection:" + url);
}
/**
* Prints information about this database connection. * <p>
* <br>Prints the following information:
* <br>   The name of this database product * <br>   This Connection's current catalog name * <br>   Is this connection in read-only mode * <br>   Does this database store tables in a local files * <br>   The name of this JDBC driver * <br>   The SQL keywords of this database * <br>   The table types of all the tables in this database * <br>   The names of all the tables in this database * <br>   The names of all the non-system tables in this database */
public void printDataBaseInfo() {
println("*****");
println("productName=" + productName);
println("*****");
println("catalogName=" + catalogName);
println("*****");
println("is ReadOnly=" + getReadOnly());
println("*****");
println("usesLocalFiles=" + getUsesLocalFiles());
println("*****");
println("driverName=" + driverName);
println("*****");
println("Non SL92 keywords:");
println(getKeyWords());
println("*****");
println("TableTypes:");
print(getTableTypes());
println("*****");
println("TableNames - All:");
println(getTableNames());
println("*****");
println("TableNames - NonSystem:");
println(getNonMSysTables());
}
/**
* Prints information about this current ResultSet. * <p>
* <br>Prints the following information:
* <br>   The column names (fields) of this database * <br>   The type name of the columns (fields) used by this database *
* @param _rs the current ResultSet
*/
public void printResultSetInfo(ResultSet _rs) {
println("*****");
println("Column Names:");
println(getTableFields(getResultSetMetaData(_rs)));
println("*****");
println("Column Types:");
println(getColumnTypeNames(getResultSetMetaData(_rs)));
println("*****");
println("Number of Rows:");
println(Integer.toString((getNumberofRows(getRows(_rs)))));
println("*****");
println("Print The First Row:");
}
/**
* Closes the connections to this database. */
public void close() {
try {
rs.close();
rs_tabletypes.close();
statement.close();
c.close();
System.out.println("closed connection");
} catch (SQLException e) {
print(e);
}
}
/**
* Creates a Result Set based on an sql query. *
* @param _sql the sql query
* @return the ResultSet
*/
public ResultSet query(String sql) {
try {
statement = c.createStatement();
currentquery = sql;
return statement.executeQuery(sql);
} catch (SQLException e) {
print(e);
}
return null;
}
/**
* Gets the MetaData for a specified ResultSet. *
* @param _rs the ResultSet
* @return the ResultSetMetaData
*/
public ResultSetMetaData getResultSetMetaData(ResultSet _rs) {
try {
return _rs.getMetaData();
} catch (SQLException e) {
print(e);
}
return null;
}
/**
* Gets the number of columns in a ResultSet. *
* @param _rsmd the ResultSetMetaData
* @return number of Columns (fields)
*/
public int getColumnCount(ResultSetMetaData _rsmd) {
try {
return _rsmd.getColumnCount();
} catch (SQLException e) {
print(e);
}
return 0;
}
/**
* Gets the keywords associated with this database. *
* @return the keywords
*/
public String getKeyWords() {
try {
return dbmd.getSQLKeywords();
} catch (SQLException e) {
print(e);
}
return null;
}
/**
* Gets the database types of the columns in a ResultSet. *
* These are the type name used by this database. If the column type is a user-defined type, * then a fully-qualified type name is returned. *
* @param _rsmd the ResultSetMetaData
* @return the column types
*/
public String[] getColumnTypeNames(ResultSetMetaData _rsmd) {
int count = getColumnCount(_rsmd);
String sa [] = new String[count];
try {
for (int i = 0; i < sa.length; i++) {
sa[i] = _rsmd.getColumnTypeName(i + 1);
}
} catch (SQLException e) {
print(e);
}
return sa;
}
/**
* Converts a row in a ResultSet to a String. *
* @param _rs the ResultSet pointed to a particular row * @return the contents of the ResultSet
*/
public String[] getRowAsString(ResultSet _rs) {
int N = getColumnCount(getResultSetMetaData(_rs));
String s[] = new String[N];
try {
for (int i = 0; i < N; i++)
s[i] = _rs.getString(i + 1);
} catch (SQLException e) {
print(e);
}
return s;
}
/**
* Converts a ResultSet to a Vector of Strings. *
* @param _rs the ResultSet
* @return the vector containing the ResultSet with each row as a String */
public Vector getRows(ResultSet _rs) {
Vector v = new Vector();
while (nextRow(_rs))
v.addElement(getRowAsString(_rs));
return v;
}
/**
* Returns the size of a vector. Used with the getRows() method that converts a ResultSet to a vector. *
* @param v the Vector
* @return the number of rows
*/
public int getNumberofRows(Vector v) {
return v.size();
}
/**
* Moves to the top of this RecordSet.
*
* @param _rs the ResultSet
*/
public void moveToTop(ResultSet _rs) {
try {
//_rs.beforeFirst();
throw new SQLException("Not implemented until jdk 1.2");
} catch (SQLException e) {
print(e);
}
}
/**
* Prints the contents of a provided ResultSet. *
* @param _rs the ResultSet
*/
public void print(ResultSet _rs) {
int i;
String cn[] = getTableFields(getResultSetMetaData(_rs));
println(cn);
boolean more = false;
while (more = nextRow(_rs))
println(getRowAsString(_rs));
}
/**
* Prints the contents of a provided Object. * Uses the toString() method for the
* provided object.
*
* @param o the Object
*/
public void println(Object o) {
System.out.println(o);
}
/**
* Prints the contents of a String array. *
* @param s[] the array
*/
public void println(String s[]) {
for (int i = 0; i < s.length; i++)
System.out.print(s[i] + '\t');
System.out.println();
}
/**
* Prints messages about this SQL Exception. *
* @param ex the exception
*/
private void print(SQLException ex) {
println("\n*** SQLException caught ***\n");
while (ex != null) {
println("SQLState: " + ex.getSQLState());
println("Message: " + ex.getMessage());
println("Vendor: " + ex.getErrorCode());
ex = ex.getNextException();
println("");
}
ex.printStackTrace();
}
/**
* Moves to the next row of a provided ResultSet. *
* @param _rs the ResultSet
*/
public boolean nextRow(ResultSet _rs) {
try {
return _rs.next();
} catch (SQLException e) {
return false;
}
}
/**
* Gets the names of the columns (fields) in a provided ResultSet. *
* @param _rs the ResultSetMetaData
* @return the table names
*/
public String[] getTableFields(ResultSetMetaData _rsmd) {
String s [] = new String[getColumnCount(_rsmd)];
try {
for (int i = 1; i <= s.length; i++)
s[i - 1] = _rsmd.getColumnLabel(i);
} catch (SQLException e) {
print(e);
}
tableFields = s;
return s;
}
/**
* Gets this DataBaseMetaData object.
*
* @return this DataBaseMetaData
*/
public DatabaseMetaData getDatabaseMetaData() {
return dbmd;
}
/**
* Process an SQL INSERT, DELETE or UPDATE statement string. *
* Additionally, this could be an SQL
* statement that returns nothing such as SQL DDL statments. *
* @param sql the SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing */
public void modifyDatabase(String sql) {
try {
Statement s = c.createStatement();
int insertResult = s.executeUpdate(sql);
println("UPDATE SUCCESSFUL");
println("SQL Statment= " + sql);
} catch (SQLException e) {
println("UPDATE NOT SUCCESSFUL");
println("SQL Statment= " + sql);
print(e);
}
}
/**
* Main entry point for application.
*
* @param args[] the command line arguments */
public static void main(String args[]) {
SqlBean sb = new SqlBean();
sb.setUrl("jdbc:odbc:NorthwindAccess");
sb.setDriver("sun.jdbc.odbc.JdbcOdbcDriver");
sb.setUserId("");
sb.setPassword("");
sb.println("Calling Init Method");
sb.init();
sb.println("****************");
sb.println("Calling printDataBaseInfo Method");
sb.printDataBaseInfo();
sb.setTableName("Customers");
sb.println("****************");
sb.println("Calling printResultSetInfo Method for Table Name: "
+ sb.getTableName());
sb.printResultSetInfo(
sb.query("SELECT * FROM " + sb.getTableName()));
sb.setTableName("Categories");
sb.println("****************");
sb.println("Calling modifyDatabase Method for Table Name: " + sb.getTableName());
sb.modifyDatabase("INSERT INTO " + sb.getTableName() + "(CategoryName,Description) VALUES ('Vegetables','Fresh Produce');");
sb.println("****************");
sb.println("Calling Method to Print the Result Set for Table Name: " + sb.getTableName());
sb.print(sb.query("SELECT CategoryID,CategoryName, Description FROM " + sb.getTableName()));
sb.setTableName("Employees");
sb.println("****************");
sb.println("Calling Method to Print the Result Set for Table Name: " + sb.getTableName());
sb.print(sb.query("SELECT EmployeeID, LastName, FirstName FROM " + sb.getTableName()));
sb.close();
}
}