/**
* Find Role by PK
*
* @param pk : get parameter
* @return bean
* @throws DatabaseException
*/
public RoleBean findByPK(long pk) throws ApplicationException {
log.debug("Model findByPK Started");
StringBuffer sql = new StringBuffer("SELECT * FROM demo_ors.st_role WHERE ID=?");
RoleBean bean = null;
Connection conn = null;
try {
conn = JDBCDataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString());
pstmt.setLong(1, pk);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
bean = new RoleBean();
bean.setId(rs.getLong(1));
bean.setName(rs.getString(2));
bean.setDescription(rs.getString(3));
bean.setCreatedBy(rs.getString(4));
bean.setModifiedBy(rs.getString(5));
bean.setCreatedDatetime(rs.getTimestamp(6));
bean.setModifiedDatetime(rs.getTimestamp(7));
}
rs.close();
} catch (Exception e) {
log.error("Database Exception..", e);
throw new ApplicationException("Exception : Exception in getting User by pk");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model findByPK End");
return bean;
}
public List search(RoleBean bean, int pageNo, int pageSize) throws ApplicationException {
log.debug("Model search Started");
StringBuffer sql = new StringBuffer("SELECT * FROM demo_ors.st_role WHERE 1=1");
if (bean != null) {
if (bean.getId() > 0) {
sql.append(" AND id = " + bean.getId());
}
if (bean.getName() != null && bean.getName().length() > 0) {
sql.append(" AND NAME LIKE '" + bean.getName() + "%'");
}
if (bean.getDescription() != null && bean.getDescription().length() > 0) {
sql.append(" AND DESCRIPTION LIKE '" + bean.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);
// sql.append(" limit " + pageNo + "," + pageSize);
}
ArrayList list = new ArrayList();
Connection conn = null;
try {
conn = JDBCDataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
bean = new RoleBean();
bean.setId(rs.getLong(1));
bean.setName(rs.getString(2));
bean.setDescription(rs.getString(3));
bean.setCreatedBy(rs.getString(4));
bean.setModifiedBy(rs.getString(5));
bean.setCreatedDatetime(rs.getTimestamp(6));
bean.setModifiedDatetime(rs.getTimestamp(7));
list.add(bean);
}
rs.close();
} catch (Exception e) {
log.error("Database Exception..", e);
throw new ApplicationException("Exception : Exception in search Role");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model search End");
return list;
}
public List list(int pageNo, int pageSize) throws ApplicationException {
log.debug("Model list Started");
ArrayList list = new ArrayList();
StringBuffer sql = new StringBuffer("select * from demo_ors.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);
}
Connection conn = null;
try {
conn = JDBCDataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
RoleBean bean = new RoleBean();
bean.setId(rs.getLong(1));
bean.setName(rs.getString(2));
bean.setDescription(rs.getString(3));
bean.setCreatedBy(rs.getString(4));
bean.setModifiedBy(rs.getString(5));
bean.setCreatedDatetime(rs.getTimestamp(6));
bean.setModifiedDatetime(rs.getTimestamp(7));
list.add(bean);
}
rs.close();
} catch (Exception e) {
log.error("Database Exception..", e);
throw new ApplicationException("Exception : Exception in getting list of Role");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model list End");
return list;
}