今日学习笔记

条件查询

利用用户ID或姓名查询用户是否存在

default://enquire查询
    User user2 = new User();
    user2.setUserId(req.getParameter("userId"));
    user2.setUserName(req.getParameter("userName"));
    String pageNum = req.getParameter("pageNum");
    String changeNum = req.getParameter("changeNum");
    
    int pl = 10, pn = 1, cn = 0, tn = userService.queryUserCount(user2);

    int tp = tn / pl + (tn % pl == 0 ? 0 : 1);
    if (pageNum != null) {
        pn = Integer.parseInt(pageNum);
    }
    if (changeNum != null) {
        cn = Integer.parseInt(changeNum);
    }

    if (!(pn == 1 && cn == -1) && !(pn == tp && cn == 1)) {
        pn = pn + cn;
    }

    List<User> list = userService.queryUsers(user2, pn, pl);
    req.setAttribute("user", user2);
    req.setAttribute("pageNum", pn);
    req.setAttribute("totalPageNum",tp);
    req.setAttribute("totalNum", tn);
    req.setAttribute("userList", list);
    req.getRequestDispatcher("/pages/user/userList.jsp").forward(req, resp);

在dao层的实现

public int queryUserCount(User u) throws SQLException {
    StringBuffer sql = new StringBuffer("select count(*) as userCount from userInfo u ");
    sql.append(" where 1 = 1 ");
    if (u.getUserId() != null && !"".equals(u.getUserId())) {
        sql.append(" and u.userId like '%" + u.getUserId() + "%' ");
    }
    if (u.getUserName() != null && !"".equals(u.getUserName())) {
        sql.append(" and u.userName like '%" + u.getUserName() + "%' ");
    }
    MySqlDB mySqlDB = new MySqlDB();
    Connection conn = mySqlDB.getConn();
    Statement stmt = null;
    ResultSet rs = null;
    int userCount = 0;
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql.toString());
        while (rs.next()) {
            userCount = rs.getInt("userCount");
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();

    } finally {
        assert rs != null;
        rs.close();
        stmt.close();
        conn.close();
    }
    return userCount;
}

@Override
public User queryUserById(String userId) throws SQLException {
    String sql = "select u.* from userInfo u where u.userId='"+userId+"'";
    User user = null;
    MySqlDB mySqlDB = new MySqlDB();
    Connection conn = mySqlDB.getConn();
    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
             user = new User();
            user.setUserId(rs.getString("userId"));
            user.setUserName(rs.getString("userName"));
            user.setUserPassword(rs.getString("userPassword"));
            user.setRoleId(rs.getInt("roleId"));
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();

    } finally {
        assert rs != null;
        rs.close();
        stmt.close();
        conn.close();
    }
    return user;

 

输入查询条件

 

 返回查询结果