本篇描述如何实现对User信息进行单表的批量新增、批量删除、单笔更新、批量查询功能。
1、新建实体类和user数据库表,类变量与user表字段对应。
2、在pojo包下再新建结果类 Result,封装接口反参的结构。
package com.example.big_event.pojo;
import lombok.Data;
import java.io.Serializable;
@Data
public class Result<T> implements Serializable {
private boolean success;
private T data;
private String errorMessage;
// 私有构造函数,只能通过静态方法创建实例
private Result() {}
// 创建成功的结果
public static <T> Result<T> success(T data) {
Result<T> result = new Result<>();
result.success = true;
result.data = data;
return result;
}
// 创建失败的结果
public static <T> Result<T> error(String errorMessage) {
Result<T> result = new Result<>();
result.success = false;
result.errorMessage = errorMessage;
return result;
}
}
3、在dto包下新建两个类,分别封装接口入参UserQueryDto和接口出参UserResultDto,入参UserQueryDto中继承实体类User,新增参数pageSize、pageNo,以支持后续实现的分页查询。
package com.example.big_event.dto;
import com.example.big_event.pojo.User;
import lombok.Data;
@Data
public class UserQueryDto extends User {
//一页 多少条数据
private int pageSize =10;
//第几页
private int pageNo =1;
}
package com.example.big_event.dto;
import com.example.big_event.pojo.User;
import lombok.Data;
import java.util.List;
@Data
public class UserResultDto {
//总数
private int count;
//页码
private int pageNo;
//用户集合
List<User> Users;
}
4、 在controller包下新建UserController,实现向外暴露接口。
package com.example.big_event.controller;
import com.example.big_event.dto.UserQueryDto;
import com.example.big_event.dto.UserResultDto;
import com.example.big_event.pojo.Result;
import com.example.big_event.pojo.User;
import com.example.big_event.service.UserService;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
@RestController
@RequestMapping("/user")
public class UserController {
@Resource
private UserService userService;
/**
* 查询用户列表
* @param UserQueryDto
* @return
*/
@PostMapping("/getUserList")
public Result getUserList(@RequestBody UserQueryDto UserQueryDto){
UserResultDto resultDto =userService.getUserByPram(UserQueryDto);
return Result.success(resultDto);
}
/**
* 新增 用户列表
* @param user
* @return
*/
@PostMapping("/addUser")
public Result addUser(@RequestBody User user){
int count = userService.addUser(user);
if(count > 0){
return Result.success("新增成功");
}else{
return Result.error("新增失败");
}
}
/**
* 批量删除用户数据
* @param id
* @return
*/
@GetMapping("/deleteUser")
public Result deleteUsers(String id){
if(StringUtils.isEmpty(id)){
return Result.error("删除失败,ID为空");
}
if (userService.deleteUserByPram(id) > 0) {
return Result.success("删除成功!");
} else {
return Result.error("删除失败!");
}
}
/**
* 更新用户信息
* @param user
* @return
*/
@PostMapping("/updateUser")
public Result updateUser(@RequestBody User user){
if(StringUtils.isEmpty(user.getId())){
return Result.error("更新失败,ID为空!");
}
if(userService.updaetUser(user) > 0){
return Result.success("更新成功!");
}else{
return Result.error("更新失败!");
}
}
5、 在service包下新建抽象类UserService,定义增删改查的抽象方法。进行事务管理,可在类上方添加注解@Transactional(rollbackFor = Exception.class),出现异常就会回滚。
package com.example.big_event.service;
import com.example.big_event.dto.UserQueryDto;
import com.example.big_event.dto.UserResultDto;
import com.example.big_event.pojo.User;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
//事务 出现异常回滚
@Transactional(rollbackFor = Exception.class)
public interface UserService {
//查询 列表用户信息
UserResultDto getUserByPram(UserQueryDto userQueryDto);
//单笔删除 根据id删除
int deleteUserByPram(String id);
//新增用户
int addUser(User user);
//更新用户
int updaetUser(User user);
}
6、在impl包中新建UserServiceImpl类,实现抽象类UserService。每个接口的具体业务逻辑可写在此处。
package com.example.big_event.service.impl;
import com.example.big_event.dto.UserQueryDto;
import com.example.big_event.dto.UserResultDto;
import com.example.big_event.mapper.UserMapper;
import com.example.big_event.pojo.User;
import com.example.big_event.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserMapper userMapper;
/**
* 查询 列表用户信息
* @return
*/
@Override
public UserResultDto getUserByPram(UserQueryDto userQueryDto) {
UserResultDto result = new UserResultDto();
result.setPageNo(userQueryDto.getPageNo());
//分页查询,计算查询从第几笔到第N笔的数据
userQueryDto.setPageNo((userQueryDto.getPageNo()-1) * userQueryDto.getPageSize());
List<User> userList = userMapper.getUserByPram(userQueryDto);
int count = userMapper.getCountByPram(userQueryDto);
result.setUsers(userList);
result.setCount(count);
return result;
}
@Override
public int deleteUserByPram(String id) {
//假设与前端人员约定进行批量删除时,多笔数据在id以英文逗号分割
String[] ids = id.split(",");
return userMapper.deleteUserByPram(ids);
}
@Override
public int addUser(User user) {
return userMapper.addUser(user);
}
@Override
public int updaetUser(User user) {
return userMapper.updateUser(user);
}
}
7、mapper包下新建抽象类UserMapper,该层定义增删改查的抽象方法。
package com.example.big_event.mapper;
import com.example.big_event.dto.UserQueryDto;
import com.example.big_event.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface UserMapper {
//查询 列表用户信息
List<User> getUserByPram(@Param("dto") UserQueryDto userQueryDto);
int getCountByPram(@Param("dto") UserQueryDto userQueryDto);
int deleteUserByPram(@Param("ids") String[] ids);
int addUser(User user);
int updateUser(User user);
}
8、src/main/resources/mapper路径下,新建UserMapper.xml文件,以配置xml文件的方式实现与数据库的交互。
8.1 单表操作使用resultMap标签,实现指定实体类中变量property与数据库字段column的一一对应
8.2 增删改查id= “抽象类UserMapper中的抽象方法名”
8.3 变量需使用#,而不是$,可以防止SQL注入
<?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">
<!--namespace = 所需实现的接口全限定名-->
<mapper namespace="com.example.big_event.mapper.UserMapper">
<resultMap id="userMap" type="com.example.big_event.pojo.User">
<id property="id" column="ID" ></id>
<result property="userName" column="USERNAME"></result>
<result property="passWord" column="PASSWORD"></result>
<result property="nickName" column="NICK_NAME"></result>
<result property="email" column="EMAIL"></result>
<result property="userPic" column="USER_PIC"></result>
<result property="createDate" column="CREATE_TIME"></result>
<result property="updateDate" column="UPDATE_TIME"></result>
</resultMap>
<insert id="addUser">
insert into user(ID,USERNAME,PASSWORD,NICK_NAME,EMAIL,USER_PIC,CREATE_TIME)
value(#{id},#{userName},#{passWord},#{nickName},#{email},#{userPic},SYSDATE())
</insert>
<update id="updateUser">
update user
<set>
<if test="userName != '' and userName != null">
USERNAME = #{userName}
</if>
<if test="passWord !='' and passWord != null">
,PASSWORD = #{passWord}
</if>
<if test="nickName != '' and nickName != null">
,NICK_NAME = #{nickName}
</if>
<if test="email !='' and email != null">
,EMAIL = #{email}
</if>
,UPDATE_TIME = SYSDATE()
</set>
where
ID = #{id}
</update>
<select id="getUserByPram" resultMap="userMap">
select ID,USERNAME,PASSWORD,NICK_NAME,EMAIL,USER_PIC,CREATE_TIME,UPDATE_TIME from user
where 1=1
<if test="dto.userName != '' and dto.userName != null">
and USERNAME like concat('%',#{dto.userName},'%')
</if>
<if test="dto.id != '' and dto.id != null">
and ID like concat('%',#{dto.id},'%')
</if>
<if test="dto.id != '' and dto.id != null">
and NICK_NAME like concat('%',#{dto.nickName},'%')
</if>
<if test="dto.id != '' and dto.id != null">
and EMAIL like concat('%',#{dto.email},'%')
</if>
limit #{dto.pageNo},#{dto.pageSize}
</select>
<select id="getCountByPram" resultType="java.lang.Integer">
select count(1) from user
where 1=1
<if test="dto.userName != '' and dto.userName != null">
and USERNAME like concat('%',#{dto.userName},'%')
</if>
</select>
<delete id="deleteUserByPram">
delete from user
where ID in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
</mapper>
9、若UserMapper.xml文件不在启动类的包或子包下,启动类上需添加注解@MapperScan("com.example.big_event.mapper"),否则项目启动时无法扫描到文件(Bean扫描和Bean注册-CSDN博客)
10、启动项目后,调用接口,此处以新增为例。