Mybatis的动态SQL和用PageHelper分页

╰+攻爆jí腚メ 2024-03-23 14:15 83阅读 0赞

**目录**

一、动态SQL

1、<sql>

2、<if> <where>

3 、<set>

4、<trim> !!! 重要

5 、<foreach>

二、PageHelper

注意：

--------------------

## 一、动态SQL ##

数据准备

DROP TABLE IF EXISTS `t_books`;
    
    CREATE TABLE `t_books` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `author` varchar(32) DEFAULT NULL,
      `publish` date DEFAULT NULL,
      `sort` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
    
    /*Data for the table `t_books` */
    
    insert  into `t_books`(`id`,`name`,`author`,`publish`,`sort`) values (8,'springcloud','java','2022-07-08',3),(16,'springboot','尤雨曦2','2021-10-22',NULL),(18,'springmvc','xxxooo','2022-04-14',2),(21,'springmvc','xxxooo','2022-04-14',2),(22,'spring','xxx','2022-07-08',NULL),(23,'springmvc','123','2022-07-08',NULL),(25,'springcloud','xxx',NULL,NULL),(26,'vue3','尤雨曦',NULL,NULL),(27,'vue','尤雨曦',NULL,NULL);

实体类

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Book {
        private Integer id;
        private String name;
        private String author;
        private Date publish;
        private Integer sort;
    }

### 1、<sql> ###

<mapper namespace="com.zhp.mapper.BookMapper">
        
        <sql id="books_field">
            SELECT id,name,author,publish,sort
        </sql>
        <select id="selectBookByCondition" resultType="com.glls.mybatis.pojo.Book">
            
            <include refid="books_field"></include>
            from t_books
        </select>

### 2、<if> <where> ###

<select id="selectBookByCondition" resultType="com.zhp.pojo.Book">
        SELECT id , name , author , publish , sort
        FROM t_books
        <where> 
            <if test="id != null"> 
                id = #{id}
            </if>
    
            <if test="name != null">
                and name = #{name}
            </if>
    
            <if test="author != null">
                and author = #{author}
            </if>
    
            <if test="publish != null">
                and publish = #{publish}
            </if>
    
            <if test="sort != null">
                and sort = #{sort}
            </if>
        </where>
    </select>

### 3、<set> ###

<update id="updateBookByCondition">
        UPDATE t_books
        <set>
            <if test="name != null">
                name = #{name} ,
            </if>
    
            <if test="author != null">
                author = #{author} ,
            </if>
    
            <if test="publish != null">
                publish = #{publish} ,
            </if>
    
            <if test="sort != null">
                sort = #{sort} ,
            </if>
        </set>
        WHERE id = #{id}
    </update>

### 4、<trim> !!! 重要 ###

<table style="width:628px;"> 
 <tbody> 
  <tr> 
   <td style="width:625px;"><strong>&lt; trim prefix="" suffix="" prefixOverrides="" suffixOverrides="" &gt;代替&lt; where &gt; 、&lt; set &gt;</strong></td> 
  </tr> 
 </tbody> 
</table>

<select id="selectBookByCondition" resultType="com.zhp.pojo.Book">
    		SELECT id,name,author,publish,sort
        FROM t_books
        <trim prefix="WHERE" prefixOverrides="AND|OR"> 
            <if test="id != null">
                and id = #{id}
            </if>
    
            <if test="name != null">
                and name = #{name}
            </if>
    
            <if test="author != null">
                and author = #{author}
            </if>
    
            <if test="publish != null">
                and publish = #{publish}
            </if>
    
            <if test="sort != null">
                and sort = #{sort}
            </if>
    		</trim>
    </select>

<update id="updateBookByCondition">
    		UPDATE t_books
    		<trim prefix="SET" suffixOverrides=","> 
    				<if test="name != null">
    						name = #{name} ,
    				</if>
    
    				<if test="author != null">
    						author = #{author} ,
    				</if>
    
    				<if test="publish != null">
    						publish = #{publish} ,
    				</if>
    
    				<if test="sort != null">
    						sort = #{sort}
    				</if>
        </trim>
    		WHERE id = #{id}
    </update>

### 5、<foreach> ###

批量删除
    <delete id="deleteBookByIds">
    		DELETE FROM t_books
    		WHERE id IN
    		<foreach collection="list" open="(" separator="," close=")"  item="id" index="i">
    				#{id}
    		</foreach>
    </delete>
    
    
    
    -----批量添加
       <insert id="addBooks">
            insert into t_books values
            <foreach collection="list" item="book"  separator=",">
                (null,#{book.name},#{book.author},#{book.publish},#{book.sort})
            </foreach>
        </insert>

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
   <th>取值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>collection</td> 
   <td>容器类型</td> 
   <td>list、array、map</td> 
  </tr> 
  <tr> 
   <td>open</td> 
   <td>起始符</td> 
   <td>(</td> 
  </tr> 
  <tr> 
   <td>close</td> 
   <td>结束符</td> 
   <td>)</td> 
  </tr> 
  <tr> 
   <td>separator</td> 
   <td>分隔符</td> 
   <td>,</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>下标号</td> 
   <td>从0开始，依次递增</td> 
  </tr> 
  <tr> 
   <td>item</td> 
   <td>当前项</td> 
   <td>任意名称（循环中通过 #{任意名称} 表达式访问）</td> 
  </tr> 
 </tbody> 
</table>

### 添加多个： ###

<table style="width:622px;"> 
 <tbody> 
  <tr> 
   <td style="width:619px;"> <pre>&lt;insert id="addBooks"&gt;
    insert into t_books<span style="background-color:#38d8f0;"> values
        </span><strong><span style="background-color:#38d8f0;">&lt;foreach collection="list" item="book" separator=","&gt;</span></strong><span style="background-color:#38d8f0;">
            (null,#{book.name},#{book.author},#{book.publish},#{book.sort})
        &lt;/foreach&gt;</span>
&lt;/insert&gt;</pre> </td> 
  </tr> 
 </tbody> 
</table>

## 删除多个： ##

<table style="width:622px;"> 
 <tbody> 
  <tr> 
   <td style="width:619px;"> <pre>&lt;delete id="deleteByIds"&gt;
    delete from t_books <span style="background-color:#38d8f0;">where id in
   </span><strong><span style="background-color:#38d8f0;"> &lt;foreach collection="list" item="id" open="(" separator="," close=")" &gt;</span></strong>
        #{id}
    &lt;/foreach&gt;
&lt;/delete&gt;</pre> </td> 
  </tr> 
 </tbody> 
</table>

测试用例：

<?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.zhp.mapper.BookMapper">
        
        <cache />
    
    
        
        <sql id="books_field">
            SELECT id,name,author,publish,sort
        </sql>
    
    
    
        <select id="selectBookByCondition" resultType="com.zhp.pojo.Book">
            
            <include refid="books_field"></include>
            from t_books
            <where>
            <if test="name!=null and name != ''">
                name=#{name}
            </if>
            <if test="author!=null and author != '' ">
                and author=#{author}
            </if>
            </where>
        </select>
        <select id="selectBookByCondition2" resultType="com.zhp.pojo.Book">
            <include refid="books_field"></include>
            from t_books
            <trim prefix="where" prefixOverrides="and|or">
                <if test="id != null">
                    and id = #{id}
                </if>
    
                <if test="name != null">
                    and name = #{name}
                </if>
    
                <if test="author != null">
                    and author = #{author}
                </if>
    
                <if test="publish != null">
                    and publish = #{publish}
                </if>
    
                <if test="sort != null">
                    and sort = #{sort}
                </if>
            </trim>
        </select>
    
    
    
        <update id="updateBookByCondition">
            update t_books
            <set>
                <if test="name !=null and name != ''">
                    name =#{name}
                </if>
                <if test="author != null">
                    author = #{author} ,
                </if>
    
                <if test="publish != null">
                    publish = #{publish} ,
                </if>
    
                <if test="sort != null">
                    sort = #{sort} ,
                </if>
            </set>
    
            where id =  #{id}
        </update>
    
    
    
        <update id="updateBookByCondition2">
            update t_books
            <trim prefix="set" suffixOverrides=",">
                <if test="name != null">
                    name = #{name} ,
                </if>
    
                <if test="author != null">
                    author = #{author} ,
                </if>
    
                <if test="publish != null">
                    publish = #{publish} ,
                </if>
    
                <if test="sort != null">
                    sort = #{sort},
                </if>
            </trim>
    
            where id =  #{id}
        </update>
    
        <delete id="deleteBookByIds">
            delete  from t_books where id in
            <foreach collection="list" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </delete>
    
    
        <insert id="addBooks">
            insert into t_books values
            <foreach collection="list" item="book" separator="," >
                (null,#{book.name},#{book.author},#{book.publish},#{book.sort} )
            </foreach>
        </insert>
    
        <select id="selectBookById" resultType="com.glls.mybatis.pojo.Book">
            select *  from t_books where id = #{id}
        </select>
    
        <delete id="deleteBookById">
            delete from t_books where id =#{id}
    
        </delete>
    </mapper>

## 二、PageHelper ##

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>概念：PageHelper是适用于MyBatis框架的一个分页插件，使用方式极为便捷，支持任何复杂的单表、多表分页查询操作。</td> 
  </tr> 
 </tbody> 
</table>

1、引入依赖

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

2、配置MyBatis-config.xml，**在别名下面**

3、使用PageHelper提供的静态方法设置分页查询条件。（在test中测试）

@Test
        public void selectAllBook() {
            BookMapper mapper = MyBatisUtils.getMapper(BookMapper.class);
    
            PageHelper.startPage(3,2);
            List<Book> books = mapper.selectAllBook();
            books.forEach(System.out::println);
    
            //将分页查询的结果集保存在PageInfo对象中
            PageInfo<Book> bookPageInfo = new PageInfo<>(books);
            System.out.println(bookPageInfo);
        }

#### 注意： ####

*  只有在PageHelper.startPage()方法之后的**第一个查询会有执行分页。**
 *  分页插件**不支持带有“for update”**的查询语句。
 *  分页插件**不支持“嵌套查询”**，由于嵌套结果方式会导致结果集被折叠，所以无法保证分页结果数量正确。。