| MetaBeanFrame.java |
package jdbc;
import java.awt.event.WindowEvent;
/**
* Facade pattern implementation
* for JDBC
* This init method creates the
* connection to the database and retrieves
* additional database information.
* @version 1.00
*/
public class MetaBeanFrame extends javax.swing.JFrame {
private String url = null;
private String driver = null;
private String userId = null;
private String password = null;
private java.sql.Connection c = null;
private java.sql.Statement statement = null;
private java.sql.DatabaseMetaData dbmd = null;
private java.sql.ResultSet rs = null;
private java.sql.ResultSetMetaData rsmd = null;
private java.sql.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;
/**
* Determines if database
* uses 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 java.sql.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(java.sql.DatabaseMetaData dbmd, String userid) {
java.util.Vector tableVector = new java.util.Vector();
try {
rs = dbmd.getTables(null, userid, null, null);
rsmd = rs.getMetaData();
while (rs.next())
tableVector.addElement(rs.getString("TABLE_NAME"));
} catch (java.sql.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 java.sql.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 MetaBeanFrame() {
}
/**
* 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 {
// ************ uncomment the following in order to work @ home
// DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());
// c = DriverManager.getConnection(url,userId,password);
Class.forName(driver);
c = java.sql.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 (java.sql.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(java.sql.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 (java.sql.SQLException e) {
print(e);
}
}
/**
* Creates a Result Set based on an sql query. *
* @param _sql the sql query
* @return the ResultSet
*/
public java.sql.ResultSet query(java.sql.Connection c, String sql) {
try {
statement = c.createStatement();
currentquery = sql;
return statement.executeQuery(sql);
} catch (java.sql.SQLException e) {
print(e);
}
return null;
}
/**
* Gets the MetaData for a specified ResultSet. *
* @param _rs the ResultSet
* @return the ResultSetMetaData
*/
public java.sql.ResultSetMetaData getResultSetMetaData(java.sql.ResultSet _rs) {
try {
return _rs.getMetaData();
} catch (java.sql.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(java.sql.ResultSetMetaData _rsmd) {
try {
return _rsmd.getColumnCount();
} catch (java.sql.SQLException e) {
print(e);
}
return 0;
}
/**
* Gets the keywords associated with this database. *
* @return the keywords
*/
public String getKeyWords() {
try {
return dbmd.getSQLKeywords();
} catch (java.sql.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(java.sql.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 (java.sql.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(java.sql.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 (java.sql.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 java.util.Vector getRows(java.sql.ResultSet _rs) {
java.util.Vector v = new java.util.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(java.util.Vector v) {
return v.size();
}
/**
* Moves to the top of this RecordSet.
*
* @param _rs the ResultSet
*/
public void moveToTop(java.sql.ResultSet _rs) {
try {
//_rs.top();
// not implemented in 1.2
throw new java.sql.SQLException("Not implemented until jdk1.2");
} catch (java.sql.SQLException e) {
print(e);
}
}
/**
* Prints the contents of a provided ResultSet. *
* @param _rs the ResultSet
*/
public void print(java.sql.ResultSet _rs) {
int i;
String cn[] = getTableFields(getResultSetMetaData(_rs));
println(cn);
boolean more = false;
while (more = nextRow(_rs))
println(getRowAsString(_rs));
}
public java.util.Vector getRecords(java.sql.ResultSet _rs) {
java.util.Vector v = new java.util.Vector();
int i;
v.addElement(getTableFields(getResultSetMetaData(_rs)));
// println(cn);
boolean more = false;
while (more = nextRow(_rs))
// println(getRowAsString(_rs));
v.addElement(getRowAsString(_rs));
return v;
}
/**
* 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(java.sql.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(java.sql.ResultSet _rs) {
try {
return _rs.next();
} catch (java.sql.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(java.sql.ResultSetMetaData _rsmd) {
String s [] = new String[getColumnCount(_rsmd)];
try {
for (int i = 1; i <= s.length; i++)
s[i - 1] = _rsmd.getColumnLabel(i);
} catch (java.sql.SQLException e) {
print(e);
}
tableFields = s;
return s;
}
/**
* Gets this DataBaseMetaData object.
*
* @return this DataBaseMetaData
*/
public java.sql.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(java.sql.Connection c, String sql) {
try {
java.sql.Statement s = c.createStatement();
int updateResult = s.executeUpdate(sql);
javax.swing.JOptionPane.showMessageDialog(
null,
"Update is Successful \n" + c,
"Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
println("UPDATE SUCCESSFUL");
println("SQL Statment= " + sql);
} catch (java.sql.SQLException e) {
javax.swing.JOptionPane.showMessageDialog(
null,
"Update not Successful \n" + e,
"Error Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
}
}
/**
*abstract class Runbutton extends Button class and implements
*Actionlistener and Runnable interfaces. This class is used create runnable buttons.
* gui.run method is called in the action event method.
*/
abstract class RunButton
extends java.awt.Button
implements java.awt.event.ActionListener,
Runnable {
RunButton(String s) {
super(s);
addActionListener(this);
}
public void actionPerformed(
java.awt.event.ActionEvent e) {
run();
}
}
/**
*The class RdbmsUtil extends MetaBean and implements Action listener interface
*
*/
class RdbmsUtil extends MetaBeanFrame implements java.awt.event.ActionListener {
// Frame properties
private java.awt.GridBagConstraints gbCons;
private java.awt.GridBagLayout gbLayout;
private java.awt.Container cont;
private javax.swing.JScrollPane tablePanel;
private javax.swing.JPanel dsnPanel,browsePanel,buttonPanel,innerDsnPanel,tabListPanel,newRecordsPanel;
private javax.swing.JLabel username,password;
private javax.swing.JList databaseList, tableList;
private javax.swing.JTextField tfUserid, tfPassword;
public javax.swing.JTextField newRow[], tf[];
public boolean connected = false, inserted = false, updated = false;
/** connectivity properites
* the following url is using oracle jdbc driver to access oracle databases
* oracle jdbc driver is downloaded from www.oracle.com and installed
* in order to access oracle 7.x and oracle8i databases
*/
private String url = "jdbc:oracle:oci8:@"; // work on oracle jdbc driver
private String driver = "jdbc.oracle.oci8:@"; // work on oracle jdbc driver
private String userId = "scott";
private String passWord = "tiger";
public java.sql.Connection conn;
private java.sql.DatabaseMetaData dbmd, tableDbmd;
private java.sql.ResultSetMetaData rsmd,tableRsmd;
private boolean isReadOnly = false;
private boolean usesLocalFiles = false;
private String driverName = null;
private String catalogName = null;
private String productName = null;
// table information
private String tableFields[] = null;
private String tableNames[] = null;
private String nonMSysTables[] = null;
public String columnNames [] = null, columnTypes [] = null;
private java.util.Vector recordsVector, newRecordsVector,updateStmtVector;
javax.swing.JPanel innerPanel;
public int rows, cols, newRows = 0;
public String tableName;
public int currentRow, currentCol;
// default tables and databases
String tables[] = {"Employee ", "Department ", "Salary ", "Bonus ", "abc", "def", "GHI", "XYZ"};
String databases[] = {"Production", "Development", "pulsed", "stst", "stqa", "stdv", "hpstqa", "Foxpro", "MySql", "Access"};
// constructor
RdbmsUtil() {
// set the layout
cont = getContentPane();
cont.setLayout(new java.awt.BorderLayout());
// new panel
javax.swing.JPanel midPanel = new javax.swing.JPanel();
// define dsnpanel
dsnPanel = new javax.swing.JPanel();
innerDsnPanel = new javax.swing.JPanel();
tabListPanel = new javax.swing.JPanel();
dsnPanel.setLayout(new java.awt.FlowLayout());
innerDsnPanel.setLayout(new java.awt.GridLayout(0, 1));
// defind tablepanel
tablePanel = new javax.swing.JScrollPane();
// define buttonpanel
buttonPanel = new javax.swing.JPanel();
buttonPanel.setLayout(new java.awt.GridLayout(1, 0));
// define browse panel
gbLayout = new java.awt.GridBagLayout();
browsePanel = new javax.swing.JPanel();
innerPanel = new javax.swing.JPanel();
newRecordsPanel = new javax.swing.JPanel();
browsePanel.add(innerPanel);
gbCons = new java.awt.GridBagConstraints();
// components
tableList = new javax.swing.JList(tables);
databaseList = new javax.swing.JList(databases);
tableList.setVisibleRowCount(5);
databaseList.setVisibleRowCount(5);
databaseList.setFixedCellWidth(200);
tableList.setFixedCellWidth(300);
tableList.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
databaseList.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
// properties to connect database and its values are defined
tfUserid = new javax.swing.JTextField("scott");
tfPassword = new javax.swing.JPasswordField("tiger");
tfUserid.setEditable(true);
tfPassword.setEditable(true);
javax.swing.JLabel user = new javax.swing.JLabel("User Name :");
javax.swing.JLabel pass = new javax.swing.JLabel("Password :");
javax.swing.JTextField testid = new javax.swing.JTextField("scott");
javax.swing.JLabel database = new javax.swing.JLabel("Databases");
javax.swing.JLabel lblTables = new javax.swing.JLabel("Tables");
// runnable buttons are created
// runnable button "query" is created
java.awt.Button connect = new RunButton("Connect") {
public void run() {
exConnect();
connected = true;
}
};
// properties are added into dsnPanel
dsnPanel.add(new javax.swing.JScrollPane(databaseList));
innerDsnPanel.add(user);
innerDsnPanel.add(tfUserid);
innerDsnPanel.add(pass);
innerDsnPanel.add(tfPassword);
innerDsnPanel.add(connect);
dsnPanel.add(innerDsnPanel);
tabListPanel.add(new javax.swing.JScrollPane(tableList));
dsnPanel.add(tabListPanel);
// add runnable query button to buttonpanel
buttonPanel.add(new RunButton("Query") {
public void run() {
if (connected) {
exQuery();
} else {
javax.swing.JOptionPane.showMessageDialog(
null,
"Database is not connected!!!",
"Error Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
}
});
// add runnable update button to buttonpanel
buttonPanel.add(new RunButton("Update") {
public void run() {
if (connected) {
exUpdate();
} else {
javax.swing.JOptionPane.showMessageDialog(
null,
"Database is not connected!!!",
"Error Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
}
});
// add runnable insert button to buttonpanel
buttonPanel.add(new RunButton("Insert") {
public void run() {
if (connected) {
exInsert(cols);
} else {
javax.swing.JOptionPane.showMessageDialog(
null,
"Database is not connected!!!",
"Error Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
}
});
// add runnable delete button to buttonpanel
buttonPanel.add(new RunButton("Delete") {
public void run() {
if (connected) {
exDelete();
} else {
javax.swing.JOptionPane.showMessageDialog(
null,
"Database is not connected!!!",
"Error Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
}
});
// add runnable save button to buttonpanel
buttonPanel.add(new RunButton("Save") {
public void run() {
if (connected) {
exSave();
} else {
javax.swing.JOptionPane.showMessageDialog(
null,
"Database is not connected!!!",
"Error Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
}
});
// add runnable exit button to buttonpanel
buttonPanel.add(new RunButton("Exit") {
public void run() {
if (connected) {
shutDown();
}
System.exit(0);
}
});
// add panels to main Panel
cont.add(dsnPanel, java.awt.BorderLayout.NORTH);
browsePanel.setSize(800, 550);
cont.add(new javax.swing.JScrollPane(browsePanel), java.awt.BorderLayout.CENTER);
cont.add(buttonPanel, java.awt.BorderLayout.SOUTH);
setSize(800, 500);
show();
}
/**
*Sets the current row
*
*sets the current row and col of the gui.mouse.m2.mouse pointer is focussed.
* these current row and column is used in the class to locate
* the row and process the entire row when update or delete takes place.
*
*@param int row - current row
*@param int col - current column
*/
public void setCurrentRowCol(int row, int col) {
currentRow = row;
currentCol = col;
}
/**
*sets the color of the column
*
*sets the current column's color in blue, this change of attribute of the JTextfield is just to
* indicate the column is being edited.
*
*@param int row - current row
*@param int col - current column
*
*/
public void setColumnColor(int row, int col) {
// tf[row * col].setColor
// tf[row * col].setcolor();
}
/**
*closed down the connection if it is open
*
*This method is called from exit button and window closing listener service.
*/
public void shutDown() {
try {
if (!conn.isClosed())
conn.commit();
conn.close();
} catch (java.sql.SQLException e) {
System.err.println("Unable to disconnect ");
e.printStackTrace();
}
}
/**
*connects the database per user selection
*
*connects the database as per the user selection
*login name and password and database name are used to connect the database.
* method is called to connect appropriate database
*/
public void exConnect() {
try {
String db = (String) databaseList.getSelectedValue();
String name = getUserId();
String pass = getPassword();
if (name == null || pass == null) {
javax.swing.JOptionPane.showMessageDialog(
null,
"Invalid user name/password",
"Error ",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
throw new Exception();
}
if (db.equals("Production")) {
System.out.println(name + pass);
conn = connectProduction(name, pass);
} else if (db.equals("Development")) {
conn = connectDevelopment(name, pass);
} else {
conn = connectOdbc(name, pass, db);
}
// get dataBaseMetaData from the connection
dbmd = conn.getMetaData();
catalogName = conn.getCatalog();
isReadOnly = conn.isReadOnly();
driverName = dbmd.getDriverName();
productName = dbmd.getDatabaseProductName();
// get the table names available in the database
tables = getTableNames(dbmd, name.toUpperCase());
// populates the table names into tableList
tableList.setListData(tables);
initArrays();
show();
} catch (java.sql.SQLException e) {
e.printStackTrace();
} catch (Exception e) {
javax.swing.JOptionPane.showMessageDialog(
null,
"Invalid user name/password" + e,
"Error Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
System.out.println("Opened Connection :" + url);
}
/**
*gets Userid entered by the user
*
*@return userid - string - returns the userid
*/
public String getUserId() {
return tfUserid.getText();
}
/**
*gets password entered by the user
*
*@return password - string - returns the password
*/
public String getPassword() {
return tfPassword.getText();
}
/**
*gets the connection to production database
*
* production database is a oracle databse;
* java uses oracle jdbc driver to
* connect database. Per oracle, java has to register
* oracle jdbc driver using method
* DriverManager.registerDriver, and get the connection
* using url "jdbc:oracle:oci8:"
* database service name with @. The service name in this
* script is null (@ ), because
* this script access oracle8i personal edition which does
* not have service name.
*
*@param userid - oracle database userid entered by user
*@param password - oracle database password
*
*@return connection - returns connection
*/
public java.sql.Connection connectProduction(
String userId,
String passWord) {
conn = null;
try {
Class c = Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver(
(java.sql.Driver) c.newInstance());
// work on oracle jdbc
String url = "jdbc:oracle:oci8:@pulseD";
// work on oracle jdbc driver
conn = java.sql.DriverManager.getConnection(url, userId, passWord);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
*gets the connection to oracle development database
*
* development database is a oracle database; java uses oracle odbc driver to
* connect the database. This development database is accessed using database
* service name with @. The service name in this script is pulsed (@pulsed ).
* this service name is defined in tnsnames.ora of oracle_home/network/admin folder.
* Oracle reads this file to get the connection specification like ip addBk.address of the machine
* where the database exist, protocol, tcp port information in order to connect the
* database.
*
*@param userid - oracle database userid entered by user
*@param password - oracle database password
*
*@return connection - returns connection
*/
public java.sql.Connection connectDevelopment(String userId, String passWord) {
conn = null;
String url = "jdbc:odbc:pulsed"; // work on pulsed
String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; // work on pulsed
try {
Class.forName(driver);
conn = java.sql.DriverManager.getConnection(url, userId, passWord);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
*gets the connection to other databases
*
* other databases like foxpro could be connected using odbc driver.
*
*@param userid - oracle database userid entered by user
*@param password - oracle database password
*
*@return connection - returns connection
*@see connectProduction
*@see connectDevelopment
*/
public java.sql.Connection connectOdbc(String userId, String passWord, String serviceName) {
conn = null;
String url = "jdbc:odbc:" + serviceName; // work on pulsed
String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; // work on pulsed
try {
Class.forName(driver);
conn = java.sql.DriverManager.getConnection(url, userId, passWord);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
*exQuery method is executed when query button is pressed.
*
*once the tables from the database are populated into tablelist user could
* select a table and press query button to get the data of the table in a table format
*@see exConnect
*/
public void exQuery() {
recordsVector = new java.util.Vector(); // where the records are stored
tableName = (String) tableList.getSelectedValue(); // selected table name
if (tableName == null) {
javax.swing.JOptionPane.showMessageDialog(
null,
"Please select a table to query data ",
"Error Message ",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
initArrays(); // init all arrays
// puts records into a vector
recordsVector = getRecords(query(conn,
"SELECT * FROM "
+ tableName));
// get the total no of columns in the table
cols = getColumnCount(
getResultSetMetaData(
query(conn,
"SELECT * FROM "
+ tableName)));
// gets the resultsetmetaData of the query
tableRsmd = getResultSetMetaData(
query(conn,
"SELECT * FROM "
+ tableName));
// gets no of rows in the table
rows = getNumberofRows(recordsVector);
columnTypes = new String[cols];
// gets the column types and stores in String array
columnTypes = getColumnTypeNames(
getResultSetMetaData(
query(conn,
"SELECT * FROM "
+ tableName)));
// formats the data into a table
formatTable(rows, cols);
javax.swing.JOptionPane.showMessageDialog(
null,
null,
"Query is executed",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
/**
*initArrays, is called to initialize array and cells
*
*/
public void initArrays() {
newRow = null;
inserted = false;
browsePanel.remove(innerPanel);
show();
}
/**
*formats the data in a presentable format Table.
*
*This method is called when the user select a table and press query button.
*
*This method takes no of rows and columns in the table through parameter and
* creates JTextFields and populates value in them. Every JTextField is associated
* with gui.mouse.m2.mouse handler, and action listener. Mouse Handler would keep track of the
* current row and column of the cell the gui.mouse.m2.mouse pointer is pressed. Action listener is
* process the update statement in the event of enter is pressed after a value is changed.
*
*@param int - row no of rows
*@param int - no of columns in a row
*/
public void formatTable(int rows, int cols) {
int r = 0;
int c = 0;
int row = 0;
browsePanel.removeAll();
browsePanel.repaint();
innerPanel = new javax.swing.JPanel();
browsePanel.setLayout(new java.awt.FlowLayout());
innerPanel.setLayout(new java.awt.GridLayout(0, cols));
newRecordsPanel.setLayout(new java.awt.GridLayout(0, cols));
javax.swing.JTextField tf [] = new javax.swing.JTextField[rows * cols];
String columns [];
for (r = 0; r < rows; r++)
for (c = 0; c < cols; c++) {
System.out.println("row " + r + " col" + c);
tf[row] = new javax.swing.JTextField(10);
tf[row].addActionListener(this);
tf[row].addMouseListener(new jdbc.RdbmsUtil.MouseHandler(r, c));
addComponent(innerPanel, tf[row]);
row++;
}
browsePanel.remove(innerPanel);
browsePanel.add(innerPanel, 0);
fillData(tf, recordsVector, rows, cols);
show();
}
/**
*getOldValue method is used to get the old value of the cell
*
*This method is called when the user changes the value of any cell, and the old
* and new values are shown in a dialog box
*
*@param int - row of the cell
*@param int - column of the cell
*@return String - old value of the cell
*/
public String getOldValue(int row, int col) {
String rowArr [];
String oldValue;
rowArr = (String[]) recordsVector.elementAt(row);
return oldValue = rowArr[col];
}
/**
*generates update statement and keeps in an array
*
*method generateUpdateStmt is used when a value in a cell is changed and pressed
* enter key to confirm the change is done. This method gets the current row and column
* that is set by the gui.mouse.m2.mouse handler, gets the old value from the vector and current value
* and generate the update statment
*
*@param int row - currect row
*@param int column - current column
*@param int String new Value
*/
public void generateUpdateStmt(int row, int col, String newValue) {
String rowArr [];
String oldValue;
String sql = null;
rowArr = (String[]) recordsVector.elementAt(row);
// init update statement vector in case of first update
if (!updated) {
updateStmtVector = new java.util.Vector();
updated = true;
}
sql = "update " +
tableName +
" set " + columnNames[col] + " = ";
if (columnTypes[col].equals("varchar2")) {
sql += "\'";
sql += newValue;
sql += "\'";
} else if (columnTypes[col].equals("number")) {
sql += newValue;
} else if (columnTypes[col].equals("date")) {
try {
sql += "to_date('";
sql += newValue.substring(0, 10);
sql += "\','YYYY-MM-DD')";
} catch (StringIndexOutOfBoundsException e) {
javax.swing.JOptionPane.showMessageDialog(
null,
"Invalid date format!!!\n Valid format is [yyyy-mm-dd]",
"Error Message ",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
} else {
sql += "\'";
sql += newValue;
sql += "\'";
}
sql += " where ";
for (int c = 0; c < cols; c++) {
if (c > 0) {
sql += " and ";
}
if (columnTypes[c].equals("date")) {
sql += "trunc("; // oracle function to truncate time in date column
sql += columnNames[c] + ")";
} else {
sql += columnNames[c];
}
sql += formatColValue(c, rowArr[c]);
}
updateStmtVector.addElement(sql);
}
/**
*formatColValue method is used to formats the sql script
*
*The method formatColValue is used to formats the sql script as the oracle
* database requires the column to be formatted using specific oracle function
* to_date to format a date type before update.
*
*@param int column index which is to be formatted
*@param String value of the column
*/
public String formatColValue(int col, String value) {
String fmtValue = null;
System.out.println("col # " + col);
if (value == null) {
fmtValue = "is ";
fmtValue += value;
} else if (columnTypes[col].equals("varchar2")) {
fmtValue = " = ";
fmtValue += "\'";
fmtValue += value;
fmtValue += "\'";
} else if (columnTypes[col].equals("number")) {
fmtValue = " = ";
fmtValue += value;
} else if (columnTypes[col].equals("date")) {
try {
fmtValue = " = ";
fmtValue += "to_date('";
fmtValue += value.substring(0, 10);
fmtValue += "\','YYYY-MM-DD')";
} catch (StringIndexOutOfBoundsException e) {
javax.swing.JOptionPane.showMessageDialog(
null,
"Invalid date format!!!\n Valid format is [yyyy-mm-dd]",
"Error Message ",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
} else {
fmtValue = " = ";
fmtValue += "\'";
fmtValue += value;
fmtValue += "\'";
}
System.out.println("column type" + columnTypes[col]);
System.out.println("fmt value " + fmtValue);
return fmtValue;
}
/**
*fillData method is called to fill up the data into JTextFields that forms a table.
*
*filldata method gets data in a vector, and the no of rows and columns. It reads the data
* from the vector which has elements of String Arrays. This methods reads the data from
*the String array and populates into JTextField .
*The column names row would be set to not editable
*
*@param JTextField Arrary - target
*@param Vector -vector where all rows are stored
*@param int rows - no of rows to be filled out
*@param int column - no of columns in a row
*/
public void fillData(javax.swing.JTextField[] tf, java.util.Vector v, int rows, int cols) {
String rowArr [];
columnNames = new String[cols];
int i = 0;
for (int r = 0; r < rows; r++) {
rowArr = (String[]) v.elementAt(r);
for (int c = 0; c < cols; c++) {
tf[i].setText(rowArr[c]);
if (r == 0) {
columnNames[i] = tf[i].getText();
tf[i].setEditable(false);
}
i++;
}
}
}
/**
*the method exUpdate is called when the update button is pressed.
*
*The exUpdate method gets the update statement (DML) from a updateStmtVector and
* reads one by one and executes it.
* once all update statements are executed it refreshes the database.
*/
public void exUpdate() {
if (updated) {
String updStmt = null;
for (int i = 0; i < updateStmtVector.size(); i++) {
updStmt = (String) updateStmtVector.elementAt(i);
System.out.println("script " + updStmt);
modifyDatabase(conn, updStmt);
}
exQuery();
} else {
javax.swing.JOptionPane.showMessageDialog(
null,
"No update statement to execute ...",
"Message",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
}
/**
*the method exDelete is called when the delete button is pressed.
*
*The exDelete method get the current row and column set by the gui.mouse.m2.mouse handler and
* deletes the row and refreshes the database after deleteion.
*/
public void exDelete() {
String rowArr [];
String sql = null;
rowArr = (String[]) recordsVector.elementAt(currentRow);
sql = "delete from " + tableName;
sql += " where ";
for (int c = 0; c < cols; c++) {
if (c > 0) {
sql += " and ";
}
if (columnTypes[c].equals("date")) {
sql += "trunc(";
sql += columnNames[c] + ")";
} else {
sql += columnNames[c];
}
sql += formatColValue(c, rowArr[c]);
}
modifyDatabase(conn, sql);
exQuery();
}
/**
*the method exInsert is called when the insert button is pressed.
*
*The exInsert method get the no of columns in a row as a parameter and inserts JTextfields
*into newRow Array of JTextFields
*
*@param int cols - no of columns in a row
*/
public void exInsert(int cols) {
if (!inserted) {
newRow = new javax.swing.JTextField[cols];
for (int c = 0; c < cols; c++) {
newRow[c] = new javax.swing.JTextField(10);
if (columnTypes[c].equals("number")) {
newRow[c].setText("0");
}
addComponent(innerPanel, newRow[c]);
}
inserted = true;
} else {
System.out.println("cols " + cols);
addNewRow(newRow, cols);
}
show();
}
/**
*the method addNewRow is called to include no of JTextfields into an old array
* of JTextFields
*
*This method is called once the insert button is pressed
*
*@param - JTextField array
*@param - int cols - no of JTextFields to be included
*@see exInsert
*/
public void addNewRow(javax.swing.JTextField[] oldArr, int cols) {
System.out.println("old length " + oldArr.length);
if (oldArr.length > 0) {
javax.swing.JTextField newArr [] = new javax.swing.JTextField[cols + oldArr.length];
copyArray(oldArr, newArr);
newRow = new javax.swing.JTextField[newArr.length];
copyArray(newArr, newRow);
} else {
javax.swing.JTextField newRow [] = new javax.swing.JTextField[cols];
for (int c = 0; c < cols; c++) {
newRow[c] = new javax.swing.JTextField(10);
if (columnTypes[c].equals("number")) {
newRow[c].setText("0");
}
}
}
for (int c = 0; c < newRow.length; c++) {
addComponent(innerPanel, newRow[c]);
}
show();
}
/**
*the method copyArray is used to copy the value of one array to another
*
*@param JTextField array - old array of JTextFields
*@param JTextField array - new array of JTextFields
*@see addNewRow
*/
public void copyArray(javax.swing.JTextField[] oldArr, javax.swing.JTextField[] newArr) {
for (int c = 0; c < oldArr.length; c++)
newArr[c] = oldArr[c];
for (int c = 0; c < newArr.length - oldArr.length; c++) {
newArr[oldArr.length + c] = new javax.swing.JTextField(10);
if (columnTypes[c].equals("number")) {
newArr[oldArr.length + c].setText("0");
}
}
}
/**
*saves the changes to database and refreshes it
*
*if the inserted flag is true then reads new records from newRow Array
* formats insert statement and executes it.
*/
public void exSave() {
String sql;
int totCols = newRow.length;
int rowCount = 0;
if (inserted) {
for (int r = 0; r < (totCols / cols); r++) {
sql = "insert into " +
tableName +
" values (";
for (int c = 0; c < cols; c++) {
System.out.println("type " + columnTypes[c]);
if (columnTypes[c].equals("varchar2")) {
sql += "\'";
sql += newRow[rowCount].getText();
sql += "\'";
} else if (columnTypes[c].equals("number")) {
if (newRow[rowCount].getText() != "") {
sql += newRow[rowCount].getText();
} else {
sql += "0";
}
} else if (columnTypes[c].equals("date")) {
if (newRow[rowCount].getText().length() >= 10) {
try {
sql += "to_date('";
sql += newRow[rowCount].getText().substring(0, 10);
sql += "\','YYYY-MM-DD')";
} catch (StringIndexOutOfBoundsException e) {
javax.swing.JOptionPane.showMessageDialog(
null,
"Invalid date format!!!\n Valid format is [yyyy-mm-dd]",
"Error Message ",
javax.swing.JOptionPane.INFORMATION_MESSAGE);
}
} else {
sql += "null";
}
} else {
sql += "\'";
sql += newRow[rowCount].getText();
sql += "\'";
System.out.println(sql);
}
if (c != cols - 1) {
sql += ",";
}
rowCount++;
}
sql += ")";
System.out.println("script " + sql);
modifyDatabase(conn, sql);
}
}
// executes query to refreshes the table after update or delete or insert
exQuery();
}
/**
*The method addComponent is called to include component into a panel.
*@param Panel - panel name where the component is to be added
*@param component - the component name that is to be added
*/
private void addComponent(
javax.swing.JPanel panel,
java.awt.Component c) {
panel.add(c);
}
/**
*The method addComponent is called to include component into a panel.
*@param Panel - panel name where the component is to be added
*@param component - the component name that is to be added
*@param row - row of the grid bag layout
*@param column - column of the grid bag layout
*@width - filler space width
*@height - filler space height
*/
private void addComponent(javax.swing.JPanel panel,
java.awt.Component c,
int row,
int col,
int width,
int height) {
// set gridx and gridy
gbCons.gridx = col;
gbCons.gridy = row;
// set width and height
gbCons.gridwidth = width;
gbCons.gridheight = height;
// set constraints
gbLayout.setConstraints(c, gbCons);
panel.add(c); // add component
}
// action performed is implemented
/**
*This action perfomed method is called when the user press enter key
*after changing a value in any of the cell. If the value is changed
*then an update statment is generated and kept in a vector which is executed later
* when the save button is pressed.
*
*@param ActionEvent - event
*/
public void actionPerformed(java.awt.event.ActionEvent e) {
String evt = e.getActionCommand();
String oldValue;
String newValue;
if (e.getSource() instanceof javax.swing.JTextField) {
newValue = e.getActionCommand();
oldValue = getOldValue(currentRow, currentCol);
if (!oldValue.equals(newValue)) {
generateUpdateStmt(currentRow, currentCol, newValue);
}
javax.swing.JOptionPane.showMessageDialog(null,
"old Value " +
oldValue +
" New Value " + evt);
}
}
/**
*main entry method which instantiates RdbmsUtil class
*/
public static void main(String args []) {
final RdbmsUtil ru = new RdbmsUtil();
ru.addWindowListener(
new java.awt.event.WindowAdapter() {
public void windowClosing(WindowEvent e) {
ru.shutDown();
System.exit(0);
}
}
);
}
/**class gui.mouse.m2.mouse handler is used to capture the current row and current column
* of the cell where the gui.mouse.m2.mouse pointer is focussed
*
*This is used later to process the current row and column
*/
class MouseHandler
extends java.awt.event.MouseAdapter
implements java.awt.event.MouseMotionListener {
// cell co-ordinates
int row;
int col;
// constructor takes the row and column
public MouseHandler(int r, int c) {
row = r;
col = c;
}
/**
*sets the current row and column in a global variable
*
*the mousePressed event sets the currentRow and currentCol variables when the gui.mouse.m2.mouse is clicked on the cell
*this current row and column variables are used to identify the current row and process it.
*/
public void mousePressed(java.awt.event.MouseEvent e) {
setCurrentRowCol(row, col);
// setColumnColor(row,col);
}
public void mouseDoubleClicked(java.awt.event.MouseEvent e) {
setCurrentRowCol(row, col);
// setRowColor(row,col);
}
public void mouseMoved(java.awt.event.MouseEvent e) {
}
public void mouseReleased(java.awt.event.MouseEvent e) {
}
public void mouseDragged(java.awt.event.MouseEvent e) {
}
}
}