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