目录

一、动态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片段 --><sql id="books_field">SELECT id,name,author,publish,sort</sql><select id="selectBookByCondition" resultType="com.glls.mybatis.pojo.Book"><!-- 通过ID引用SQL片段 --><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 , sortFROM t_books<where> <if test="id != null"> <!-- WHERE，会自动忽略前后缀（如：and | or） -->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"><!-- where子句中满足条件的if，会自动忽略后缀（如：,） -->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> !!! 重要

<select id="selectBookByCondition" resultType="com.zhp.pojo.Book">SELECT id,name,author,publish,sortFROM t_books<trim prefix="WHERE" prefixOverrides="AND|OR"> <!-- 增加WHERE前缀，自动忽略前缀 --><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=","> <!-- 增加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></trim>WHERE id = #{id}
</update>

5、<foreach>

批量删除
<delete id="deleteBookByIds">DELETE FROM t_booksWHERE 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>

二、PageHelper

1、引入依赖

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

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

<configuration><typeAliases></typeAliases><plugins><!-- com.github.pagehelper为PageHelper类所在包名 --><plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin></plugins><environments>...</environments>
</configuration>

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

 @Testpublic 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”的查询语句。

• 分页插件不支持“嵌套查询”，由于嵌套结果方式会导致结果集被折叠，所以无法保证分页结果数量正确。。