【Mybatis】动态SQL 实例

大家好，又见面了，我是你们的朋友全栈君。

目录

1. if

2. where

3. set

4. foreach

5. choose、when、otherwise

6. 完整项目实例

0. 简介

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

本篇文章要讲的mybatis元素主要有

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

首先创建以下运行环境——数据库。

代码语言：javascript复制

CREATE TABLE `blog` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `author` varchar(30) DEFAULT NULL,
  `_time` datetime DEFAULT NULL,
  `read_count` int(11) DEFAULT NULL,
  `label` varchar(50) DEFAULT NULL,
  `_like` int(11) DEFAULT NULL,
  `visible` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of blog
-- ----------------------------
INSERT INTO `blog` VALUES ('116012859', '【Spring】Spring简介、组成及拓展', '牧心.', '2021-04-22 14:44:35', '3823', 'Spring', '1', '1');
INSERT INTO `blog` VALUES ('116017799', '【Spring】IOC理论推导、IOC本质', '牧心.', '2021-04-22 22:01:03', '3829', 'Spring', '1', '1');
INSERT INTO `blog` VALUES ('116492927', '【Spring】一个spring简单实例', '牧心.', '2021-05-07 15:01:39', '3154', 'Spring', '1', '1');
INSERT INTO `blog` VALUES ('116494838', '【Spring】IOC创建对象的方式', '牧心.', '2021-05-07 17:50:21', '3150', 'Spring', '1', '1');
INSERT INTO `blog` VALUES ('116502734', '【Spring】DI依赖注入', '牧心.', '2021-05-12 15:13:05', '2908', 'Spring', '2', '1');
INSERT INTO `blog` VALUES ('117374430', '【Spring】AOP（一）使用Spring的API接口', '牧心.', '2021-05-30 19:00:46', '228', 'Spring', '1', '1');
INSERT INTO `blog` VALUES ('118054659', '【Spring】AOP（二）自定义来实现AOP', '牧心.', '2021-06-19 15:50:23', '192', 'Spring', '1', '1');
INSERT INTO `blog` VALUES ('118058442', '【Spring】AOP（三）注解实现AOP', '牧心.', '2021-06-19 20:06:54', '203', 'Spring', '1', '1');
INSERT INTO `blog` VALUES ('118074003', '【Mybatis】Mybatis入门', '牧心.', '3921-07-20 20:03:04', '172', 'MyBatis', '1', '1');
INSERT INTO `blog` VALUES ('118074590', '【Mybatis】一个Mybatis程序', '牧心.', '2021-06-22 21:03:03', '129', 'MyBatis', '1', '1');
INSERT INTO `blog` VALUES ('118311033', '【Mybatis】增删改查的实现', '牧心.', '2021-06-28 20:20:29', '108', 'MyBatis', '2', '1');
INSERT INTO `blog` VALUES ('118343512', '【Mybatis】Map传参和模糊查询', '牧心.', '2021-06-29 21:22:36', '11', 'MyBatis', '1', '1');
INSERT INTO `blog` VALUES ('118344763', '【Mybatis】配置之属性优化', '牧心.', '2021-06-29 21:59:54', '21', 'MyBatis', '1', '1');

1. if

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如：

代码语言：javascript复制

<select id="getVisibleBlogListByLabel" resultType="com.company.org.pojo.Blog">
    select * from blog
    where visible = 1
    <if test="label != null and label != ''">
        AND label = #{label}
    </if>
</select>

这条语句提供了可选的查找文本功能。如果不传入label，那么只返回visible=1的博客；如果传入了label，那么就会对title一列进行查找并返回对应的结果。

如果希望通过 “title” 和 “label” 两个参数进行可选搜索该怎么办呢？很容易想到，我们可以利用下面的语句：

代码语言：javascript复制

<select id="getBlogListByLabelAndTitle" parameterType="map" resultType="com.company.org.pojo.Blog">
    select * from blog
    where
    <if test="label != null">
        label = #{label}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
</select>

但是，这会产生一个问题，当label为空时，sql语句会变成：

代码语言：javascript复制

select * from blog where and title like 'xxx'

甚至当label和title都为空时，sql语句则变成：

代码语言：javascript复制

select * from blog where

很显然，这样会造成运行错误！

那么，如何解决上面所说的问题呢？mybatis给出了<where>元素。

2. where

上面提到的语句就可以改为以下形式：

代码语言：javascript复制

<select id="getBlogListByLabelAndTitle2" parameterType="map" resultType="com.company.org.pojo.Blog">
    select * from blog
    <where>
        <if test="label != null">
            label = #{label}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
    </where>
</select>

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

代码语言：javascript复制

<select id="getBlogListByLabelAndTitle3" parameterType="map" resultType="com.company.org.pojo.Blog">
    select * from blog
    <trim prefix="where" prefixOverrides="and |or ">
        <if test="label != null">
            label = #{label}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
    </trim>
</select>

到这里，你是否发现上面3个语句都包含：

代码语言：javascript复制

<if test="label != null">
    label = #{label}
</if>
<if test="title != null">
    AND title like #{title}
</if>

这造成了代码的冗余，那我们是否可以将其提出来呢？答案是肯定的，mybatis提供了<sql>元素。

代码语言：javascript复制

<sql id="if-label-title">
    <if test="label != null">
        label = #{label}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
</sql>

需要引入的地方使用<include>元素即可，（<include>就相当于）如：

代码语言：javascript复制

    <select id="getBlogListByLabelAndTitle2" parameterType="map" resultType="com.company.org.pojo.Blog">
        select * from blog
        <where>
<!--            <if test="label != null">-->
<!--                label = #{label}-->
<!--            </if>-->
<!--            <if test="title != null">-->
<!--                AND title like #{title}-->
<!--            </if>-->
            <include refid="if-label-title"></include>
        </where>
    </select>

3. set

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：

代码语言：javascript复制

<update id="updateBlogById">
    update blog
    <set>
        <if test="title != null">title = #{title},</if>
        <if test="label != null">label = #{label},</if>
        <if test="visible != null">visible = #{visible},</if>
    </set>
    where id = #{id}
</update>

4. foreach

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

属性	描述
collection	表示迭代集合的名称，可以使用@Param注解指定，如下图所示 该参数为必选
item	表示本次迭代获取的元素，若collection为List、Set或者数组，则表示其中的元素；若collection为map，则代表key-value的value，该参数为必选
open	表示该语句以什么开始，最常用的是左括弧’(’，注意:mybatis会将该字符拼接到整体的sql语句之前，并且只拼接一次，该参数为可选项
close	表示该语句以什么结束，最常用的是右括弧’)’，注意:mybatis会将该字符拼接到整体的sql语句之后，该参数为可选项
separator	mybatis会在每次迭代后给sql语句append上separator属性指定的字符，该参数为可选项
index	在list、Set和数组中,index表示当前迭代的位置，在map中，index代指是元素的key，该参数是可选项。

比如：表格引自：https://blog.csdn.net/wt_better/article/details/80941646

代码语言：javascript复制

<select id="getBlogListByIds" parameterType="map" resultType="com.company.org.pojo.Blog">
    select * from blog
    <where>
        <foreach collection="ids" item="id" separator="or">
           id = #{id}
        </foreach>
    </where>
</select>

上面这个例子的作用是，查询id在ids（列表）中的博客。

相当于sql语句：

代码语言：javascript复制

select * from blog where id = 116012859 or id = 117374430 or id = 118058442

上面的语句的功能等价于以下语句：

代码语言：javascript复制

<select id="getBlogListByIds" parameterType="map" resultType="com.company.org.pojo.Blog">
    select * from blog
    <where>
        id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
           #{id}
        </foreach>
    </where>
</select>

相当于sql语句：

代码语言：javascript复制

select * from blog where id IN (116012859,117374430,118058442)

5. choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。如：

代码语言：javascript复制

<select id="getBlogListByTitleOrLabel" parameterType="map" resultType="com.company.org.pojo.Blog">
    select * from blog
    <where>
        <choose>
            <when test="label != null">label = #{label}</when>
            <when test="title != null">and title like #{title}</when>
            <otherwise>and visible = #{visible}</otherwise>
        </choose>
    </where>
</select>

其中，when可以有多个，otherwise必须存在！

6. 完整项目实例

项目结构

（1）父工程pom.xml

代码语言：javascript复制

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <!-- 父工程 -->
    <groupId>org.example</groupId>
    <artifactId>mybatisProject</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis-01</module>
        <module>mybatis-02</module>
    </modules>

    <!-- 导入依赖 -->
    <dependencies>
        <!--https://repo.maven.apache.org/maven2/-->
        <!-- mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>
        <!-- mubatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
        <!-- junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
</project>

（2）子工程pom.xml

代码语言：javascript复制

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>mybatisProject</artifactId>
        <groupId>org.example</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis-02</artifactId>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

</project>

（3）Blog.java

代码语言：javascript复制

package com.company.org.pojo;


import java.util.Date;

public class Blog {
    private int id;
    private String title;
    private String author;
    private String time;
    private int read_count;
    private String label;
    private int like;
    private int visible;

    public Blog(){}

    public Blog(int id, String title, String author, String time, int read_count, String label, int like, int visible) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.time = time;
        this.read_count = read_count;
        this.label = label;
        this.like = like;
        this.visible = visible;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public int getRead_count() {
        return read_count;
    }

    public void setRead_count(int read_count) {
        this.read_count = read_count;
    }

    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }

    public int getLike() {
        return like;
    }

    public void setLike(int like) {
        this.like = like;
    }

    public int getVisible() {
        return visible;
    }

    public void setVisible(int visible) {
        this.visible = visible;
    }

    @Override
    public String toString() {
        return "Blog{"  
                "id="   id  
                ", title='"   title   '''  
                ", author='"   author   '''  
                ", time='"   time   '''  
                ", read_count="   read_count  
                ", label='"   label   '''  
                ", like="   like  
                ", visible="   visible  
                '}';
    }
}

（4）BlogMapper.java

代码语言：javascript复制

package com.company.org.dao;

import com.company.org.pojo.Blog;

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

public interface BlogMapper {
    // insert blog
    int addBlog(Blog blog);

    // select all blogs
    List<Blog> getBlogList();

    // select visible blogs by label
    List<Blog> getVisibleBlogListByLabel(String label);

    // select blogs by label and title <if>
    List<Blog> getBlogListByLabelAndTitle(Map<String, Object> map);

    // select blogs by label and title <where>
    List<Blog> getBlogListByLabelAndTitle2(Map<String, Object> map);

    // select blogs by label and title <where>
    List<Blog> getBlogListByLabelAndTitle3(Map<String, Object> map);

    // update blog by id
    int updateBlogById(Map<String, Object> map);

    // select blogs by id-list
    List<Blog> getBlogListByIds(Map<String, Object> map);

    // select blogs / choose、when、otherwise
    List<Blog> getBlogListByTitleOrLabel(Map<String, Object> map);
}

（5）BlogMapper.xml

代码语言：javascript复制

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

<!--https://mybatis.org/mybatis-3/zh/getting-started.html-->
<!-- namespace=绑定一个对应的Dao/Mapper接口 -->
<mapper namespace="com.company.org.dao.BlogMapper">
    <insert id="addBlog" parameterType="com.company.org.pojo.Blog">
        insert into blog(id, title, author, _time, read_count, label, _like, visible) values (#{id}, #{title}, #{author}, #{time}, #{read_count}, #{label}, #{like}, #{visible})
    </insert>

    <select id="getBlogList" resultType="com.company.org.pojo.Blog">
        select * from blog;
    </select>

    <sql id="if-label-title">
        <if test="label != null">
            label = #{label}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
    </sql>

    <select id="getVisibleBlogListByLabel" resultType="com.company.org.pojo.Blog">
        select * from blog
        where visible = 1
        <if test="label != null and label != ''">
            AND label = #{label}
        </if>
    </select>

    <select id="getBlogListByLabelAndTitle" parameterType="map" resultType="com.company.org.pojo.Blog">
        select * from blog
        where
        <if test="label != null">
            label = #{label}
        </if>
        <if test="title != null">
            AND title like #{title}
        </if>
    </select>

    <select id="getBlogListByLabelAndTitle2" parameterType="map" resultType="com.company.org.pojo.Blog">
        select * from blog
        <where>
<!--            <if test="label != null">-->
<!--                label = #{label}-->
<!--            </if>-->
<!--            <if test="title != null">-->
<!--                AND title like #{title}-->
<!--            </if>-->
            <include refid="if-label-title"></include>
        </where>
    </select>

    <select id="getBlogListByLabelAndTitle3" parameterType="map" resultType="com.company.org.pojo.Blog">
        select * from blog
        <trim prefix="where" prefixOverrides="and |or ">
            <if test="label != null">
                label = #{label}
            </if>
            <if test="title != null">
                AND title like #{title}
            </if>
        </trim>
    </select>
    
    <update id="updateBlogById">
        update blog
        <set>
            <if test="title != null">title = #{title},</if>
            <if test="label != null">label = #{label},</if>
            <if test="visible != null">visible = #{visible},</if>
        </set>
        where id = #{id}
    </update>

<!--    <select id="getBlogListByIds" parameterType="map" resultType="com.company.org.pojo.Blog">-->
<!--        select * from blog-->
<!--        <where>-->
<!--            id in-->
<!--            <foreach collection="ids" item="id" open="(" separator="," close=")">-->
<!--               #{id}-->
<!--            </foreach>-->
<!--        </where>-->
<!--    </select>-->

    <select id="getBlogListByIds" parameterType="map" resultType="com.company.org.pojo.Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id"  separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

    <select id="getBlogListByTitleOrLabel" parameterType="map" resultType="com.company.org.pojo.Blog">
        select * from blog
        <where>
            <choose>
                <when test="label != null">label = #{label}</when>
                <when test="title != null">and title like #{title}</when>
                <otherwise>and visible = #{visible}</otherwise>
            </choose>
        </where>
    </select>

</mapper>

（6）MybatisUtils.java

代码语言：javascript复制

package com.company.org.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

// sqlSessionFactory 来构建sqlSession
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            // https://mybatis.org/mybatis-3/zh/getting-started.html
            // 使用Mybatis第一步：获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。
    // SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。
    // 你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

（7）BlogMapperTest.java

代码语言：javascript复制

package com.company.org.dao;

import com.company.org.pojo.Blog;
import com.company.org.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.text.SimpleDateFormat;
import java.util.*;

public class BlogMapperTest {
    @Test
    public void addBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        Blog blog = new Blog(118074003, "【Mybatis】Mybatis入门", "牧心.", " 2021-06-20 20:03:04", 172, "MyBatis", 1, 1);
        Blog blog2 = new Blog(118074590, "【Mybatis】一个Mybatis程序", "牧心.", "2021-06-22 21:03:03", 129, "MyBatis", 1, 1);
        Blog blog3 = new Blog(118311033, "【Mybatis】增删改查的实现", "牧心.", "2021-06-28 20:20:29", 108, "MyBatis", 2, 1);
        Blog blog4 = new Blog(118343512, "【Mybatis】Map传参和模糊查询", "牧心.", "2021-06-29 21:22:36", 11, "MyBatis", 1, 1);
        Blog blog5 = new Blog(118344763, "【Mybatis】配置之属性优化", "牧心.", "2021-06-29 21:59:54", 21, "MyBatis", 1, 1);

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        mapper.addBlog(blog);
        mapper.addBlog(blog2);
        mapper.addBlog(blog3);
        mapper.addBlog(blog4);
        mapper.addBlog(blog5);

        sqlSession.commit();

        sqlSession.close();
    }

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

        List<Blog> blogs = mapper.getBlogList();
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

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

        List<Blog> blogs = mapper.getVisibleBlogListByLabel("");
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
//        map.put("label", "Spring");
        map.put("title", "%AOP%");
        List<Blog> blogs = mapper.getBlogListByLabelAndTitle(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("label", "MyBatis");
//        map.put("title", "%AOP%");
        List<Blog> blogs = mapper.getBlogListByLabelAndTitle2(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
//        map.put("label", "Spring");
        map.put("title", "%AOP%");
        List<Blog> blogs = mapper.getBlogListByLabelAndTitle3(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id", 116502734);
        map.put("title", "【Spring】DI依赖注入-new");
        map.put("visible", 0);
        mapper.updateBlogById(map);
        sqlSession.commit();

        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
        ArrayList<Integer> ids = new ArrayList<Integer>();
        ids.add(116012859);
        ids.add(118058442);
        ids.add(117374430);
        map.put("ids", ids);

        List<Blog> blogs = mapper.getBlogListByIds(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
//        map.put("label", "Spring");
//        map.put("title", "%AOP%");
        map.put("visible", 0);
        List<Blog> blogs = mapper.getBlogListByTitleOrLabel(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
}

（8）mybatis-config.xml

代码语言：javascript复制

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

<!-- configuration 核心配置文件 -->
<configuration>
    <environments default="development">
        <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;userUnicode=true&amp;characterEncoding=utf8&amp;autoReconnect=true&amp;failOverReadOnly=false"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 每一个Mapper.XML都需要在Mybatis核心配置文件中注册！ -->
    <mappers>
        <mapper resource="com/company/org/dao/BlogMapper.xml"/>
    </mappers>
</configuration>

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/152487.html原文链接：https://javaforall.cn

sql java xml mybatis https

0 人点赞