staffuserdao.java

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

      closeStatement(stmt);
      closeConnection(dbConnection);
    }
  }
  public static Collection queryAll() throws SysException {
    String sql = "SELECT * FROM WAP_STAFF_USER";
    return queryBySql(sql);
  }
  public static Collection queryAll(int beginRow, int endRow) throws SysException {
    String sql = "SELECT * FROM WAP_STAFF_USER";
    return queryBySql(sql, beginRow, endRow);
  }
  public static int getRowCountOfAll() throws SysException {
    String sql = "SELECT COUNT(*) FROM WAP_STAFF_USER";
    return getRowCountBySql(sql);
  }
  public static StaffUserModel toModel(Hashtable element) {
    StaffUserModel model = new StaffUserModel();
    model.setUserId(Integer.parseInt((String)element.get("userId")));
    model.setUserName((String)element.get("userName"));
    model.setUserPwd((String)element.get("userPwd"));
    model.setGroupId(Integer.parseInt((String)element.get("groupId")));
    model.setUserRegdate((Timestamp)element.get("userRegdate"));
    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_USER'";
    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_USER'";
        stmt.executeUpdate(sql);
      }else{
        sql = "INSERT INTO WAP_SEQUENCE (TABLE_NAME, LAST_VALUE, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE) VALUES ('WAP_STAFF_USER', 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(StaffUserModel model) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "INSERT INTO WAP_STAFF_USER (USER_ID, USER_NAME, USER_PWD, GROUP_ID, USER_REGDATE) VALUES ("
          + model.getUserId() + ", '"
          + Function.writeDBEncode(model.getUserName()) + "', '"
          + Function.writeDBEncode(model.getUserPwd()) + "', "
          + model.getGroupId() + ", TO_DATE('"
          + Function.DateToString(model.getUserRegdate()) + "', 'YYYY/MM/DD HH24:MI:SS'))";
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "INSERT INTO WAP_STAFF_USER (USER_ID, USER_NAME, USER_PWD, GROUP_ID, USER_REGDATE) VALUES ("
          + model.getUserId() + ", '"
          + Function.writeDBEncode(model.getUserName()) + "', '"
          + Function.writeDBEncode(model.getUserPwd()) + "', "
          + model.getGroupId() + ", '"
          + Function.DateToString(model.getUserRegdate()) + "')";
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "INSERT INTO WAP_STAFF_USER (USER_ID, USER_NAME, USER_PWD, GROUP_ID, USER_REGDATE) VALUES ("
          + model.getUserId() + ", '"
          + Function.writeDBEncode(model.getUserName()) + "', '"
          + Function.writeDBEncode(model.getUserPwd()) + "', "
          + model.getGroupId() + ", '"
          + Function.DateToString(model.getUserRegdate()) + "')";
    }
    return sql;
  }
  protected static String getUpdateSQL(StaffUserModel model) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "UPDATE WAP_STAFF_USER SET USER_NAME = '"
          + Function.writeDBEncode(model.getUserName()) + "', USER_PWD = '"
          + Function.writeDBEncode(model.getUserPwd()) + "', GROUP_ID = "
          + model.getGroupId() + ", USER_REGDATE = TO_DATE('"
          + Function.DateToString(model.getUserRegdate()) + "', 'YYYY-MM-DD HH24:MI:SS') WHERE USER_ID = " + model.getUserId();
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "UPDATE WAP_STAFF_USER SET USER_NAME = '"
          + Function.writeDBEncode(model.getUserName()) + "', USER_PWD = '"
          + Function.writeDBEncode(model.getUserPwd()) + "', GROUP_ID = "
          + model.getGroupId() + ", USER_REGDATE = '"
          + Function.DateToString(model.getUserRegdate()) + "' WHERE USER_ID = " + model.getUserId();
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "UPDATE WAP_STAFF_USER SET USER_NAME = '"
          + Function.writeDBEncode(model.getUserName()) + "', USER_PWD = '"
          + Function.writeDBEncode(model.getUserPwd()) + "', GROUP_ID = "
          + model.getGroupId() + ", USER_REGDATE = '"
          + Function.DateToString(model.getUserRegdate()) + "' WHERE USER_ID = " + model.getUserId();
    }
    return sql;
  }
  protected static String getDeleteSQL(int userId) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "DELETE FROM WAP_STAFF_USER WHERE USER_ID = " + userId;
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "DELETE FROM WAP_STAFF_USER WHERE USER_ID = " + userId;
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "DELETE FROM WAP_STAFF_USER WHERE USER_ID = " + userId;
    }
    return sql;
  }
  protected static String getDeleteSQL(int[] ids) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "DELETE FROM WAP_STAFF_USER WHERE 1 = 2";
      for ( int i = 0; i < ids.length; i++ ) {
        sql += " OR USER_ID = " + ids[i];
      }
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "DELETE FROM WAP_STAFF_USER WHERE 1 = 2";
      for ( int i = 0; i < ids.length; i++ ) {
        sql += " OR USER_ID = " + ids[i];
      }
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "DELETE FROM WAP_STAFF_USER WHERE 1 = 2";
      for ( int i = 0; i < ids.length; i++ ) {
        sql += " OR USER_ID = " + ids[i];
      }
    }
    return sql;
  }
  protected static String getFindByPKSQL(int userId) {
    String sql = "";
    if (Database.dbType == DBType.ORACLE) {
      sql = "SELECT * FROM WAP_STAFF_USER WHERE USER_ID = " + userId;
    } else if (Database.dbType == DBType.SQLSERVER) {
      sql = "SELECT * FROM WAP_STAFF_USER WHERE USER_ID = " + userId;
    } else if (Database.dbType == DBType.MYSQL) {
      sql = "SELECT * FROM WAP_STAFF_USER WHERE USER_ID = " + userId;
    }
    return sql;
  }
  protected static boolean isValidData(StaffUserModel model) {
    return true;
  }
  private static Connection getDBConnection() throws SysException {
    return Database.getConnection();
  }
  private static void closeConnection(Connection dbConnection) throws SysException {
    try {
      if (dbConnection != null && !dbConnection.isClosed()) {
        dbConnection.close();
        dbConnection = null;
      }
    } catch (SQLException se) {
      //throw new SysException("SQL Exception while closing DB connection : \n" + se);
    }
  }
  private static void closeResultSet(ResultSet result) throws SysException {
    try {
      if (result != null) {
        result.close();
      }
    } catch (SQLException se) {
      //throw new SysException("SQL Exception while closing Result Set : \n" + se);
    }
  }
  private static void closeStatement(Statement stmt) throws SysException {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException se) {
      //throw new SysException("SQL Exception while closing Statement : \n" + se);
    }
  }
  public static void main(String[] args) throws Exception{
    String action = "";
    if (args.length == 1) {
      action = args[0];
    }
    if (action == null || "".equals(action)) {

    } else if ("insert".equalsIgnoreCase(action)) {
      StaffUserModel model=new StaffUserModel();
      model.setUserId(StaffUserBean.getSequenceNextValue());
      model.setUserName("abc");
      model.setUserPwd("abc");
      model.setGroupId(1);
      model.setUserRegdate(Function.getCurrTime());
      StaffUserModel staffUserModel = StaffUserBean.insert(model);
    }else if ("update".equalsIgnoreCase(action)){
      StaffUserModel staffUserModel = StaffUserBean.findByPK(1);
      int userId = staffUserModel.getUserId();
      String userName = staffUserModel.getUserName();
      String userPwd = staffUserModel.getUserPwd();
      int groupId = staffUserModel.getGroupId();
      Timestamp userRegdate = staffUserModel.getUserRegdate();
      staffUserModel.setUserName(userName);
      staffUserModel.setUserPwd(userPwd);
      staffUserModel.setGroupId(groupId);
      staffUserModel.setUserRegdate(userRegdate);
      update(staffUserModel);
    }else if ("delete".equalsIgnoreCase(action)){
      int[] ids = new int[3];
      ids[0] = 1;
      ids[1] = 2;
      ids[2] = 3;
      StaffUserBean.delete(ids);
    }else if ("findByPK".equalsIgnoreCase(action)){
      StaffUserModel staffUserModel = StaffUserBean.findByPK(1);
      int userId = staffUserModel.getUserId();
      String userName = staffUserModel.getUserName();
      String userPwd = staffUserModel.getUserPwd();
      int groupId = staffUserModel.getGroupId();
      Timestamp userRegdate = staffUserModel.getUserRegdate();
      System.out.println("userId = " + userId);
      System.out.println("userName = " + userName);
      System.out.println("userPwd = " + userPwd);
      System.out.println("groupId = " + groupId);
      System.out.println("userRegdate = " + Function.getYYYYMMDDHHMISS(userRegdate));
    }else if ("queryBySql".equalsIgnoreCase(action)){
      Collection coll = StaffUserBean.queryBySql("SELECT * FROM WAP_STAFF_USER");
    }else if ("getRowCountBySql".equalsIgnoreCase(action)){
      int rowCount = StaffUserBean.getRowCountBySql("SELECT COUNT(*) FROM WAP_STAFF_USER");
      System.out.println("rowCount = " + rowCount);
    }else if ("queryAll".equalsIgnoreCase(action)){
      Collection coll = StaffUserBean.queryAll();
      Iterator it = coll.iterator();
      while ( it.hasNext() ) {
        Hashtable element = (Hashtable)it.next();
        String userId = (String)element.get("userId");
        String userName = (String)element.get("userName");
        String userPwd = (String)element.get("userPwd");
        String groupId = (String)element.get("groupId");
        String userRegdate = Function.getYYYYMMDDHHMISS((Timestamp)element.get("userRegdate"));
        System.out.print("userId = " + userId + ",\t");
        System.out.print("userName = " + userName + ",\t");
        System.out.print("userPwd = " + userPwd + ",\t");
        System.out.print("groupId = " + groupId + ",\t");
        System.out.print("userRegdate = " + userRegdate + ",\t");
      }
    }else if ("getRowCountOfAll".equalsIgnoreCase(action)){
      int rowCount = StaffUserBean.getRowCountOfAll();
      System.out.println("rowCount = " + rowCount);
    }else if ("getSequenceNextValue".equalsIgnoreCase(action)){
      int sequenceValue = StaffUserBean.getSequenceNextValue();
      System.out.println("sequenceValue = " + sequenceValue);
    }
  }
}
/*
int userId = (model==null?0:model.getUserId());
String userName = (model==null?"":model.getUserName());
String userPwd = (model==null?"":model.getUserPwd());
int groupId = (model==null?0:model.getGroupId());
Timestamp userRegdate = (model==null?null:model.getUserRegdate());
int userId = PageUtil.parseIntField(request.getParameter("userId"), "userId", false, 0, );
String userName = PageUtil.parseStringField(request.getParameter("userName"), "userName", false, 19);
String userPwd = PageUtil.parseStringField(request.getParameter("userPwd"), "userPwd", false, 19);
int groupId = PageUtil.parseIntField(request.getParameter("groupId"), "groupId", false, 0, 19);


int userId = PageUtil.parseIntField(request.getParameter("userId"), "userId", false, 0, 2147483647);
String userName = PageUtil.parseStringField(request.getParameter("userName"), "userName", true, 20);
String userPwd = PageUtil.parseStringField(request.getParameter("userPwd"), "userPwd", true, 20);
int groupId = PageUtil.parseIntField(request.getParameter("groupId"), "groupId", true, 0, 2147483647);
Timestamp userRegdate = PageUtil.parseDateField(request.getParameter("userRegdate"), "userRegdate", true);
String userId = (model==null?"0":""+model.getUserId());
String userName = (model==null?"":""+model.getUserName());
String userPwd = (model==null?"":""+model.getUserPwd());
String groupId = (model==null?"0":""+model.getGroupId());
Timestamp userRegdate = (model==null?null:model.getUserRegdate());

Collection coll = StaffUserBean.queryAll((pageNum-1) * pageRow + 1, pageNum * pageRow);
Iterator it = coll.iterator();
while ( it.hasNext() ) {
    Hashtable element = (Hashtable)it.next();
    StaffUserModel staffUserModel = StaffUserBean.toModel(element);
    int userId = staffUserModel.getUserId();
    String userName = staffUserModel.getUserName();
    String userPwd = staffUserModel.getUserPwd();
    int groupId = staffUserModel.getGroupId();
    Timestamp userRegdate = staffUserModel.getUserRegdate();

        excel.setCellData(new ExcelCell(ExcelUtil.INT_CELL_TYTE,    currRow, currCol++), userId+"");
        excel.setCellData(new ExcelCell(ExcelUtil.STRING_CELL_TYTE, currRow, currCol++), userName+"");
        excel.setCellData(new ExcelCell(ExcelUtil.STRING_CELL_TYTE, currRow, currCol++), userPwd+"");
        excel.setCellData(new ExcelCell(ExcelUtil.INT_CELL_TYTE,    currRow, currCol++), groupId+"");
        excel.setCellData(new ExcelCell(ExcelUtil.DATE_CELL_TYTE,   currRow, currCol++), userRegdate);
*/