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