资讯详情

学习博客：【MyBatis】动态SQL

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

1. 搭建环境

  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>

  2. 编制配置文件

    <?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>

  3. 编写实体类

    import java.util.Date;

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

  4. 编写实体类对应 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

  1. 接口

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

  2. 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>

  3. 测试

    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>

标签

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>

锐单商城 - 一站式电子元器件采购平台