/** * 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; }