本章主要记录MyBatis动态SQL的标签中的bind和foreach,涉及到的知识点有:
1. MyBatis动态SQL标签:用于降低根据不同条件拼接 SQL 语句的难度。
2. 多DB供应商支持:通过_databaseId字段判断当前数据库供应厂商(oracle、mysql等等)。
本章介绍的动态SQL的标签有:bind、foreach,下面对这些标签进行简单说明:
- bind标签:用于拼接字段形成新的变量,常用于like查询等。
- foreach标签:用于遍历读取集合类型的参数,常用于in操作、批量insert操作等。
1.业务场景与SQL
本章业务场景与SQL,与MyBatis代码实例系列-06:Mybatis动态SQL标签(一)—if、where、set、trim、choose一致,不再赘述。
2.目录结构
本章目录结构与MyBatis代码实例系列-06:Mybatis动态SQL标签(一)—if、where、set、trim、choose一致,不再赘述。
3.XML映射文件:Book.xml
数据库操作主要集中在XML映射文件中。
3.1.bind标签
bind标签用于拼接字段形成新的变量,常用于like查询等。
业务情景:查询书籍,要求书籍编号符合一个年级的编号,如初中、高中等等
普通SQL(#表示不确定):
SELECT * FROM `book`
where number like 'CZ-SX-%'
MyBatis动态SQL:
bind标签会读取相应的字段进行拼接。
<!--查询书籍,要求书籍编号符合一个年级的编号,如初中、高中等等-->
<select id="selectBookWithBind" parameterType="String" resultType="Book">
SELECT * FROM `book`
<bind name="grade_number" value="number + '%'"/>
<where>
number like #{grade_number}
</where>
</select>
3.2.foreach标签
foreach语法:
<foreach collection="" index="" item="" open="" separator="" close=""></foreach>
下面对这些属性进行介绍:
序号 | 属性 | 描述 | 备注 |
---|---|---|---|
1 | collection | 集合对象 | 1.如果参数为List<E> 类型,则collection=list2.如果参数为 Object[] 类型,则collection=array3.如果参数为 Map<objectKey,Object> 类型,则collection=objectKey |
2 | index | 元素索引 | 1.如果参数为List<E> 类型,则index为List<E> 的序号2.如果参数为 Object[] 类型,则index为Object[] 的下标3.如果参数为 Map<mapKey,List<E>> 类型,则index为List<E> 的序号4.如果参数为 Map<mapKey,Object[]> 类型,则index为Object[] 的下标5.如果参数为 Map<mapKey,Map<key,value>> 类型,则index=key |
3 | item | 集合元素 | 1.如果参数为List<E> 类型,则item=List<E>.get(i) 2.如果参数为 Object[] 类型,则item=Object[i] 3.如果参数为 Map<mapKey,List<E>> 类型,则item=List<E>.get(i) 4.如果参数为 Map<mapKey,Object[]> 类型,则item=Object[i] 5.如果参数为 Map<mapKey,Map<key,value>> 类型,则item=value |
4 | open | 拼接字符串的开始符号 | 一般为"(" ,与close配合构成一个完整的括号。 |
5 | separator | 拼接字符串时的分隔符 | 一般为"," ,用来分割元素,如 in (1,2,3) 和values('1001','name','sex') |
6 | close | 拼接字符串的结束符号 | 一般为")" ,与open配合构成一个完整的括号。 |
foreach标签:用于遍历读取集合类型的参数,常用于in操作、批量insert操作等。
业务情景:查询书籍,查询条件为多个书籍名称
普通SQL(#表示不确定):
SELECT * FROM `book`
# where name IN ('初中数学2','高中数学4','大学英语4')
MyBatis动态SQL:
foreach标签遍历集合类型的参数,读取集合元素,通过分隔符拼接成新的字符串。
3.2.1.参数类型为数组的foreach
<!--查询书籍,查询条件为多个书籍名称{array}-->
<!--在使用ForEach标签时,如果参数类型是Array类型,则collection="array"-->
<!--参数是String[]类型,index是数组下表,item是数组元素array[i]-->
<select id="selectBookWithForEachArray" resultType="Book">
SELECT * FROM `book`
<where>
name IN
<foreach collection="array" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>
3.2.2.参数类型为列表的foreach
<!--查询书籍,查询条件为多个书籍名称{list}-->
<!--在使用ForEach标签时,如果参数类型是List类型,则collection="list"-->
<!--参数是List<String>类型,index是元素的序号,item是列表元素list.get(i)-->
<select id="selectBookWithForEachList" resultType="Book">
SELECT * FROM `book`
<where>
name IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>
3.2.3.参数类型为键值对的foreach
<!--查询书籍,查询条件为多个书籍名称{map}-->
<!--在使用ForEach标签时,如果参数类型是Map类型,则collection=map.key-->
<!--参数map是Map<key1,Map<key2,value2>>类型,collection是Map<key2,value2>,index=key2,item=value2-->
<select id="selectBookWithForEachMap" resultType="Book">
SELECT * FROM `book`
<where>
name IN
<foreach collection="nameMap" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>
备注:
- 当参数类型为数组和列表时,配置方式是固定的,collection只能是array和list。
- 当参数类型为键值对时,配置方式是灵活的,因为collection=key,key是自定义的,所以可以自定义collection的取值。
- 如果是一般开发,使用数组列表很方便;如果有一些特殊的情况,可以考虑通过键值对的自定义功能进行实现。
完整的Book.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">
<!--MyBatis的分配置文件,分别对应每个实体,用来配置SQL操作及SQL语句-->
<!--namespace,定义这个映射的命名域,这里指向Dao层接口-->
<mapper namespace="pers.hanchao.himybatis.dynamic.IBookDAO">
<!--Integer的空值形式是null,String的空值形式是''-->
<!--根据id查询一本书-->
<select id="selectBookById" resultType="Book" parameterType="Integer">
SELECT * FROM `book` WHERE id = #{id}
</select>
<!--查询书籍,查询条件为多个书籍名称{array}-->
<!--在使用ForEach标签时,如果参数类型是Array类型,则collection="Array"-->
<!--参数是String[]类型,index是数组下表,item是数组元素array[i]-->
<select id="selectBookWithForEachArray" resultType="Book">
SELECT * FROM `book`
<where>
name IN
<foreach collection="array" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>
<!--查询书籍,查询条件为多个书籍名称{list}-->
<!--在使用ForEach标签时,如果参数类型是List类型,则collection="list"-->
<!--参数是List<String>类型,index是元素的序号,item是列表元素list.get(i)-->
<select id="selectBookWithForEachList" resultType="Book">
SELECT * FROM `book`
<where>
name IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>
<!--查询书籍,查询条件为多个书籍名称{map}-->
<!--在使用ForEach标签时,如果参数类型是Map类型,则collection=map.key-->
<!--参数map是Map<key1,Map<key2,value2>>类型,collection是Map<key2,value2>,index=key2,item=value2-->
<select id="selectBookWithForEachMap" resultType="Book">
SELECT * FROM `book`
<where>
name IN
<foreach collection="nameMap" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
</select>
<!--查询书籍,要求书籍编号符合一个年级的编号,如初中、高中等等-->
<select id="selectBookWithBind" parameterType="String" resultType="Book">
SELECT * FROM `book`
<bind name="grade_number" value="number + '%'"/>
<where>
number like #{grade_number}
</where>
</select>
</mapper>
4.其他文件
4.1.实体类Book.java
实体类Book.java与MyBatis代码实例系列-06:Mybatis动态SQL标签(一)—if、where、set、trim、choose一致,不再赘述。
4.2.业务控制类:BookController.java
package pers.hanchao.himybatis.dynamic;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* <p>MyBatis动态SQL:If,Where,Set,Trim,Choose,ForEach,Bind</p>
* @author hanchao 2018/1/31 22:46
**/
@RestController
public class BookController {
private static final Logger LOGGER = Logger.getLogger(BookController.class);
@Autowired
private BookService bookService;
/**
* <p>查询书籍,查询条件为多个书籍名称(Array形式)</p>
* @author hanchao 2018/1/31 23:08
**/
@PostMapping("/book/foreach/array")
public MyJsonResult<Book> selectBookWithForEachArray(){
String[] names = new String []{"初中数学1","大学英语4"};
List<Book> bookList = this.bookService.selectBookWithForEachArray(names);
if (null == bookList || bookList.size() == 0){
return new MyJsonResult<Book>(2,"暂无记录!");
}else {
return new MyJsonResult<Book>(1,"success!",bookList);
}
}
/**
* <p>查询书籍,查询条件为多个书籍名称(list形式)</p>
* @author hanchao 2018/1/31 23:08
**/
@PostMapping("/book/foreach/list")
public MyJsonResult<Book> selectBookWithForEachList(){
List<String> nameList = new ArrayList<String>();
nameList.add(new String("高中数学2"));
nameList.add(new String("初中数学2"));
List<Book> bookList = this.bookService.selectBookWithForEachList(nameList);
if (null == bookList || bookList.size() == 0){
return new MyJsonResult<Book>(2,"暂无记录!");
}else {
return new MyJsonResult<Book>(1,"success!",bookList);
}
}
/**
* <p>查询书籍,查询条件为多个书籍名称(Map形式)</p>
* @author hanchao 2018/1/31 23:08
**/
@PostMapping("/book/foreach/map")
public MyJsonResult<Book> selectBookWithForEachMap(){
Map<String,String > nameMap = new HashMap<String, String>();
nameMap.put("1","高中数学3");
nameMap.put("2","大学英语1");
Map<String ,Map<String ,String >> map = new HashMap<String, Map<String, String>>();
map.put("nameMap",nameMap);
List<Book> bookList = this.bookService.selectBookWithForEachMap(map);
if (null == bookList || bookList.size() == 0){
return new MyJsonResult<Book>(2,"暂无记录!");
}else {
return new MyJsonResult<Book>(1,"success!",bookList);
}
}
/**
* <p>查询书籍,要求书籍编号符合一个年级的编号,如初中、高中等等</p>
* @author hanchao 2018/1/31 23:10
**/
@PostMapping("/book/bind")
public MyJsonResult<Book> selectBookWithForBind(@RequestBody Book book){
LOGGER.info(book);
List<Book> bookList = this.bookService.selectBookWithForBind(book);
if (null == bookList || bookList.size() == 0){
return new MyJsonResult<Book>(2,"暂无记录!");
}else {
return new MyJsonResult<Book>(1,"success!",bookList);
}
}
}
4.3.DAO层接口类:IBookDAO.java
package pers.hanchao.himybatis.dynamic;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* <p>书籍类的Dao层接口</p>
* @author hanchao 2018/1/30 22:47
**/
@Repository
public interface IBookDAO {
/** 根据id查询一本书 */
Book selectBookById(Integer id);
/** 查询书籍,查询条件为多个书籍名称,数组形式 */
List<Book> selectBookWithForEachArray(String[] names);
/** 查询书籍,查询条件为多个书籍名称,列表形式*/
List<Book> selectBookWithForEachList(List nameList);
/** 查询书籍,查询条件为多个书籍名称,键值对形式 */
List<Book> selectBookWithForEachMap(Map nameMap);
/** 查询书籍,要求书籍编号符合一个年级的编号,如初中、高中等等 */
List<Book> selectBookWithBind(Book book);
}
4.4.返回封装类:MyJsonResult.java
package pers.hanchao.himybatis.dynamic;
import java.util.List;
/**
* <p>Json返回值</p>
* @author hanchao 2018/1/31 22:44
**/
public class MyJsonResult<E> {
/** 状态码 */
private Integer code = 1;
/** 消息 */
private String message = "success!";
/** 数据 */
private List<E> data;
public void setCodeAndMessage(Integer code,String message){
this.code = code;
this.message = message;
}
//constructor toString setter getter
}
4.5.前台页面:book.jsp
<%--
Created by IntelliJ IDEA.
User: hanchao
Date: 2018/1/31
Time: 23:13
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>书籍管理</title>
<style type="text/css">
div{margin: 5px 5px; }
.div-left {float: left;margin: 5px 5px;border:1px solid #96c2f1; background:#eff7ff;}
.div-text-center {text-align:center; border:1px solid #96c2f1; background:#eff7ff;width: 300px;}
textarea{border:1px solid #9bdf70;background:#f0fbeb}
</style>
</head>
<body>
<div class="div-left div-text-center">
<textarea id="show" style="width: 300px;height: 575px;"></textarea>
</div>
<div class="div-left">
<div class="div-text-center">
<label>书籍主键:</label><input type="text" id="id" name="id"/><hr/>
<label>书籍编号:</label><input type="text" id="number" name="number"/><hr/>
<label>书籍名称:</label><input type="text" id="name" name="name"/><hr/>
<label>书籍价格:</label><input type="text" id="price" name="price"/><hr/>
<label>是否旧书:</label><input type="text" id="old" name="old"/><hr/>
<label>是否汉化:</label><input type="text" id="chinese" name="chinese"/><hr/>
</div>
<div>
<input type="button" value="selectBookWithForEach--Array" onclick="books('foreach/array')"/><hr/>
<input type="button" value="selectBookWithForEach--List" onclick="books('foreach/list')"/><hr/>
<input type="button" value="selectBookWithForEach--Map" onclick="books('foreach/map')"/><hr/>
<input type="button" value="selectBookWithForBind" onclick="book('bind')"/><hr/>
</div>
</div>
</body>
<script type="text/javascript" src="../static/jquery-3.2.1.min.js"></script>
<script type="text/javascript">
function book(url_suffix) {
$.ajax({
type:'POST',
url:'/book/' + url_suffix,
data:JSON.stringify({
id:$('#id').val(),
number:$('#number').val(),
name:$('#name').val(),
price:$('#price').val(),
old:$('#old').val(),
chinese:$('#chinese').val()
}),
contentType:'application/json;charset=utf-8',
success:function (data) {
console.log(data);
var html = "url:/book/" + url_suffix + "\nresult:\n" + JSON.stringify(data,null,4);
$("#show").html(html)
},
error:function (data) {
alert(data.method);
}
});
}
function books(url_suffix) {
$.ajax({
type:'POST',
url:'/book/' + url_suffix,
contentType:'application/json;charset=utf-8',
success:function (data) {
console.log(data);
var html = "url:/book/" + url_suffix + "\nresult:\n" + JSON.stringify(data,null,4);
$("#show").html(html)
},
error:function (data) {
alert(data.method);
}
});
}
</script>
</html>
5.result
5.1.bind标签
查询书籍,要求书籍编号符合一个年级的编号,如初中、高中等等
5.2.foreach标签
查询书籍,查询条件为多个书籍名称
6.多DB供应商支持
通过_databaseId字段判断当前数据库供应厂商(oracle、mysql等等)。
我没有进行测试,这里只是给出代码:
<!--根据数据库厂商类型进行不同的操作-->
<select id="selectBookWithMultiDB" parameterType="Book" resultType="Book">
SELECT * FROM `book`
<where>
<if test="_databaseId == 'oracle'">
SUBSTR(name,0,2) == '初中'
</if>
<if test="_databaseId == 'mysql'">
SUBSTRING (name,0,2) == '初中'
</if>
</where>
</select>