import java.sql.*;
import java.io.*;
public class MakeDB
{
public static void main(String[] args) throws Exception
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:StockTracker";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
// The following code deletes each index and table, if they exist.
// If they do not exist, a message is displayed and execution continues.
System.out.println("Dropping indexes & tables ...");
try
{
stmt.executeUpdate("DROP INDEX PK_UserStocks ON UserStocks");
}
catch (Exception e)
{
System.out.println("Could not drop primary key on UserStocks Table: "
+ e.getMessage());
}
try
{
stmt.executeUpdate("DROP TABLE UserStocks");
}
catch (Exception e)
{
System.out.println("Could not drop UserStocks table: "
+ e.getMessage());
}
try
{
stmt.executeUpdate("DROP TABLE Users");
}
catch (Exception e)
{
System.out.println("Could not drop Users table: "
+ e.getMessage());
}
try
{
stmt.executeUpdate("DROP TABLE Stocks");
}
catch (Exception e)
{
System.out.println("Could not drop Stocks table: "
+ e.getMessage());
}
////////// Create the database tables ///////////////
System.out.println("\nCreating tables ............");
// Create Stocks table with primary key index
try
{
System.out.println("Creating Stocks table with primary key index...");
stmt.executeUpdate("CREATE TABLE Stocks ("
+"symbol TEXT(8) NOT NULL "
+"CONSTRAINT PK_Stocks PRIMARY KEY, "
+"name TEXT(50)"
+")");
}
catch (Exception e)
{
System.out.println("Exception creating Stocks table: "
+ e.getMessage());
}
// Create Users table with primary key index
try
{
System.out.println("Creating Users table with primary key index...");
stmt.executeUpdate("CREATE TABLE Users ("
+"userID TEXT(20) NOT NULL "
+"CONSTRAINT PK_Users PRIMARY KEY, "
+"lastName TEXT(30) NOT NULL, "
+"firstName TEXT(30) NOT NULL, "
+"pswd LONGBINARY, "
+"admin BIT"
+")");
}
catch (Exception e)
{
System.out.println("Exception creating Users table: "
+ e.getMessage());
}
// Create UserStocks table with foreign keys to Users and Stocks tables
try
{
System.out.println("Creating UserStocks table ...");
stmt.executeUpdate("CREATE TABLE UserStocks ("
+"userID TEXT(20) "
+"CONSTRAINT FK1_UserStocks REFERENCES Users (userID), "
+"symbol TEXT(8), "
+"CONSTRAINT FK2_UserStocks FOREIGN KEY (symbol) "
+"REFERENCES Stocks (symbol))");
}
catch (Exception e)
{
System.out.println("Exception creating UserStocks table: "
+ e.getMessage());
}
// Create UserStocks table primary key index
try
{
System.out.println("Creating UserStocks table primary key index... ");
stmt.executeUpdate("CREATE UNIQUE INDEX PK_UserStocks "
+"ON UserStocks (userID, symbol) "
+"WITH PRIMARY DISALLOW NULL");
}
catch (Exception e)
{
System.out.println("Exception creating UserStocks index: "
+ e.getMessage());
}
// Create one administrative user with password as initial data
String userID = "admin01";
String firstName = "Default";
String lastName = "Admin";
String initialPswd = "admin01";
Password pswd = new Password(initialPswd);
boolean admin = true;
PreparedStatement pStmt =
con.prepareStatement("INSERT INTO Users VALUES (?,?,?,?,?)");
try
{
pStmt.setString(1, userID);
pStmt.setString(2, lastName);
pStmt.setString(3, firstName);
pStmt.setBytes(4, serializeObj(pswd));
pStmt.setBoolean(5, admin);
pStmt.executeUpdate();
}
catch (Exception e)
{
System.out.println("Exception inserting user: "
+e.getMessage());
}
pStmt.close();
// Read and display all User data in the database
ResultSet rs = stmt.executeQuery("SELECT * FROM Users");
System.out.println("Database created.\n");
System.out.println("Displaying data from database...\n");
System.out.println("Users table contains:");
Password pswdFromDB;
byte[] buf = null;
while(rs.next())
{
System.out.println("Logon ID = "
+ rs.getString("userID"));
System.out.println("First name = "
+ rs.getString("firstName"));
System.out.println("Last name = "+rs.getString("lastName"));
System.out.println("Administrative = "+rs.getBoolean("admin"));
System.out.println("Initial password = "+initialPswd);
// Do NOT use with JDK 1.2.2 using JDBC-QDBC Bridge as
// SQL NULL data value is not handled correctly.
buf = rs.getBytes("pswd");
if (buf != null)
{
System.out.println("password Object = "
+ (pswdFromDB=(Password)deserializeObj(buf)));
System.out.println(" AutoExpires = "+ pswdFromDB.getAutoExpires());
System.out.println(" Expiring now = "+ pswdFromDB.isExpiring());
System.out.println(" Remaining Uses = "
+ pswdFromDB.getRemainingUses()+"\n");
}
else
System.out.println("Password Object = NULL");
}
rs = stmt.executeQuery("SELECT * FROM Stocks");
if(!rs.next())
System.out.println("Stocks table contains no records.");
else
System.out.println("Stocks table still contains records!");
rs = stmt.executeQuery("SELECT * FROM UserStocks");
if(!rs.next())
System.out.println("UserStocks table contains no records,");
else
System.out.println("UserStocks table still contains records!");
stmt.close(); //closing Statement also closes ResultSet
} //end of main()
// Method to write object to byte array and then insert into prepared statement.
public static byte[] serializeObj(Object obj)
throws IOException
{
ByteArrayOutputStream baOStream = new ByteArrayOutputStream();
ObjectOutputStream objOStream = new ObjectOutputStream(baOStream);
objOStream.writeObject(obj); // object must be Serializable
objOStream.flush();
objOStream.close();
return baOStream.toByteArray(); // returns stream as byte array
}
// Method to read bytes from result set into a byte array and then
// create an input stream and read the data into an object
public static Object deserializeObj(byte[] buf)
throws IOException, ClassNotFoundException
{
Object obj = null;
if (buf != null)
{
ObjectInputStream objIStream =
new ObjectInputStream(new ByteArrayInputStream(buf));
obj = objIStream.readObject(); // throws IOException, ClassNotFoundException
}
return obj;
}
} // end of class
Code for StockTrackerDB.java...
import java.io.*;
import java.sql.*;
import java.util.*;
public class StockTrackerDB
{
private Connection con = null;
// Constructor: makes database connection
public StockTrackerDB() throws ClassNotFoundException,SQLException
{
if(con == null)
{
String url = "jdbc:odbc:StockTracker";
try
{
Class.forName("sun.jdbc.odbc.jdbcOdbcDriver");
}
catch(ClassNotFoundException ex)
{
throw new ClassNotFoundException(ex.getMessage() +
"\nCannot locate sun.jdbc.odbc.JdbcOdbcDriver");
}
try
{
con = DriverManager.getConnection(url);
}
catch(SQLException ex)
{
throw new SQLException(ex.getMessage()+
"\nCannot open database connection for "+url);
}
}
}
// Close makes database connection; null reference to connection
public void close() throws SQLException,IOException,ClassNotFoundException
{
con.close();
con = null;
}
// Method to serialize object to byte array
private byte[] serializeObj(Object obj) throws IOException
{
ByteArrayOutputStream baOStream = new ByteArrayOutputStream();
ObjectOutputStream objOStream = new ObjectOutputStream(baOStream);
objOStream.writeObject(obj); // object must be Serializable
objOStream.flush();
objOStream.close();
return baOStream.toByteArray(); // returns stream as byte array
}
// Method to deserialize bytes from a byte array into an object
private Object deserializeObj(byte[] buf)
throws IOException, ClassNotFoundException
{
Object obj = null;
if (buf != null)
{
ObjectInputStream objIStream =
new ObjectInputStream(new ByteArrayInputStream(buf));
obj = objIStream.readObject(); //IOException, ClassNotFoundException
}
return obj;
}
////////////////////////////////////////////////////////////////////////////
// Methods for adding a record to a table
////////////////////////////////////////////////////////////////////////////
// add to the Stocks Table
public void addStock(String stockSymbol, String stockDesc)
throws SQLException, IOException, ClassNotFoundException
{
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO Stocks VALUES ('"
+stockSymbol+"'"
+",'"+stockDesc+"')");
stmt.close();
}
// add to the Users table
public boolean addUser(User user) throws SQLException,IOException,
ClassNotFoundException
{
boolean result = false;
String dbUserID;
String dbLastName;
String dbFirstName;
Password dbPswd;
boolean isAdmin;
dbUserID = user.getUserID();
if(getUser(dbUserID) == null)
{
dbLastName = user.getLastName();
dbFirstName = user.getFirstName();
Password pswd = user.getPassword();
isAdmin = user.isAdmin();
PreparedStatement pStmt = con.prepareStatement(
"INSERT INTO Users VALUES (?,?,?,?,?)");
pStmt.setString(1, dbUserID);
pStmt.setString(2, dbLastName);
pStmt.setString(3, dbFirstName);
pStmt.setBytes(4, serializeObj(pswd));
pStmt.setBoolean(5, isAdmin);
pStmt.executeUpdate();
pStmt.close();
result = true;
}
else
throw new IOException("User exists - cannot add.");
return result;
}
// add to the UserStocks table
public void addUserStocks(String userID, String stockSymbol)
throws SQLException,IOException,ClassNotFoundException
{
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO UserStocks VALUES ('"
+userID+"'"
+",'"+stockSymbol+"')");
stmt.close();
}
//////////////////////////////////////////////////////////////////
// Methods for updating a record in a table
//////////////////////////////////////////////////////////////////
// updating the Users table
public boolean updUser(User user) throws SQLException,IOException,
ClassNotFoundException
{
boolean result = false;
String dbUserID;
String dbLastName;
String dbFirstName;
Password dbPswd;
boolean isAdmin;
dbUserID = user.getUserID();
if(getUser(dbUserID) != null)
{
dbLastName = user.getLastName();
dbFirstName = user.getFirstName();
Password pswd = user.getPassword();
isAdmin = user.isAdmin();
PreparedStatement pStmt = con.prepareStatement("UPDATE Users SET lastName = ? "
+" firstName = ?, pswd = ?, admin = ? WHERE userID = ?");
pStmt.setString(1, dbLastName);
pStmt.setString(2, dbFirstName);
pStmt.setBytes(3, serializeObj(pswd));
pStmt.setBoolean(4, isAdmin);
pStmt.setString(5, dbUserID);
pStmt.executeUpdate();
pStmt.close();
result = true;
}
else
throw new IOException("User does not exist = cannot update.");
return result;
}
////////////////////////////////////////////////////////////////////
// Methods for deleting a record from a table
////////////////////////////////////////////////////////////////////
// delete a record from the Stocks table
private void delStock(String stockSymbol)
throws SQLException,IOException,ClassNotFoundException
{
Statement stmt = con.createStatement();
stmt.executeUpdate("DELETE FROM Stocks WHERE "
+"symbol = '"+stockSymbol+"'");
stmt.close();
}
// delete a record from the Users table
public void delUser(User user) throws SQLException,IOException,
ClassNotFoundException
{
String dbUserID;
String stockSymbol;
Statement stmt = con.createStatement();
try {
con.setAutoCommit(false);
dbUserID = user.getUserID();
if(getUser(dbUserID) != null) // verify user exists in database
{
ResultSet rsl = stmt.executeQuery("SELECT userID, symbol "
+"FROM UserStocks WHERE userID = '"+dbUserID+"'");
while(rsl.next())
{
try
{
stockSymbol = rsl.getString("symbol");
delUserStocks(dbUserID, stockSymbol);
}
catch(SQLException ex)
{
throw new SQLException("Deletion of user stock failed: "
+ex.getMessage());
}
} // end of loop thru UserStocks
try
{ // holdings deleted, now delete user
stmt.executeUpdate("DELETE FROM Users WHERE "
+"userID = '"+dbUserID+"'");
}
catch(SQLException ex)
{
throw new SQLException("User deletion failed: "+ex.getMessage());
}
}
else
throw new IOException("User not found in database - cannot delete.");
try
{
con.commit();
}
catch(SQLException ex)
{
throw new SQLException("Transaction commit failed: "+ex.getMessage());
}
}
catch (SQLException ex)
{
try
{
con.rollback();
}
catch (SQLException sqx)
{
throw new SQLException("Transaction failed then rollback failed: "
+sqx.getMessage());
}
// Transaction failed, was rolled back
throw new SQLException("Transaction failed: was rolled back: "
+ex.getMessage());
}
stmt.close();
}
// delete a record from the UserStocks table
public void delUserStocks(String userID, String stockSymbol)
throws SQLException,IOException,ClassNotFoundException
{
Statement stmt = con.createStatement();
ResultSet rs;
stmt.executeUpdate("DELETE FROM UserStocks WHERE "
+"userID = '"+userID+"'"
+"AND symbol = '"+stockSymbol+"'");
rs = stmt.executeQuery("SELECT symbol FROM UserStocks "
+"WHERE symbol = '"+stockSymbol+"'");
if(!rs.next()) // no users have this stock
delStock(stockSymbol);
stmt.close();
}
/////////////////////////////////////////////////////////////////////
// Methods for listing record data from a table
// Ordered by:
// methods that obtain individual field(s),
// methods that obtain a complete record, and
// methods that obtain multiple records
////////////////////////////////////////////////////////////////////
// Methods to access one or more individual fields
// get a stock description from the Stocks table
public String getStockDesc(String stockSymbol)
throws SQLException, IOException, ClassNotFoundException
{
Statement stmt = con.createStatement();
String stockDesc = null;
ResultSet rs = stmt.executeQuery("SELECT symbol, name FROM Stocks "
+"WHERE symbol = '"+stockSymbol+"'");
if(rs.next())
stockDesc = rs.getString("name");
rs.close();
stmt.close();
return stockDesc;
}
// Methods to access a complete record
// get User data from the Users table
public User getUser(String userID) throws SQLException, IOException,
ClassNotFoundException
{
Statement stmt = con.createStatement();
String dbUserID;
String dbLastName;
String dbFirstName;
Password dbPswd;
boolean isAdmin;
byte[] buf = null;
User user = null;
ResultSet rs = stmt.executeQuery("SELECT * FROM Users WHERE userID = '"
+userID+"'");
if(rs.next())
{
dbUserID = rs.getString("userID");
dbLastName = rs.getString("lastName");
dbFirstName = rs.getString("firstName");
// Do NOT use with JDK 1.2.2 using JDBC-ODBC bridge as
// SQL NULL data value is not handled correctly.
buf = rs.getBytes("pswd");
dbPswd=(Password)deserializeObj(buf);
isAdmin = rs.getBoolean("admin");
user = new User(dbUserID,dbFirstName,dbLastName,dbPswd,isAdmin);
}
rs.close();
stmt.close();
return user; // User object created for userID
}
// Methods to access a list of records
// get list of selected fields for all records from the Users Table
public ArrayList listUsers() throws SQLException,IOException,
ClassNotFoundException
{
ArrayList aList = new ArrayList();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT userID, firstName, lastName, admin "
+"FROM Users ORDER BY userID");
while(rs.next())
{
aList.add(rs.getString("userID"));
aList.add(rs.getString("firstName"));
aList.add(rs.getString("lastName"));
aList.add(new Boolean(rs.getBoolean("admin")));
}
rs.close();
stmt.close();
return aList;
}
// get all fields in all records for a given user from the Userstocks table
public ArrayList listUserStocks(String userID) throws SQLException,IOException,
ClassNotFoundException
{
ArrayList aList = new ArrayList();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM UserStocks "
+"WHERE userID = '"+userID+"' ORDER BY symbol");
while(rs.next())
aList.add(rs.getString("symbol"));
rs.close();
stmt.close();
return aList;
}
}
LinkBack URL
About LinkBacks
Cannot locate sun.jdbc.odbc.jbcOdbc driver
Reply With Quote
Related threads:
Re: Cannot locate sun.jdbc.odbc.jbcOdbc driver
