JDBC + SqlOperation registry

[Martin Kersten]

Hi there,

I played with iBatis lately, and I liked it. I also created a jdbc version (simple userDAO (manager) stuff) to compare both versions. Since I use JDK 1.5, I got a problem with List vs List<Object> type-safety and so my code lighted up like a christmas tree (yellow warning signes everywhere). So I also tried to limit the impact.

At the end I got a SqlOperation registry where I can now register certain operations. Registering the sample user operations looks like this:

         registerUpdateOperation(INSERT_OPERATION, 
			"INSERT INTO users (user_id, name, password, email, status) VALUES (?,?,?,?,?)",
			new SqlParameter("userId",Types.INTEGER),
			new SqlParameter("name",Types.VARCHAR),
			new SqlParameter("password",Types.VARCHAR),
			new SqlParameter("email",Types.VARCHAR),
			new SqlParameter("status",Types.VARCHAR));
		
		RowDataMapper rowToUserDataMapper=createRowToUserDataMapper();
		
		registerQueryOperation(SELECT_USER_BY_ID, 
			"SELECT user_id, name, password, email, status FROM users WHERE user_id=?",
			rowToUserDataMapper,
			new SqlParameter("userId",Types.INTEGER));
		
		registerQueryOperation(SELECT_USER_BY_NAME, 
			"SELECT user_id, name, password, email, status FROM users WHERE name=?",
			rowToUserDataMapper,
			new SqlParameter("name",Types.VARCHAR));
		
		registerUpdateOperation(DELETE_OPERATION, 
			"DELETE FROM users WHERE user_id=?", 
			new SqlParameter("userId",Types.INTEGER));

plus a rowToUserDataMapper (used to avoid the need of subclassing on both query operations):

private RowDataMapper createRowToUserDataMapper() {
		return new RowDataMapper() {
			public Object mapRow(ResultSet rs,int rowIndex) throws SQLException {
				User user=new User();
				user.setUserId(rs.getInt("user_id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmailAddress(rs.getString("email"));
				user.setUserStatus(
						UserStatusFactory.getUserStatus(rs.getString("status")));
				
				return user;
			}
		};		
	}

To use the operations, it simply ended with these little lines:

        public void persistUser(User user) throws UserAlreadyExistException {
		if(!isUserExisting(user.getName())) {
			user.setUserId(getNextUserId());
			executeUpdate(INSERT_OPERATION,
					user.getUserId(),
					user.getName(),
					user.getPassword(),
					user.getEmailAddress(),
					UserStatusFactory.getUserStatusDescription(user.getUserStatus()));
		}
		else
			throw new UserAlreadyExistException();
	}

	private int getNextUserId() {
		return getNextSequenceNumber("users","user_id");
	}

	private boolean isUserExisting(String username) {
		return getUser(username)!=null;
	}
	
	public User getUser(int userId) {
		return (User)findObject(SELECT_USER_BY_ID,userId);
	}

	public User getUser(String username) {
		return (User)findObject(SELECT_USER_BY_NAME,username);
	}

	public void deleteUser(User user) {
		executeUpdate(DELETE_OPERATION,user.getUserId());
	}

I also extended the JdbcDaoSupport class to hide the registry aspect.

Pros:

• - No inner classes used
  - quite descriptive (single place where to setup operations)

Contra:

• - I dont like the use of String constants (like DELETE_OPERATION) to identify the operations, maybe I just drop the registry and go with a factory instead

I currently like this one but I am not sure, if this is a way to go any further. Did anyone tried such an approach, too? What was the outcome?


Cheers,

Martin (Kersten)

PS: Additionally it would be quite possible to inject the sql statements using dependency injection or using resource files to map them from configuration file (xml etc)... .