预计阅读时间 5 分钟
在开发中,经常遇到根据搜索条件查询的情况。可以利用 MyBatis 中的动态SQL的特性来实现多条件选择查询。
1、多条件查询的实现方式
根据 MAPPER接口 入参的不同形式,有三种方法来实现:
1.1、条件入参为实体类
/**
* 条件查询BO
* @param condition
* @return
*/
List<EmployeeBO> searchByConditionBO(SearchEmployeeConditionBO condition);
// 对应的 MAPPER
<select id="searchByConditionBO" resultType="com.turato.mybatis.web.model.bo.EmployeeBO">
<bind name="firstNamePattern" value="'%' + _parameter.getFirstName() + '%'" />
select
<include refid="Base_Column_List"/>
from employees
<where>
<if test="null != firstName">
and first_name like #{firstNamePattern}
</if>
<if test="null != lastName">
last_name like concat('%',#{lastName}, '%')
</if>
</where>
</select>
测试代码:
@Test
public void searchByConditionBO() {
SearchEmployeeConditionBO searchEmployeeConditionBO = new SearchEmployeeConditionBO();
searchEmployeeConditionBO.setFirstName("tuhao");
List<EmployeeBO> employeeBOS = employeesMapper.searchByConditionBO(searchEmployeeConditionBO);
log.info("employeeBOS:{}", employeeBOS);
}
1.2、条件入参为 Map
/**
* 条件查询MAP
* @param conditionMap
* @return
*/
List<EmployeeBO> searchByConditionMap(Map<String, Object> conditionMap);
// 对应的 MAPPER
<select id="searchByConditionMap" resultType="com.turato.mybatis.web.model.bo.EmployeeBO">
select
<include refid="Base_Column_List"/>
from employees
<where>
<if test="null != firstName">
and first_name like concat('%',#{firstName}, '%')
</if>
<if test="null != lastName">
and last_name like concat('%',#{lastName}, '%')
</if>
</where>
</select>
测试代码:
@Test
public void searchByConditionMap() {
Map<String, Object> paramMap = new HashMap<>(16);
paramMap.put("firstName", "Georgi");
List<EmployeeBO> employeeBOS = employeesMapper.searchByConditionMap(paramMap);
log.info("employeeBOS:{}", employeeBOS);
}
1.3、条件入参为单个参数
_parameter 是 Mybatis 的内置参数,表示传入 MAPPER 接口的入参。 单个参数:_parameter就是这个参数 多个参数:参数会被封装为一个map:_parameter就是代表这个map
/**
* 条件查询参数
* @param firstName
* @param lastName
* @return
*/
List<EmployeeBO> searchByConditionParam(@Param("firstName") String firstName,
@Param("lastName") String lastName);
<select id="searchByConditionParam" resultType="com.turato.mybatis.web.model.bo.EmployeeBO">
<bind name="firstNamePattern" value="'%' + _parameter.firstName + '%'" />
select
<include refid="Base_Column_List"/>
from employees
<where>
<if test="null != firstName">
and first_name like #{firstNamePattern}
</if>
<if test="null != lastName">
and last_name like concat('%',#{lastName}, '%')
</if>
</where>
</select>
测试代码:
@Test
public void searchByConditionParam() {
String firstName = "Georgi";
String lastName = "Facello";
List<EmployeeBO> employeeBOS = employeesMapper.searchByConditionParam(firstName, lastName);
log.info("employeeBOS:{}", employeeBOS);
}
注意:多个参数的时候,要使用 @Param 给指定参数,否则会出现找不到参数的错误:
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException:
Parameter 'firstName' not found. Available parameters are [arg1, arg0, param1, param2]
2、参考文献
[1] MyBatis 中文文档:动态SQL https://mybatis.org/mybatis-3/zh/dynamic-sql.html
[2] 本文代码地址 https://gitee.com/tuhaocat/mybatis-demo