目标页面:
可以展示出用户管理页面。以下内容都是为了这个页面而工作。
1.导入分页的工具类
//OOP特性:封装(属性私有,get/set,在set中限定一些不安全的情况)
2.用户列表页面导入
建议js和jsp一次性全部导入。
一、获取用户数量
1.UserDao(接口)
//查询用户总数
public int getUserCount(Connection connection,String username,int userRole) throws SQLException;
//userRole是为了根据用户角色查询内容。
2.UserDaoImpl
//根据用户名或角色,查询用户总数
//拼接sql。你可以两个表单都不选,就是select all。选一个名字,就是username=某某,选职位就是userRole=某某
//拼接就是append
public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
int count = 0;
if (connection!=null){
StringBuffer sql = new StringBuffer();
sql.append("select count(*) as count from smbms_user u,smbms_role r where u.userRole = r.id");
ArrayList<Object> list = new ArrayList<Object>();//存放我们的参数
//用户不为空,才追加
if (!StringUtils.isNullOrEmpty(username)){
sql.append(" and u.userName like ?");//记得加空格
list.add("%"+username+"%");//默认的index = 0
}
if (userRole>0){
sql.append(" and u.userRole = ?");
list.add(userRole);
}
//怎么把list转化为数组
Object[] params = list.toArray();
System.out.println("UserDaoImp1->getUserCount"+sql.toString());//输出最后完整的SQL语句
rs = BaseDao.execute(connection, sql.toString(), params, rs, pstm);
if (rs.next()){
count = rs.getInt("count");//从结果集中获取最终的数量
}
BaseDao.closeResource(null, rs, pstm);
}
return count;
}
3.UserService(接口)
//查询记录数
public int getUserCount(String username, int userRole);
4.UserServiceImpl
public int getUserCount(String username, int userRole) {
Connection connection = null;
int count = 0;
try {
connection = BaseDao.getConnection();
count = userDao.getUserCount(connection, username, userRole);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
BaseDao.closeResource(connection, null, null);
}
return count;
}
二.获取用户列表(和一的内容步骤一样,重复度很高)
可以直接用之前github的那个文件,下载上直接cv就可以。
1. UserDao
//通过条件查询-userlist
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)throws Exception;
2.UserDaoImpl
//
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception {
PreparedStatement pstm = null;
ResultSet rs = null;
List<User> userList = new ArrayList<User>();
if(connection != null){
StringBuffer sql = new StringBuffer();
sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
List<Object> list = new ArrayList<Object>();
if(!StringUtils.isNullOrEmpty(userName)){
sql.append(" and u.userName like ?");
list.add("%"+userName+"%");
}
if(userRole > 0){
sql.append(" and u.userRole = ?");
list.add(userRole);
}
sql.append(" order by creationDate DESC limit ?,?");//后面的?是页面客户的多少,是固定的。这个项目是5。前面的?是起始值。
currentPageNo = (currentPageNo-1)*pageSize;
list.add(currentPageNo);
list.add(pageSize);
Object[] params = list.toArray();
System.out.println("sql ----> " + sql.toString());
rs = BaseDao.execute(connection, sql.toString(), params, rs, pstm);
while(rs.next()){
User _user = new User();
_user.setId(rs.getInt("id"));
_user.setUserCode(rs.getString("userCode"));
_user.setUserName(rs.getString("userName"));
_user.setGender(rs.getInt("gender"));
_user.setBirthday(rs.getDate("birthday"));
_user.setPhone(rs.getString("phone"));
_user.setUserRole(rs.getInt("userRole"));
_user.setUserRoleName(rs.getString("userRoleName"));
userList.add(_user);
}
BaseDao.closeResource(null, rs , pstm);
}
return userList;
}
3.UserService
//根据条件查询用户列表
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize);
4.UserServiceImpl
//就两个关键句
public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
Connection connection = null;
List<User> userList = null;
System.out.println("queryUserName ---- > " + queryUserName);
System.out.println("queryUserRole ---- > " + queryUserRole);
System.out.println("currentPageNo ---- > " + currentPageNo);
System.out.println("pageSize ---- > " + pageSize);
try {
connection = BaseDao.getConnection();//关键句1
userList = userDao.getUserList(connection, queryUserName,queryUserRole,currentPageNo,pageSize);//关键句2
} catch (Exception e) {
e.printStackTrace();
}finally{
BaseDao.closeResource(connection, null, null);
}
return userList;
}
测试:
查出所有管理员的数量和查询密码。
正确!