staffdao.java

WAP PUSH后台源码,WAP PUSH后台源码

        Hashtable element = new Hashtable();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          String columnName = rsmd.getColumnName(i);
          String fieldName = Function.format(columnName);
          String fieldType = ColumnType.getFieldType(Database.dbType, rsmd, i);
          if (fieldType == null) continue;
          if ("Timestamp".equals(fieldType)) {
            element.put(fieldName, rs.getTimestamp(columnName));
          } else if ("String".equals(fieldType)) {
            String value = Function.readDBEncode(rs.getString(columnName));
            if (value == null) value = "";
            element.put(fieldName, value);
          } else if ("int".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getInt(columnName)));
          } else if ("long".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getLong(columnName)));
          } else if ("float".equals(fieldType)) {
            element.put(fieldName, String.valueOf(rs.getFloat(columnName)));
          }
        }
        list.add(element);
      }
      return list;
    } catch(SQLException e) {
      Debug.println(sql + "\n" + "SQLException while execute queryBySql mothod :\n" + e);
      throw new SysException("SQLException while execute queryBySql mothod :\n" + e);
    } finally {
      closeResultSet(rs);
      closeStatement(stmt);
      closeConnection(dbConnection);
    }
  }
  public static int getRowCountBySql(String sql) throws SysException {
    Connection dbConnection = null;
    Statement stmt = null;
    ResultSet rs = null;
    int rowCount = 0;
    try {
      dbConnection = getDBConnection();
      stmt = dbConnection.createStatement();
      rs = stmt.executeQuery(sql);
      if ( rs.next() ) {
        rowCount = rs.getInt(1);
      }else{
        Debug.println(sql + "\n" + "ERROR in get row count!!");
        throw new SysException("ERROR in get row count!!");
      }
      return rowCount;
    } catch(SQLException e) {
      Debug.println(sql + "\n" + "SQLException while execute getRowCountBySql mothod :\n" + e);
      throw new SysException("SQLException while execute getRowCountBySql mothod :\n" + e);
    } finally {
      closeResultSet(rs);
      closeStatement(stmt);
      closeConnection(dbConnection);
    }
  }
  public static Collection queryAll() throws SysException {
    String sql = "SELECT * FROM WAP_STAFF";
    return queryBySql(sql);
  }
  public static Collection queryAll(int beginRow, int endRow) throws SysException {
    String sql = "SELECT * FROM WAP_STAFF";
    return queryBySql(sql, beginRow, endRow);
  }
  public static int getRowCountOfAll() throws SysException {
    String sql = "SELECT COUNT(*) FROM WAP_STAFF";
    return getRowCountBySql(sql);
  }
  public static StaffModel toModel(Hashtable element) {
    StaffModel model = new StaffModel();
    model.setId(Integer.parseInt((String)element.get("id")));
    model.setUserMdn((String)element.get("userMdn"));
    model.setUserName((String)element.get("userName"));
    model.setSex(Integer.parseInt((String)element.get("sex")));
    model.setTel((String)element.get("tel"));
    model.setFax((String)element.get("fax"));
    model.setJob((String)element.get("job"));
    model.setAddress((String)element.get("address"));
    model.setPost((String)element.get("post"));
    model.setRemark((String)element.get("remark"));
    model.setGroupId(Integer.parseInt((String)element.get("groupId")));
    model.setDepartmentId(Integer.parseInt((String)element.get("departmentId")));
    model.setOrderId(Integer.parseInt((String)element.get("orderId")));
    model.setPicture((String)element.get("picture"));
    model.setEmail((String)element.get("email"));
    return model;
  }
  public static Collection allToModel(Collection coll) {
    Collection value = new ArrayList();
    Iterator it = coll.iterator();
    while (it.hasNext()) {
      value.add(toModel((Hashtable)it.next()));
    }
    return value;
  }
  public static int getSequenceNextValue() throws SysException {
    String sql = "SELECT * FROM WAP_SEQUENCE WHERE TABLE_NAME = 'WAP_STAFF'";
    Connection dbConnection = null;
    Statement stmt = null;
    ResultSet rs = null;
    int lastValue = 1;
    try {
      dbConnection = getDBConnection();
      stmt = dbConnection.createStatement();
      rs = stmt.executeQuery(sql);
      if ( rs.next() ) {
        lastValue = rs.getInt("LAST_VALUE");
        int minValue = rs.getInt("MIN_VALUE");
        int maxValue = rs.getInt("MAX_VALUE");
        int incrementBy = rs.getInt("INCREMENT_BY");
        int cycle = rs.getInt("CYCLE");
        if (lastValue < 0) {
          Debug.println("id last value can't smaller than 0");
          throw new SysException("id last value can't smaller than 0");
        }
        if (minValue < 0) {
          Debug.println("id min value can't smaller than 0");
          throw new SysException("id min value can't smaller than 0");
        }
        if (maxValue < 0) {
          Debug.println("id max value can't smaller than 0");
          throw new SysException("id max value can't smaller than 0");
        }
        if (incrementBy < 1) {
          Debug.println("id increment value can't smaller than 1");
          throw new SysException("id increment value can't smaller than 1");
        }
        if (cycle != 0 && cycle != 1) {
          Debug.println("id cycle value error, it can be 0 or 1 only");
          throw new SysException("id cycle value error, it can be 0 or 1 only");
        }
        if (lastValue < minValue) {
          Debug.println("id value is " + lastValue + ": \n" + "id value can't smaller than min value");
          throw new SysException("id value can't smaller than min value");
        }
        if (minValue >= maxValue) {
          Debug.println("id max value must bigger than min value");
          throw new SysException("id max value must bigger than min value");
        }
        if (lastValue > maxValue) {
          if (cycle == 0) {
            Debug.println("id value is " + lastValue + ": \n" + "id value exceeds max value");
            throw new SysException("id value exceeds max value");
          } else {
            lastValue = minValue;
          }
        }
        sql = "UPDATE WAP_SEQUENCE SET LAST_VALUE = " + (lastValue + incrementBy) + " WHERE TABLE_NAME = 'WAP_STAFF'";
        stmt.executeUpdate(sql);
      }else{
        sql = "INSERT INTO WAP_SEQUENCE (TABLE_NAME, LAST_VALUE, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE) VALUES ('WAP_STAFF', 2, 1, 999999999, 1, 1)";
        stmt.executeUpdate(sql);
      }
      return lastValue;
    } catch(SQLException e) {
      Debug.println(sql + "\n" + "SQLException while execute getRowCountBySql method :\n" + e);
      throw new SysException("SQLException while execute getRowCountBySql method :\n" + e);
    } finally {
      closeResultSet(rs);
      closeStatement(stmt);
      closeConnection(dbConnection);
    }
  }
  protected static String getInsertSQL(StaffModel model) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "INSERT INTO WAP_STAFF (ID, USER_MDN, USER_NAME, SEX, TEL, FAX, JOB, ADDRESS, POST, REMARK, GROUP_ID, DEPARTMENT_ID, ORDER_ID, PICTURE, EMAIL) VALUES ("
          + model.getId() + ", '"
          + Function.writeDBEncode(model.getUserMdn()) + "', '"
          + Function.writeDBEncode(model.getUserName()) + "', "
          + model.getSex() + ", '"
          + Function.writeDBEncode(model.getTel()) + "', '"
          + Function.writeDBEncode(model.getFax()) + "', '"
          + Function.writeDBEncode(model.getJob()) + "', '"
          + Function.writeDBEncode(model.getAddress()) + "', '"
          + Function.writeDBEncode(model.getPost()) + "', '"
          + Function.writeDBEncode(model.getRemark()) + "', "
          + model.getGroupId() + ", "
          + model.getDepartmentId() + ", "
          + model.getOrderId() + ", '"
          + Function.writeDBEncode(model.getPicture()) + "', '"
          + Function.writeDBEncode(model.getEmail()) + "')";
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "INSERT INTO WAP_STAFF (ID, USER_MDN, USER_NAME, SEX, TEL, FAX, JOB, ADDRESS, POST, REMARK, GROUP_ID, DEPARTMENT_ID, ORDER_ID, PICTURE, EMAIL) VALUES ("
          + model.getId() + ", '"
          + Function.writeDBEncode(model.getUserMdn()) + "', '"
          + Function.writeDBEncode(model.getUserName()) + "', "
          + model.getSex() + ", '"
          + Function.writeDBEncode(model.getTel()) + "', '"
          + Function.writeDBEncode(model.getFax()) + "', '"
          + Function.writeDBEncode(model.getJob()) + "', '"
          + Function.writeDBEncode(model.getAddress()) + "', '"
          + Function.writeDBEncode(model.getPost()) + "', '"
          + Function.writeDBEncode(model.getRemark()) + "', "
          + model.getGroupId() + ", "
          + model.getDepartmentId() + ", "
          + model.getOrderId() + ", '"
          + Function.writeDBEncode(model.getPicture()) + "', '"
          + Function.writeDBEncode(model.getEmail()) + "')";
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "INSERT INTO WAP_STAFF (ID, USER_MDN, USER_NAME, SEX, TEL, FAX, JOB, ADDRESS, POST, REMARK, GROUP_ID, DEPARTMENT_ID, ORDER_ID, PICTURE, EMAIL) VALUES ("
          + model.getId() + ", '"
          + Function.writeDBEncode(model.getUserMdn()) + "', '"
          + Function.writeDBEncode(model.getUserName()) + "', "
          + model.getSex() + ", '"
          + Function.writeDBEncode(model.getTel()) + "', '"
          + Function.writeDBEncode(model.getFax()) + "', '"
          + Function.writeDBEncode(model.getJob()) + "', '"
          + Function.writeDBEncode(model.getAddress()) + "', '"
          + Function.writeDBEncode(model.getPost()) + "', '"
          + Function.writeDBEncode(model.getRemark()) + "', "
          + model.getGroupId() + ", "
          + model.getDepartmentId() + ", "
          + model.getOrderId() + ", '"
          + Function.writeDBEncode(model.getPicture()) + "', '"
          + Function.writeDBEncode(model.getEmail()) + "')";
    }
    return sql;
  }
  protected static String getUpdateSQL(StaffModel model) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "UPDATE WAP_STAFF SET USER_MDN = '"
          + Function.writeDBEncode(model.getUserMdn()) + "', USER_NAME = '"
          + Function.writeDBEncode(model.getUserName()) + "', SEX = "
          + model.getSex() + ", TEL = '"
          + Function.writeDBEncode(model.getTel()) + "', FAX = '"
          + Function.writeDBEncode(model.getFax()) + "', JOB = '"
          + Function.writeDBEncode(model.getJob()) + "', ADDRESS = '"
          + Function.writeDBEncode(model.getAddress()) + "', POST = '"
          + Function.writeDBEncode(model.getPost()) + "', REMARK = '"
          + Function.writeDBEncode(model.getRemark()) + "', GROUP_ID = "
          + model.getGroupId() + ", DEPARTMENT_ID = "
          + model.getDepartmentId() + ", ORDER_ID = "
          + model.getOrderId() + ", PICTURE = '"
          + Function.writeDBEncode(model.getPicture()) + "', EMAIL = '"
          + Function.writeDBEncode(model.getEmail()) + "' WHERE ID = " + model.getId();
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "UPDATE WAP_STAFF SET USER_MDN = '"
          + Function.writeDBEncode(model.getUserMdn()) + "', USER_NAME = '"
          + Function.writeDBEncode(model.getUserName()) + "', SEX = "
          + model.getSex() + ", TEL = '"
          + Function.writeDBEncode(model.getTel()) + "', FAX = '"
          + Function.writeDBEncode(model.getFax()) + "', JOB = '"
          + Function.writeDBEncode(model.getJob()) + "', ADDRESS = '"
          + Function.writeDBEncode(model.getAddress()) + "', POST = '"
          + Function.writeDBEncode(model.getPost()) + "', REMARK = '"
          + Function.writeDBEncode(model.getRemark()) + "', GROUP_ID = "
          + model.getGroupId() + ", DEPARTMENT_ID = "
          + model.getDepartmentId() + ", ORDER_ID = "
          + model.getOrderId() + ", PICTURE = '"
          + Function.writeDBEncode(model.getPicture()) + "', EMAIL = '"
          + Function.writeDBEncode(model.getEmail()) + "' WHERE ID = " + model.getId();
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "UPDATE WAP_STAFF SET USER_MDN = '"
          + Function.writeDBEncode(model.getUserMdn()) + "', USER_NAME = '"
          + Function.writeDBEncode(model.getUserName()) + "', SEX = "
          + model.getSex() + ", TEL = '"
          + Function.writeDBEncode(model.getTel()) + "', FAX = '"
          + Function.writeDBEncode(model.getFax()) + "', JOB = '"
          + Function.writeDBEncode(model.getJob()) + "', ADDRESS = '"
          + Function.writeDBEncode(model.getAddress()) + "', POST = '"
          + Function.writeDBEncode(model.getPost()) + "', REMARK = '"
          + Function.writeDBEncode(model.getRemark()) + "', GROUP_ID = "
          + model.getGroupId() + ", DEPARTMENT_ID = "
          + model.getDepartmentId() + ", ORDER_ID = "
          + model.getOrderId() + ", PICTURE = '"
          + Function.writeDBEncode(model.getPicture()) + "', EMAIL = '"
          + Function.writeDBEncode(model.getEmail()) + "' WHERE ID = " + model.getId();
    }
    return sql;
  }
  protected static String getDeleteSQL(int id) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "DELETE FROM WAP_STAFF WHERE ID = " + id;
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "DELETE FROM WAP_STAFF WHERE ID = " + id;
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "DELETE FROM WAP_STAFF WHERE ID = " + id;
    }
    return sql;
  }
  protected static String getDeleteSQL(int[] ids) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "DELETE FROM WAP_STAFF WHERE 1 = 2";
      for ( int i = 0; i < ids.length; i++ ) {
        sql += " OR ID = " + ids[i];
      }
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "DELETE FROM WAP_STAFF WHERE 1 = 2";
      for ( int i = 0; i < ids.length; i++ ) {
        sql += " OR ID = " + ids[i];
      }
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "DELETE FROM WAP_STAFF WHERE 1 = 2";
      for ( int i = 0; i < ids.length; i++ ) {