MyBatis之动态SQL

最新推荐文章于 2023-09-14 22:25:18 发布

Java追求者

最新推荐文章于 2023-09-14 22:25:18 发布

阅读量382

点赞数

分类专栏： # MyBatis 文章标签：动态SQL mybatis

本文链接：https://blog.csdn.net/Yearingforthefuture/article/details/116278312

版权

MyBatis 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1. 什么是动态SQL

动态SQL：指不同的条件下生成不同的SQL（拼接SQL）。

2. 环境搭建

2.1表准备

CREATE TABLE IF NOT	EXISTS `blog`(
		`id` VARCHAR(50) NOT NULL COMMENT '博客id',
		`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
		`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
		`create_time` DATETIME NOT NULL COMMENT '创建时间',
		 `views` INT(30) NOT NULL COMMENT'浏览量'
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

2.2 工程准备

创建一个基础工程
导入依赖、日志配置文件和数据库文件
pom.xml

<!--导入依赖-->
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
         <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
    </dependencies>
    <!--在build中配置resources，来防止我们资源导出失败的问题-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

log4j.properties

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/log4j.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?userSSL=true&amp;userUnicode=true&amp;characterEncoding=UTF-8
username=root
password=123456

编写实体类和工具类
Blog.java

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

工具类：
MybatisUtils.java

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession() {
        //自动提交事务
        return sqlSessionFactory.openSession(true);
    }
}

IDUtils.java (生成UUID)

@SuppressWarnings("all")//排除警告
public class IDUtils {
    public static String  getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
    //测试
    @Test
    public void test(){
        System.out.println(getId());
    }
}

编写核心配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration核心配置文件-->
<configuration>
    <properties resource="db.properties" />
    <settings>
        <setting name="logImpl" value="LOG4J"/>
        <!--驼峰命名转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--起别名-->
    <typeAliases>
        <typeAlias type="com.test.pojo.Blog" alias="blog"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <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>
        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?userSSL=true&amp;
                userUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--每一个Mapper. XML都需要在Mybatis核心配置文件中注册!-->
    <mappers>
        <mapper resource="mapper/BlogMapper.xml"></mapper>
    </mappers>
</configuration>

编写相关的实体类Mapper接口和Mapper.xml
BlogMapper.java

public interface BlogMapper {
    //插入博客
    int addBlog(Blog blog);
}

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into blog (id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>
</mapper>

编写测试类进行测试

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

        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("JavaSE");
        blog.setAuthor("张三");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("JavaEE");
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("JavaWeb");
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("SSM");
        mapper.addBlog(blog);


    }
}

3. if

1.BlogMapper.java

public interface BlogMapper {
    //查询博客
    List<Blog> queryBlogIF(Map<String, Object> map);
}

2.BlogMapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.dao.BlogMapper">
    <select id="queryBlogIF" resultType="blog" parameterType="map">
        select * from blog where 1 =1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null ">
            and author = #{author}
        </if>
    </select>
</mapper>

3.测试

@Test
public void test2() {
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
     Map<String, Object> map = new HashMap<String, Object>();
     map.put("title","JavaSE");
     map.put("author","张三");
     List<Blog> blogs = mapper.queryBlogIF(map);
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
     sqlSession.close();
 }

4. when

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

public interface BlogMapper {
    //查询博客
    List<Blog> queryBlogWhere(Map<String, Object> map);
}

2.BlogMapper.xml

<select id="queryBlogWhere" resultType="blog" parameterType="map">
        select * from blog 
        <where>
            <if test="title != null">
                 title = #{title}
            </if>
            <if test="author != null ">
                and author = #{author}
            </if> 
        </where> 
    </select>

3.测试

@Test
public void test3() {
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
     Map<String, Object> map = new HashMap<String, Object>();
    // map.put("title","JavaSE");
     map.put("author","张三");
     List<Blog> blogs = mapper.queryBlogIF(map);
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
     sqlSession.close();
 }

where标签首先判断语句是不是为第一子句，如果不是则会自动帮你去掉and。如果什么都不传则会去掉where。

5. choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
就近原则，选择符合条件的一个执行。
1.BlogMapper.java

public interface BlogMapper {
    //查询博客
    List<Blog> queryBlogChoose(Map<String, Object> map);
}

2.BlogMapper.xml

<select id="queryBlogChoose" resultType="blog" parameterType="map">
   select * from 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>

3.测试

@Test
public void test4() {
     SqlSession sqlSession = MybatisUtils.getSqlSession();
     BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
     Map<String, Object> map = new HashMap<String, Object>();
     map.put("title","JavaSE");
     //map.put("author","张三");
     map.put("views",9999);
     List<Blog> blogs = mapper.queryBlogChoose(map);
     for (Blog blog : blogs) {
         System.out.println(blog);
     }
     sqlSession.close();
 }

6. set

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号
1.BlogMapper.java

public interface BlogMapper {
    //更新博客
    int updateBlog(Map<String, Object> map);
}

2.BlogMapper.xml

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

3.测试

@Test
public void test5() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("title","JavaSE2");
    map.put("author","张三2");
    map.put("id","51e04b9804484d75b549c6271463c4c4");
    mapper.updateBlog(map);

    sqlSession.close();
}

set会判断后面是否跟子句，如果不跟就自动去掉逗号。

7. trim（where,set）

trim：定制化功能

prefix：前缀
prefixOverrides：去除前缀
suffix：后缀
suffixOverrides：去除后缀

前面实现的标签的功能都是定制化来的

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

不熟的话，建议多做一些练习题
可参考：https://www.pianshen.com/article/12411707055/

8. foreach

1.BlogMapper.java

    List<Blog> queryBlogForeach(Map<String, Object> map);

2.BlogMapper.xml

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

3.测试

@Test
    public void test6() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String, Object> map = new HashMap<String, Object>();
        List<String> list = new ArrayList<String>();
        list.add("1");
        list.add("2");
        map.put("ids",list);
        List<Blog> blogList = mapper.queryBlogForeach(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

9. sql片段（了解即可）

将经常用的sql抽取出来，进行复用
1.使用SQL标签抽取公共的部分

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

2.在需要使用的地方使用Include标签弓|用即可

<select id="queryBlogWhere" resultType="blog" parameterType="map">
    select * from blog
    <where>
      <include refid="if-title-author"></include>
    </where>
</select>

Java追求者

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
MyBatis之动态SQL

1. 什么是动态SQL动态SQL：指不同的条件下生成不同的SQL（拼接SQL）。2. 环境搭建2.1表准备CREATE TABLE IF NOT EXISTS `blog`( `id` VARCHAR(50) NOT NULL COMMENT '博客id', `title` VARCHAR(100) NOT NULL COMMENT '博客标题', `author` VARCHAR(30) NOT NULL COMMENT '博客作者', `create_time` DATETIM
复制链接

扫一扫