0

Hello, I am a current Java student and am having a problem when I run my compiled program from an in chapter lab assignment called "Stock Tracker". I receive a message from a dialog box "Cannot locate sun.jdbc.odbc.jbcOdbc Driver. Class not found exception creating database object. Following are a list of files that make up the whole program...

Files copied over from the previous chapter's java files:
Activator.class
PasswordException.class
PasswordExpiredException.class
PasswordInternalException.class
PasswordInvalidException.class
PasswordSizeException.class
PasswordUsedException.class
Password.java (modified from previous chapter)

Java source code files provided by the author of the book:
STAction.java
STLogon.java
StockTracker.java
User.java
UserMaintFrame.java

Files created in chapter for this excercise:
MakeDB.java
StockTrackerDB.java

Created a database called "StockTracker" in Microsoft Access through Control panel- administrative tools - Data sources (ODBC)- system DSN tab - Add - Microsoft Access Driver *.mdb - create database. The database is stored in the same file as all the above files.

In order to test the program, we must run the STLogon.java file. All the files compiled sucessfully. The following are the steps which I have taken to double check my work...

1. Double checked the settings for the database in my control panel
2. Wrote a sample program off the web to see if that could connect through the database driver - the program ran sucessfully.
3. Printed the 2 files which I hand coded (MakeDB.java and StockTrackerDB.java) and checked for any entry errors.
4. Copied the files to a flash drive and tried to run the program on another machine. Receievd the same error.

The only other thing I could think of is maybe there is an issue with the way the java files were compiled or possibly using the wrong jdk. I installed TextPad which came with my text book on a cd. Later on, I installed NetBeans with a more current jdk (for troubleshooting purposes). All the files were compiled in TextPad since the installation of NetBeans. I then saved all those compiled files to a flash drive and copied them on another machine which only has TextPad on it. I guess I could try recompiling on the other machine which only has TextPad installed. BTW, both machines have Oracle Express Edition and Microsoft Visual Basic installed on them. I even tried signing in on both machines as an Administrator. There is no way to sign in as Administrator. However, both accounts on both machines have administrative rights.

If any additional source code or files are needed to verify my entries, I would be happy to pass it along. The connection string is coded in the MakeDB.java file.

Any advice would be greatly appreciated.

Attachments
/*
	Chapter 11: The MakeDB Class
	Programmer:	Michele Klinkman
	Date:		11/3/10
	Filename:	MakeDB.java
	Purpose:	To build an initial database for the StockTracker application
*/

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
/*
	Chapter 11:	The Password Class
	Programmer:	Michele Klinkman
	Date:		11/3/10
	Filename:	Password.java
	Purpose:	To provide a reusable Password class which uses PaswordException
*/

import java.util.*;
import java.io.*;

public class Password implements Serializable
{
	final static int MIN_SIZE = 6;
	final static int MAX_SIZE = 15;
		  static int maxHistory = 4;
		  static int expiresNotifyLimit = 3;

	private int maxUses = 120;
	private int remainingUses = maxUses;
	private boolean autoExpires = true;
	private boolean expired = false;

	private ArrayList pswdHistory;


	//Constructors for objects of class Password
	public Password(String newPassord) throws PasswordException
	{
		pswdHistory = new ArrayList(maxHistory);
		set(newPassord);
	}

	public Password(String newPassword, int numMaxUses) throws PasswordException
	{
		pswdHistory = new ArrayList(maxHistory);
		maxUses = numMaxUses;
		remainingUses = numMaxUses;
		set(newPassword);
	}

	public Password(String newPassword, boolean pswdAutoExpires) throws PasswordException
	{
		pswdHistory = new ArrayList(maxHistory);
		autoExpires = pswdAutoExpires;
		set(newPassword);
	}

	public Password(String newPassword, int numMaxUses, boolean pswdAutoExpires) throws PasswordException
	{
		pswdHistory = new ArrayList(maxHistory);
		maxUses = numMaxUses;
		remainingUses = numMaxUses;
		autoExpires = pswdAutoExpires;
		set(newPassword);
	}

    	public boolean getAutoExpires()
	{
		return autoExpires;
	}

	public void setAutoExpires(boolean autoExpires)
	{
		this.autoExpires = autoExpires;
		if(autoExpires)
			remainingUses = maxUses;
	}

	public boolean isExpired()
	{
		return expired;
	}

	public void setExpired(boolean newExpired)
	{
		expired = newExpired;
	}

	public int getExpiresNotifyLimit()
	{
		return expiresNotifyLimit;
	}

	public void setExpiresNotifyLimit(int newNotifyLimit)
	{
		if(newNotifyLimit >= 2 && newNotifyLimit <=20)
			expiresNotifyLimit = newNotifyLimit;
	}

	public int getMaxHistory()
	{
		return maxHistory;
	}

	public void setMaxHistory(int newMaxHistory)
	{
		int overage = 0;
		if(newMaxHistory >= 1 && newMaxHistory <= 10)
		{
			maxHistory = newMaxHistory;
			overage = getHistorySize() - maxHistory;
			if(overage > 0)				// if size > max allowed
			{
				do {
					pswdHistory.remove(0);	// then remove overage number
					overage--;				// of oldest pswds from list
				} while(overage > 0);

				pswdHistory.trimToSize();	// resize capacity to max allowed
			}
		}
	}

	public int getRemainingUses()
	{
		return remainingUses;
	}

	public int getHistorySize()
	{
		return pswdHistory.size();
	}

	public boolean isExpiring()
	{
		boolean expiring = false;

		if(autoExpires && remainingUses <= expiresNotifyLimit)
			expiring = true;

			return expiring;
	}

	// Sets password to a new value: keeps current & previous values in history up to max number
	public void set(String pswd) throws PasswordException
	{
		String encryptPswd;
		boolean pswdAdded = true;

		pswd = pswd.trim();	// remove any leading, trailing white space
		verifyFormat(pswd);	// verify password was entered properly
		encryptPswd = encrypt(pswd); // convert to encrypted form

		if(!pswdHistory.contains(encryptPswd)) // if pswd not in recently used list
		{
			if(pswdHistory.size() == maxHistory) // if list is at max size
			   pswdHistory.remove(0);			// remove 1st, oldest, pswd from list

		pswdAdded = pswdHistory.add(encryptPswd); // add new pswd to end of ArrayList

		if(!pswdAdded)	// should never happen
		   throw new PasswordInternalException("Internal list error - Password not accepted");

		if(expired)			// if pswd has expired,
			expired = false; // reset to not expired

		if(autoExpires)		// if pswd auto expires,
			remainingUses = maxUses; // reset uses to max
	}
	else
		throw new PasswordUsedException("Password recently used");
	}

	// Validates entered password against most recently saved value
	public void validate(String pswd) throws PasswordException
	{
		String encryptPswd;
		String currentPswd;
		int currentPswdIndex;

		verifyFormat(pswd);		// verify password was entered properly
		encryptPswd = encrypt(pswd); // convert to encrypted form

		if(!pswdHistory.isEmpty())	// at least one password entry is in history
		{
			currentPswdIndex = pswdHistory.size()-1;
			currentPswd = (String)pswdHistory.get(currentPswdIndex);

			if(!encryptPswd.equals(currentPswd)) // if not most recent pswd
				throw new PasswordInvalidException("Password is invalid");

			if(expired)
				throw new PasswordExpiredException("Password has expired - please change");

			if(autoExpires)
			{
				--remainingUses;
				if(remainingUses <= 0)
					expired = true;
			}
		}
		else
			throw new PasswordInvalidException("No password on file - list corrupted!"); // should never happen

	new PasswordInvalidException().resetCount(); //resets count of invalid exceptions
	}

	// Verfies password has proper format
	private void verifyFormat(String pswd) throws PasswordException
	{
		boolean numFound = false;

		if(pswd.length() == 0)
			throw new PasswordInvalidFormatException("No password provided!");

		if(pswd.length() < MIN_SIZE)
			throw new PasswordSizeException("Password < mimimum size",pswd.length(),MIN_SIZE,MAX_SIZE);

		if(pswd.length() > MAX_SIZE)
			throw new PasswordSizeException("Password > maximum size",pswd.length(),MIN_SIZE,MAX_SIZE);

		// scan through password to find if at least 1 number is used
		for(int i=0; i < pswd.length() && !numFound; ++i)
			if(Character.isDigit(pswd.charAt(i)))
				numFound = true;

		if(!numFound)
			throw new PasswordInvalidFormatException("Password is invalid - must have at least one numeric digit");
		}

	// Encrypts original password returning new encrypted String
	private String encrypt(String pswd)
	{
		StringBuffer encryptPswd;
		int pswdSize = 0;
		int midpoint = 0;
		int hashCode = 0;

		// swap first and last half of password
		pswdSize = pswd.length();
		midpoint = pswdSize/2;
		encryptPswd = new StringBuffer(pswd.substring(midpoint)	//get last half of pswd
			+ pswd.substring(0,midpoint));	//and concatenate first half

		encryptPswd.reverse();	// reverses order of characters in password

		for(int i=0; i < pswdSize; ++i)	// encrypt each character
			encryptPswd.setCharAt(i, (char)(encryptPswd.charAt(i) & pswd.charAt(i)));

		hashCode = pswd.hashCode();	// hash code for original password
		encryptPswd.append(hashCode);

		return encryptPswd.toString();
	}
}
/*
	Chapter 11:	The STLogon Class
	Programmer:	Michael Mick
	Date:		December 12, 2007
	Filename:	STLogon.java
	Purpose:	Provides a user interface to log on to the StockTracker application
*/

import javax.swing.*;
import javax.swing.border.TitledBorder;
import static javax.swing.JOptionPane.*;			// updated for v5.0
import java.awt.*;
import java.awt.event.*;
import static java.awt.BorderLayout.*;				// updated for v5.0
import java.util.*;
import java.io.*;
import java.sql.*;

public class STLogon extends JFrame implements ActionListener, Activator
{
    StockTrackerDB db;
    User user = null;
    String userID;
    String password;

    JTextField userIDField;
    JPasswordField passwordField;
    JButton jbtLogon;

    public STLogon()
    {
        super("Stock Tracker"); // call super (JFrame) constructor

		int width = 300;
		int height = 100;

        try{
        	db = new StockTrackerDB();
        }
        catch(ClassNotFoundException ex){
				JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "Class not found exception creating database object",
                ERROR_MESSAGE);					// updated for v5.0
   				System.exit(0);
		}
        catch(SQLException ex){
				JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "SQL exception creating database object",
                ERROR_MESSAGE);					// updated for v5.0
   				System.exit(0);
		}

		// define GUI components
        JLabel label1 = new JLabel("User ID: ");
        userIDField = new JTextField(20);

        JLabel label2 = new JLabel("Password:   ");
        passwordField = new JPasswordField(20);
        passwordField.setEchoChar('*');

        jbtLogon = new JButton("Log on");

		// set up GUI
        JPanel userPanel= new JPanel(new BorderLayout());
        userPanel.add(label1,CENTER);
        userPanel.add(userIDField,EAST);

        JPanel pswdPanel= new JPanel(new BorderLayout());
        pswdPanel.add(label2,CENTER);
        pswdPanel.add(passwordField,EAST);

        JPanel buttonPanel= new JPanel(new FlowLayout());
        buttonPanel.add(jbtLogon);

        JPanel contentPanel= new JPanel(new BorderLayout());
        contentPanel.add(userPanel, NORTH);
        contentPanel.add(pswdPanel, CENTER);
        contentPanel.add(buttonPanel, SOUTH);
    	contentPanel.setBorder(new TitledBorder("Log on"));

        setContentPane(contentPanel);

        // add listeners
        jbtLogon.addActionListener(this);

        addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e)
            			{
							try {db.close();
            				}
            				catch(Exception ex)
            				{}
            				System.exit(0);
            			}
            });

		// Enable Enter key for each JButton
		InputMap map;
		map = jbtLogon.getInputMap();
		if (map != null){
			map.put(KeyStroke.getKeyStroke(KeyEvent.VK_ENTER,0,false), "pressed");
			map.put(KeyStroke.getKeyStroke(KeyEvent.VK_ENTER,0,true), "released");
		}

        pack();
		if( width < getWidth())				// prevent setting width too small
		   width = getWidth();
		if(height < getHeight())			// prevent setting height too small
			height = getHeight();
		centerOnScreen(width, height);
    }

  	public void centerOnScreen(int width, int height)
  	{
  	  int top, left, x, y;

  	  // Get the screen dimension
  	  Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();

  	  // Determine the location for the top left corner of the frame
  	  x = (screenSize.width - width)/2;
  	  y = (screenSize.height - height)/2;
  	  left = (x < 0) ? 0 : x;
  	  top = (y < 0) ? 0 : y;

  	  // Set the frame to the specified location & size
	  this.setBounds(left, top, width, height);
  	}

	private boolean validUser(String userID,String password)
				throws PasswordException,SQLException,IOException,ClassNotFoundException
	{
		boolean userOK = false;
		user = db.getUser(userID); // get user object from DB for this ID
		if(user != null)
		{
			user.validate(password); // throws PasswordException
			userOK = true;
			if(user.pswdAutoExpires())	// if tracking uses
				db.updUser(user);		// update DB for this use
		}

	    return userOK;
	}

    private void doStockActivity()throws PasswordException,SQLException,
    									 IOException,ClassNotFoundException
    {
        StockTracker f = new StockTracker(user,this,db);
        f.pack();
        this.setVisible(false);
        f.setVisible(true);
    }

    public void activate()
    {
        this.setVisible(true);
        userIDField.setText("");
        userIDField.requestFocus();
        user = null;
	}

    public void actionPerformed(ActionEvent e)
    {
		try
        {
			userID = userIDField.getText();
            if(userID.equals(""))
            {
   				JOptionPane.showMessageDialog(this,
			    	"Please enter a valid user ID.",
			        "Missing User ID.",
			        ERROR_MESSAGE);					// updated for v5.0
			    userIDField.requestFocus();
			}
			else
			{
				password = new String(passwordField.getPassword());
            	if(password.equals(""))
            	{
   					JOptionPane.showMessageDialog(this,
				    	"Please enter a valid password.",
				        "Missing Password.",
				        ERROR_MESSAGE);					// updated for v5.0
				    passwordField.requestFocus();
				}
				else
				{
					try
					{
						// See if userID exists and validate password
						if(validUser(userID,password))
			        	{
		            	    if(user.pswdIsExpiring())
		 		    	       	JOptionPane.showMessageDialog(this,
		 		    	       				user.getUserID()+" logon successful; "
                    	                   +user.getPswdUses()+" use(s) remaining.");
            				if(e.getSource() == jbtLogon)
		           		       	doStockActivity();
						}
    		        	else
    		        		JOptionPane.showMessageDialog(this, "Invalid user.");
					}
					catch (PasswordExpiredException ex)
					{
						JPasswordField pf1 = new JPasswordField();
						JPasswordField pf2 = new JPasswordField();
						Object[] message1 = new Object[]
								{"Password has expired. Please enter a new password.", pf1};
						Object[] options = new String[] {"OK", "Cancel"};
						JOptionPane op1 = new JOptionPane(message1,
											  WARNING_MESSAGE,				// updated for v5.0
						                      OK_CANCEL_OPTION, null, options);	// updated for v5.0
						JDialog dialog1 = op1.createDialog(null, "Change Password");
						dialog1.setVisible(true);	// updated for v5.0

						if(op1.getValue() != null && options[0].equals(op1.getValue()))
						{
							String pswd1 = new String(pf1.getPassword());
							if(pswd1 != null)
							{
							    Object[] message2 = new Object[]
							    				{"Please verify new password.", pf2};
								JOptionPane op2 = new JOptionPane(message2,
													  WARNING_MESSAGE,  // updated for v5.0
							                          OK_CANCEL_OPTION,	// updated for v5.0
							                                null, options);
								JDialog dialog2 = op2.createDialog(null, "Verify Password");
								dialog2.setVisible(true);	// updated for v5.0
								if(op2.getValue() != null && options[0].equals(op2.getValue()))
								{
									String pswd2 = new String(pf2.getPassword());
									if(pswd2 != null)
									{
										if(pswd1.equals(pswd2))
										{
						        		    user.changePassword(password, pswd1);
											db.updUser(user);
											doStockActivity();
										}
										else
											JOptionPane.showMessageDialog(this,
			    							"Both passwords are not identical.",
									        "Password not changed",
									        ERROR_MESSAGE);					// updated for v5.0
									}
								}
							}
						}
			        }
    		    }
			}
	        userIDField.setText("");
            passwordField.setText("");
            userIDField.requestFocus();
        }// end of try
        catch (PasswordUsedException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "Password Previously Used. Try again.",
                ERROR_MESSAGE);					// updated for v5.0
        }
        catch (PasswordSizeException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "Invalid password size. Try again.",
                ERROR_MESSAGE);					// updated for v5.0
        }
        catch (PasswordInvalidFormatException ex)
        {
            if(ex.getCount() > 2) // allows only 3 tries, then exits program
            	System.exit(0);
            else
            	JOptionPane.showMessageDialog(this,ex.getMessage()+", count:"+ex.getCount(),
            	                                   "Invalid password format. Try again.",
            	    						       ERROR_MESSAGE);	// updated for v5.0
		}
        catch (PasswordInvalidException ex)
        {
            if(ex.getCount() > 2) // allows only 3 tries, then exits program
            	System.exit(0);
            else
            	JOptionPane.showMessageDialog(this,ex.getMessage()+", count:"+ex.getCount(),
            	                                   "Invalid password. Try again.",
            	    						       ERROR_MESSAGE);	// updated for v5.0
		}
        catch (PasswordException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "PasswordException.",
                ERROR_MESSAGE);					// updated for v5.0
        }

        catch (IOException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "IOException.",
                ERROR_MESSAGE);					// updated for v5.0
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(this,
                ex.getMessage(),
                "SQLException.",
                ERROR_MESSAGE);					// updated for v5.0
/*
	Chapter 11:	The StockTrackerDB Class
	Programmer:	Michele Klinkman
	Date:		11/3/10
	Filename:	StockTrackerDB.java
	Purpose:	To provide a data access class for the StockTracker database
*/

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 th
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by ~s.o.s~
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.