用户信息的增删改查:
1.数据表的设计与分析:
所需要的数据表有2张,分别为:user_p,user_info
建表语句:
user_p的语句:
CREATE TABLE `USER_P` (
`USER_ID` varchar(40) NOT NULL,
`DEPT_ID` varchar(40) DEFAULT NULL,
`USERNAME` varchar(50) DEFAULT NULL,
`PASSWORD` varchar(20) DEFAULT NULL,
`STATE` int(11) DEFAULT NULL COMMENT '1启用0停用',
`CREATE_BY` varchar(40) DEFAULT NULL,
`CREATE_DEPT` varchar(40) DEFAULT NULL,
`CREATE_TIME` timestamp NULL DEFAULT NULL,
`UPDATE_BY` varchar(40) DEFAULT NULL,
`UPDATE_TIME` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `USER_P` VALUES ('1','1','smeb','123123',1,'koro',NULL,NULL,NULL,NULL),('2','2','mata','123123',1,'koro',NULL,NULL,NULL,NULL),('c955260c-dd29-4a0b-9a79-b94fdce60240','2','Mouse','123123',1,NULL,NULL,NULL,NULL,NULL);
user_info_p的建表语句:
CREATE TABLE `USER_INFO_P` (
`USER_INFO_ID` varchar(40) NOT NULL,
`NAME` varchar(20) DEFAULT NULL,
`CARD_NO` varchar(20) DEFAULT NULL,
`MANAGER_ID` varchar(40) DEFAULT NULL,
`JOIN_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`SALARY` decimal(8,2) DEFAULT NULL,
`BIRTHDAY` timestamp NULL DEFAULT NULL,
`GENDER` char(1) DEFAULT NULL,
`STATION` varchar(20) DEFAULT NULL,
`TELEPHONE` varchar(100) DEFAULT NULL,
`USER_LEVEL` char(1) DEFAULT NULL COMMENT '4-普通用户 3-部门经理 2-副总 1-总经理 0-超级管理员',
`REMARK` varchar(600) DEFAULT NULL,
`ORDER_NO` int(11) DEFAULT NULL,
`CREATE_BY` varchar(40) DEFAULT NULL COMMENT '登录人编号',
`CREATE_DEPT` varchar(40) DEFAULT NULL COMMENT '登录人所属部门编号',
`CREATE_TIME` timestamp NULL DEFAULT NULL,
`UPDATE_BY` varchar(40) DEFAULT NULL,
`UPDATE_TIME` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`USER_INFO_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `USER_INFO_P` VALUES ('1','smeb','123123123','--???--','2018-01-03 16:00:00',2500.00,NULL,'?','top1','12312312312','4','?',NULL,NULL,NULL,NULL,NULL,NULL),('2','mata','234234234',NULL,'2018-01-04 02:15:29',2222.00,NULL,'男','辅助','23423423423','2','游走',NULL,NULL,NULL,NULL,NULL,NULL),('c955260c-dd29-4a0b-9a79-b94fdce60240','?','66666666666','1','2017-12-31 16:00:00',10111.00,'2018-01-02 16:00:00','?','??','12312312314','4','?????????',NULL,NULL,NULL,NULL,NULL,NULL);
用户增删改查的sql语句:
新增用户:新增一条数据需要添加信息的表:user_p, user_info_p,两者关系为一一对应,user_p为主表,user_info_p为副表。
user_p中: insert into user_p (user_id,username,password,state,dept_id) values (#{userId},#{username},#{password},#{state},#{dept.deptId})
user_info_p中:insert into user_info_p (user_info_id,name,card_no,manager_id,join_date,salary,birthday,gender,station,telephone,user_level,remark) values (#{userInfoId},#{name},#{cardNo},#{managerUserInfo.userInfoId},#{joinDate},#{salary},#{birthday},#{gender},#{station},#{telephone},#{userLevel},#{remark})
两者通过id相关联 user_id = user_info_id
删除用户:
user_p中: delete from user_p WHERE user_id in(..........);
user_info_p中:insert into user_info_p (user_info_id,name,card_no,manager_id,join_date,salary,birthday,gender,station,telephone,user_level,remark) values (#{userInfoId},#{name},#{cardNo},#{managerUserInfo.userInfoId},#{joinDate},#{salary},#{birthday},#{gender},#{station},#{telephone},#{userLevel},#{remark})
在删除过程中,将用户id分别传入这两个sql语句当中,执行同步删除。
修改用户:
user_p中: update user_p set state = #{state} where user_id in(..........)
user_info_p中:update user_info_p set name=#{name},card_no=#{cardNo},manager_id=#{managerUserInfo.userInfoId},join_Date=#{joinDate},salary=#{salary},birthday=#{birthday},gender=#{gender},station=#{station},telephone=#{telephone},user_level=#{userLevel},remark=#{remark} where user_info_id=#{userInfoId}
在修改过程中,将用户id分别传入这两个sql语句当中,执行同步修改。
查询用户:
查询用户稍微比较麻烦一点,牵扯到四表查询(自链接的一个表当作两个表,分为三步进行)
第一步:确定主从表
user_p 表 1
user_info_p ui 表 2
user_info_p mui 表 3
dept 表4
第二步:确定表与表的主从关系
a.表2和表3为自关联,两者关联查询出当前用户的上级是谁? 第一次查询->得出新表,取名new
b.新表new和user一一对应,为一对一关联,查询出当前用户的详细信息,包括它的上级,得出新表取名new2
c.新表new2和dept一一对应,为一对一关联,在当前用户的信息在新增其所在的部门 两者查询,得出最后的信息,也就是最终的sql语句。
第三步:书写sql语句,按照上面的步骤,整合sql语句
a. select * from user_info_p ui left join user_info_p mui on ui.manager_id = mui.m_id. =>ui,mui均为别名
这样查询出来重复的字段太多,所以优化一些,只查询固定字段。
select * from user_info_p ui left join (select user_info_id m_id, name m_name from user_info_p) mui on ui.manager_id = nui.m_id
b. select * from user left join (new) ui on u.user_id = ui.user_info_id =>. user_id = user_info_id两个id关联
然后将new替换为上一次的查询语句,得出的结果为:
select * from
(select user_id,dept_id,username,password,state from user_p) u =>因为也有些重复字段,所以查询某些必要字段名
left join
(select * from
user_info_p ui
left join
(select user_info_id m_id,name m_name from user_info_p ) mui
on ui.manager_id = mui.m_id) ui
on u.user_id = ui.user_info_id
c.将得到的新表与dept相关联
select * from
(new2) u
left join
(select dept_id d_id, dept_name from dept_p) d =>这个同样有重复数据,只查询某些字段
ON u.dept_id=d.d_id
将new2替换为上面=得出的sql语句,最终得出最后的sql语句
select * from
(select * from
(select user_id, dept_id, username, password, state from user_p) u
left join
(select * from
user_info_p ui
left join
(select user_info_id m_id, name m_name from user_info_p) mui
on ui.manager_id=mui.m_id)ui
on u.user_id=ui.user_info_id) u
left join
(select dept_id d_id, dept_name from dept_p) d
on u.dept_id=d.d_id
这样就得出了最终的查询的sql语句。
2.用户增删改查的代码逻辑实现
增加用户:(设置好jsp页面,从外到里一层一层写)
controller层:(UserController)
因为新增用户的时候,有选择用户的上级,所属部门等下拉列表,所以要查询其相关信息,然后在新增页面中将数据渲染出来,
代码:
定义新增方法,跳转到对应页面 //跳转到新增页面
@RequestMapping("/tocreate")
public String create(Model model) {
//得到所有的部门
List<Dept> depts = deptService.findAll(); ->在day01的时候就写过了
model.addAttribute("depts", depts);
//得到所有用户
model.addAttribute("users", userService.findAll());
return "/sysadmin/user/UserCreate";
}
用户填写完表单,将数据提交到服务器,然后处理逻辑,用户新增逻辑的方法如下:
@RequestMapping("/save")
public String save(User user) {
userService.saveUser(user);
return "redirect:/sysadmin/user/list";
}
Service层:
UserService接口:
public interface UserService
void saveUser(User user);
}
在UserServiceImp中实现这个方法
public void saveUser(User user) //生产用户的id,id为唯一性的,所以用uuid
String userId = UUID.randomUUID().toString();
//分别为user和userInfo传递要操作的用户id
user.setUserId(userId);
user.getUserInfo().setUserInfoId(userId);
// TODO Auto-generated method stub
//调用两张表的保存方法
userMapper.saveUser(user);
userInfoMapper.saveUser(user.getUserInfo());
}
分别在userMapper,和userInfoMapper中定义save方法
这里就不做演示,详情在源码中查看。以后修改,查询等方法,也是。
在usemaper.xml中,书写sql语句
<insert id="saveUser">
insert into user_p (user_id,username,password,state,dept_id) values (#{userId},#{username},#{password},#{state},#{dept.deptId})
</insert>
这样就是完整的流程了,之后修改等=,删除等方法和这个是一样的,直接上源码:
UserController:
package cn.tedu.controller;
import cn.tedu.pojo.Dept;
import cn.tedu.pojo.User;
import cn.tedu.service.DeptService;
import cn.tedu.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import java.util.List;
@Controller
@RequestMapping("/sysadmin/user")
public class UserController extends BaseController {
@Autowired
private UserService userService;
@Autowired
private DeptService deptService;
@RequestMapping("/list")
public String list(Model model) {
List<User> users = userService.findAll();
model.addAttribute("users", users);
return "/sysadmin/user/UserList";
}
//点击禁用实现通用功能
@RequestMapping("/stop1")
public String stop(Model model, @RequestParam(value = "userId", required = false) String[] userIds) {
if (userIds != null) {
userService.changeState(0, userIds);
}
//重定向当前页面
return "redirect:/sysadmin/user/list";
}
//点击启用实现通用功能
@RequestMapping("/start1")
public String start(Model model, @RequestParam(value = "userId", required = false) String[] userIds) {
if (userIds != null) {
userService.changeState(1, userIds);
}
//重定向当前页面
return "redirect:/sysadmin/user/list";
}
@RequestMapping("/delete")
public String delete(@RequestParam(value = "userId", required = false) String[] userIds) {
if (userIds != null) {
userService.delete(userIds);
}
return "redirect:/sysadmin/user/list";
}
//跳转到新增页面
@RequestMapping("/tocreate")
public String create(Model model) {
//得到所有的部门
List<Dept> depts = deptService.findAll();
model.addAttribute("depts", depts);
//得到所有用户
model.addAttribute("users", userService.findAll());
return "/sysadmin/user/UserCreate";
}
@RequestMapping("/save")
public String save(User user) {
userService.saveUser(user);
return "redirect:/sysadmin/user/list";
}
@RequestMapping("/toupdate")
public String toupdate(Model model,String userId){
User user = userService.findOneById(userId);
List<Dept> depts = deptService.findAll();
List<User> users = userService.findAll();
model.addAttribute("user",user);
model.addAttribute("users",users);
model.addAttribute("depts",depts);
return "/sysadmin/user/jUserUpdate";
}
@RequestMapping("/update")
public String update(User user){
userService.update(user);
return "redirect:/sysadmin/user/list";
}
}
BaseController:
package cn.tedu.controller;
import java.text.SimpleDateFormat;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.web.bind.ServletRequestDataBinder;
import org.springframework.web.bind.annotation.InitBinder;
public abstract class BaseController {
@InitBinder
public void InitBinder(ServletRequestDataBinder binder) {
//注册自定义的编辑器
binder.registerCustomEditor(java.util.Date.class,
new CustomDateEditor(
new SimpleDateFormat("yyyy-MM-dd"), true));
//后面的true代表的是 允许为null false 不允许null
}
}
UserService:
package cn.tedu.service;
import cn.tedu.pojo.User;
import java.util.List;
public interface UserService {
public List<User> findAll();
public void changeState(int state,String[] userIds);
void delete(String[] userIds);
void saveUser(User user);
User findOneById(String userId);
void update(User user);
}
UserServiceImp:
package cn.tedu.service;
import cn.tedu.mapper.UserInfoMapper;
import cn.tedu.mapper.UserMapper;
import cn.tedu.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.UUID;
@Service
public class UserServiceImp implements UserService {
@Autowired
private UserMapper userMapper;
@Autowired
private UserInfoMapper userInfoMapper;
public List<User> findAll() {
return userMapper.findAll();
}
public void changeState(int state, String[] userIds) {
userMapper.changeState(state, userIds);
}
public void delete(String[] userIds) {
userMapper.delete(userIds);
userInfoMapper.delete(userIds);
}
public void saveUser(User user) {
//生产用户的id
String userId = UUID.randomUUID().toString();
user.setUserId(userId);
user.getUserInfo().setUserInfoId(userId);
// TODO Auto-generated method stub
userMapper.saveUser(user);
userInfoMapper.saveUser(user.getUserInfo());
}
public User findOneById(String userId) {
// TODO Auto-generated method stub
return userMapper.findOneById(userId);
}
public void update(User user) {
//因为提交过来的数据user里面有id userinfo里面没有id
user.getUserInfo().setUserInfoId(user.getUserId());
userMapper.update(user);
userInfoMapper.update(user.getUserInfo());
}
}
UserMapper:
package cn.tedu.mapper;
import cn.tedu.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserMapper {
public List<User> findAll();
void changeState(@Param("state") int state, @Param("userIds")String[] userIds);
void delete(String[] userIds);
void saveUser(User user);
User findOneById(String userId);
void update(User user);
}
UserInfoMapper:
package cn.tedu.mapper;
import cn.tedu.pojo.UserInfo;
public interface UserInfoMapper {
void delete(String[] userIds);
void saveUser(UserInfo userInfo);
void update(UserInfo userInfo);
}
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="cn.tedu.mapper.UserMapper">
<resultMap id="userRM" type="User" autoMapping="true">
<id column="user_id" property="userId"/>
<association property="userInfo" javaType="UserInfo">
<id column="user_info_id" property="userInfoId"/>
<result column="name" property="name"/>
<result column="card_no" property="cardNo"/>
<result column="join_date" property="joinDate"/>
<result column="salary" property="salary"/>
<result column="birthday" property="birthday"/>
<result column="gender" property="gender"/>
<result column="station" property="station"/>
<result column="telephone" property="telephone"/>
<result column="userLevel" property="userLevel"/>
<result column="remark" property="remark"/>
<result column="order_no" property="orderNo"/>
<association property="managerUserInfo" javaType="UserInfo">
<id column="m_id" property="userInfoId"/>
<result column="m_name" property="name"/>
</association>
</association>
<association property="dept" javaType="Dept">
<id column="d_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
</association>
</resultMap>
<select id="findAll" resultMap="userRM">
SELECT * FROM
(SELECT * FROM
(SELECT user_id, dept_id, username, PASSWORD, state FROM user_p) u
LEFT JOIN
(SELECT * FROM
user_info_p ui
LEFT JOIN
(SELECT user_info_id m_id, NAME m_name FROM user_info_p) mui
ON ui.manager_id=mui.m_id)ui
ON u.USER_ID=ui.user_info_id) u
LEFT JOIN
(SELECT dept_id d_id, dept_name FROM dept_p) d
ON u.dept_id=d.d_id
</select>
<update id="changeState">
update user_p set state = #{state} where user_id in
<foreach collection="userIds" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</update>
<delete id="delete">
delete from user_p WHERE user_id in (<foreach collection="array" item="id" separator=",">
#{id}
</foreach>)
</delete>
<insert id="saveUser">
insert into user_p (user_id,username,password,state,dept_id) values (#{userId},#{username},#{password},#{state},#{dept.deptId})
</insert>
<select id="findOneById" resultMap="userRM">
SELECT * FROM
(SELECT * FROM
(SELECT user_id,dept_id,username,PASSWORD,state FROM user_p) u
LEFT JOIN
(SELECT * FROM
user_info_p ui
LEFT JOIN
(SELECT user_info_id m_id, NAME m_name FROM user_info_p) mui
ON ui.MANAGER_ID=mui.m_ID) ui
ON u.USER_ID = ui.user_info_id
)u
LEFT JOIN
(SELECT dept_id d_id,dept_name FROM dept_p) d
ON u.dept_id = d.d_id WHERE user_id = #{userId}
</select>
<update id="update">
update user_p set username=#{username},password=#{password},state=#{state},dept_id=#{dept.deptId} where user_id=#{userId}
</update>
</mapper>
UserInfoMapper.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="cn.tedu.mapper.UserInfoMapper">
<delete id="delete">
delete from user_info_p WHERE user_info_id in (<foreach collection="array" item="id" separator=",">
#{id}
</foreach>)
</delete>
<insert id="saveUser">
insert into user_info_p (user_info_id,name,card_no,manager_id,join_date,salary,birthday,gender,station,telephone,user_level,remark) values (#{userInfoId},#{name},#{cardNo},#{managerUserInfo.userInfoId},#{joinDate},#{salary},#{birthday},#{gender},#{station},#{telephone},#{userLevel},#{remark})
</insert>
<update id="update">
update user_info_p set name=#{name},card_no=#{cardNo},manager_id=#{managerUserInfo.userInfoId},join_Date=#{joinDate},salary=#{salary},birthday=#{birthday},gender=#{gender},station=#{station},telephone=#{telephone},user_level=#{userLevel},remark=#{remark} where user_info_id=#{userInfoId}
</update>
</mapper>
这样所有的代码就书写完毕了,页面在如下地址:
https://gitee.com/smeb1/HuiTongday02/