crm系统管理之用户管理分页查询
思路:
首先这是操作3张表的功能,3表连查的分页查询
t_user 用户表
t_role 角色表
t_user_role 用户角色表
1.准备sql
注意 SQL 结尾的分号,在 xml 文件中不需要写,否则这个查询会出现语法错误,是因为我们最后会通过分页工具追加 limit 语句,如果有分号,则语句在分号处结束,limit 就会报错。
SELECT
u.id id,
u.user_name userName,
u.true_name trueName,
u.email email,
u.phone phone,
u.create_date createDate,
u.update_date updateDate,
GROUP_CONCAT(r.id) roleIdsStr,
GROUP_CONCAT(r.role_name) roleName
FROM
t_user u
left join t_user_role ur on u.id=ur.user_id
left join t_role r on ur.role_id = r.id
group by
u.id
2.UserDto.java:记得在 Spring.xml 中添加 Dto 的扫描包。
package com.shsxt.crm.system.dto;
import com.shsxt.crm.system.pojo.User;
/**
* Created by Administrator on 2019/9/5.
*/
public class UserDto extends User{
// 角色 id 字符串
private String roleIdsStr;
// 角色名称字符串
private String roleName;
// 接收前台多个角色 id
private Integer[] roleIds;
public String getRoleIdsStr() {
return roleIdsStr;
}
public void setRoleIdsStr(String roleIdsStr) {
this.roleIdsStr = roleIdsStr;
}
public Integer[] getRoleIds() {
return roleIds;
}
public void setRoleIds(Integer[] roleIds) {
this.roleIds = roleIds;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
}
3.UserQuery.java
package com.shsxt.crm.system.query;
import com.shsxt.crm.base.BaseQuery;
/**
* Created by Administrator on 2019/9/5.
*/
public class UserQuery extends BaseQuery {
private String userName;
private String email;
private String phone;
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
4.最重要因为导入dto,所以以前代码需要重构.
UserMapper.java 接口继承 BaseDao
UserServiceI.java 接口改为抽象类继承 BaseService
删除无用的代码,修改拦截器、service 部分调用方法,耐心的完成这一步,然后测试。
LoginInterceptor -> 改为 userMapper.selectById(id);
SaleChanceServiceImpl -> 改为 userMapper.selectById(id);
删除 UserMapper.java 和 UserService 多余的代码。
修改方法名,xml 文件的返回对象类型。
5.UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shsxt.crm.system.dao.UserMapper">
<resultMap id="BaseResultMap" type="com.shsxt.crm.system.pojo.User">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="user_pwd" jdbcType="VARCHAR" property="userPwd"/>
<result column="true_name" jdbcType="VARCHAR" property="trueName"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="phone" jdbcType="VARCHAR" property="phone"/>
<result column="is_valid" jdbcType="INTEGER" property="isValid"/>
<result column="create_date" jdbcType="TIMESTAMP" property="createDate"/>
<result column="update_date" jdbcType="TIMESTAMP" property="updateDate"/>
</resultMap>
<sql id="Base_Column_List">
id, user_name, user_pwd, true_name, email, phone, is_valid, create_date, update_date
</sql>
<resultMap id="BaseResultUserRoleMap" type="UserDto" extends="BaseResultMap">
<result column="role_name" jdbcType="VARCHAR" property="roleName"/>
</resultMap>
<select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultUserRoleMap">
select
<include refid="Base_Column_List"/>
from t_user
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="delete" parameterType="java.lang.Integer">
delete from t_user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="save" parameterType="com.shsxt.crm.system.pojo.User">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
SELECT LAST_INSERT_ID()
</selectKey>
insert into t_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userName != null">
user_name,
</if>
<if test="userPwd != null">
user_pwd,
</if>
<if test="trueName != null">
true_name,
</if>
<if test="email != null">
email,
</if>
<if test="phone != null">
phone,
</if>
<if test="isValid != null">
is_valid,
</if>
<if test="createDate != null">
create_date,
</if>
<if test="updateDate != null">
update_date,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="userName != null">
#{userName,jdbcType=VARCHAR},
</if>
<if test="userPwd != null">
#{userPwd,jdbcType=VARCHAR},
</if>
<if test="trueName != null">
#{trueName,jdbcType=VARCHAR},
</if>
<if test="email != null">
#{email,jdbcType=VARCHAR},
</if>
<if test="phone != null">
#{phone,jdbcType=VARCHAR},
</if>
<if test="isValid != null">
#{isValid,jdbcType=INTEGER},
</if>
<if test="createDate != null">
#{createDate,jdbcType=TIMESTAMP},
</if>
<if test="updateDate != null">
#{updateDate,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="update" parameterType="com.shsxt.crm.system.dto.UserDto">
update t_user
<set>
<if test="userName != null">
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="userPwd != null">
user_pwd = #{userPwd,jdbcType=VARCHAR},
</if>
<if test="trueName != null">
true_name = #{trueName,jdbcType=VARCHAR},
</if>
<if test="email != null">
email = #{email,jdbcType=VARCHAR},
</if>
<if test="phone != null">
phone = #{phone,jdbcType=VARCHAR},
</if>
<if test="isValid != null">
is_valid = #{isValid,jdbcType=INTEGER},
</if>
<if test="createDate != null">
create_date = #{createDate,jdbcType=TIMESTAMP},
</if>
<if test="updateDate != null">
update_date = #{updateDate,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<!--根据用户名查询用户-->
<select id="selectUserByUserName" resultMap="BaseResultMap">
SELECT
*
FROM
t_user
WHERE
user_name = #{userName}
</select>
<!--查询所有客户经理-->
<select id="selectCustomerLists" resultType="map">
select
u.id,
u.true_name trueName
from
t_user u
left join t_user_role ur on u.id=ur.user_id
left join t_role r on r.id=ur.role_id
where
r.role_name='客户经理'
</select>
<!-- 查询所有用户及所拥有的角色信息-->
<select id="selectByParams" parameterType="UserQuery" resultMap="BaseResultUserRoleMap">
SELECT
u.id id,
u.user_name userName,
u.true_name trueName,
u.email email,
u.phone phone,
u.create_date createDate,
u.update_date updateDate,
GROUP_CONCAT(r.id) roleIdsStr,
GROUP_CONCAT(r.role_name) roleName
FROM
t_user u
LEFT JOIN t_user_role ur
ON u.id = ur.user_id
LEFT JOIN t_role r
ON ur.role_id = r.id
<where>
u.is_valid= 1
<if test="null!=userName and ''!=userName">
and user_name like concat('%',#{userName},'%')
</if>
<if test="null!=email and ''!=email">
and email like concat('%',#{email},'%')
</if>
<if test="null!=phone and ''!=phone">
and phone like concat('%',#{phone},'%')
</if>
</where>
GROUP BY
u.id
</select>
</mapper>
6.UserController.java
package com.shsxt.crm.system.controller;
import com.shsxt.crm.base.BaseController;
import com.shsxt.crm.base.BaseResult;
import com.shsxt.crm.base.util.Base64Util;
import com.shsxt.crm.base.util.CookieUtil;
import com.shsxt.crm.system.dto.UserDto;
import com.shsxt.crm.system.model.UserModel;
import com.shsxt.crm.system.pojo.User;
import com.shsxt.crm.system.query.UserQuery;
import com.shsxt.crm.system.service.UserServiceI;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletRequest;
import java.util.List;
import java.util.Map;
/**
* 用户controller
* Created by Administrator on 2019/8/26.
*/
@Controller
@RequestMapping("/user")
public class UserController extends BaseController{
@Autowired
private UserServiceI userServiceI;
/**
* 用户登录
*
* @param request
* @return
*/
@RequestMapping("/login")
@ResponseBody
public BaseResult userLogin(HttpServletRequest request, User user, String rememberMe) {
UserModel userModel = userServiceI.userLogin(user, rememberMe);
//将用户信息存入session
request.getSession().setAttribute("user", userModel);
return BaseResult.success("登录成功",userModel);
}
/**
* 安全退出
*
* @param request
* @return
*/
@RequestMapping("/logout")
public String userLogout(HttpServletRequest request) {
//清除session信息
request.getSession().removeAttribute("user");
return "login";
}
@RequestMapping("/updateUserPwd")
@ResponseBody
public BaseResult updateUserPwd(HttpServletRequest request, String oldPassword,
String newPassword, String confirmPassword) {
//从cookie获取用户加密id
String userIdStr = CookieUtil.getCookieValue(request, "userIdStr");
//将获取的加密id解密
Integer id = Integer.valueOf(Base64Util.decoder(userIdStr));
//修改密码
userServiceI.updateUserPwd(id, oldPassword, newPassword, confirmPassword);
return BaseResult.success("修改成功");
}
/**
* 查询所有客户经理信息
* @return
*/
@RequestMapping("/selectCustomerLists")
@ResponseBody
public List<Map<Object,Object>> selectCustomerLists(){
return userServiceI.selectCustomerLists();
}
/**
* 跳转用户管理首页
* @return
*/
@RequestMapping("/index")
public String index(){
return "user";
}
/**
*分页查询
* @param query
* @param page
* @param rows
* @return
*/
@RequestMapping("selectUserByParams")
@ResponseBody
public Map<String,Object>selectUserByParams(UserQuery query,
@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "10") Integer rows){
query.setPageNum(page);
query.setPageSize(rows);
return userServiceI.selectForPage(query);
}
/**
*添加或修改用户
* @param userDto
* @return
*/
@RequestMapping("/saveOrUpdateUser")
@ResponseBody
public BaseResult saveOrUpdateUser(UserDto userDto){
userServiceI.saveOrUpdateUser(userDto);
return BaseResult.success();
}
}
7.user.ftl
<html>
<head>
<#include "common.ftl" >
<script type="text/javascript" src="${ctx}/js/user.js"></script>
</head>
<body style="margin: 1px">
<table id="dg" class="easyui-datagrid"
pagination="true" rownumbers="true"
url="${ctx}/user/selectUserByParams" fit="true" toolbar="#tb">
<thead>
<tr>
<th field="cb" checkbox="true" align="center"></th>
<th field="id" width="50" align="center">编号</th>
<th field="userName" width="100" align="center" >用户名</th>
<th field="trueName" width="100" align="center" >真实名称</th>
<th field="email" width="100" align="center" >邮箱</th>
<th field="phone" width="100" align="center" >手机号</th>
<th field="createDate" width="100" align="center" >创建时间</th>
<th field="updateDate" width="100" align="center" >更新时间</th>
<th field="roleName" width="100" align="center">所属角色</th>
</tr>
</thead>
</table>
<div id="tb">
<a href="javascript:openAddUserDailog()" class="easyui-linkbutton" iconCls="icon-save" plain="true">添加</a>
<a href="javascript:openModifyUserDialog()" class="easyui-linkbutton" iconCls="icon-edit" plain="true">更新</a>
<a href="javascript:deleteUser()" class="easyui-linkbutton" iconCls="icon-remove" plain="true">删除</a>
<br/>
用户名:<input type="text" id="userName"/>
邮箱:<input type="text" id="email"/>
手机号:<input type="text" id="phone"/>
<a href="javascript:selectUserByParams()" class="easyui-linkbutton" iconCls="icon-search" plain="true">搜索</a>
</div>
<div id="dlg" class="easyui-dialog" title="添加用户记录" closed="true"
style="width: 500px;height:300px" buttons="#bt">
<form id="fm" method="post">
用户名:<input type="text" class="easyui-validatebox" required="required" id="userName02" name="userName"/><br/><br/>
真实名称:<input type="text" id="trueName" class="easyui-validatebox" name="trueName" required="required"/><br/><br/>
邮箱:<input type="text" id="email02" name="email" class="easyui-validatebox" required="required"/><br/><br/>
手机号:<input type="text" name="phone" id="phone02" class="easyui-validatebox" required="required"/><br/><br/>
角色:<input class="easyui-combobox" id="roleIds" name="roleIds"
valueField="id" textField="roleName" editable="false"
url="${ctx}/role/selectRoleList" panelHeight="auto" multiple="true" /><br/><br/>
<input name="id" id="id" type="hidden"/>
</form>
</div>
<div id="bt">
<a href="javascript:saveOrUpdateUser()" class="easyui-linkbutton" plain="true" iconCls="icon-save">保存</a>
<a href="javascript:closeDlg()" class="easyui-linkbutton" plain="true" iconCls="icon-cancel">取消</a>
</div>
</body>
</html>
分页功能实现
搜索功能实现前台
// 搜索
function selectUserByParams() {
$('#dg').datagrid('load', {
userName: $('#userName').val(),
email: $('#email').val(),
phone: $('#phone').val()
});
}