Springboot 集成Mybatis 增删改查 使用XML 动态SQL

依据https://www.cnblogs.com/suphowe/p/13157474.html 进行修改

一、mapper\TestMapper.xml

1、if语句

?
1
2
3
4
5
6
7
8
9
10
<!-- 使用if进行条件判断,where 元素只会在子元素返回任何内容的情况下才插入 "WHERE" 子句。-->
<!-- 而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除 -->
<select id="findByNameUseIf" resultType="com.soft.entity.Test">
    select * from sys_test
    <where>
        <if test="name != null">
        and name=#{name}
        </if>
    </where>
</select>

2、like查询

?
1
2
3
4
5
6
7
8
9
<!-- like 查询 -->
<select id="findByNameUseLike" resultType="com.soft.entity.Test">
    select * from sys_test
    <where>
        <if test="name != null">
            and name like concat('%',#{name},'%')
        </if>
    </where>
</select>

3、Choose、When、Otherwise

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!-- 传入了 “user” 就按 “user” 查找，传入了 “name” 就按 “name” 查找，传入了 “tel” 就按 “tel” 查找的情形。-->
<!-- 若三者都没有传入，就返回标记为 1=1 的 BLOG -->
<select id="findUseChooseWhenOtherwise" resultType="com.soft.entity.Test">
    select * from sys_test where 1=1
    <choose>
        <when test="user != null">
            and user=#{title}
        </when>
        <when test="name != null">
            and name=#{name}
        </when>
        <when test="tel != null">
            and tel like concat('%',#{tel},'%')
        </when>
        <otherwise>
            and 1 = 1
        </otherwise>
    </choose>
</select>

4、Set

?
1
2
3
4
5
6
7
8
9
10
<!-- set 元素可以用于动态包含需要更新的列，忽略其它不更新的列 -->
<update id="updateSysTestUseSet" parameterType="com.soft.entity.Test">
    update sys_test
    <set>
        <if test="user != null">user=#{user},</if>
        <if test="name != null">name=#{name},</if>
        <if test="tel != null">tel=#{tel}</if>
    </set>
    where id=#{id}
</update>

二、Java代码

com\soft\dao\MybatisTestMapper.java

新增:

?
1
2
3
4
5
6
7
List<Test> findByNameUseIf(Test test);
 
List<Test> findByNameUseLike(Test test);
 
List<Test> findUseChooseWhenOtherwise(Test test);
 
int updateSysTestUseSet(Test test);

com\soft\service\impl\MybatisTestServiceImpl.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
@Override
public List<Test> findByNameUseIf(String name) {
    Test test = new Test();
    test.setName(name);
    return mybatisTestMapper.findByNameUseIf(test);
}
 
@Override
public List<Test> findByNameUseLike(String name) {
    Test test = new Test();
    test.setName(name);
    return mybatisTestMapper.findByNameUseLike(test);
}
 
@Override
public List<Test> findUseChooseWhenOtherwise(String user, String name, String tel) {
    Test test = new Test();
    test.setUser(user);
    test.setName(name);
    test.setTel(tel);
    return mybatisTestMapper.findUseChooseWhenOtherwise(test);
}
 
@Override
public HashMap<String, Object> updateSysTestUseSet(int id, String user, String name, String tel) {
    Test test = new Test();
    test.setId(id);
    test.setUser(user);
    test.setName(name);
    test.setTel(tel);
    int result = mybatisTestMapper.updateSysTestUseSet(test);
    HashMap<String, Object> returnMap = new HashMap<>(1);
    returnMap.put("count", result);
    return returnMap;
}

com\soft\service\IMybatisServiceTest.java

新增:

?
1
2
3
4
5
6
7
List<Test> findByNameUseIf(String name);
 
List<Test> findByNameUseLike(String name);
 
List<Test> findUseChooseWhenOtherwise(String user, String name, String tel);
 
HashMap<String, Object> updateSysTestUseSet(int id, String user, String name, String tel);

com\soft\controller\MybatisTestController.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
@RequestMapping(value = "/findByNameUseIf", method = RequestMethod.POST)
@ApiOperation(value = "Mybatis查询测试,动态Sql,if语句", notes = "按名称查询")
@ApiImplicitParams({
        @ApiImplicitParam(paramType = "query", name = "name", value = "name", dataType = "String")
})
public String findByNameUseIf(String name) {
    List<Test> list = mybatisServiceTest.findByNameUseIf(name);
    HashMap<String, Object> result = new HashMap<>();
    bsUtil.createReturnMsg(result, 200, list);
    return new Gson().toJson(result);
}
 
@RequestMapping(value = "/findByNameUseLike", method = RequestMethod.POST)
@ApiOperation(value = "Mybatis查询测试,动态Sql,Like写法", notes = "按名称查询")
@ApiImplicitParams({
        @ApiImplicitParam(paramType = "query", name = "name", value = "name", dataType = "String")
})
public String findByNameUseLike(String name) {
    List<Test> list = mybatisServiceTest.findByNameUseLike(name);
    HashMap<String, Object> result = new HashMap<>();
    bsUtil.createReturnMsg(result, 200, list);
    return new Gson().toJson(result);
}
 
@RequestMapping(value = "/findUseChooseWhenOtherwise", method = RequestMethod.POST)
@ApiOperation(value = "Mybatis查询测试,动态Sql,choose、when、otherwise", notes = "查询")
@ApiImplicitParams({
        @ApiImplicitParam(paramType = "query", name = "user", value = "user", dataType = "String"),
        @ApiImplicitParam(paramType = "query", name = "name", value = "name", dataType = "String"),
        @ApiImplicitParam(paramType = "query", name = "tel", value = "tel", dataType = "String")
})
public String findUseChooseWhenOtherwise(String user, String name, String tel) {
    List<Test> list = mybatisServiceTest.findUseChooseWhenOtherwise(user, name, tel);
    HashMap<String, Object> result = new HashMap<>();
    bsUtil.createReturnMsg(result, 200, list);
    return new Gson().toJson(result);
}
 
@RequestMapping(value = "/updateSysTestUseSet", method = RequestMethod.POST)
@ApiOperation(value = "Mybatis测试,动态Sql,使用Set", notes = "修改")
@ApiImplicitParams({
        @ApiImplicitParam(paramType = "query", name = "id", value = "id", dataType = "int"),
        @ApiImplicitParam(paramType = "query", name = "user", value = "user", dataType = "String"),
        @ApiImplicitParam(paramType = "query", name = "name", value = "name", dataType = "String"),
        @ApiImplicitParam(paramType = "query", name = "tel", value = "tel", dataType = "String")
})
public String updateSysTestUseSet(int id, String user, String name, String tel) {
    HashMap<String, Object> updateSysTest = mybatisServiceTest.updateSysTestUseSet(id, user, name, tel);
    HashMap<String, Object> result = new HashMap<>();
    bsUtil.createReturnMsg(result, 200, updateSysTest);
    return new Gson().toJson(result);
}

三、测试

1、if测试

 

2、like测试

 

 

3、choose、when、otherwise

 

 

 

 4、set

 

posted @   suphowe  阅读(1465)  评论(0)  编辑  收藏  举报
编辑推荐：
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 单元测试从入门到精通
· 上周热点回顾（3.3-3.9）
点击右上角即可分享
微信分享提示
AI IDE Trae

目录导航