コード例 #1
0
ファイル: TestDB.java プロジェクト: xsnxj/miniui
  public HashMap SearchEmployees(
      String key, int index, int size, String sortField, String sortOrder) throws Exception {
    // System.Threading.Thread.Sleep(300);
    if (key == null) key = "";

    String sql =
        "select a.*, b.name dept_name, c.name position_name, d.name educational_name \n"
            + "from t_employee a \n"
            + "left join t_department b \n"
            + "on a.dept_id = b.id \n"
            + "left join t_position c \n"
            + "on a.position = c.id \n"
            + "left join t_educational d \n"
            + "on a.educational = d.id \n"
            + "where a.name like '%"
            + key
            + "%' \n";

    if (StringUtil.isNullOrEmpty(sortField) == false) {
      if ("desc".equals(sortOrder) == false) sortOrder = "asc";
      sql += " order by " + sortField + " " + sortOrder;
    } else {
      sql += " order by createtime desc";
    }

    ArrayList dataAll = DBSelect(sql);

    // ʵ��һ���ڴ��ҳ(ʵ��Ӧ��ʹ��SQL��ҳ)
    ArrayList data = new ArrayList();
    int start = index * size, end = start + size;

    for (int i = 0, l = dataAll.size(); i < l; i++) {
      HashMap record = (HashMap) dataAll.get(i);
      if (record == null) continue;
      if (start <= i && i < end) {
        data.add(record);
      }
    }

    HashMap result = new HashMap();
    result.put("data", data);
    result.put("total", dataAll.size());

    // ���һЩ������Ϣ
    // 1)���䣺minAge, maxAge, avgAge
    ArrayList ages =
        DBSelect(
            "select min(age) as minAge, max(age) as maxAge, avg(age) as avgAge from t_employee");
    HashMap ageInfo = (HashMap) ages.get(0);
    result.put("minAge", ageInfo.get("minAge"));
    result.put("maxAge", ageInfo.get("maxAge"));
    result.put("avgAge", ageInfo.get("avgAge"));

    // 2)��Ա���� total

    return result;
  }
コード例 #2
0
ファイル: TestDB.java プロジェクト: xsnxj/miniui
  public String InsertEmployee(HashMap user) throws Exception {
    String id =
        (user.get("id") == null || user.get("id").toString().equals(""))
            ? UUID.randomUUID().toString()
            : user.get("id").toString();
    user.put("id", id);

    if (user.get("name") == null) user.put("name", "");
    if (StringUtil.isNullOrEmpty(user.get("gender").toString())) user.put("gender", 0);

    Connection conn = getConn();

    String sql =
        "insert into t_employee (id, loginname, name, age, married, gender, birthday, country, city, dept_id, position, createtime, salary, educational, school, email, remarks)"
            + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    PreparedStatement stmt = conn.prepareStatement(sql);

    stmt.setString(1, ToString(user.get("id")));
    stmt.setString(2, ToString(user.get("loginname")));
    stmt.setString(3, ToString(user.get("name")));
    stmt.setInt(4, ToInt(user.get("age")));
    stmt.setInt(5, ToInt(user.get("married")));
    stmt.setInt(6, ToInt(user.get("gender")));
    stmt.setTimestamp(7, ToDate(user.get("birthday")));
    stmt.setString(8, ToString(user.get("country")));
    stmt.setString(9, ToString(user.get("city")));
    stmt.setString(10, ToString(user.get("dept_id")));
    stmt.setString(11, ToString(user.get("position")));
    stmt.setTimestamp(12, ToDate(user.get("createtime")));
    stmt.setString(13, ToString(user.get("salary")));
    stmt.setString(14, ToString(user.get("educational")));
    stmt.setString(15, ToString(user.get("school")));
    stmt.setString(16, ToString(user.get("email")));
    stmt.setString(17, ToString(user.get("remarks")));

    stmt.executeUpdate();

    stmt.close();
    conn.close();

    return id;
  }