springboot数据访问操作

添加操作

异常类

package com.ky.springboot.exceptions;

public class ParamsException extends RuntimeException{

    private Integer code = 500;
    private String msg ="参数异常";


    public ParamsException() {
        super("参数异常");
    }
    public ParamsException(String msg) {
        super(msg);
        this.msg=msg;
    }
    public ParamsException(Integer code) {
        super("参\n数异常");
        this.code=code;
    }

    public ParamsException(Integer code, String msg) {
        super(msg);
        this.code = code;
        this.msg = msg;
    }

    public ParamsException(String message, Integer code, String msg) {
        super(message);
        this.code = code;
        this.msg = msg;
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }
}

工具类

public class AssertUtil {

    public static void isTrue(Boolean flag,String msg){
        if (flag){
            throw new ParamsException(msg);
        }
    }
}

UserMapper类
方法·int addUser(User user);
xml

 <insert id="addUser">
        insert into
            tb_user(user_name, user_pwd)
        values
            (#{userName},#{userPwd})
    </insert>

UserService类

public void addUser(User user) {
        //判断是否为null，StringUtils.isBlank（str）：判断str是否为null
        //判断用户时否为空
        AssertUtil.isTrue(StringUtils.isBlank(user.getUserName()),"用户姓名不能为null");
        //判断密码是否为null
        AssertUtil.isTrue(StringUtils.isBlank(user.getUserName()),"用户密码不能为null");
        //执行添加操作，判断受影响的行数
        int row = userMapper.addUser(user);
        //通过用户名查询用户对象是否存在
        User temp = userMapper.queryUserByName(user.getUserName());
        //判断用户是否存在
        AssertUtil.isTrue(temp!=null,"用户和已存在");
        //判断用户时否存在
        AssertUtil.isTrue(row<1,"添加失败");


    }

Controller类

  @PostMapping("user")
    public Map<String,Object> addUser(User user){


        Map<String,Object> map = new HashMap<>();
        //通过try catch
        try {
            userService.addUser(user);
            map.put("code",200);
            map.put("msg","添加用户成功");
        }catch (ParamsException p){
            map.put("code",p.getCode());
            map.put("msg",p.getMsg());
            p.printStackTrace();
        }catch (Exception e){
            map.put("code",500);
            map.put("msg","添加失败");
            e.printStackTrace();
        }

        //调用service层添加方法，返回map对象
       userService.addUser(user);
        return  map;
    }

修改操作

UserMapper类

 int updateUser(User user);

xml

  <update id="updateUser">
        update
            tb_user
        set
            user_name = #{userName}
            user_pwd = #{userPwd}
        where
            id=#{id}
    </update>

UserService类

  public void  updateUser(User user){

        //判断id
        AssertUtil.isTrue(user.getId() == null,"索引id不存在");
        //判断用户时否为空
        AssertUtil.isTrue(StringUtils.isBlank(user.getUserName()), "用户姓名不能为null");
        //判断密码是否为null
        AssertUtil.isTrue(StringUtils.isBlank(user.getUserName()), "用户密码不能为null");
        //执行添加操作，判断受影响的行数
        int row = userMapper.addUser(user);
        //通过用户名查询用户对象是否存在
        User temp = userMapper.queryUserByName(user.getUserName());
        //判断用户是否存在,如果存在表示用户已存在(修改操作本身存在一条记录，如果要修改的用户是本身占用的名称，是可用的)
        AssertUtil.isTrue(temp != null && !user.getId().equals(temp.getId()), "用户和已存在");
        AssertUtil.isTrue(userMapper.updateUser(user)<1, "添加失败");
    }

Controller类

  @PutMapping("user")

public Map<String,Object> updateUser(User user){


    Map<String,Object> map = new HashMap<>();
    //通过try catch
    try {
        userService.updateUser(user);
        map.put("code",200);
        map.put("msg","修改用户成功");
    }catch (ParamsException p){
        map.put("code",p.getCode());
        map.put("msg",p.getMsg());
        p.printStackTrace();
    }catch (Exception e){
        map.put("code",500);
        map.put("msg","修改失败");
        e.printStackTrace();
    }

    //调用service层添加方法，返回map对象
    userService.updateUser(user);
    return  map;
}

删除操作

UserMapper类

int deleteUserById(Integer id);

xml

 <delete id="deleteUserById">
        delete
            tb_user
        where
            id = #{id}
    </delete>

Service类

//    1.判断参数非空，且删除的记录存在
//    2.执行删除，判断受影响的行数
    public void deleteUserById(Integer id){
//        判断参数非空，且删除的记录存在
        AssertUtil.isTrue(null == id || userMapper.queryUserById(id) == null,"删除记录不存在");
//        执行删除，判断受影响的行数
        AssertUtil.isTrue(userMapper.deleteUserById(id) < 1 ,"删除用户失败");
    }

Controller类

//    删除用户
    @DeleteMapping("user/{id}")
    public Map<String,Object> deleteUser(@PathVariable Integer id){


        Map<String,Object> map = new HashMap<>();
        //通过try catch
        try {
            userService.deleteUserById(id);
            map.put("code",200);
            map.put("msg","删除用户成功");
        }catch (ParamsException p){
            map.put("code",p.getCode());
            map.put("msg",p.getMsg());
            p.printStackTrace();
        }catch (Exception e){
            map.put("code",500);
            map.put("msg","删除失败");
            e.printStackTrace();
        }

        //调用service层添加方法，返回map对象
        userService.deleteUserById(id);
        return  map;
    }

分页条件查询操作

查询类

public class UserQuery {

//    分页参数
    private Integer pageNum = 1;//当前页
    private Integer pageSize = 10;//每页显示的数量
    private String userName;//查询条件；用户名

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }
}

UserMapper类

 //通过指定条件查询用户集合
    List<User> queryUserByParams(UserQuery userQuery);

xml

<select id="queryUserByParams" parameterType="com.ky.springboot.query.UserQuery" resultType="com.ky.springboot.po.User">
    select
    *
    from
       tb_user
    <where>
        <if test="null !=userName and '' != userName">
            and user_name like concat('%',#{userName},'%')
        </if>
    </where>

UserService类

  public PageInfo<User> queryUserByParams(UserQuery userQuery){
        //开启分页
        PageHelper.startPage(userQuery.getPageNum(),userQuery.getPageSize());

        //查询用户集合
        List<User> userList = userMapper.queryUserByParams(userQuery);

        //分页查询
        PageInfo<User> pageInfo = new PageInfo<>(userList);
        return pageInfo;
    }

Controller

//分页条件查询
    @RequestMapping("list")
    public PageInfo<User> queryUserByParams(UserQuery userQuery){
        return userService.queryUserByParams(userQuery);
    }