目录
一、动态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 , sort
FROM 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> !!! 重要
| < trim prefix="" suffix="" prefixOverrides="" suffixOverrides="" >代替< where > 、< set > |
<select id="selectBookByCondition" resultType="com.zhp.pojo.Book">
SELECT id,name,author,publish,sort
FROM 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_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>
| 参数 | 描述 | 取值 |
|---|---|---|
| collection | 容器类型 | list、array、map |
| open | 起始符 | ( |
| close | 结束符 | ) |
| separator | 分隔符 | , |
| index | 下标号 | 从0开始，依次递增 |
| item | 当前项 | 任意名称（循环中通过 #{任意名称} 表达式访问） |
添加多个：
<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> |
删除多个：
<delete id="deleteByIds">
delete from t_books where id in
<foreach collection="list" item="id" open="(" separator="," close=")" >
#{id}
</foreach>
</delete> |
测试用例：
<?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">
<!--namespace = 所需实现的接口全限定名-->
<mapper namespace="com.zhp.mapper.BookMapper">
<!--开启 二级缓存 -->
<cache />
<!-- 定义SQL片段 -->
<sql id="books_field">
SELECT id,name,author,publish,sort
</sql>
<select id="selectBookByCondition" resultType="com.zhp.pojo.Book">
<!-- 通过ID引用SQL片段 -->
<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
| 概念：PageHelper是适用于MyBatis框架的一个分页插件，使用方式极为便捷，支持任何复杂的单表、多表分页查询操作。 |
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中测试）
@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”的查询语句。
-
分页插件不支持“嵌套查询”，由于嵌套结果方式会导致结果集被折叠，所以无法保证分页结果数量正确。。
扫一扫
944
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
