mybatis 模糊查询

在进行模糊查询时，如果是 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}) &gt; 0 
	  </if>
	  <if test="bookAuthor != null and bookAuthor !=''">
	    and INSTR(book_author, #{bookAuthor}) &gt; 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());
		}
	}

运行效果如下