I would like to see some psuedo-code or details for how you avoid "manual result set handling".No pseudo-code, bunky. This here's th' real stuff.
This is the JDBC equivalent of HibUserManager, albeit an older version of the interface. Note that even so, this is much better than "straight" JDBC in that it has been Springified. Raw JDBC has a lot more try/catch logic.
"mapRow()" is an example of manual result set handling. There will be one line per column in the table, at least.
\npublic class JdbcUserManager implements UserManager\n{\n private DataSource mDatasource;\n\n\tprivate HashMap mUserCache = new HashMap();\n\n\tprivate UserQuery mUserNicknameRetriever = null;\n\n\tprivate UserQuery mUserIdRetriever = null;\n\n\tprivate UserQuery mAllUsersRetriever = null;\n\n\tprivate SqlFunction mUserLogin = null;\n\n\tprivate SqlUpdate mUserCreater = null;\n\n\tprivate SqlUpdate mUserRemover = null;\n\n\tprivate SqlUpdate mUserUpdatePassword = null;\n\n\tprivate SqlFunction mUserCounter = null;\n\n\tprivate SqlFunction mUserExists = null;\n\n\tprivate SqlFunction mActiveUserCounter = null;\n\n\tprivate SqlUpdate mUserPresent = null;\n\n\tprivate UserPropertyQuery mUserPropertyRetriever = null;\n\n\tprivate UserPropertiesQuery mAllUserPropertiesRetriever = null;\n\n\tprivate SqlFunction mUserPropertySetter = null;\n\n public List getUserList()\n\t\t{\n\t\t\treturn mAllUsersRetriever.execute();\n\t\t}\n\n public User getUserByNickname(String nickname)\n\t\t{\n\t\t\tUser user = (User) mUserCache.get(nickname);\n\n\t\t\tif (user == null)\n\t\t\t\t{\n\t\t\t\t\tuser = (User) mUserNicknameRetriever.findObject(nickname);\n\t\t\t\t\tif (user != null)\n\t\t\t\t\t\tmUserCache.put(nickname, user);\n\t\t\t\t}\n\n\t\t\treturn user;\n\t\t}\n\n public User getUserById(int id)\n\t\t{\n\t\t\treturn (User) mUserIdRetriever.findObject(id);\n\t\t}\n\n public boolean checkLogin(User user)\n\t\t{\n\t\t\tif (mUserLogin.runGeneric(new Object[] { user.getNickname(), user.getEncryptedPassword() }) == null)\n\t\t\t\treturn false;\n\t\t\telse\n\t\t\t\treturn true;\n\t\t}\n\n public void createNewUser(User user)\n\t\t{\n\t\t\tString password = user.getEncryptedPassword();\n\t\t\tint count = mUserCreater.update(new Object[] { user.getNickname(), password });\n\t\t}\n\n\tpublic void saveUser(User user)\n\t\t{\n\t\t\tif (userExists(user))\n\t\t\t\t{\n\t\t\t\t\tif (user.getEncryptedPassword() != null && user.getEncryptedPassword().length() > 0)\n\t\t\t\t\t\tmUserUpdatePassword.update(user.getEncryptedPassword(), user.getNickname());\n\t\t\t\t}\n\t\t\telse\n\t\t\t\t{\n\t\t\t\t\tcreateNewUser(user);\n\t\t\t\t}\n\n\t\t\tMap props = user.getProperties();\n\t\t\tif (props != null)\n\t\t\t\t{\n\t\t\t\t\tSet keys = props.keySet();\n\t\t\t\t\tIterator iter = keys.iterator();\n\t\t\t\t\twhile (iter.hasNext())\n\t\t\t\t\t\t{\n\t\t\t\t\t\t\tString key = (String) iter.next();\n\t\t\t\t\t\t\tsetUserProperty(user.getId(), key, (String) props.get(key));\n\t\t\t\t\t\t}\n\t\t\t\t}\n\n\t\t\tmUserCache.remove(user.getNickname());\n\t\t}\n\n public void removeUser(User user)\n\t\t{\n\t\t\tlogger.info("deleting user: " + user.getNickname());\n\n\t\t\tint count = mUserRemover.update(user.getNickname());\n\t\t\tmUserCache.remove(user.getNickname());\n\t\t}\n\n public void userPresent(User user)\n\t\t{\n\t\t\tint count = mUserPresent.update(new Object[] { new Timestamp(user.getLastPresent().getTime()),\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t user.getNickname() } );\n\t\t}\n\n\tpublic int getUserCount()\n\t\t{\n\t\t\treturn mUserCounter.run();\n\t\t}\n\n\tpublic int getActiveUserCount()\n\t\t{\n\t\t\treturn mActiveUserCounter.run(new Object[] {"10 minutes"});\n\n\t\t}\n\n\tpublic boolean userExists(String nickname)\n\t\t{\n\t\t\treturn mUserExists.run(new Object[] {nickname} ) == 1;\n\t\t}\n\n\tpublic boolean userExists(User user)\n\t\t{\n\t\t\treturn userExists(user.getNickname());\n\t\t}\n\n\tpublic Map getUserProperties(User user)\n\t\t{\n\t\t\treturn mAllUserPropertiesRetriever.execute(user.getId());\n\t\t}\n\n\tpublic String getUserProperty(int id , String name)\n\t\t{\n\t\t\treturn (String) mUserPropertyRetriever.findObject(\n\t\t\t\tnew Object[] { new Integer(id), name }\n\t\t\t\t);\n\t\t}\n\n\tpublic void setUserProperty(int id, String name, String value)\n\t\t{\n\t\t\tint count = mUserPropertySetter.run(\n\t\t\t\tnew Object[] { new Integer(id), name, value }\n\t\t\t\t);\n\t\t}\n\n\tpublic void clearUserCache()\n\t\t{\n\t\t\tmUserCache = new HashMap();\n\t\t}\n\n protected class UserQuery extends MappingSqlQuery\n\t{\n protected UserQuery(DataSource ds, String sql)\n\t\t\t{\n\t\t\t\tsuper(ds, sql);\n\t\t\t}\n \n protected Object mapRow(ResultSet rs, int rowNum) throws SQLException\n\t\t\t{\n\t\t\t\tString nickname = rs.getString("nickname");\n\t\t\t\tUser user = (User) mUserCache.get(nickname);\n\t\t\t\t\n\t\t\t\tif (user == null)\n\t\t\t\t\t{\n\t\t\t\t\t\tuser = new JdbcUser(JdbcUserManager.this, rs);\n\n\t\t\t\t\t\tuser.setProperties(JdbcUserManager.this.getUserProperties(user));\n\n\t\t\t\t\t\tmUserCache.put(nickname, user);\n\t\t\t\t\t}\n\n\t\t\t\treturn user;\n\t\t\t}\n\n }\n\n protected class UserPropertyQuery extends MappingSqlQuery\n\t{\n protected UserPropertyQuery(DataSource ds, String sql)\n\t\t\t{\n\t\t\t\tsuper(ds, sql);\n\t\t\t}\n \n protected Object mapRow(ResultSet rs, int rowNum) throws SQLException\n\t\t\t{\n\t\t\t\treturn rs.getString("value");\n\t\t\t}\n\n }\n\n\tpublic class UserPropertiesQuery extends SqlOperation\n\t{\n\t\tpublic UserPropertiesQuery(DataSource ds, String sql)\n\t\t\t{\n\t\t\t\tsetDataSource(ds);\n\t\t\t\tsetSql(sql);\n\t\t\t}\n\n\t\tpublic Map execute(int id)\n\t\t\tthrows DataAccessException\n\t\t\t{\n\t\t\t\tPropertyHandler handler = new PropertyHandler();\n\t\t\t\tgetJdbcTemplate().query(newPreparedStatementCreator(new Object[] { new Integer(id) } ), handler);\n\t\t\t\treturn handler.getResults();\n\t\t\t}\n\n\t\tprivate class PropertyHandler implements RowCallbackHandler\n\t\t{\n\t\t\tprivate Map mResults = new HashMap();\n\n\t\t\tpublic void processRow(java.sql.ResultSet rs)\n\t\t\t\tthrows SQLException\n\t\t\t\t{\n\t\t\t\t\tmResults.put(rs.getString("name"), rs.getString("value"));\n\t\t\t\t}\n\n\t\t\tMap getResults() { return mResults; }\n\t\t}\n\t}\n\n\tprivate static final String GET_ALL_USERS_SQL = \n\t"select user_id, nickname, created, last_present from iwethey_user order by nickname";\n\n\tprivate static final String GET_USER_SQL = \n\n\t"select user_id, nickname, created, last_present from iwethey_user where nickname = ?";\n\n\tprivate static final String GET_USER_ID_SQL = \n\t"select user_id, nickname, created, last_present from iwethey_user where user_id = ?";\n\n\tprivate static final String USER_LOGIN_SQL = \n\t"select 1 from iwethey_user where nickname = ? and password = ?";\n\n\tprivate static final String CREATE_USER_SQL = \n\t"insert into iwethey_user ( nickname, password ) values (?, ?)";\n\n\tprivate static final String REMOVE_USER_SQL = \n\t"delete from iwethey_user where nickname = ?";\n\n\tprivate static final String COUNT_USERS_SQL = \n\t"select count(1) from iwethey_user";\n\n\tprivate static final String USER_EXISTS_SQL = \n\t"select count(1) from iwethey_user where nickname = ?";\n\n\tprivate static final String COUNT_ACTIVE_USERS_SQL = \n\t"select count(nickname) as usercount from iwethey_user where ('now' - last_present) < interval ?";\n\n\tprivate static final String USER_PRESENT_SQL = \n\t"update iwethey_user set last_present = ? where nickname = ?";\n\n\tprivate static final String USER_UPDATE_PASSWORD_SQL = \n\t"update iwethey_user set password = ? where nickname = ?";\n\n\tprivate static final String GET_USER_PROPERTY_SQL = \n\t"select value from user_property where user_id = ? and name = ?";\n\n\tprivate static final String GET_ALL_USER_PROPERTIES_SQL = \n\t"select user_id, name, value from user_property where user_id = ?";\n\n\tprivate static final String SET_USER_PROPERTY_SQL = \n\t"select setUserProperty(?, ?, ?)";\n\n public void setDataSource(DataSource ds)\n\t\t{\n\t\t\tmDatasource = ds;\n\n\t\t\tmAllUsersRetriever = new UserQuery(ds, GET_ALL_USERS_SQL);\n\t\t\tmAllUsersRetriever.compile();\n\n\t\t\tmUserNicknameRetriever = new UserQuery(ds, GET_USER_SQL);\n\t\t\tmUserNicknameRetriever.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserNicknameRetriever.compile();\n\n\t\t\tmUserIdRetriever = new UserQuery(ds, GET_USER_ID_SQL);\n\t\t\tmUserIdRetriever.declareParameter(new SqlParameter(java.sql.Types.INTEGER));\n\t\t\tmUserIdRetriever.compile();\n\n\t\t\tmUserLogin = new SqlFunction(ds, USER_LOGIN_SQL);\n\t\t\tmUserLogin.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserLogin.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserLogin.compile();\n\n\t\t\tmUserCreater = new SqlUpdate(ds, CREATE_USER_SQL);\n\t\t\tmUserCreater.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserCreater.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserCreater.compile();\n\n\t\t\tmUserRemover = new SqlUpdate(ds, REMOVE_USER_SQL);\n\t\t\tmUserRemover.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserRemover.compile();\n\n\t\t\tmUserPresent = new SqlUpdate(ds, USER_PRESENT_SQL);\n\t\t\tmUserPresent.declareParameter(new SqlParameter(java.sql.Types.TIMESTAMP));\n\t\t\tmUserPresent.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserPresent.compile();\n\n\t\t\tmUserUpdatePassword = new SqlUpdate(ds, USER_UPDATE_PASSWORD_SQL);\n\t\t\tmUserUpdatePassword.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserUpdatePassword.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserUpdatePassword.compile();\n\n\t\t\tmUserPropertyRetriever = new UserPropertyQuery(ds, GET_USER_PROPERTY_SQL);\n\t\t\tmUserPropertyRetriever.declareParameter(new SqlParameter(java.sql.Types.INTEGER));\n\t\t\tmUserPropertyRetriever.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserPropertyRetriever.compile();\n\n\t\t\tmAllUserPropertiesRetriever = new UserPropertiesQuery(ds, GET_ALL_USER_PROPERTIES_SQL);\n\t\t\tmAllUserPropertiesRetriever.declareParameter(new SqlParameter(java.sql.Types.INTEGER));\n\t\t\tmAllUserPropertiesRetriever.compile();\n\n\t\t\tmUserPropertySetter = new SqlFunction(ds, SET_USER_PROPERTY_SQL);\n\t\t\tmUserPropertySetter.declareParameter(new SqlParameter(java.sql.Types.INTEGER));\n\t\t\tmUserPropertySetter.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserPropertySetter.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserPropertySetter.compile();\n\n\t\t\tmUserCounter = new SqlFunction(ds, COUNT_USERS_SQL);\n\t\t\tmUserCounter.compile();\n\n\t\t\tmUserExists = new SqlFunction(ds, USER_EXISTS_SQL);\n\t\t\tmUserExists.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmUserExists.compile();\n\n\t\t\tmActiveUserCounter = new SqlFunction(ds, COUNT_ACTIVE_USERS_SQL);\n\t\t\tmActiveUserCounter.declareParameter(new SqlParameter(java.sql.Types.VARCHAR));\n\t\t\tmActiveUserCounter.compile();\n\t\t}\n}\n
In order to have lazy instantiation and caching, you need the JdbcUser object as well:
\npublic class JdbcUser extends User\n{\n\tprivate UserManager mManager = null;\n\n\tpublic JdbcUser() { }\n\n\tpublic JdbcUser(User user)\n\t\t{\n\t\t\tsuper(user);\n\t\t}\n\n\tpublic JdbcUser(UserManager manager)\n\t\t{\n\t\t\tsuper(manager);\n\t\t}\n\n\tpublic JdbcUser(UserManager manager, ResultSet rs)\n\t\tthrows SQLException\n\t\t{\n\t\t\tsuper(manager);\n\n\t\t\tsetId(rs.getInt("user_id"));\n\t\t\tsetNickname(rs.getString("nickname"));\n\t\t\tsetCreated(rs.getTimestamp("created"));\n\t\t\tsetLastPresent(rs.getTimestamp("last_present"));\n\t\t}\n\n\tpublic JdbcUser(UserManager manager, String nickname, String password)\n\t\t{\n\t\t\tsuper(manager, nickname, password);\n\t\t}\n\n\tpublic Map getProperties()\n\t\t{\n\t\t\tMap props = super.getProperties();\n\n\t\t\tif (props == null || props.size() == 0)\n\t\t\t\t{\n\t\t\t\t\tif (mManager != null)\n\t\t\t\t\t\tprops = ((JdbcUserManager) mManager).getUserProperties(this);\n\n\t\t\t\t\tif (props == null)\n\t\t\t\t\t\tprops = new HashMap();\n\t\t\t\t}\n\n\t\t\tsetProperties(props);\n\t\t\treturn props;\n\t\t}\n}\n
Note that the JdbcUser is actually NOT serializable, which prevents it being used in sessions and the like.