Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

JdbcUserAccountDaoImpl.java

By: a guest on Mar 10th, 2010  |  syntax: Java  |  size: 7.48 KB  |  views: 69  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. package com.apress.timesheets.dao.jdbc;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.util.Collection;
  8. import java.util.HashSet;
  9. import java.util.List;
  10. import java.util.Set;
  11. import java.util.logging.Level;
  12. import java.util.logging.Logger;
  13.  
  14. import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
  15. import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
  16. import org.springframework.jdbc.core.support.JdbcDaoSupport;
  17.  
  18. import com.apress.timesheets.dao.UserAccountDao;
  19. import com.apress.timesheets.entity.UserAccount;
  20. import com.apress.timesheets.entity.UserRole;
  21.  
  22. /**
  23.  * Note that this implementation is database-specific.
  24.  *
  25.  * @author Dave Minter
  26.  */
  27. public class JdbcUserAccountDaoImpl extends JdbcDaoSupport implements UserAccountDao {
  28.  
  29.    private static final Logger log = Logger.getAnonymousLogger();
  30.    
  31.    public static final String INSERT_ACCOUNT = "insert into UserAccount(accountName) values (?)";
  32.    public static final String SELECT_LAST_ACCOUNT_ID = "select max(id) from UserAccount";
  33.    public static final String INSERT_ROLE = "insert into UserRole(roleName) values (?)";
  34.    public static final String SELECT_LAST_ROLE_ID = "select max(id) from UserRole";  
  35.    public static final String INSERT_ACCOUNT_ROLE = "insert into account_role(user,role) values(?,?)";
  36.    public static final String DELETE_ACCOUNT_ROLE = "delete from account_role where user = ? and role = ?";
  37.    public static final String DELETE_BY_ID = "delete from UserAccount where id = ?";
  38.    public static final String SELECT_ACCOUNT_BY_ID = "select id, accountName from UserAccount where id = :id";
  39.    public static final String SELECT_ACCOUNT_ID_BY_NAME = "select id from UserAccount where accountName = :name";
  40.    public static final String SELECT_ACCOUNT_EMAIL_BY_NAME = "select email from UserAccount where accountName = :name";  
  41.    public static final String SELECT_ACCOUNTS = "select id, accountName from UserAccount order by accountName";
  42.    public static final String SELECT_ROLES_BY_ACCOUNT = "select id, roleName from UserRole left join account_role on UserRole.id = account_role.role where user = ?";
  43.    public static final String UPDATE_ACCOUNT = "update UserAccount set accountName = :name where id = :id";
  44.    public static final String SELECT_USER_ROLE_ID_BY_NAME = "select id from UserRole where roleName = ?";
  45.  
  46.    final ParameterizedRowMapper<UserAccount> userMapper =
  47.          new ParameterizedRowMapper<UserAccount>() {
  48.       public UserAccount mapRow(ResultSet rs, int rowNum)
  49.          throws SQLException
  50.       {
  51.          UserAccount account = new UserAccount();
  52.          account.setId(rs.getLong("id"));
  53.          account.setAccountName(rs.getString("accountName"));
  54.          return account;
  55.       }
  56.    };
  57.  
  58.    final ParameterizedRowMapper<UserRole> roleMapper =
  59.       new ParameterizedRowMapper<UserRole>() {
  60.       public UserRole mapRow(ResultSet rs, int rowNum)
  61.          throws SQLException
  62.       {
  63.          UserRole role = new UserRole();
  64.          role.setId(rs.getLong("id"));
  65.          role.setRoleName(rs.getString("roleName"));
  66.          return role;
  67.       }
  68.    };
  69.  
  70.    private SimpleJdbcTemplate template;
  71.    
  72.    private SimpleJdbcTemplate getTemplate() {
  73.       if( template == null ) {
  74.          template = new SimpleJdbcTemplate(getJdbcTemplate());        
  75.       }
  76.       return this.template;
  77.    }
  78.    
  79.    public void create(final UserAccount account) {
  80.       // Make the account entity persistent
  81.       getTemplate().update(INSERT_ACCOUNT, account.getAccountName());
  82.       final Long accountId = getTemplate().queryForLong(SELECT_LAST_ACCOUNT_ID);
  83.       account.setId(accountId);
  84.       persistTransientRoles(account);
  85.    }
  86.    
  87.    private void persistTransientRoles(final UserAccount account) {
  88.       // Make any transient role entities persistent
  89.       for( final UserRole role : account.getRoles()) {
  90.          if( role.getId() == null ) {
  91.             getTemplate().update(INSERT_ROLE, role.getRoleName());
  92.             final Long roleId = getTemplate().queryForLong(SELECT_LAST_ROLE_ID);
  93.             role.setId(roleId);
  94.             getTemplate().update(INSERT_ACCOUNT_ROLE, account.getId(),roleId);
  95.          }
  96.       }      
  97.    }
  98.  
  99.    public void delete(final UserAccount account) {
  100.       delete(account.getId());
  101.    }
  102.  
  103.    public void delete(final Long id) {
  104.       getTemplate().update(DELETE_BY_ID, id);
  105.    }
  106.    
  107.    public void traditionalDelete(final Long id) {
  108.       Connection conn = null;
  109.       PreparedStatement stat = null;
  110.       try {
  111.          conn = getDataSource().getConnection();
  112.          stat = conn.prepareStatement(DELETE_BY_ID);
  113.          stat.setLong(1, id);
  114.          stat.execute();
  115.       } catch( final SQLException e ) {
  116.       } finally {
  117.          try {
  118.             if( stat != null) stat.close();
  119.          } catch( SQLException e ) {
  120.             log.log(Level.SEVERE,"Problem closing statement",e);
  121.          }
  122.          try {
  123.             if( conn != null) conn.close();
  124.          } catch( SQLException e ) {
  125.             log.log(Level.SEVERE,"Problem closing connection",e);
  126.          }
  127.       }
  128.    }
  129.  
  130.    public List<UserAccount> list() {
  131.       final List<UserAccount> list =
  132.          getTemplate().query(SELECT_ACCOUNTS, userMapper);
  133.       for(final UserAccount account : list) {
  134.          populateRoles(account);
  135.       }
  136.       return list;
  137.    }
  138.  
  139.    public UserAccount read(final Long id) {
  140.       final UserAccount account =
  141.          getTemplate().
  142.             queryForObject(SELECT_ACCOUNT_BY_ID,userMapper,id);
  143.       populateRoles(account);
  144.       return account;
  145.    }
  146.  
  147.    public UserAccount read(final String accountName) {
  148.       final Long accountId = getTemplate().queryForLong(SELECT_ACCOUNT_ID_BY_NAME, accountName);
  149.       final UserAccount account = new UserAccount(accountName);
  150.       account.setId(accountId);
  151.       populateRoles(account);
  152.       return account;
  153.    }
  154.    
  155.    public UserRole readUserRole(final String roleName) {
  156.       final Long userRoleId = getTemplate().queryForLong(SELECT_USER_ROLE_ID_BY_NAME, roleName);
  157.       final UserRole userRole = new UserRole(roleName);
  158.       userRole.setId(userRoleId);
  159.       return userRole;
  160.    }
  161.  
  162.    public void update(final UserAccount account) {
  163.       // Update the account entity
  164.       getTemplate().update(UPDATE_ACCOUNT, account.getAccountName(), account.getId());
  165.      
  166.       // Update the role relationships
  167.       persistTransientRoles(account);
  168.       removeDeletedRoles(account);
  169.    }
  170.    
  171.    private void removeDeletedRoles(final UserAccount account) {
  172.       final List<UserRole> roleList =
  173.          getTemplate().query(SELECT_ROLES_BY_ACCOUNT,roleMapper,account.getId());
  174.  
  175.       final Set<Long> actual = roleIdSet(roleList);
  176.       final Set<Long> expected = roleIdSet(account.getRoles());
  177.      
  178.       for( final Long roleId : actual ) {
  179.          if( !expected.contains(roleId) ) {
  180.             getTemplate().update(DELETE_ACCOUNT_ROLE, account.getId(),roleId);
  181.          }
  182.       }      
  183.    }
  184.    
  185.    private Set<Long> roleIdSet(final Collection<UserRole> roles) {
  186.       final Set<Long> set = new HashSet<Long>();
  187.       for( final UserRole role : roles ) {
  188.          set.add(role.getId());
  189.       }
  190.       return set;
  191.    }
  192.    
  193.    private void populateRoles(final UserAccount account) {
  194.       final List<UserRole> roles =
  195.          getTemplate().query(
  196.                   SELECT_ROLES_BY_ACCOUNT,roleMapper,account.getId());
  197.       account.getRoles().addAll(roles);
  198.    }
  199. }