/**
* 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 long add(RoleBean bean) throws ApplicationException, DuplicateRecordException {
log.debug("Model add Started");
Connection conn = null;
int pk = 0;
RoleBean duplicataRole = findByName(bean.getName());
// Check if create Role already exist
if (duplicataRole != null) {
throw new DuplicateRecordException("Role already exists");
}
try {
conn = JDBCDataSource.getConnection();
pk = nextPK();
// Get auto-generated next primary key
System.out.println(pk + " in ModelJDBC");
conn.setAutoCommit(false); // Begin transaction
PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO demo_ors.st_role VALUES(?,?,?,?,?,?,?)");
pstmt.setInt(1, pk);
pstmt.setString(2, bean.getName());
pstmt.setString(3, bean.getDescription());
pstmt.setString(4, bean.getCreatedBy());
pstmt.setString(5, bean.getModifiedBy());
pstmt.setTimestamp(6, bean.getCreatedDatetime());
pstmt.setTimestamp(7, bean.getModifiedDatetime());
pstmt.executeUpdate();
conn.commit(); // End transaction
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
log.error("Database Exception..", e);
try {
conn.rollback();
} catch (Exception ex) {
throw new ApplicationException("Exception : add rollback exception " + ex.getMessage());
}
throw new ApplicationException("Exception : Exception in add Role");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model add End");
return pk;
}
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;
}
public void delete(RoleBean bean) throws ApplicationException {
log.debug("Model delete Started");
Connection conn = null;
try {
conn = JDBCDataSource.getConnection();
conn.setAutoCommit(false); // Begin transaction
PreparedStatement pstmt = conn.prepareStatement("DELETE FROM demo_ors.st_role WHERE ID=?");
pstmt.setLong(1, bean.getId());
pstmt.executeUpdate();
conn.commit(); // End transaction
pstmt.close();
} catch (Exception e) {
log.error("Database Exception..", e);
try {
conn.rollback();
} catch (Exception ex) {
throw new ApplicationException("Exception : Delete rollback exception " + ex.getMessage());
}
throw new ApplicationException("Exception : Exception in delete Role");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model delete Started");
}
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 void update(RoleBean bean) throws ApplicationException, DuplicateRecordException {
log.debug("Model update Started");
Connection conn = null;
RoleBean duplicataRole = findByName(bean.getName());
// Check if updated Role already exist
if (duplicataRole != null && duplicataRole.getId() != bean.getId()) {
throw new DuplicateRecordException("Role already exists");
}
try {
conn = JDBCDataSource.getConnection();
conn.setAutoCommit(false); // Begin transaction
PreparedStatement pstmt =
conn.prepareStatement(
"UPDATE demo_ors.st_role SET NAME=?,DESCRIPTION=?,CREATED_BY=?,MODIFIED_BY=?,CREATED_DATETIME=?,MODIFIED_DATETIME=? WHERE ID=?");
pstmt.setString(1, bean.getName());
pstmt.setString(2, bean.getDescription());
pstmt.setString(3, bean.getCreatedBy());
pstmt.setString(4, bean.getModifiedBy());
pstmt.setTimestamp(5, bean.getCreatedDatetime());
pstmt.setTimestamp(6, bean.getModifiedDatetime());
pstmt.setLong(7, bean.getId());
pstmt.executeUpdate();
conn.commit(); // End transaction
pstmt.close();
} catch (Exception e) {
log.error("Database Exception..", e);
try {
conn.rollback();
} catch (Exception ex) {
throw new ApplicationException("Exception : Delete rollback exception " + ex.getMessage());
}
throw new ApplicationException("Exception in updating Role ");
} finally {
JDBCDataSource.closeConnection(conn);
}
log.debug("Model update End");
}