Пример #1
1
 /**
  * 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;
 }
Пример #2
0
  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;
  }
Пример #3
0
  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;
  }
Пример #4
0
 public Integer nextPK() throws DatabaseException {
   log.debug("Model nextPK Started");
   Connection conn = null;
   int pk = 0;
   try {
     conn = JDBCDataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement("SELECT MAX(ID) FROM demo_ors.st_role");
     ResultSet rs = pstmt.executeQuery();
     while (rs.next()) {
       pk = rs.getInt(1);
     }
     rs.close();
   } catch (Exception e) {
     log.error("Database Exception..", e);
     throw new DatabaseException("Exception : Exception in getting PK");
   } finally {
     JDBCDataSource.closeConnection(conn);
   }
   log.debug("Model nextPK End");
   return pk + 1;
 }
Пример #5
0
  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");
  }
Пример #6
0
 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");
 }
Пример #7
0
 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;
 }