在进行模糊查询时,如果是 mysql 数据库,我们常常用‘ %’ 和参数相连接;如果是 oracle 数据库,则使用连接符号 ‘||’ 。这样,就需要两套 sql 语句。而 mybatis 提供了 bind 元素,使用它,我们就不必写两套 sql,只需使用 mybatis 的语言与所需参数连接即可
bind 元素用法如下
<select id="selectBookByName" resultMap="BaseResultMap">
<bind name="pattern_bookName" value="'%' + bookName + '%'" />
<bind name="pattern_bookAuthor" value="'%' + bookAuthor + '%'" />
select * from book
where 1 = 1
<if test="bookName != null and bookName !=''">
and book_name LIKE #{pattern_bookName}
</if>
<if test="bookAuthor != null and bookAuthor !=''">
and book_author LIKE #{pattern_bookAuthor}
</if>
</select>
此处的 pattern_bookName ,即 %bookName %
注意: 如果只有一个模糊查询,则只写一个 <bind> 标签
测试示例如下
数据库表设计及数据
以下 java 代码使用 springboot 框架
实体类
package com.demo.bean;
public class Book {
private Integer id;
private String bookName;
private String bookAuthor;
private String createDate;
private String updateDate;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getBookAuthor() {
return bookAuthor;
}
public void setBookAuthor(String bookAuthor) {
this.bookAuthor = bookAuthor;
}
public String getCreateDate() {
return createDate;
}
public void setCreateDate(String createDate) {
this.createDate = createDate;
}
public String getUpdateDate() {
return updateDate;
}
public void setUpdateDate(String updateDate) {
this.updateDate = updateDate;
}
}
mapper层
package com.demo.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Mapper;
import com.demo.bean.Book;
@Mapper
public interface BookMapper {
//添加数据
int insert(Book book);
//模糊查询
List<Book> selectBookByName(Map<String, Object> map);
}
mapper.xml文件
<?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" >
<mapper namespace="com.demo.mapper.BookMapper">
<resultMap id="BaseResultMap" type="com.demo.bean.Book">
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="book_name" property="bookName" jdbcType="VARCHAR" />
<result column="book_author" property="bookAuthor" jdbcType="VARCHAR" />
<result column="create_date" property="createDate" jdbcType="VARCHAR" />
<result column="update_date" property="updateDate" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List">
book_name as bookName, book_author as bookAuthor,
create_date as createDate, update_date as updateDate
</sql>
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.demo.bean.Book">
insert into book(book_name, book_author, create_date, update_date)
values(#{bookName}, #{bookAuthor}, #{createDate}, #{updateDate})
</insert>
<select id="selectBookByName" resultMap="BaseResultMap">
<bind name="pattern_bookName" value="'%' + bookName + '%'" />
<bind name="pattern_bookAuthor" value="'%' + bookAuthor + '%'" />
select * from book
where 1 = 1
<if test="bookName != null and bookName !=''">
and book_name LIKE #{pattern_bookName}
</if>
<if test="bookAuthor != null and bookAuthor !=''">
and book_author LIKE #{pattern_bookAuthor}
</if>
</select>
</mapper>
测试类
package com.demo;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.demo.bean.Book;
import com.demo.mapper.BookMapper;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootJspApplicationTests {
@Autowired
private BookMapper bookMapper;
@Test
public void contextLoads() {
Book book = new Book();
book.setBookName("隋唐演义");
book.setBookAuthor("褚人获");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
book.setCreateDate(sdf.format(new Date()));
book.setUpdateDate(sdf.format(new Date()));
bookMapper.insert(book);
System.out.println("返回的主键: "+book.getId());
}
@Test
public void query() {
Map<String, Object> map = new HashMap<String, Object>();
map.put("bookName", "经");
map.put("bookAuthor", "老");
List<Book> list = bookMapper.selectBookByName(map);
for(Book b : list) {
System.out.println(b.getBookName());
}
}
}
注意:此处 mybatis 使用 map 传参,如果map只有键没有值,则传参空字符串,即 map.put("bookAuthor", "") ,否则报空异常
运行query测试用例控制台打印如下
INSTR 函数实现模糊查询
除了使用 bind 元素实现模糊查询外,还可以使用 mysql 的 INSTR 函数来实现模糊查询,用法如下
<select id="selectBookByNameINSTR" resultMap="BaseResultMap">
select * from book
where 1 = 1
<if test="bookName != null and bookName !=''">
and INSTR(book_name, #{bookName}) > 0
</if>
<if test="bookAuthor != null and bookAuthor !=''">
and INSTR(book_author, #{bookAuthor}) > 0
</if>
</select>
测试示例
还使用上边的代码
mapper层添加接口
package com.demo.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Mapper;
import com.demo.bean.Book;
@Mapper
public interface BookMapper {
//添加数据
int insert(Book book);
//模糊查询
List<Book> selectBookByName(Map<String, Object> map);
//模糊查询
List<Book> selectBookByNameINSTR(Map<String, Object> map);
}
测试类添加方法
@Test
public void queryINSTR() {
Map<String, Object> map = new HashMap<String, Object>();
map.put("bookName", "经");
map.put("bookAuthor", "");
List<Book> list = bookMapper.selectBookByNameINSTR(map);
for(Book b : list) {
System.out.println(b.getBookName());
}
}
运行效果如下