RoleDAOJDBCImpl.java

Source Code‎ > ‎src‎ > ‎in.co.sunrays.proj1‎ > ‎dao‎ > ‎

package in.co.sunrays.proj1.dao;

import in.co.sunrays.proj1.dto.RoleDTO;
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 RoleDAO
*
* @author SUNRAYS Technologies
* @version 1.0
* @Copyright (c) SUNRAYS Technologies
*/
public class RoleDAOJDBCImpl extends JdbcDaoSupport implements RoleDAOInt {

    /**
    * Logger
    */
    private static Logger log = Logger.getLogger(RoleDAOJDBCImpl.class);

    /**
    * Find next PK for Role
    *
    * @throws DatabaseException
    */
    public long nextPK() throws DatabaseException {
        log.debug("DAO nextPK Started");
        long pk = 0;
        try {
            pk = getJdbcTemplate().queryForLong("SELECT MAX(ID) FROM ST_ROLE");
        } 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 Role
    *
    * @param dto
    * @throws DatabaseException
    */
    public long add(RoleDTO dto) throws DatabaseException {
        log.debug("DAO add Started");
        long pk = nextPK();
        try {
            Object[] values = new Object[] { pk, dto.getName(),
                    dto.getDescription(), dto.getCreatedBy(),
                    dto.getModifiedBy(), dto.getCreatedDatetime(),
                    dto.getModifiedDatetime() };
            getJdbcTemplate().update(
                    "INSERT INTO ST_ROLE VALUES(?,?,?,?,?,?,?)", values);
        } catch (Exception e) {
            log.error("Database Exception..", e);
            throw new DatabaseException("Exception : Exception in add Role");
        }
        log.debug("DAO add End");
        return pk;
    }

    /**
    * Update a Role
    *
    * @param dto
    * @throws DatabaseException
    */
    @Override
    public void update(RoleDTO dto) throws DatabaseException {
        log.debug("DAO update Started");
        try {

            Object[] values = new Object[] { dto.getName(),
                    dto.getDescription(), dto.getCreatedBy(),
                    dto.getModifiedBy(), dto.getCreatedDatetime(),
                    dto.getModifiedDatetime(), dto.getId() };
            getJdbcTemplate()
                    .update("UPDATE ST_ROLE SET NAME=?,DESCRIPTION=?,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 Role");
        }
        log.debug("DAO update End");
    }

    /**
    * Delete a Role
    *
    * @param dto
    * @throws DatabaseException
    */
    public void delete(RoleDTO dto) throws DatabaseException {
        log.debug("DAO delete Started");
        try {
            getJdbcTemplate().update("DELETE FROM ST_ROLE WHERE ID=?",
                    new Object[] { dto.getId() });
        } catch (Exception e) {
            log.error("Database Exception..", e);
            throw new DatabaseException("Exception : Exception in delete Role");
        }
        log.debug("DAO delete Started");
    }

    /**
    * Find Role by Name
    *
    * @param name
    *            : get parameter
    * @return dto
    * @throws DatabaseException
    */
    @Override
    public RoleDTO findByName(String name) throws DatabaseException {
        log.debug("DAO findByName Started");
        RoleDTO dto = null;
        try {
            dto = (RoleDTO) getJdbcTemplate().queryForObject(
                    "SELECT * FROM ST_ROLE WHERE NAME=?",
                    new Object[] { name }, new RoleMapper());
        } catch (Exception e) {
            log.error("Database Exception..", e);
            throw new DatabaseException(
                    "Exception : Exception in getting Role by Name");
        }
        log.debug("DAO findByName End");
        return dto;
    }

    /**
    * Find Role by PK
    *
    * @param pk
    *            : get parameter
    * @return dto
    * @throws DatabaseException
    */
    @Override
    public RoleDTO findByPK(long pk) throws DatabaseException {
        log.debug("DAO findByPK Started");
        RoleDTO dto = null;
        try {
            dto = (RoleDTO) getJdbcTemplate().queryForObject(
                    "SELECT * FROM ST_ROLE WHERE ID=?", new Object[] { pk },
                    new RoleMapper());
        } catch (Exception e) {
            log.error("Database Exception..", e);
            throw new DatabaseException(
                    "Exception : Exception in getting Role by pk");
        }
        log.debug("DAO findByPK End");
        return dto;
    }

    /**
    * Search Roles with pagination
    *
    * @return list : List of Roles
    * @param dto
    *            : Search Parameters
    * @param pageNo
    *            : Current Page No.
    * @param pageSize
    *            : Size of Page
    * @throws DatabaseException
    */
    @Override
    public List search(RoleDTO dto, int pageNo, int pageSize)
            throws DatabaseException {
        log.debug("DAO search Started");
        StringBuffer sql = new StringBuffer("SELECT * FROM ST_ROLE WHERE 1=1");
        if (dto.getId() > 0) {
            sql.append(" AND ID = " + dto.getId());
        }
        if (dto.getName() != null && dto.getName().length() > 0) {
            sql.append(" AND NAME LIKE '" + dto.getName() + "%'");
        }
        if (dto.getDescription() != null && dto.getDescription().length() > 0) {
            sql.append(" AND DESCRIPTION LIKE '" + dto.getDescription() + "%'");
        }
        // 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 RoleMapper());
        } catch (Exception e) {
            log.error("Database Exception..", e);
            throw new DatabaseException("Exception : Exception in search Role");
        }
        log.debug("DAO search End");
        return list;
    }

    /**
    * Search Roles
    *
    * @return list : List of Roles
    * @param dto
    *            : Search Parameters
    * @throws DatabaseException
    */
    @Override
    public List search(RoleDTO dto) throws DatabaseException {
        return search(dto, 0, 0);
    }

    /**
    * Get List of Roles
    *
    * @return list : List of Roles
    * @throws DatabaseException
    */
    @Override
    public List list() throws DatabaseException {
        return list(0, 0);
    }

    /**
    * Get List of Roles with pagination
    *
    * @return list : List of Roles
    * @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_ROLE");
        // 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 RoleMapper());
        } catch (Exception e) {
            log.error("Database Exception..", e);
            throw new DatabaseException(
                    "Exception : Exception in getting list of Role");
        }
        log.debug("DAO list End");
        return list;
    }

    class RoleMapper implements RowMapper {
        @Override
        public RoleDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
            RoleDTO dto = new RoleDTO();
            dto.setId(rs.getLong(1));
            dto.setName(rs.getString(2));
            dto.setDescription(rs.getString(3));
            dto.setCreatedBy(rs.getString(4));
            dto.setModifiedBy(rs.getString(5));
            dto.setCreatedDatetime(rs.getTimestamp(6));
            dto.setModifiedDatetime(rs.getTimestamp(7));
            return dto;
        }
    }
}

Comments