package com.sunilos.annotation.dao;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import com.sunilos.dto.College;
/**
* College Data Access Object provides Database CRUD operations. It is
* implemented by JDBC Spring DAO.
*
* All methods propagate unchecked DataAccessException. It is a generic
* exception handling provided by Spring-DAO.
*
* If DataAccessException is propagated from method then declarative transaction
* is rolled back by Spring AOP.
*
*
* @since 01 Jan 2015
* @author SunilOS
* @version 1.0
* @Copyright (c) SunilOS
*/
// @Repository
public class CollegeDAOJDBCImpl implements CollegeDAOInt {
/**
* Template object ofJDBC
*/
private JdbcTemplate jdbcTemplate;
/**
* Setter Injection point for DataSource
*
* Best practice is to inject DataSource and create JDBCTemplate object
* because JDBCTemplate object is thread-safe and participate in transaction
*
* @param dataSource
*/
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
/**
* Finds next PK of College
*
*/
public long nextPK() {
int rowCount = jdbcTemplate.queryForObject(
"SELECT MAX(ID) FROM ST_COLLEGE", Integer.class);
return rowCount + 1;
}
/**
* Adds a College
*
*/
public long add(College dto) {
long pk = nextPK();
String sql = "INSERT INTO ST_COLLEGE VALUES(?,?,?,?,?,?,?,?,?,?)";
jdbcTemplate.update(sql, pk, dto.getName(), dto.getAddress(),
dto.getState(), dto.getCity(), dto.getPhoneNo(),
dto.getCreatedBy(), dto.getModifiedBy(),
dto.getCreatedDatetime(), dto.getModifiedDatetime());
return pk;
}
/**
* Updates a College
*/
public long update(College dto) {
String sql = "UPDATE ST_COLLEGE SET NAME=?,ADDRESS=?,STATE=?,CITY=?,PHONE_NO=?,"
+ " CREATED_BY=?,MODIFIED_BY=?,CREATED_DATE_TIME=?,MODIFIED_DATE_TIME=? "
+ " WHERE ID=?";
jdbcTemplate.update(sql, dto.getName(), dto.getAddress(),
dto.getState(), dto.getCity(), dto.getPhoneNo(),
dto.getCreatedBy(), dto.getModifiedBy(),
dto.getCreatedDatetime(), dto.getModifiedDatetime(),
dto.getId());
return dto.getId();
}
/**
* Deletes a College
*/
public College delete(long id) {
College dto = findByPK(id);
String sql = "delete from ST_COLLEGE where id = ?";
int i = jdbcTemplate.update(sql, id);
return dto;
}
/**
* Finds User by College ID
*/
public College findByPK(long pk) {
String sql = "SELECT * FROM ST_COLLEGE WHERE ID=?";
College dto = jdbcTemplate.queryForObject(sql, new Object[] { pk },
new CollegeMapper());
return dto;
}
/**
* Finds User by College
*
*/
public College findByName(String name) {
String sql = "SELECT * FROM ST_COLLEGE WHERE NAME=?";
College dto = jdbcTemplate.queryForObject(sql, new Object[] { name },
new CollegeMapper());
return dto;
}
/**
* Searches College
*/
public List<College> search(College dto) {
return search(dto, 0, 0);
}
/**
* Searches College with pagination
*/
public List<College> search(College dto, int pageNo, int pageSize) {
StringBuffer sql = new StringBuffer(
"SELECT * FROM ST_COLLEGE WHERE 1=1");
if (dto != null) {
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.getAddress() != null && dto.getAddress().length() > 0) {
sql.append(" AND ADDRESS LIKE '" + dto.getAddress() + "%'");
}
if (dto.getState() != null && dto.getState().length() > 0) {
sql.append(" AND STATE LIKE '" + dto.getState() + "%'");
}
if (dto.getCity() != null && dto.getCity().length() > 0) {
sql.append(" AND CITY LIKE '" + dto.getCity() + "%'");
}
if (dto.getPhoneNo() != null && dto.getPhoneNo().length() > 0) {
sql.append(" AND PHONE_NO = " + dto.getPhoneNo());
}
}
// 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);
// sql.append(" limit " + pageNo + "," + pageSize);
}
List<College> colleges = jdbcTemplate.query(sql.toString(),
new CollegeMapper());
return colleges;
}
}
