mysql动态sql动态传参 mysql动态sql语句

转载

网络锐评 2023-07-28 10:58:41

文章标签 mysql动态sql动态传参数据库 mysql mybatis java 文章分类 MySQL 数据库

一、动态SQL简介

动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句
我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误
使用 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率

二、动态SQL代码演示

创建数据库表

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `id` varchar(100) NOT NULL,
  `title` varchar(100) NOT NULL,
  `author` varchar(30) NOT NULL,
  `create_time` datetime NOT NULL,
  `view` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据

INSERT INTO `book` VALUES ('0aa6d0b27e614ef5ac7c8666b4ff1932', '西游记', '吴承恩', '2021-04-16 15:36:53', '666');
INSERT INTO `book` VALUES ('46bcb1501a284d75911efdfdca79d5eb', '水浒传', '施耐庵', '2021-04-16 15:36:53', '777');
INSERT INTO `book` VALUES ('32213ab01e02424c99801d5ab1f6b7f5', '三国演义', '罗贯中', '2021-04-16 15:36:53', '888');
INSERT INTO `book` VALUES ('d9356ba6f9774469858d4497656db76c', '红楼梦', '曹雪芹', '2021-04-16 15:36:53', '999');

创建实体类

@Data
public class Book {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int view;

}

创建ID工具类

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

创建数据库工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
            ;
        }
    }

    //获取连接
    public static SqlSession getSession() {
        return sqlSessionFactory.openSession();
    }
}

创建Mappe层

public interface BookMapper {
 
}

创建xml编写sql语句

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

</mapper>

创建核心配置文件层

<?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>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/mybaties?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="admin123"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/lc/mapper/BookMapper.xml"/>
    </mappers>
</configuration>

1、if标签（根据title或者author或者两者查询数据）

接口代码编写

Book getBookByTitleOrName(Book book);

xml文件添加sql语句

<select id="getBookByTitleOrName" resultType="com.lc.entity.Book" parameterType="com.lc.entity.Book">
        select * from book
               <if test="title !=null and title != ''">
                   title=#{title}
               </if>
               <if test="author !=null and author != ''">
                   and author=#{author}
               </if>
    </select>

测试代码

//当title有值，author为空 
    @Test
    public void testQuery() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setTitle("三国演义");
//        book.setAuthor("罗贯中");
        Book bookByTitleOrName = mapper.getBookByTitleOrName(book);
        System.out.println("查询结果为：" + bookByTitleOrName);

    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_数据库

在这种情况下，当title值为空，author有值，我们会发现报错

mysql动态sql动态传参 mysql动态sql语句_mybatis_02

2、where标签（会根据if标签有无值自动判断，并且会删除后面的and或者or拼接符）

xml编写sql语句

<select id="getBookByTitleOrName" resultType="com.lc.entity.Book" parameterType="com.lc.entity.Book">
        select * from book
        <where><if test="title !=null and title != ''">
            title=#{title}
        </if>
            <if test="author !=null and author != ''">
                and author=#{author}
            </if></where>
            
    </select>

测试代码1(title有值，author没有值)

@Test
    public void testQuery() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setTitle("三国演义");
  //      book.setAuthor("罗贯中");
        Book bookByTitleOrName = mapper.getBookByTitleOrName(book);
        System.out.println("查询结果为：" + bookByTitleOrName);

    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_mysql_03

测试代码2(title没有值，author有值)

@Test
    public void testQuery() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
//        book.setTitle("三国演义");
        book.setAuthor("罗贯中");
        Book bookByTitleOrName = mapper.getBookByTitleOrName(book);
        System.out.println("查询结果为：" + bookByTitleOrName);

    }

运行结果:

mysql动态sql动态传参 mysql动态sql语句_mysql_04

测试代码3(title有值，author有值)

@Test
    public void testQuery() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setTitle("三国演义");
        book.setAuthor("罗贯中");
        Book bookByTitleOrName = mapper.getBookByTitleOrName(book);
        System.out.println("查询结果为：" + bookByTitleOrName);

    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_mysql动态sql动态传参_05

3、set标签（类似于查询中的where标签，更新的if标签写在set标签中，也会主动判断其中值是否为空进行拼接）

接口代码编写

int updateBook(Book book);

xml中编写sql语句

<update id="updateBook" parameterType="com.lc.entity.Book">
        update book
        <set>
            <if test="title !=null and title != ''">
                title=#{title},
            </if>
            <if test="author !=null and author != ''">
                author=#{author}
            </if>
        </set>
        where id=#{id}
    </update>

测试代码1（title有值，author无值）

@Test
    public void updateBook() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setId("d9356ba6f9774469858d4497656db76c");
        book.setTitle("红楼梦修改");
//        book.setAuthor("曹雪芹");
        int i = mapper.updateBook(book);
        System.out.println("结果为：" + i);

        session.commit();
        session.close();
    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_java_06

测试代码2（title没有值，author有值）

@Test
    public void updateBook() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setId("d9356ba6f9774469858d4497656db76c");
//        book.setTitle("红楼梦修改");
        book.setAuthor("曹雪芹修改");
        int i = mapper.updateBook(book);
        System.out.println("结果为：" + i);

        session.commit();
        session.close();
    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_mysql动态sql动态传参_07

测试代码3（title有值，author有值）

@Test
    public void updateBook() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setId("d9356ba6f9774469858d4497656db76c");
        book.setTitle("红楼梦再修改");
        book.setAuthor("曹雪芹再修改");
        int i = mapper.updateBook(book);
        System.out.println("结果为：" + i);

        session.commit();
        session.close();
    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_java_08

4、choose标签（类似云java中的switch语句，只要满足其中应该条件即可）

编写接口代码

Book getBookByChoose(Book book);

xml文件编写sql语句

<select id="getBookByChoose" resultType="com.lc.entity.Book">
        select * from book
        <where>
            <choose>
                <when test="title !=null and title != ''">
                    title=#{title}
                </when>
                <when test="author !=null and author != ''">
                    and author=#{author}
                </when>
                <otherwise>
                    and view=#{view}
                </otherwise>
            </choose>
        </where>
    </select>

测试代码（会根据sql语句中的顺序，从title开始到author最后到view搜索那个条件存在，最先匹配的就是这个sql 的唯一条件）

@Test
    public void selectByChoose() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setTitle("西游记");
        book.setAuthor("施耐庵");
        book.setView(888);
        Book mapperBookByChoose = mapper.getBookByChoose(book);
        System.out.println("查询结果为：" + mapperBookByChoose);
    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_mysql_09

5、sql片段标签（把需要多次使用的sql语句放进应该sql片段里面，需要在此使用的时候直接引用，减少了重复代码的编写，但是不能包括where标签）

以上面第一个查询为例子
sql片段编写

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

查询sql语句编写（使用include标签进行引用）

<select id="getBookByTitleOrName" resultType="com.lc.entity.Book" parameterType="com.lc.entity.Book">
        select * from book
        <where>
            <include refid="baseSql"></include>
        </where>

    </select>

测试代码

@Test
    public void testQuery() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setTitle("水浒传");
        book.setAuthor("施耐庵");
        Book bookByTitleOrName = mapper.getBookByTitleOrName(book);
        System.out.println("查询结果为：" + bookByTitleOrName);

    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_mysql_10

6、foreach标签（当查询或者修改的条件为一个集合或者数组时，foreach可以遍历条件进行操作）

查询接口编写

List<Book> getBooks(Map map);

xm中sql语句编写

<!--
       collection:指定输入对象中的集合属性
       item:每次遍历生成的对象
       open:开始遍历时的拼接字符串
       close:结束时拼接的字符串
       separator:遍历对象之间需要拼接的字符串
     -->


 <select id="getBooks" parameterType="map" resultType="com.lc.entity.Book">

        select * from book
        where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

测试代码（查询以下三个id的数据）

@Test
    public void getBookList() {
        SqlSession session = MybatisUtils.getSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        HashMap<String, Object> hashMap = new HashMap<String, Object>();
        ArrayList<String> ids = new ArrayList<String>();
        ids.add("0aa6d0b27e614ef5ac7c8666b4ff1932");
        ids.add("46bcb1501a284d75911efdfdca79d5eb");
        ids.add("32213ab01e02424c99801d5ab1f6b7f5");
        hashMap.put("ids", ids);
        List<Book> books = mapper.getBooks(hashMap);
        for (Book book : books
        ) {
            System.out.println("查询结果为：" + book);
        }

        System.out.println();

    }

运行结果：

mysql动态sql动态传参 mysql动态sql语句_mysql_11