FileDocCategorySizeDatePackage
UserDBJDBC.javaAPI DocExample6288Wed Jun 16 21:54:58 BST 2004None

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.

ToDo: Consider whether this should be an Entity EJB.

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


	   
	  
	    
		super();

System.out.println("UserDBJDBC.<init> starting...");
/*
System.out.println("Getting InitialContext");
		Context ctx = new InitialContext();
System.out.println("Getting JNDI Context");
		Context env = (Context)ctx.lookup("java:comp/env");
System.out.println("Getting DataSource");
		DataSource ds = (DataSource)env.lookup("jdbc/jabadotDB");
System.out.println("Getting Connection");
		Connection conn = ds.getConnection();
System.out.println("Connection = " + conn);
*/
System.out.println("Loading Driver Class");
try {
	Class.forName("org.hsqldb.jdbcDriver");
} catch (ClassNotFoundException ex) {
	System.out.println("FAILED: " + ex.toString());
	throw new IllegalStateException(ex.toString());
}
System.out.println("Getting Connection");
	Connection conn = DriverManager.getConnection(
		"jdbc:hsqldb:/home/ian/src/jabadot/WEB-INF/jabadot",
		"jabadmin", "fredonia");

		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.
			// Could be an Entity EJB with CMP: this is unnecessarily 
			// chummy with the SQL. See CreateUserDatabase.java for field#'s!
			int i = 1;
			String nick = rs.getString(i++).trim();
			String pass = rs.getString(i++).trim();
System.err.println(nick + " (" + pass + ")");
			String first = rs.getString(i++);
			String last = rs.getString(i++);
			String email = rs.getString(i++);
			String city = rs.getString(i++);
			String prov = rs.getString(i++);
			String ctry = rs.getString(i++);
			java.sql.Date credt = rs.getDate(i++);
			java.sql.Date lastlog = rs.getDate(i++);
			String skin = rs.getString(i++);
			boolean editPrivs = rs.getBoolean(i++);
			boolean adminPrivs = rs.getBoolean(i++);

			// Construct a user object from the fields
System.out.println("Constructing User object");
			User u = new User(nick, pass, first, last, email,
				prov, ctry, credt, lastlog,
				skin, editPrivs, adminPrivs);

System.out.println("Adding User object " + u + " to " + users);
			// Add it to the in-memory copy.
			users.add(u);
System.err.println("User " + nick + "; pass " + pass.charAt(0));
		}
		rs.close();		// All done with that resultset
		stmt.close();

		// 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
		int i = 1;
		addUserStmt.setString(i++, nu.name);
		addUserStmt.setString(i++, nu.password);
		addUserStmt.setString(i++, nu.firstName); 
		addUserStmt.setString(i++, nu.lastName);
		addUserStmt.setString(i++, nu.email);
		addUserStmt.setString(i++, nu.city);
		addUserStmt.setString(i++, nu.prov);
		addUserStmt.setString(i++, nu.country);
		java.sql.Date now = new java.sql.Date(System.currentTimeMillis());
		addUserStmt.setDate(i++, now);
		addUserStmt.setDate(i++, now);
		addUserStmt.setString(i++, nu.skin);
		addUserStmt.setBoolean(i++, false);
		addUserStmt.setBoolean(i++, false);
		--i;

		if (i != 13) {
			System.out.println("Warning: not enough fields set! i = " + i);
		}

		// 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);