package in.co.sunrays.proj1.dao;
import in.co.sunrays.proj1.dto.UserDTO; import in.co.sunrays.proj1.exception.DatabaseException; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; /** * JDBC Implementation of UserDAO * * @author SUNRAYS Technologies * @version 1.0 * @Copyright (c) SUNRAYS Technologies */ public class UserDAOJDBCImpl extends JdbcDaoSupport implements UserDAOInt { /** * Logger */ private static Logger log = Logger.getLogger(UserDAOJDBCImpl.class); /** * Find next PK of user * * @throws DatabaseException */ public long nextPK() throws DatabaseException { log.debug("DAO nextPK Started"); long pk = 0; try { pk = getJdbcTemplate().queryForLong("SELECT MAX(ID) FROM ST_USER"); } catch (Exception e) { log.error("Database Exception..", e); throw new DatabaseException("Exception : Exception in getting PK"); } log.debug("DAO nextPK End"); return pk + 1; } /** * Add a User * * @param dto * @throws DatabaseException */ public long add(UserDTO dto) throws DatabaseException { log.debug("DAO add Started"); // Get auto-generated next primary key long pk = nextPK(); try { Object[] values = new Object[] { pk, dto.getFirstName(), dto.getLastName(), dto.getLogin(), dto.getPassword(), new java.sql.Date(dto.getDob().getTime()), dto.getMobileNo(), dto.getRoleId(), dto.getUnSuccessfulLogin(), dto.getGender(), dto.getLastLogin(), dto.getLock(), dto.getRegisteredIP(), dto.getLastLoginIP(), dto.getCreatedBy(), dto.getModifiedBy(), dto.getCreatedDatetime(), dto.getModifiedDatetime() }; getJdbcTemplate() .update("INSERT INTO ST_USER VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", values); } catch (Exception e) { log.error("Database Exception..", e); throw new DatabaseException( "Exception : Exception in add marksheet"); } log.debug("DAO add End"); return pk; } /** * Update a User * * @param dto * @throws DatabaseException */ @Override public void update(UserDTO dto) throws DatabaseException { log.debug("DAO update Started"); try { Object[] values = new Object[] { dto.getFirstName(), dto.getLastName(), dto.getLogin(), dto.getPassword(), new java.sql.Date(dto.getDob().getTime()), dto.getMobileNo(), dto.getRoleId(), dto.getUnSuccessfulLogin(), dto.getGender(), dto.getLastLogin(), dto.getLock(), dto.getRegisteredIP(), dto.getLastLoginIP(), dto.getCreatedBy(), dto.getModifiedBy(), dto.getCreatedDatetime(), dto.getModifiedDatetime(), dto.getId() }; getJdbcTemplate() .update("UPDATE ST_USER SET FIRST_NAME=?,LAST_NAME=?,LOGIN=?,PASSWORD=?,DOB=?,MOBILE_NO=?,ROLE_ID=?,UNSUCCESSFUL_LOGIN=?,GENDER=?,LAST_LOGIN=?,USER_LOCK=?,REGISTERED_IP=?,LAST_LOGIN_IP=?,CREATED_BY=?,MODIFIED_BY=?,CREATED_DATETIME=?,MODIFIED_DATETIME=? WHERE ID=?", values); } catch (Exception e) { log.error("Database Exception..", e); throw new DatabaseException("Exception in updating User "); } log.debug("DAO update End"); } /** * Delete a User * * @param dto * @throws DatabaseException */ public void delete(UserDTO dto) throws DatabaseException { log.debug("DAO delete Started"); try { getJdbcTemplate().update("DELETE FROM ST_USER WHERE ID=?", new Object[] { dto.getId() }); } catch (Exception e) { log.error("Database Exception..", e); throw new DatabaseException( "Exception : Exception in delete marksheet"); } log.debug("DAO delete Started"); } /** * Find User by Login * * @param login * : get parameter * @return dto * @throws DatabaseException */ @Override public UserDTO findByLogin(String login) throws DatabaseException { log.debug("DAO findByLogin Started"); UserDTO dto = null; try { dto = (UserDTO) getJdbcTemplate().queryForObject( "SELECT * FROM ST_USER WHERE LOGIN=?", new Object[] { login }, new UserMapper()); } catch (Exception e) { log.error("Database Exception..", e); throw new DatabaseException( "Exception : Exception in getting marksheet by login"); } log.debug("DAO findByLogin End"); return dto; } /** * Find User by PK * * @param pk * : get parameter * @return dto * @throws DatabaseException */ @Override public UserDTO findByPK(long pk) throws DatabaseException { log.debug("DAO findByPK Started"); UserDTO dto = null; try { dto = (UserDTO) getJdbcTemplate().queryForObject( "SELECT * FROM ST_USER WHERE ID=?", new Object[] { pk }, new UserMapper()); } catch (Exception e) { e.printStackTrace(); log.error("Database Exception..", e); throw new DatabaseException( "Exception : Exception in getting marksheet by pk"); } log.debug("DAO findByPK End"); return dto; } /** * Search Users with pagination * * @return list : List of Users * @param dto * : Search Parameters * @param pageNo * : Current Page No. * @param pageSize * : Size of Page * @throws DatabaseException */ @Override public List search(UserDTO dto, int pageNo, int pageSize) throws DatabaseException { log.debug("DAO search Started"); StringBuffer sql = new StringBuffer("SELECT * FROM ST_USER WHERE 1=1"); if (dto.getId() > 0) { sql.append(" AND ID = " + dto.getId()); } if (dto.getFirstName() != null && dto.getFirstName().length() > 0) { sql.append(" AND FIRST_NAME LIKE '" + dto.getFirstName() + "%'"); } if (dto.getLastName() != null && dto.getLastName().length() > 0) { sql.append(" AND LAST_NAME LIKE '" + dto.getLastName() + "%'"); } if (dto.getLogin() != null && dto.getLogin().length() > 0) { sql.append(" AND LOGIN LIKE '" + dto.getLogin() + "%'"); } if (dto.getPassword() != null && dto.getPassword().length() > 0) { sql.append(" AND PASSWORD LIKE '" + dto.getPassword() + "%'"); } if (dto.getDob() != null && dto.getDob().getDate() > 0) { sql.append(" AND DOB = " + dto.getGender()); } if (dto.getMobileNo() != null && dto.getMobileNo().length() > 0) { sql.append(" AND MOBILE_NO = '" + dto.getMobileNo() + "'"); } if (dto.getRoleId() > 0) { sql.append(" AND ROLE_ID = " + dto.getRoleId()); } if (dto.getUnSuccessfulLogin() > 0) { sql.append(" AND UNSUCCESSFUL_LOGIN = " + dto.getUnSuccessfulLogin()); } if (dto.getGender() != null && dto.getGender().length() > 0) { sql.append(" AND GENDER = '" + dto.getGender() + "'"); } if (dto.getLastLogin() != null && dto.getLastLogin().getTime() > 0) { sql.append(" AND LAST_LOGIN = " + dto.getLastLogin()); } // if page size is greater than zero then apply pagination if (pageSize > 0) { // Calculate start record index pageNo = (pageNo - 1) * pageSize; sql.append(" LIMIT " + pageNo + ", " + pageSize); } List list = new ArrayList(); try { list = getJdbcTemplate().query(sql.toString(), new UserMapper()); } catch (Exception e) { log.error("Database Exception..", e); throw new DatabaseException("Exception : Exception in search user"); } log.debug("DAO search End"); return list; } /** * Search Users * * @param dto * : Search Parameters * @throws DatabaseException */ @Override public List search(UserDTO dto) throws DatabaseException { return search(dto, 0, 0); } /** * Get List of Users * * @return list : List of Users * @throws DatabaseException */ @Override public List list() throws DatabaseException { return list(0, 0); } /** * Get List of Users with pagination * * @return list : List of Users * @param pageNo * : Current Page No. * @param pageSize * : Size of Page * @throws DatabaseException */ @Override public List list(int pageNo, int pageSize) throws DatabaseException { log.debug("DAO list Started"); StringBuffer sql = new StringBuffer("SELECT * FROM ST_USER"); // if page size is greater than zero then apply pagination if (pageSize > 0) { // Calculate start record index pageNo = (pageNo - 1) * pageSize; sql.append(" LIMIT " + pageNo + "," + pageSize); } List list = new ArrayList(); try { list = getJdbcTemplate().query(sql.toString(), new UserMapper()); } catch (Exception e) { log.error("Database Exception..", e); throw new DatabaseException( "Exception : Exception in getting list of users"); } log.debug("DAO list End"); return list; } class UserMapper implements RowMapper { @Override public UserDTO mapRow(ResultSet rs, int rowNum) throws SQLException { UserDTO dto = new UserDTO(); dto.setId(rs.getLong(1)); dto.setFirstName(rs.getString(2)); dto.setLastName(rs.getString(3)); dto.setLogin(rs.getString(4)); dto.setPassword(rs.getString(5)); dto.setDob(rs.getDate(6)); dto.setMobileNo(rs.getString(7)); dto.setRoleId(rs.getLong(8)); dto.setUnSuccessfulLogin(rs.getInt(9)); dto.setGender(rs.getString(10)); dto.setLastLogin(rs.getTimestamp(11)); dto.setLock(rs.getString(12)); dto.setRegisteredIP(rs.getString(13)); dto.setLastLoginIP(rs.getString(14)); dto.setCreatedBy(rs.getString(15)); dto.setModifiedBy(rs.getString(16)); dto.setCreatedDatetime(rs.getTimestamp(17)); dto.setModifiedDatetime(rs.getTimestamp(18)); return dto; } } } |