学习博客：【MyBatis】动态SQL

最新推荐文章于 2024-05-31 09:42:54 发布

「已注销」

最新推荐文章于 2024-05-31 09:42:54 发布

阅读量253

点赞数

分类专栏： # MyBatis 文章标签：学习 sql java mybatis

本文链接：https://blog.csdn.net/Aurinko324/article/details/124809660

版权

MyBatis 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

根据不同条件生成不同的SQL语句

本质还是SQL语句，只是在SQL层面执行逻辑代码

if
choose (when, otherwise)
trim (where, set)
foreach

1. 搭建环境

导包

<dependencies>
    <!--lombok-->
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
        <scope>provided</scope>
    </dependency>

    <!--junit-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

</dependencies>

编写配置文件

<?xml version="1.0" encoding="GBK" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!--核心配置文件-->
<configuration>

    <!--引入外部配置文件-->
    <properties resource="db.properties">
        <property name="username" value="root"/>
        <property name="password" value="yL@98"/>
    </properties>

    <!--日志-->
    <settings>
        <!--标准日志输出-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--<setting name="logImpl" value="LOG4J"/>-->
        <!--驼峰命名转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>


    <!--给实体类起别名-->
    <typeAliases>
        <!--<typeAlias type="com.yl.pojo.User" alias="User"/>-->
        <package name="com.yl.pojo"/>
    </typeAliases>

    <environments default="test">

        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="yL@98"/>
            </dataSource>
        </environment>

        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.yl.dao.BlogMapper"/>
    </mappers>

</configuration>

编写实体类

import java.util.Date;

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date create_time;
    private int views;
}

编写实体类对应 Mapper 接口和 Mapper.xml 文件

import com.yl.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {

}

<?xml version="1.0" encoding="GBK" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--核心配置文件-->
<mapper namespace="com.yl.dao.BlogMapper">


</mapper>

2. if

接口

    //插入数据
    int addBlog(Blog blog);
    //查询博客
    List<Blog> queryBlogIF(Map map);

Mapper.xml

<insert id="addBlog" parameterType="blog">
    insert into blog(id,title,author,create_time,views)
    values(#{id},#{title},#{author},#{createTime},#{views})
</insert>

<!--查询博客-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

测试

public class MapperTest {

    @Test
    public void addBlogTest() {
        SqlSession sqlSession = MyBatisUntils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis");
        blog.setAuthor("杨杨");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Java");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("微服务");
        mapper.addBlog(blog);

        sqlSession.close();
    }

    @Test
    public void queryBlogIF(){
        SqlSession sqlSession = MyBatisUntils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        //map.put("title", "Java");
        map.put("author", "杨杨");
        List<Blog> blogs = mapper.queryBlogIF(map);

        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

}

3. choose (when, otherwise)

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

@Test
public void queryBlogChoose(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    HashMap map = new HashMap();
    //map.put("title", "Java");
    map.put("author", "杨杨");
    map.put("views", 9999);
    List<Blog> blogs = mapper.queryBlogChoose(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

4. trim (where, set)

<!--查询博客-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>

</select>

<update id="updateBlog" parameterType="map">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

@Test
public void updateBlog(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    HashMap map = new HashMap();
    map.put("title", "JVM");
    //map.put("author", "杨");
    map.put("id", "0c1db0ef618b458f964c6b29401d22c1");
    mapper.updateBlog(map);

    sqlSession.close();
}

5. SQL片段

抽取公共部分方便复用

<sql id="if-title-author">
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<!--查询博客-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>

最好基于单表定义SQL片段

不设置where标签

6. foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

接口
```
List<Blog> queryBlogForeach(Map map);
```

xml

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

测试

@Test
public void queryBlogForeach(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    HashMap map = new HashMap();
    ArrayList<Integer> ids = new ArrayList<Integer>();

    ids.add(1);
    ids.add(2);
    ids.add(3);

    map.put("ids", ids);

    List<Blog> blogs = mapper.queryBlogForeach(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

动态SQL本质就是拼接SQL语句

「已注销」

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
学习博客：【MyBatis】动态SQL

根据不同条件生成不同的SQL语句本质还是SQL语句，只是在SQL层面执行逻辑代码ifchoose (when, otherwise)trim (where, set)foreach1. 搭建环境导包<dependencies>   <dependency&gt
复制链接

扫一扫