当我们点击用户管理的时候。不是像修改密码一样单纯跳转到另一个页面，而是发出请求
点击密码修改：
点击用户管理：
思路：
至少得查3张表
流程图
实现步骤：
1.导入分页的工具类（自己写的）
2.用户列表的导入
userlist.jsp分析：
底下分页的实现：
一---获取用户数量
- UserDao
- 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 }
- UserService
- 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 }
二---获取用户列表：
- UserDao
1 // 通过条件查询userList 2 List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
- 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 }
- UserService
1 // 根据条件查询用户列表 2 List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
- 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()); } } }