FileDocCategorySizeDatePackage
UserDBJDBC.javaAPI DocExample5664Sat Oct 27 15:52:34 BST 2001jabadot

UserDBJDBC

public class UserDBJDBC extends UserDB
A UserDB using JDBC and a relational DBMS.. We use the inherited getUser ("Find the User object for a given nickname") since we keep everything in memory in this version.

(Omit source code)

Fields Summary
protected PreparedStatement
setPasswordStatement
protected PreparedStatement
addUserStmt
protected PreparedStatement
setLastLoginStmt
protected PreparedStatement
deleteUserStmt
static final String
SQL_INSERT_USER
insert the 19 fields into the user database
Constructors Summary
protected UserDBJDBC()
Default constructor


	   
	  
	    
		super();


		// Load the database driver
		String className = JDConstants.getProperty("jabadot.jabadb.driver");
		Class.forName(className);

		Connection conn = DriverManager.getConnection(
			JDConstants.getProperty("jabadot.jabadb.url"),
			JDConstants.getProperty("jabadot.jabadb.user"),
			JDConstants.getProperty("jabadot.jabadb.password"));

		Statement stmt = conn.createStatement();

		ResultSet rs = stmt.executeQuery("select * from users");

		while (rs.next()) {
			//name:password:fullname:City:Prov:Country:privs

			// Get the fields from the query.
			// Should be an Entity EJB with CMP: this is unnecessarily 
			// chummy with the SQL. See CreateUserDatabase.java for field#'s!
			String nick = rs.getString(1);
			String pass = rs.getString(2);
			String first = rs.getString(3);
			String last = rs.getString(4);
			String email = rs.getString(5);
			String company = rs.getString(6);
			String addr1 = rs.getString(7);
			String addr2 = rs.getString(8);
			String city = rs.getString(9);
			String prov = rs.getString(10);
			String ctry = rs.getString(11);
			// java.sql.Date credt = rs.getDate(12);
			// java.sql.Date lastlog = rs.getDate(13);
			String skin = rs.getString(14);
			String job = rs.getString(15);
			String os = rs.getString(16);
			String gui = rs.getString(17);
			String lang = rs.getString(18);
			int iprivs = rs.getInt(19);

			// Construct a user object from the fields
			User u = new User(nick, pass, first, last, email,
				company, addr1, addr2, city, prov, ctry, 
				skin, job, os, gui, lang);
			// u.setCreationDate(credt);
			// u.setLastLoginDate(lastlog);
			u.setPrivs(iprivs);

			// Add it to the in-memory copy.
			users.add(u);
		}
		stmt.close();
		rs.close();		// All done with that resultset

		// Set up the PreparedStatements now so we don't have to
		// re-create them each time needed.
		addUserStmt = conn.prepareStatement(SQL_INSERT_USER);
		setPasswordStatement = conn.prepareStatement(
			"update users SET password = ? where name = ?");
		setLastLoginStmt = conn.prepareStatement(
			"update users SET lastLogin = ? where name = ?");
		deleteUserStmt = conn.prepareStatement(
			"delete from users where name = ?");
Methods Summary
public synchronized voidaddUser(User nu)
Add one user to the list, both in-memory and on disk.

		// Add it to the in-memory list
		super.addUser(nu);

		// Copy fields from user to DB
		// XXX WAY INCOMPLETE NOW
		addUserStmt.setString(1, nu.name);
		addUserStmt.setString(2, nu.password);
		addUserStmt.setString(3, nu.firstName); 
		addUserStmt.setString(4, nu.lastName);
		addUserStmt.setString(5, nu.email);
		addUserStmt.setString(6, nu.company);
		addUserStmt.setString(7, nu.address);
		addUserStmt.setString(8, nu.address2);
		addUserStmt.setString(9, nu.city);
		addUserStmt.setString(10, nu.prov);
		addUserStmt.setString(11, nu.country);
		// java.sql.Date now = new java.sql.Date(System.currentTimeMillis());
		// addUserStmt.setDate(12, now);
		// addUserStmt.setDate(13, now);
		addUserStmt.setString(14, nu.jobDescr);
		addUserStmt.setString(15, nu.os);
		addUserStmt.setString(16, nu.unixGUI);
		addUserStmt.setString(17, nu.proglang);
		addUserStmt.setString(18, nu.skin);
		addUserStmt.setInt   (19, nu.getPrivs());

		// Store in persistent DB
		addUserStmt.executeUpdate();
public voiddeleteUser(java.lang.String nick)

		// Find the user object
		User u = getUser(nick);
		if (u == null) {
			throw new SQLException("User " + nick + " not in in-memory DB");
		}
		deleteUserStmt.setString(1, nick);
		int n = deleteUserStmt.executeUpdate();
		if (n != 1) {	// not just one row??
			/*CANTHAPPEN */
			throw new SQLException("ERROR: deleted " + n + " rows!!");
		}

		// IFF we deleted it from the DB, also remove from the in-memory list
		users.remove(u);
public synchronized voidsetLoginDate(java.lang.String nick, java.util.Date date)
Update the Last Login Date field.

	
		// Find the user object
		User u = getUser(nick);

		// Change it in DB first; if this fails, the date in
		// the in-memory copy won't be changed either.
		// Have to convert from java.util.Date to java.sql.Date here.
		// Would be more efficient to use java.sql.Date everywhere.
		setLastLoginStmt.setDate(1, new java.sql.Date(date.getTime()));
		setLastLoginStmt.setString(2, nick);
		setLastLoginStmt.executeUpdate();

		// Change it in-memory
		u.setLastLoginDate(date);
public synchronized voidsetPassword(java.lang.String nick, java.lang.String newPass)


		// Find the user object
		User u = getUser(nick);

		// Change it in DB first; if this fails, the info in
		// the in-memory copy won't be changed either.
		setPasswordStatement.setString(1, newPass);
		setPasswordStatement.setString(2, nick);
		setPasswordStatement.executeUpdate();

		// Change it in-memory
		u.setPassword(newPass);