这是我参与11月更文挑战的第7天,活动详情查看:2021最后一次更文挑战
动态SQL
概述
动态SQL是MYbatis的强大功能之一, 动态SQL就是同一个方法根据不同的条件表示不同的SQL语句,查询不同的结果,主要是where的条件变化,使用mybatis提供的标签实现动态SQL的功能,主要有if,where ,foreach,sql等,
动态SQL 第一个if标签
语法
<if test="boolean判断结果">
sql语句
</if>
<--在mapper中-->
<select id="findUserListVo" resultMap="UserVo">
select * from user
<if test="条件">
sql 语句
</if>
</select>
第一条Sql语句叫主sql,一旦if条件成立,就把if里的部分sql语句拼接到主sql上,形成一个完整的sql,if条件可以有多个,条件为真都会拼接到主sql上
接口
List<User> SelectUserIf(User user);
mapper
<select id="SelectUserIf" resultType="user">
select * from user
where 1=1
<if test="name !=null and name!='' ">
and name = #{name}
</if>
<if test="pwd !=null and name!='' ">
or pwd = #{pwd}
</if>
</select>
测试类
@Test
public void test1(){
//获取sqlsession
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setName("李四");
user.setPwd("12345");
List<User> users = mapper.SelectUserIf(user);
System.out.println(users);
session.close();
}
结果
为了看到Mybatis到底执行了什么,我们加上打印SQL语句的配置,在主配置文件李加上如下配置
<settings>
<!-- 打印查询语句 -->
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
第一个先看两个条件都成立
我们先把where 1=1 删除掉,然后值给name成立,语法不正确了,为了防止这个问题,我们一般加上where 1=1 避免这个错误,
再把第二个条件放开,name不要了,看一下输出语句,与预期的结果一样,我们就实现了动态的sql,还有一个注意点,动态sql的入参要是一个对象类型。
我们可以看出 if条件多用于多条件查询的情况下
在mapper的动态SQL中如果出现大于小于符号<>,特别是小于< 符号跟xml的开始标签重合,解析就会报错,所以就要使用实体符号来代替,表如下
动态SQL第二个 where标签
在使用if时,不加永真条件的话,拼接sql语句时会报错,引发sql语法错误,使用where标签就可以解决这个问题,在使用where标签时,里边是一个个或多个if标签,当if为true时,where标签会转变为where关键字加到sql语句的后边,如果没有真的条件则没有条件,
语法
<where>
<if test="条件">部分sql</if>
<if test="条件">部分sql</if>
<if test="条件">部分sql</if>
</where>
接口
List<User> SelectUserwhere(User user);
mapper
这里两个条件都成立的话,sql语句应该长这个样子,select * from user where and name = #{name} or pwd = #{pwd},但实际运行结果却是第一个and不见了,Mybatis自动帮我们剔除掉了,第一个标签中 and可写可不写,写了Mybatis会帮我们剔除掉,但其余的if标签里部分sql前必须要写上,要不然就会报错
<select id="SelectUserwhere" resultType="user">
select * from user
<where>
<if test="name !=null and name!='' "> and name = #{name}</if>
<if test="pwd !=null and name!='' "> or pwd = #{pwd}</if>
</where>
</select>
我们不给name值看一下执行结果,where依然帮我们删除掉了or标签,语句结果也是对的
动态SQL第三个foreach标签
foreach用来实现对传入的集合的循环取值使用,一般使用在in里边
语法
<foreach collection="集合类型" open="开始" close="结束" item="集合一个值" separator="分隔符" index="序号或者key">
#{item}
</foreach>
List简单类型
接口
List<User> SelectUserForeach(ArrayList<Integer> user);
mapper
<select id="SelectUserForeach" resultType="user">
select * from user
<if test="list != null and list.size > 0">
where id in
<foreach collection="list" separator="," open="(" close=")" item="id">
#{id}
</foreach>
</if>
</select>
测试类
@Test
public void testForeach(){
//获取sqlsession
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
List<User> users = mapper.SelectUserForeach(ids);
System.out.println(users);
session.close();
}
总结
我们在写foreach标签的时候必须搭配if where一起使用,上边的例子,如果没有if标签,当list喂null时或者为空我们的语法就有错误(select * from user where id in),所以要灵活应用,根据标签来拼接语句。
List对象类型
接口
List<User> SelectUserForeach1(ArrayList<User> user);
mapper
<select id="SelectUserForeach1" resultType="user">
select * from user
<if test="list != null and list.size > 0">
where id in
<foreach collection="list" separator="," open="(" close=")" item="user">
#{user.id}
</foreach>
</if>
</select>
测试类
@Test
public void testForeach1(){
//获取sqlsession
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
ArrayList<User> users = new ArrayList<>();
User user = new User();
user.setId(3);
User user1 = new User();
user1.setId(4);
users.add(user1);
users.add(user);
List<User> userList = mapper.SelectUserForeach1(users);
System.out.println(users);
session.close();
}
结果
动态SQL第四个 SQL标签
标签用于定义 SQL 片断,以便其它 SQL 标签复用。而其它标签使用该 SQL 片断,需要使用子标签。该标签可以定义 SQL 语句中的任何部分,所以子标签可以放在动态 SQL的任何位置
我们把刚才的代码使用SQL标签来表示下
<sql id="SelectUser">
select * from user
</sql>
<select id="SelectUserForeach1" resultType="user">
<include refid="SelectUser"></include>
<if test="list != null and list.size > 0">
where id in
<foreach collection="list" separator="," open="(" close=")" item="user">
#{user.id}
</foreach>
</if>
</select>
查询结果跟之前是一样的,实现了代码的复用
配置文件
Mybatis的配置文件有两种,主配置文件,还有各个mapper
- 主配置文件,提供mybatis全局设置的,日志,数据源,各个mapper文件
- mapper文件,对应写sql语句的
settings部分
全局设置,可以不配置使用默认的,我们刚才的sql语句打印就是在settings里开启的
配置如下
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="multipleResultSetsEnabled" value="true"/>
<setting name="useColumnLabel" value="true"/>
<setting name="useGeneratedKeys" value="false"/>
<setting name="autoMappingBehavior" value="PARTIAL"/>
<setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
<setting name="defaultExecutorType" value="SIMPLE"/>
<setting name="defaultStatementTimeout" value="25"/>
<setting name="defaultFetchSize" value="100"/>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="false"/>
<setting name="localCacheScope" value="SESSION"/>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>
类型别名(typeAliases)
这个我们上边讲过了,一种配置一个的,一个配置多个的
环境配置(environments)
可以配置多个,数据库的连接信息
- 默认使用的环境 ID(比如:default="development")。
- 每个 environment 元素定义的环境 ID(比如:id="development")。
- 事务管理器的配置(比如:type="JDBC")。
- 数据源的配置(比如:type="POOLED")。
<environments default="development">
<environment id="development">
<transactionManager type="JDBC">
<property name="..." value="..."/>
</transactionManager>
<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>
</environments>
类型处理器(typeHandlers)
分页查询PageHelper
PageHelper做数据分页,在select语句加上分页的sql内容 使用步骤
- 加入依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
- 加入 plugin 配置 在之前加入
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor" />
</plugins>
使用查询所有
@Test
public void testAll(){
//获取sqlsession
SqlSession session = MybatisUtils.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
PageHelper.startPage(1,1);
List<User> userList = mapper.SelectAll();
System.out.println(userList);
session.close();
}
控制台输出