Announcement Announcement Module
Collapse
No announcement yet.
JDBC + SqlOperation registry Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC + SqlOperation registry

    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:

    Code:
             registerUpdateOperation&#40;INSERT_OPERATION, 
    			"INSERT INTO users &#40;user_id, name, password, email, status&#41; VALUES &#40;?,?,?,?,?&#41;",
    			new SqlParameter&#40;"userId",Types.INTEGER&#41;,
    			new SqlParameter&#40;"name",Types.VARCHAR&#41;,
    			new SqlParameter&#40;"password",Types.VARCHAR&#41;,
    			new SqlParameter&#40;"email",Types.VARCHAR&#41;,
    			new SqlParameter&#40;"status",Types.VARCHAR&#41;&#41;;
    		
    		RowDataMapper rowToUserDataMapper=createRowToUserDataMapper&#40;&#41;;
    		
    		registerQueryOperation&#40;SELECT_USER_BY_ID, 
    			"SELECT user_id, name, password, email, status FROM users WHERE user_id=?",
    			rowToUserDataMapper,
    			new SqlParameter&#40;"userId",Types.INTEGER&#41;&#41;;
    		
    		registerQueryOperation&#40;SELECT_USER_BY_NAME, 
    			"SELECT user_id, name, password, email, status FROM users WHERE name=?",
    			rowToUserDataMapper,
    			new SqlParameter&#40;"name",Types.VARCHAR&#41;&#41;;
    		
    		registerUpdateOperation&#40;DELETE_OPERATION, 
    			"DELETE FROM users WHERE user_id=?", 
    			new SqlParameter&#40;"userId",Types.INTEGER&#41;&#41;;
    plus a rowToUserDataMapper (used to avoid the need of subclassing on both query operations):

    Code:
    private RowDataMapper createRowToUserDataMapper&#40;&#41; &#123;
    		return new RowDataMapper&#40;&#41; &#123;
    			public Object mapRow&#40;ResultSet rs,int rowIndex&#41; throws SQLException &#123;
    				User user=new User&#40;&#41;;
    				user.setUserId&#40;rs.getInt&#40;"user_id"&#41;&#41;;
    				user.setName&#40;rs.getString&#40;"name"&#41;&#41;;
    				user.setPassword&#40;rs.getString&#40;"password"&#41;&#41;;
    				user.setEmailAddress&#40;rs.getString&#40;"email"&#41;&#41;;
    				user.setUserStatus&#40;
    						UserStatusFactory.getUserStatus&#40;rs.getString&#40;"status"&#41;&#41;&#41;;
    				
    				return user;
    			&#125;
    		&#125;;		
    	&#125;
    To use the operations, it simply ended with these little lines:

    Code:
            public void persistUser&#40;User user&#41; throws UserAlreadyExistException &#123;
    		if&#40;!isUserExisting&#40;user.getName&#40;&#41;&#41;&#41; &#123;
    			user.setUserId&#40;getNextUserId&#40;&#41;&#41;;
    			executeUpdate&#40;INSERT_OPERATION,
    					user.getUserId&#40;&#41;,
    					user.getName&#40;&#41;,
    					user.getPassword&#40;&#41;,
    					user.getEmailAddress&#40;&#41;,
    					UserStatusFactory.getUserStatusDescription&#40;user.getUserStatus&#40;&#41;&#41;&#41;;
    		&#125;
    		else
    			throw new UserAlreadyExistException&#40;&#41;;
    	&#125;
    
    	private int getNextUserId&#40;&#41; &#123;
    		return getNextSequenceNumber&#40;"users","user_id"&#41;;
    	&#125;
    
    	private boolean isUserExisting&#40;String username&#41; &#123;
    		return getUser&#40;username&#41;!=null;
    	&#125;
    	
    	public User getUser&#40;int userId&#41; &#123;
    		return &#40;User&#41;findObject&#40;SELECT_USER_BY_ID,userId&#41;;
    	&#125;
    
    	public User getUser&#40;String username&#41; &#123;
    		return &#40;User&#41;findObject&#40;SELECT_USER_BY_NAME,username&#41;;
    	&#125;
    
    	public void deleteUser&#40;User user&#41; &#123;
    		executeUpdate&#40;DELETE_OPERATION,user.getUserId&#40;&#41;&#41;;
    	&#125;
    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)... .
Working...
X