Springboot 集成Mybatis 使用注解SQL

一、建立表sys_user_tab

?
1
2
3
4
5
6
7
8
9
CREATE TABLE `suphowe`.`Untitled`  (
  `id` int(11) NOT NULL,
  `user` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `addr` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `duty` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

 二、添加依赖

?
1
2
3
4
5
6
<!-- mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>

　　

 三、配置properties

?
1
2
3
# ===================mybatis========================
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.soft.entity

 

 

四、编写Java

User.java
?
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
package com.soft.entity;
 
public class User {
 
    private Integer id;
 
    private String user;
 
    private String name;
 
    private String tel;
 
    private String addr;
 
    private String duty;
 
    public Integer getId() {
        return id;
    }
 
    public void setId(Integer id) {
        this.id = id;
    }
 
    public String getUser() {
        return user;
    }
 
    public void setUser(String user) {
        this.user = user;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getTel() {
        return tel;
    }
 
    public void setTel(String tel) {
        this.tel = tel;
    }
 
    public String getAddr() {
        return addr;
    }
 
    public void setAddr(String addr) {
        this.addr = addr;
    }
 
    public String getDuty() {
        return duty;
    }
 
    public void setDuty(String duty) {
        this.duty = duty;
    }
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", user='" + user + '\'' +
                ", name='" + name + '\'' +
                ", tel='" + tel + '\'' +
                ", addr='" + addr + '\'' +
                ", duty='" + duty + '\'' +
                '}';
    }
}

　　

UserController.java
?
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
package com.soft.controller;
 
import com.google.gson.Gson;
import com.soft.entity.User;
import com.soft.service.IUserService;
import com.soft.util.BsUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
 
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.util.HashMap;
import java.util.List;
 
@RestController
@Api(value = "Mybatis测试,注解")
@RequestMapping(value = "/user")
public class UserController {
 
    @Autowired
    public IUserService userService;
 
    @Autowired
    public BsUtil bsUtil;
 
    @RequestMapping(value = "/findUser", method = RequestMethod.POST)
    @ApiOperation(value = "Mybatis查询测试", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "id", value = "id", required = false, dataType = "Integer")
    })
    public String findUser(Integer id) {
        List<User> list = userService.findUser(id);
        HashMap<String, Object> result = new HashMap<String, Object>();
        bsUtil.createReturnMsg(result, "0", list);
        return new Gson().toJson(result);
    }
 
    @RequestMapping(value = "/login", method = RequestMethod.POST)
    @ApiOperation(value = "User登陆测试", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "user", value = "user", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "passwd", value = "passwd", required = false, dataType = "String")
    })
    public String login(String user, String passwd) {
        int code = 0;
        String message = "success";
        if(!user.equals("admin") || !passwd.equals("admin123")){
            code = 1;
            message = "fail to login";
        }
        HashMap<String, Object> result = new HashMap<String, Object>();
        HashMap<String, Object> meta = new HashMap<String, Object>();
        meta.put("code", code);
        meta.put("message", message);
        result.put("meta", meta);
        return new Gson().toJson(result);
    }
 
    @RequestMapping(value = "/findUserByName", method = RequestMethod.POST)
    @ApiOperation(value = "Mybatis查询测试LIKE", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "name", value = "name", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "beginRow", value = "beginRow", required = false, dataType = "int")
    })
    public String findUserByName(String name, int beginRow) {
        List<User> list = userService.findUserByName(name, beginRow);
        HashMap<String, Object> result = new HashMap<String, Object>();
        bsUtil.createReturnMsg(result, "0", list);
        return new Gson().toJson(result);
    }
 
    @RequestMapping(value = "/findUserChoose", method = RequestMethod.POST)
    @ApiOperation(value = "Mybatis查询测试,注解choose", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "id", value = "id", required = false, dataType = "Integer"),
            @ApiImplicitParam(paramType = "query", name = "name", value = "name", required = false, dataType = "String")
    })
    public String findUserChoose(Integer id, String name) {
        List<User> list = userService.findUserChoose(id, name);
        HashMap<String, Object> result = new HashMap<String, Object>();
        bsUtil.createReturnMsg(result, "0", list);
        return new Gson().toJson(result);
    }
 
    @RequestMapping(value = "/insertUser", method = RequestMethod.POST)
    @ApiOperation(value = "Mybatis新增测试", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "id", value = "id", required = false, dataType = "Integer"),
            @ApiImplicitParam(paramType = "query", name = "user", value = "user", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "name", value = "name", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "tel", value = "tel", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "addr", value = "addr", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "duty", value = "duty", required = false, dataType = "String")
    })
    public String insertUser(Integer id, String user, String name, String tel, String addr, String duty) {
        userService.insertUser(id, user, name, tel, addr, duty);
        HashMap<String, Object> result = new HashMap<String, Object>();
        bsUtil.createReturnMsg(result, "0", null);
        return new Gson().toJson(result);
    }
 
    @RequestMapping(value = "/updateUser", method = RequestMethod.POST)
    @ApiOperation(value = "Mybatis修改测试", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "id", value = "id", required = false, dataType = "Integer"),
            @ApiImplicitParam(paramType = "query", name = "user", value = "user", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "name", value = "name", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "tel", value = "tel", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "addr", value = "addr", required = false, dataType = "String"),
            @ApiImplicitParam(paramType = "query", name = "duty", value = "duty", required = false, dataType = "String")
    })
    public String updateUser(Integer id, String user, String name, String tel, String addr, String duty) {
        userService.updateUser(id, user, name, tel, addr, duty);
        HashMap<String, Object> result = new HashMap<String, Object>();
        bsUtil.createReturnMsg(result, "0", null);
        return new Gson().toJson(result);
    }
 
    @RequestMapping(value = "/deleteUser", method = RequestMethod.POST)
    @ApiOperation(value = "Mybatis删除测试", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "id", value = "id", required = false, dataType = "Integer")
    })
    public String deleteUser(Integer id) {
        userService.deleteUser(id);
        HashMap<String, Object> result = new HashMap<String, Object>();
        bsUtil.createReturnMsg(result, "0", null);
        return new Gson().toJson(result);
    }
 
    @RequestMapping(value = "upload", produces = "text/plain;charset=UTF-8", method = RequestMethod.POST)
    @ApiOperation(value = "文件上传测试", notes = "")
    public String upload(HttpServletRequest request, MultipartFile file) throws Exception{
        //file对象名记得和前端name属性值一致
        System.out.println(new String (file.getOriginalFilename().getBytes("ISO-8859-1"), "UTF-8"));
        System.out.println(file.getOriginalFilename());
        System.out.println(file.getName());
        HashMap reshm = new HashMap();
        reshm.put("code", 0);
        reshm.put("message", "success");
        return new Gson().toJson(reshm);
    }
}

　　

IUserService.java
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.soft.service;
 
 
import com.soft.entity.User;
 
import java.util.List;
 
public interface IUserService {
 
    List<User> findUser(Integer id);
 
    List<User> findUserByName(String name, int beginRow);
 
    List<User> findUserChoose(Integer id, String name);
 
    void insertUser(Integer id, String user, String name, String tel, String addr, String duty);
 
    void updateUser(Integer id, String user, String name, String tel, String addr, String duty);
 
    void deleteUser(Integer id);
}

　　

UserService.java
?
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
package com.soft.service.impl;
 
import com.soft.dao.UserDao;
import com.soft.entity.User;
import com.soft.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
@Service
public class UserService implements IUserService {
 
    /**
     * 事务控制,手动
     */
    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;
 
    /**
     * 事务控制,手动
     */
    @Autowired
    private TransactionDefinition transactionDefinition;
 
    @Autowired
    UserDao userDao;
 
    @Override
    public List<User> findUser(Integer id){
        return userDao.findUser(id);
    }
 
    @Override
    public List<User> findUserByName(String name, int beginRow){
        return userDao.findUserByName(name, beginRow);
    }
 
    @Override
    public List<User> findUserChoose(Integer id, String name){
        return userDao.findUserChoose(id, name);
    }
 
    @Override
    public void deleteUser(Integer id){
        userDao.deleteUser(id);
    }
}

　　

UserDao.java
?
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
package com.soft.dao;
 
import com.soft.entity.User;
import org.apache.ibatis.annotations.*;
 
import java.util.List;
 
@Mapper
public interface UserDao {
 
    /**
     * if 对内容进行判断
     * 在注解方法中，若要使用MyBatis的动态SQL，需要编写在<script></script>标签内
     * 在 <script></script>内使用特殊符号，则使用java的转义字符，如  双引号 "" 使用"" 代替
     * concat函数：mysql拼接字符串的函数
     */
    @Select("<script>" +
            "select * from sys_user_tab " +
            "<where>" +
            "   <if test='id!=null and id!=""'>" +
            "   and id=#{id}" +
            "   </if>" +
            "</where>" +
            "</script>"
    )
    List<User> findUser(@Param("id") Integer id);
 
    /**
     * if 对内容进行判断
     * 在注解方法中，若要使用MyBatis的动态SQL，需要编写在<script></script>标签内
     * 在 <script></script>内使用特殊符号，则使用java的转义字符，如  双引号 "" 使用"" 代替
     * concat函数：mysql拼接字符串的函数
     */
    @Select("<script>" +
            "select * from sys_user_tab " +
            "<where>" +
            "   <if test='name!=null and name!=""'>" +
            "   and name like CONCAT('%', #{name}, '%')" +
            "   </if>" +
            "   <if test='beginRow!=null and beginRow!=""'>" +
            "   limit #{beginRow}, 10" +
            "   </if>" +
            "</where>" +
            "</script>"
    )
    List<User> findUserByName(@Param("name") String name, @Param("beginRow") int beginRow);
 
    /**
     * choose when otherwise 类似Java的Switch，选择某一项
     * when...when...otherwise... == if... if...else...
     * choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束
     * 当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql
     */
    @Select("<script>" +
            "select * from sys_user_tab " +
            "<where>" +
            "   <choose>" +
            "       <when test='id!=null and id!=""'>" +
            "           and id=#{id}" +
            "       </when>" +
            "       <otherwise test='name!=null and name!=""'>" +
            "           and name like CONCAT('%', #{name}, '%')" +
            "       </otherwise>" +
            "   </choose>" +
            "</where>" +
            "</script>"
    )
    List<User> findUserChoose(@Param("id") Integer id, @Param("name") String name);
 
    @Insert("insert into sys_user_tab (id, user, name, tel, addr, duty)" +
            " values (#{id},#{user},#{name},#{tel},#{addr},#{duty})")
    void insertUser(User user);
 
    /**
     * set 动态更新语句，类似<where>
     */
    @Update("<script>" +
            "UPDATE sys_user_tab  " +
            "<set>" +
            "   <if test='user!=null and user!=""'> user=#{user}, </if>" +
            "   <if test='name!=null and name!=""'> name=#{name}, </if>" +
            "   <if test='tel!=null and tel!=""'> tel=#{tel}, </if>" +
            "   <if test='addr!=null and addr!=""'> addr=#{addr}, </if>" +
            "   <if test='duty!=null and duty!=""'> duty=#{duty}, </if>" +
            "</set>" +
            " where id=#{id}" +
            "</script>")
    void updateUser(User user);
 
    /**
     * foreach 遍历一个集合，常用于批量更新和条件语句中的 IN
     * foreach 批量更新
     * (1) item：集合的元素，访问元素的Filed 使用 #{item.Filed}
     * (2) index: 下标，从0开始计数
     * (3) collection：传入的集合参数
     * (4) open：以什么开始
     * (5) separator：以什么作为分隔符
     * (6) close：以什么结束
     */
    @Insert("<script>                                   "
            + "insert into sys_user_tab                 "
            + "(id, user, name, tel, addr, duty) values "
            + "<foreach collection='list' item='item' index='index' separator=','> "
            + "(#{item.id}, #{item.user}, #{item.name}, #{item.tel}, #{item.addr}, #{item.duty}) "
            + "</foreach>                              "
            + "</script>                               ")
    int insertUserListWithForeach(List<User> list);
 
    @Delete("<script>"
          + "DELETE FROM sys_user_tab"
          + "<where>"
          + "<if test='id!=null and id!=""'>"
          + "   and id=#{id}"
          + "</if>"
          + "</where>"
          + "</script>"
    )
    void deleteUser(@Param("id") Integer id);
}

　五、测试

 

 

 

  

配置成功！

 

posted @   suphowe  阅读(4259)  评论(0)  编辑  收藏  举报
编辑推荐：
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示

目录导航