用户管理底层实现

当我们点击用户管理的时候。不是像修改密码一样单纯跳转到另一个页面，而是发出请求

点击密码修改：

点击用户管理：

 思路：

至少得查3张表

流程图

实现步骤：

1.导入分页的工具类（自己写的）

2.用户列表的导入

userlist.jsp分析：

 

 底下分页的实现：

一---获取用户数量

  1. UserDao
  2. UserDaoImpl 
     1 // 根据用户名或者角色查询用户总数（最难理解的SQL)
 2     @Override
 3     public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
 4         PreparedStatement pstm = null;
 5         ResultSet rs = null;
 6         int count = 0;
 7 
 8         if (connection != null) {
 9             StringBuffer sql = new StringBuffer();  // 保证线程安全还是用StringBuffer，所以如果需要对字符串进行修改推荐使用 StringBuffer。
10             sql.append("select count(1) as count from smbms_user u, smbms_role r where u.userRole = r.id");
11             ArrayList<Object> list = new ArrayList<>();// 存放我们的参数,万能解决方法
12 
13             if (!StringUtils.isNullOrEmpty(username)) {  // 前台传的用户名不为空，才执行下面的语句
14                 sql.append(" and u.userName like ?"); // and前面空格不要丢，否则报错
15                 list.add("%"+  username+ "%"); // 模糊查询 index:0
16             }
17 
18             if (userRole > 0) {  // 如果前端输入要查询的userrole>0.追加sql语句
19                 sql.append(" and u.userRole = ?");
20                 list.add(userRole); // index:1
21             }
22 
23             // 怎么把list转换为数组
24             Object[] params = list.toArray();
25             System.out.println("UserDaoImpl->getUserCount:" + sql.toString()); // 输出最后完整的sql语句
26 
27             rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
28             if (rs.next()) { // 链表的形式
29                 count = rs.getInt("count");// 从结果集中获取最终的数量
30             }
31             BaseDao.closeResource(null,pstm,rs);
32         }
33         return count;
34 
35     }
  3. UserService
  4. UserserviceImpl 
     1 // 查询记录数
 2     @Override
 3     public int getUserCount(String username, int userRole) {
 4         int count = 0;
 5         Connection connection = null;
 6         try {
 7             connection = BaseDao.getConnection(); // 连接数据库
 8             count = userDao.getUserCount(connection, username, userRole);
 9         } catch (SQLException e) {
10             e.printStackTrace();
11         } finally {
12             BaseDao.closeResource(connection,null,null);
13         }
14 
15       return count;
16     }

二---获取用户列表：

  1. UserDao 
    1 // 通过条件查询userList
2      List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
  2. UserDaoImpl 
     1 @Override
 2     public List<User> getUserList(Connection connection, String userName,int userRole,int currentPageNo, int pageSize)
 3             throws Exception {
 4         // TODO Auto-generated method stub
 5         PreparedStatement pstm = null;
 6         ResultSet rs = null;
 7         List<User> userList = new ArrayList<User>();
 8         if(connection != null){
 9             StringBuffer sql = new StringBuffer();
10             sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
11             List<Object> list = new ArrayList<Object>();
12             if(!StringUtils.isNullOrEmpty(userName)){
13                 sql.append(" and u.userName like ?");
14                 list.add("%"+userName+"%");
15             }
16             if(userRole > 0){
17                 sql.append(" and u.userRole = ?");
18                 list.add(userRole);
19             }
20             sql.append(" order by creationDate DESC limit ?,?");
21             currentPageNo = (currentPageNo-1)*pageSize;
22             list.add(currentPageNo);
23             list.add(pageSize);
24 
25             Object[] params = list.toArray();
26             System.out.println("sql ----> " + sql.toString());
27             rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
28             while(rs.next()){
29                 User user = new User();
30                 user.setId(rs.getInt("id"));
31                 user.setUserCode(rs.getString("userCode"));
32                 user.setUserName(rs.getString("userName"));
33                 user.setGender(rs.getInt("gender"));
34                 user.setBirthday(rs.getDate("birthday"));
35                 user.setPhone(rs.getString("phone"));
36                 user.setUserRole(rs.getInt("userRole"));
37                 user.setUserRoleName(rs.getString("userRoleName"));
38                 userList.add(user);
39             }
40             BaseDao.closeResource(null, pstm, rs);
41         }
42         return userList;
43     }
  3. UserService 
    1 // 根据条件查询用户列表
2      List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
  4. UserServiceImpl 
     1 @Override
 2     public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
 3         // TODO Auto-generated method stub
 4         Connection connection = null;
 5         List<User> userList = null;
 6         System.out.println("queryUserName ---- > " + queryUserName);
 7         System.out.println("queryUserRole ---- > " + queryUserRole);
 8         System.out.println("currentPageNo ---- > " + currentPageNo);
 9         System.out.println("pageSize ---- > " + pageSize);
10         try {
11             connection = BaseDao.getConnection();
12             userList = userDao.getUserList(connection, queryUserName,queryUserRole,currentPageNo,pageSize);
13         } catch (Exception e) {
14             // TODO Auto-generated catch block
15             e.printStackTrace();
16         }finally{
17             BaseDao.closeResource(connection, null, null);
18         }
19         return userList;
20     }

 三---获取角色列表：

1:RoleDao

public interface RoleDao {
    // 获取角色列表
    public List<Role> getRoleList(Connection connection) throws Exception;

}

2:RoleDaoImpl

package com.mine.dao.role;

import com.mine.dao.BaseDao;
import com.mine.pojo.Role;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class RoleDaoImpl implements RoleDao{
    // 获取角色列表
    @Override
    public List<Role> getRoleList(Connection connection) throws Exception {
        PreparedStatement pstm = null;
        ResultSet resultSet = null;
        ArrayList<Role> roleList = new ArrayList<>();
        if(connection != null) {
            String sql = "select * from smbms_role";
            Object[] params = {};
            resultSet = BaseDao.execute(connection, pstm, resultSet, sql, params);
            while (resultSet.next()) {
                Role _role = new Role();
                _role.setId(resultSet.getInt("id"));
                _role.setRoleName(resultSet.getString("roleName"));
                _role.setRoleCode(resultSet.getString("roleCode"));
                roleList.add(_role);
            }
            BaseDao.closeResource(null,pstm,resultSet);
        }
        return roleList;

    }

}

3:RoleService

package com.mine.service.role;

import com.mine.pojo.Role;

import java.util.List;

public interface RoleService {
    public List<Role> getRoleList();
}

4:RoleServiceImpl

package com.mine.service.role;

import com.mine.dao.BaseDao;
import com.mine.dao.role.RoleDao;
import com.mine.dao.role.RoleDaoImpl;
import com.mine.pojo.Role;
import org.junit.Test;

import java.sql.Connection;
import java.util.List;

public class RoleServiceImpl implements RoleService{
    // 引入DAO
    private RoleDao roleDao;
    public RoleServiceImpl(){
        roleDao = new RoleDaoImpl();
    }

    @Override
    public List<Role> getRoleList() {
        Connection connection = null;
        List<Role> roleList = null;
        try {
            connection = BaseDao.getConnection();
            roleList = roleDao.getRoleList(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseDao.closeResource(connection, null, null);
        }
        return roleList;
    }
    @Test
    public void test(){
        RoleServiceImpl roleService = new RoleServiceImpl();
        List<Role> roleList = roleService.getRoleList();
        for (Role role : roleList) {
            System.out.println(role.getRoleName());
        }

    }

}
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/YXBLOGXYY/p/14775686.html