【SpringBoot】实现增删改查

目录结构

查询全部数据

• controller

    //注入mapper
    @Autowired
    private UserMapper userMapper;
    //查询数据
    @GetMapping
    public List<User> getUser(){
        List<User> userList = userMapper.findAll();
        return userList;
    }
1
2
3
4
5
6
7
8
9

• mapper

    @Select("SELECT * from sys_user")
    List<User> findAll();
1
2

增加数据

• controller

    //新增数据
    @PostMapping
    public Integer adduser(@RequestBody User user){
        System.out.println(user);
        return userMapper.insert(user);
    }
1
2
3
4
5
6

• mapper

    @Insert("INSERT into sys_user(username,password,nickname,email,phone) VALUES (#{username},#{password},#{nickname},#{email},#{phone})")
    int insert(User user);
1
2

修改数据

• controller

    //修改用户
    @PostMapping("/updateuser")
    public Integer updateuser(@RequestBody User user){
        return userMapper.update(user);
    }
1
2
3
4
5

• mapper

    int update(User user);
1

• 动态sql mapper

<?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接口的位置-->
<mapper namespace="com.spring2.mapper.UserMapper">
    <!--sql语句代码-->
    <update id="update">
    update sys_user
    <set>
        <if test="username != null">
            username = #{username},
        </if>
        <if test="password != null">
            password = #{password},
        </if>
        <if test="nickname != null">
            nickname = #{nickname},
        </if>
        <if test="email != null">
            nickname = #{email}
        </if>
        <if test="phone != null">
            nickname = #{phone}
        </if>
    </set>
    <where>
        id = #{id}
    </where>
    </update>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

删除数据

• controller

    //删除用户
    @DeleteMapping("/{id}")
    public Integer deletes(@PathVariable Integer id){
        return userMapper.deleteById(id);
    }
1
2
3
4
5

• mapper

    @Delete("delete from sys_user where id = #{id}")
    Integer deleteById(Integer id);
1
2

分页查询

• controller

    //分页查询
    @GetMapping("/pageList")
    public Object pageList(@RequestParam Integer  pageNum, @RequestParam Integer pageSize){
            pageNum = (pageNum - 1) * pageSize;
            //查询列表总数
            Integer selectTotal = userMapper.selectTotal();
            Map<String, Object> obj = new HashMap<>();
            obj.put("total",selectTotal);
            //分页数据
            List<User> data = userMapper.selectPage(pageNum,pageSize);
            obj.put("data",data);
            return obj;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

• mapper

    //分页查询
    @Select("SELECT * from sys_user limit #{pageNum}, #{pageSize}")
    List<User> selectPage(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);
    //查询总数
    @Select("select count(*) from sys_user")
    Integer selectTotal();
1
2
3
4
5
6