Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package com.apress.timesheets.dao.jdbc;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.Collection;
- import java.util.HashSet;
- import java.util.List;
- import java.util.Set;
- import java.util.logging.Level;
- import java.util.logging.Logger;
- import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
- import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
- import org.springframework.jdbc.core.support.JdbcDaoSupport;
- import com.apress.timesheets.dao.UserAccountDao;
- import com.apress.timesheets.entity.UserAccount;
- import com.apress.timesheets.entity.UserRole;
- /**
- * Note that this implementation is database-specific.
- *
- * @author Dave Minter
- */
- public class JdbcUserAccountDaoImpl extends JdbcDaoSupport implements UserAccountDao {
- private static final Logger log = Logger.getAnonymousLogger();
- public static final String INSERT_ACCOUNT = "insert into UserAccount(accountName) values (?)";
- public static final String SELECT_LAST_ACCOUNT_ID = "select max(id) from UserAccount";
- public static final String INSERT_ROLE = "insert into UserRole(roleName) values (?)";
- public static final String SELECT_LAST_ROLE_ID = "select max(id) from UserRole";
- public static final String INSERT_ACCOUNT_ROLE = "insert into account_role(user,role) values(?,?)";
- public static final String DELETE_ACCOUNT_ROLE = "delete from account_role where user = ? and role = ?";
- public static final String DELETE_BY_ID = "delete from UserAccount where id = ?";
- public static final String SELECT_ACCOUNT_BY_ID = "select id, accountName from UserAccount where id = :id";
- public static final String SELECT_ACCOUNT_ID_BY_NAME = "select id from UserAccount where accountName = :name";
- public static final String SELECT_ACCOUNT_EMAIL_BY_NAME = "select email from UserAccount where accountName = :name";
- public static final String SELECT_ACCOUNTS = "select id, accountName from UserAccount order by accountName";
- 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 = ?";
- public static final String UPDATE_ACCOUNT = "update UserAccount set accountName = :name where id = :id";
- public static final String SELECT_USER_ROLE_ID_BY_NAME = "select id from UserRole where roleName = ?";
- final ParameterizedRowMapper<UserAccount> userMapper =
- new ParameterizedRowMapper<UserAccount>() {
- public UserAccount mapRow(ResultSet rs, int rowNum)
- throws SQLException
- {
- UserAccount account = new UserAccount();
- account.setId(rs.getLong("id"));
- account.setAccountName(rs.getString("accountName"));
- return account;
- }
- };
- final ParameterizedRowMapper<UserRole> roleMapper =
- new ParameterizedRowMapper<UserRole>() {
- public UserRole mapRow(ResultSet rs, int rowNum)
- throws SQLException
- {
- UserRole role = new UserRole();
- role.setId(rs.getLong("id"));
- role.setRoleName(rs.getString("roleName"));
- return role;
- }
- };
- private SimpleJdbcTemplate template;
- private SimpleJdbcTemplate getTemplate() {
- if( template == null ) {
- template = new SimpleJdbcTemplate(getJdbcTemplate());
- }
- return this.template;
- }
- public void create(final UserAccount account) {
- // Make the account entity persistent
- getTemplate().update(INSERT_ACCOUNT, account.getAccountName());
- final Long accountId = getTemplate().queryForLong(SELECT_LAST_ACCOUNT_ID);
- account.setId(accountId);
- persistTransientRoles(account);
- }
- private void persistTransientRoles(final UserAccount account) {
- // Make any transient role entities persistent
- for( final UserRole role : account.getRoles()) {
- if( role.getId() == null ) {
- getTemplate().update(INSERT_ROLE, role.getRoleName());
- final Long roleId = getTemplate().queryForLong(SELECT_LAST_ROLE_ID);
- role.setId(roleId);
- getTemplate().update(INSERT_ACCOUNT_ROLE, account.getId(),roleId);
- }
- }
- }
- public void delete(final UserAccount account) {
- delete(account.getId());
- }
- public void delete(final Long id) {
- getTemplate().update(DELETE_BY_ID, id);
- }
- public void traditionalDelete(final Long id) {
- Connection conn = null;
- PreparedStatement stat = null;
- try {
- conn = getDataSource().getConnection();
- stat = conn.prepareStatement(DELETE_BY_ID);
- stat.setLong(1, id);
- stat.execute();
- } catch( final SQLException e ) {
- } finally {
- try {
- if( stat != null) stat.close();
- } catch( SQLException e ) {
- log.log(Level.SEVERE,"Problem closing statement",e);
- }
- try {
- if( conn != null) conn.close();
- } catch( SQLException e ) {
- log.log(Level.SEVERE,"Problem closing connection",e);
- }
- }
- }
- public List<UserAccount> list() {
- final List<UserAccount> list =
- getTemplate().query(SELECT_ACCOUNTS, userMapper);
- for(final UserAccount account : list) {
- populateRoles(account);
- }
- return list;
- }
- public UserAccount read(final Long id) {
- final UserAccount account =
- getTemplate().
- queryForObject(SELECT_ACCOUNT_BY_ID,userMapper,id);
- populateRoles(account);
- return account;
- }
- public UserAccount read(final String accountName) {
- final Long accountId = getTemplate().queryForLong(SELECT_ACCOUNT_ID_BY_NAME, accountName);
- final UserAccount account = new UserAccount(accountName);
- account.setId(accountId);
- populateRoles(account);
- return account;
- }
- public UserRole readUserRole(final String roleName) {
- final Long userRoleId = getTemplate().queryForLong(SELECT_USER_ROLE_ID_BY_NAME, roleName);
- final UserRole userRole = new UserRole(roleName);
- userRole.setId(userRoleId);
- return userRole;
- }
- public void update(final UserAccount account) {
- // Update the account entity
- getTemplate().update(UPDATE_ACCOUNT, account.getAccountName(), account.getId());
- // Update the role relationships
- persistTransientRoles(account);
- removeDeletedRoles(account);
- }
- private void removeDeletedRoles(final UserAccount account) {
- final List<UserRole> roleList =
- getTemplate().query(SELECT_ROLES_BY_ACCOUNT,roleMapper,account.getId());
- final Set<Long> actual = roleIdSet(roleList);
- final Set<Long> expected = roleIdSet(account.getRoles());
- for( final Long roleId : actual ) {
- if( !expected.contains(roleId) ) {
- getTemplate().update(DELETE_ACCOUNT_ROLE, account.getId(),roleId);
- }
- }
- }
- private Set<Long> roleIdSet(final Collection<UserRole> roles) {
- final Set<Long> set = new HashSet<Long>();
- for( final UserRole role : roles ) {
- set.add(role.getId());
- }
- return set;
- }
- private void populateRoles(final UserAccount account) {
- final List<UserRole> roles =
- getTemplate().query(
- SELECT_ROLES_BY_ACCOUNT,roleMapper,account.getId());
- account.getRoles().addAll(roles);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement