（六）Mybatis-动态sql

一、什么是动态sql

动态sql就是指根据不同的条件生产不同的sql语句。

1.1 传统动态sql

jdbc原生的动态sql：使用StringBuilder和判断条件是否为null，动态拼接。

@Override
    public int getUserCount(Connection connection, String username, int roleId) throws SQLException {
        Integer count = 0;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
//        1. 编写动态sql
//        删选条件都要走动态sql
//        String sql="SELECT COUNT(1) AS COUNT FROM smbms_user u LEFT JOIN smbms_role r ON u.`userRole`=r.`id` WHERE username LIKE ? AND r.id =? ";
//         必须写动态sql，因为无法知道筛选条件有几个
//        基本sql,默认执行
        String sql = "SELECT COUNT(1) AS COUNT FROM smbms_user u LEFT JOIN smbms_role r ON u.`userRole`=r.`id` where 1=1";
        StringBuilder sb = new StringBuilder(sql);

        //2.编写参数
        List<Object> paramsList = new ArrayList<>();

//        如果筛选条件username不为空，则追加sql
        if (!StringUtils.isNullOrEmpty(username)) {
            sb.append(" and u.username LIKE ?");
            paramsList.add("%" + username + "%");
        }
        if (roleId > 0) {
            sb.append(" and u.userRole=? ");
            paramsList.add(roleId);
        }
        sql = sb.toString();
        System.out.println("【UserDaoImpl】最终查询动态sql--->" + sql);

        Object[] params = paramsList.toArray();
//        Object[] params2={username,roleId};
        System.out.println("【UserDaoImpl】最终动态sql参数数组--->" + Arrays.toString(params));

        if (connection != null) {
            preparedStatement = connection.prepareStatement(sql);
//            不用传connection后面用不上了，不用传sql到basedao，因为prepareStatement已经封装了
            resultSet = BaseDao.executeQuery(null, preparedStatement, resultSet, null, params);
        }
        if (resultSet.next()) {
            count = resultSet.getInt("count");
            System.out.println("【UserDaoImpl】查询count数量：" + count);
        }

        BaseDao.release(null, preparedStatement, resultSet);
        return count;
    }

1.2 mybatis的动态sql

Mybatis的强大特性之一便是它的动态sql，如果你有使用jdbc或其他类似框架的经验，你就能体会到根据不同条件拼接sql语句的痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号，利用动态sql这一特性可以彻底拜托这种痛苦。

虽然在以前使用动态sql并非意见易事，但正是mybatis提供了可以被用在任意sql映射语句中的强大动态sql语言得以改进这种情形。

动态sql元素和JSTL或基于类似XML的文本处理器相似。在mybatis之前的版本中，有很多元素要花时间了解。mybatis3大大精简了元素种类，现在只需要学习原来一半的元素即可。

mybatis采用强大的基于OGNL的表达式来淘汰其他大部分元素。

- if
- choose（when，otherwise）
- trim（where，set）
- foreach

二、搭建环境

2.1 创建数据库环境

1 创建表

create table `blog`(
`id` varchar(50) not null comment '博客id',
`title` varchar(100) not null comment '博客标题',
`author` varchar(100) not null comment '博客作则',
`create_time` datetime not null comment '创建时间',
`views` int(30) not null comment '浏览量'
) engine=innodb default charset=utf8

2 插入数据

INSERT INTO blog VALUES ('happy0','title1','happy1',CURRENT_TIMESTAMP,1),
('happy1','title1','happy1',CURRENT_TIMESTAMP,1111),
('happy2','title2','happy2',CURRENT_TIMESTAMP,2222),
('happy3','title3','happy3',CURRENT_TIMESTAMP,3333)

2.2 创建基础工程

1 导入包

<?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>com.happy</groupId>
    <artifactId>mabatis-study</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis-01</module>
        <module>mybatis-02-config</module>
        <module>mybatis-08-动态sql</module>
    </modules>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>


<!--    导入依赖-->
     <dependencies>
<!--         导入mysql驱动-->
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>8.0.28</version>
         </dependency>
         <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
         <dependency>
             <groupId>org.mybatis</groupId>
             <artifactId>mybatis</artifactId>
             <version>3.5.9</version>
         </dependency>
         <dependency>
             <groupId>junit</groupId>
             <artifactId>junit</artifactId>
             <version>4.12</version>
         </dependency>
         <dependency>
             <groupId>org.projectlombok</groupId>
             <artifactId>lombok</artifactId>
             <version>1.16.20</version>
             <scope>provided</scope>
         </dependency>
         <dependency>
             <groupId>org.clojure</groupId>
             <artifactId>java.jdbc</artifactId>
             <version>0.7.11</version>
         </dependency>

         <dependency>
             <groupId>com.github.pagehelper</groupId>
             <artifactId>pagehelper</artifactId>
             <version>5.3.0</version>
         </dependency>

         <dependency>
             <groupId>log4j</groupId>
             <artifactId>log4j</artifactId>
             <version>1.2.17</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 编写配置文件

<?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">
        <property name="pwd" value="123456"/>
    </properties>

    <settings>
    <!--    <setting name="logImpl" value="STDOUT_LOGGING"/>-->
        <setting name="logImpl" value="LOG4J"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="com.happy.pojo"/>
<!--        <typeAlias type="com.happy.pojo.Teacher" alias="teacher"></typeAlias>-->
    </typeAliases>

    <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 -->
            <property name="param1" value="value1"/>
        </plugin>
    </plugins>
    <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="uat">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/shop?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="1987518g"/>
            </dataSource>
        </environment>
    </environments>
<mappers>
<!--    <mapper class="com.happy.dao.UserMapper"></mapper>-->
    <package name="com.happy.dao"/>
<!--    使用*通配符报错-->
<!--    <mapper resource="com/happy/dao/*.xml"></mapper>-->
</mappers>



</configuration>

3 编写实体类

package com.happy.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    //注意这里的不一致，数据为create_time 可以开启驼峰即可，不用起别名或者使用resultType
    private Date createTime;
    private int views;
}

`开启驼峰命名转换`

<settings>
    <!--    <setting name="logImpl" value="STDOUT_LOGGING"/>-->
    <setting name="logImpl" value="LOG4J"/>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

4 编写mapper接口

package com.happy.dao;

import com.happy.pojo.Blog;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface BlogMapper {

    @Select("select * from blog")
    List<Blog> getBlogList();


    int addUser(Blog blog);
}

5 编写mapper.xml

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

<mapper namespace="com.happy.dao.BlogMapper">

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

</mapper>

6 测试使用

`编写IDUtils类获取UUID`

真实实战中常常用UUID作为唯一主键，而不用整数

package com.happy.utils;

import org.junit.Test;

import java.util.UUID;

public class IDUtils {
    public static String getId(){
        String uuid = UUID.randomUUID().toString().replaceAll("-","");
        return uuid;
    }

    @Test
    public void test(){
        System.out.println(IDUtils.getId());
    }
}

package com.happy.dao;

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

import java.util.Date;
import java.util.List;

public class TestBlog {
    @Test
    public void testGetBlogList() {
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            List<Blog> blogList = mapper.getBlogList();
            for (Blog blog : blogList) {
                System.out.println(blog);
            }
        }
    }

    @Test
    public void testAddBlog(){
        try(SqlSession sqlSession=MybatisUtils.getSqlSession()){
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            Blog blog=new Blog(IDUtils.getId(),"title518","happy518",new Date(),518);
            mapper.addUser(blog);

        }
    }
}

三、动态SQL语法

`3.1 if`

动态sql通常要做的事情是根据条件包含where子句的一部分。比如：

1 mapper接口

import com.happy.pojo.Blog;
import org.apache.ibatis.annotations.Select;

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

public interface BlogMapper {

    @Select("select * from blog")
    List<Blog> getBlogList();

    int addUser(Blog blog);

//    传入map灵活设置where condition
    List<Blog> getBlogListIf(Map<String,Object> ifMap);
}

2 mapper文件

<select id="getBlogListIf" 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 测试使用

 @Test
    public void testGetBlogListIf(){
        try(SqlSession sqlSession=MybatisUtils.getSqlSession()){
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            Map<String,Object> ifMap =new HashMap<>();
            ifMap.put("title","title1");
//            ifMap.put("views",2000);
            ifMap.put("author","happy1");
            List<Blog> blogList = mapper.getBlogListIf(ifMap);
            for (Blog blog : blogList) {
                System.out.println(blog);
            }
        }
    }

`3.2 where`

对比上面，代替传统的条件语句拼接的前面where 1=1 and 的方法，使得sql更规范。

where元素只会在至少有一个子元素的条件返回sql子句的情况下才去插入where子句
而且，若语句的开头为"and"或"OR"，where元素也会将他们去除。

mapper文件

<select id="getBlogListIf" 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>

3.3 choose，when，otherwise

有时我们不想应用到所有的条件语句，而只想从中择其一项。

针对这种情况，mybatis提供了choose元素，它有点像java中的Switch语句。

注意：

otherwise里面没有test，为前面都不成立的兜底语句，相当于switch-case里的default:
从上到下匹配，匹配即拼接where条件，不会往下匹配

还是上面的例子，但是这次变为提供了"title"就按title查找，提供了"author"就按author查找的情形。如两者都没有提供，就返回所有符合条件的blog。

1 mapper接口

//    使用choose传入设置where condition
    List<Blog> getBlogListChoose(Map<String,Object> ifMap);

2 mapper文件

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

3 测试使用

 @Test
    public void testGetBlogListChoose(){
        try(SqlSession sqlSession=MybatisUtils.getSqlSession()){
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            Map<String,Object> ifMap =new HashMap<>();
//            ifMap.put("title","title1");
//            ifMap.put("views",2000);
//            ifMap.put("author","happy1");
            List<Blog> blogList = mapper.getBlogListChoose(ifMap);
            for (Blog blog : blogList) {
                System.out.println(blog);
            }
        }
    }

`3.4 set`

原生的update set语句为下面：注意最后一句set分句没有逗号

update `tablename` set 
`columnName1`='value1',
`columnName2`='value2',
`columnName3`='value3'
where 条件

set元素功能：

set元素会动态前置set关键字，
同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的sql语句的后面留下这些逗号

1 mapper接口

    //    使用set
    int updateBlog(Blog blog);

2 mapper文件

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

3 测试使用

 @Test
    public void testUpdateBlog() {
        try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
//            Blog blog = new Blog("6ec43510df574092a8f3a4f3ccb93b3f", "title88", "happy88", null, 88);
            Blog blog = new Blog();
            blog.setId("6ec43510df574092a8f3a4f3ccb93b3f");
            blog.setTitle("title888");
            blog.setViews(888);
            int result = mapper.updateBlog(blog);
            if (result > 0) {
                System.out.println("更新成功：" + blog.getId());
            }
        }
    }

3.5 trim

trim是set和where的爸爸，是通用格式。一般where和set够用了。

如果where元素没有按正常套路出牌，我们可以通过自定义trim元素定制where元素的功能。比如用where元素等价的自定义trim元素为:

<trim prefix="where" prefixOverrides="and|or">
	...
</trim>

如果set元素没有按正常套路出牌，我们可以通过自定义trim元素定制set元素的功能。比如用set元素等价的自定义trim元素为:

<trim prefix="set" suffixOverrides=",">
</trim>

3.6 sql片段

有的时候，我们会将公共的部分抽取出来，方便复用。

使用标签提取公共sql部分，任意部分。
在需要使用的地方用标签引用刚才sql提取的部分。

注意：

尽量不要提取太复杂的sql语句，因为提取的sql片段越复杂，复用的可能性就越少，就越没有提取的意义
不要包含where标签

原来：

<select id="getBlogListIf" 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>

提取后：

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

<select id="getBlogListIf" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <include refid="blogIf"></include>
    </where>
</select>

`3.7 Foreach`

动态sql的另外一个常用的操作需求是对一个集合进行遍历，通常是在构造IN条件语句的时候。比如：

原生的sql语句为下面：注意in的是一个集合

select * from blog where id in ("happy1","happy2","happy3")

使用foreach元素：

foreach元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量，它也允许你指定开头和结尾字符串以及在迭代结果之间放置分隔符，这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

1 mapper接口

//    使用foreach
List<Blog> getBlogListByIds(@Param("ids") List<String> ids);

使用@Param("ids")取名

注意：给List参数使用@Param赋予别名，否则mybatis的mapper.xml的available参数名为list或者collection

2 编写mapper文件

 <select id="getBlogListByIds" parameterType="list" resultType="blog">
        select * from blog
        <where>
            <if test="ids!=null">
                id in
                <foreach item="id" index="index" collection="ids" open=" (" separator="," close=")">
                     #{id}
                </foreach>
            </if>
        </where>
    </select>

3 测试使用

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

            List<String> idList=new ArrayList<String>();
            idList.add("happy1");
            idList.add("happy2");
            idList.add("happy3");
//            String[] idArray={"happy1","happy2","happy3"};

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

3.8 动态sql总结

本质上还是sql语句，只是在sql层面可以增加执行一些逻辑代码。根据不同条件生成不同sql。
建议先在mysql工具里编写sql执行成功后，再使用动态sql语法修改拼接。

posted @ 2022-05-02 18:28 高兴518 阅读(150) 评论(0) 收藏举报

刷新页面返回顶部

happycarpediem

（六）Mybatis-动态sql

（六）Mybatis-动态sql

一、什么是动态sql

1.1 传统动态sql

1.2 mybatis的动态sql

二、搭建环境

2.1 创建数据库环境

1 创建表

2 插入数据

2.2 创建基础工程

1 导入包

2 编写配置文件

3 编写实体类

开启驼峰命名转换

4 编写mapper接口

5 编写mapper.xml

6 测试使用

编写IDUtils类获取UUID

三、动态SQL语法

3.1 if

1 mapper接口

2 mapper文件

3 测试使用

3.2 where

mapper文件

3.3 choose，when，otherwise

1 mapper接口

2 mapper文件

3 测试使用

3.4 set

1 mapper接口

2 mapper文件

3 测试使用

3.5 trim

3.6 sql片段

3.7 Foreach

1 mapper接口

使用@Param("ids")取名

2 编写mapper文件

3 测试使用

3.8 动态sql总结

公告

`开启驼峰命名转换`

`编写IDUtils类获取UUID`

`3.1 if`

`3.2 where`

`3.4 set`

`3.7 Foreach`