MyBatis使用动态sql与模糊查询
一、动态SQL介绍
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
二、动态SQL元素种类
元素 | 作用 | 描述 |
---|---|---|
if | 条件判断 | 单条件判断 |
choose(when、oherwise) | 条件选择,相当Java when | 多条件分支判断 |
trim(where、set) | 辅助 | 处理sql语句拼接问题 |
foreach | 循环 | 循环 |
三、使用动态SQL语句的方式、例子
3.1 先创建个表,添加数据
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`money` double DEFAULT NULL,
`isdeleted` tinyint(4) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `account` VALUES (1, '张小三', 2000, 1, '2021-09-02 00:00:00', '2022-05-03 00:00:00');
INSERT INTO `account` VALUES (2, '李四', 1000, 0, '2019-03-03 00:00:00', '2021-02-02 00:00:00');
INSERT INTO `account` VALUES (3, '王五', 6000, 1, '2020-01-01 00:00:00', '2021-01-01 00:00:00');
3.2 MyBatis、数据库、日期等文件配置完毕,相关依赖已加载
3.2.1 实体类Account
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Account {
private Integer id;
private String name;
private Double money;
private Integer isdeleted;
private Date created;
private Date updated;
}
3.2.2 dao层
public interface AccountDao {
/**
* 单个条件查询,根据name查询
* @param name
* @return
*/
List<Account> findByName(String name);
/**
* 多条件查询,使用choose
* @param name
* @param isdeleted
* @return
*/
List<Account> findByChoose(@Param("name") String name,@Param("isdeleted") Integer isdeleted);
List<Account> findByWhere(@Param("name") String name,@Param("isdeleted") Integer isdeleted);
List<Account> findByTrim(@Param("name") String name,@Param("money") Double money);
int updateBySet(@Param("name") String name,@Param("money") Double money,@Param("isdeleted") Integer isdeleted,@Param("id") Integer id);
List<Account> findByForeach(@Param("isdList") Integer[] isdeleted);
}
3.3 使用if元素进行单条件判断
3.3.1 在AccountMapper.xml映射文件编写如下代码
<mapper namespace="com.hongda.space.dao.AccountDao">
<select id="findByName" resultType="com.hongda.space.entity.Account">
select * from account where 1=1
<if test="name !=null and name!=''">
and name like concat('%',#{name},'%')
</if>
</select>
</mapper>
3.3.2 编写测试代码
public class AccountTest {
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
AccountDao mapper = session.getMapper(AccountDao.class);
List<Account> accounts = mapper.findByName(null);
System.out.println(accounts);
List<Account> accounts1 = mapper.findByName("小");
System.out.println(accounts1);
}
}
3.3.3 测试结果
3.4 使用choose元素进行多条件判断
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
例如,如果传入了name就按name查找,传入了isdeleted就按照isdeleted查找,若两者都没有传入,则执行otherwis中的代码。
3.4.1 在AccountMapper.xml映射文件编写如下代码
<select id="findByChoose" resultType="com.hongda.space.entity.Account">
select * from account where 1=1
<choose>
<when test="name !=null and name!=''">
and name like concat('%',#{name},'%')
</when>
<when test="isdeleted != null">
and isdeleted =#{isdeleted}
</when>
<otherwise>
and money <1000
</otherwise>
</choose>
</select>
3.4.2 编写测试代码
第一次传入了name,结果按照name查找。
第二次没有传入,执行oherwise代码。
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
AccountDao mapper = session.getMapper(AccountDao.class);
List<Account> accounts2 = mapper.findByChoose("小", null);
System.out.println(accounts2);
List<Account> accounts3 = mapper.findByChoose(null, null);
System.out.println(accounts3);
}
3.4.3 测试结果
3.5 使用trim(where、set)元素进行辅助
前面的几个例子已经方便地解决了动态sql问题,但是细心地小伙伴可能发现每次sql语句都有 where 1=1.这是因为如果不写where 1=1 ,并且没有匹配的条件,那么sql语句将会出现语法错误。
而where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素会将它们去除。
3.5.1 在AccountMapper.xml映射文件编写where元素代码
<select id="findByWhere" resultType="com.hongda.space.entity.Account">
select * from account
<where>
<if test="name != null and name!=''">
name like concat('%',#{name},'%')
</if>
<if test="isdeleted != null">
and isdeleted =#{isdeleted}
</if>
</where>
</select>
3.5.1.1 编写测试代码
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
AccountDao mapper = session.getMapper(AccountDao.class);
List<Account> accounts1 = mapper.findByWhere(null, null);
System.out.println(accounts1);
List<Account> accounts = mapper.findByWhere("小", null);
System.out.println(accounts);
List<Account> accounts2 = mapper.findByWhere(null, 1);
System.out.println(accounts2);
}
3.5.1.2 测试结果
3.5.2 在AccountMapper.xml映射文件编写trim元素代码
如果 where 元素与自己期望的不太一样,也可以通过自定义 trim 元素来定制 where 元素的功能。比如,trim元素可以帮助我们去掉一下and、or等,prefix代表语句前缀, prefixOverrides代表要去掉的字符串。
举个栗子
<select id="findByTrim" resultType="com.hongda.space.entity.Account">
select * from account
<trim prefix="where" prefixOverrides="and">
<choose>
<when test="name != null and name !=''">
and name like concat('%',#{name},'%')
</when>
<when test="money != null">
and money =#{money}
</when>
<otherwise>
and isdeleted =1
</otherwise>
</choose>
</trim>
</select>
3.5.2.1 编写测试代码
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
AccountDao mapper = session.getMapper(AccountDao.class);
List<Account> accounts = mapper.findByTrim("小", null);
System.out.println(accounts);
List<Account> accounts1 = mapper.findByTrim(null, 1000.0);
System.out.println(accounts1);
}
3.5.2.2 测试结果
3.5.3 在AccountMapper.xml映射文件编写set元素代码
set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。
<update id="updateBySet">
update account
<set>
<if test="name != null and name !=''">
name = #{name},
</if>
<if test="money !=null and money != ''">
money =#{money},
</if>
<if test="isdeleted != null and isdeleted !=''">
isdeleted=#{isdeleted}
</if>
</set>
where id=#{id}
</update>
3.5.3.1 编写测试代码
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
AccountDao mapper = session.getMapper(AccountDao.class);
int i = mapper.updateBySet("赵六", 2500.0, 1, 2);
System.out.println(i);
int i1 = mapper.updateBySet("赵六", null, 1, 2);
System.out.println(i1);
}
3.5.3.2 测试结果
这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)
3.6. 使用foreach元素对集合进行遍历
动态 SQL 的另一个常见使用场景是对集合进行遍历
3.6.1 在AccountMapper.xml映射文件编写如下代码
item:每次遍历生成的对象
open:开始遍历时拼接的字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
collection有一下3种情况:
如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了
<select id="findByForeach" resultType="com.hongda.space.entity.Account">
select * from account
<where>
<if test="isdList!=null and isdList.length>0">
isdeleted in
<!--
item:每次遍历生成的对象
open:开始遍历时拼接的字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
-->
<foreach collection="isdList" open="(" close=")" separator="," item="isdeleted">
#{isdeleted}
</foreach>
</if>
</where>
</select>
3.6.2 编写测试代码
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
AccountDao mapper = session.getMapper(AccountDao.class);
Integer []isdList = {1,2,3,4};
List<Account> accounts = mapper.findByForeach(isdList);
System.out.println(accounts);
}