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 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 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"); }