MyBatis之动态SQL
if
修改配置文件
Product.xml中添加listProduct_if方法。添加完毕如下所示：
<?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.nyf.pojo">
<resultMap type="Product" id="productBean">
<id column="pid" property="id" />
<result column="pname" property="name" />
<result column="price" property="price" />
<!-- 多对一的关系 -->
<!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
<association property="category" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<!-- 根据id查询Product, 关联将Orders查询出来 -->
<select id="listProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘ from category_ c left join product_ p on c.id = p.cid
</select>
<select id="getProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘
from category_ c
left join product_ p on c.id = p.cid
where p.id = #{id}
</select>
<!-- concat(‘%‘,#{name},‘%‘)的用途是拼接字符串 -->
<select id="listProduct_if" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat(‘%‘,#{name},‘%‘)
</if>
</select>
</mapper>
编写测试类
package com.nyf.tests;
import com.nyf.pojo.Product;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DynamicSQL {
public static void main(String[] args) throws IOException {
String resource = "com/nyf/config/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("查询所有的");
List<Product> ps = session.selectList("listProduct_if");
for (Product p : ps) {
System.out.println(p);
}
System.out.println("模糊查询");
Map<String,Object> params = new HashMap<>();
params.put("name","a");
List<Product> ps2 = session.selectList("listProduct_if",params);
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
}
结果验证
正确结果应该如下所示：
查询所有的
Product [id=1, name=product a, price=88.88]
Product [id=2, name=product b, price=88.88]
Product [id=3, name=product c, price=88.88]
Product [id=4, name=product x, price=88.88]
Product [id=5, name=product y, price=88.88]
Product [id=6, name=product z, price=88.88]
模糊查询
Product [id=1, name=product a, price=88.88]
where
修改配置文件
Product.xml中添加listProduct_where方法，添加完毕如下所示：
<?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.nyf.pojo">
<resultMap type="Product" id="productBean">
<id column="pid" property="id" />
<result column="pname" property="name" />
<result column="price" property="price" />
<!-- 多对一的关系 -->
<!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
<association property="category" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<!-- 根据id查询Product, 关联将Orders查询出来 -->
<select id="listProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘ from category_ c left join product_ p on c.id = p.cid
</select>
<select id="getProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘
from category_ c
left join product_ p on c.id = p.cid
where p.id = #{id}
</select>
<!-- concat(‘%‘,#{name},‘%‘)的用途是拼接字符串 -->
<select id="listProduct_if" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat(‘%‘,#{name},‘%‘)
</if>
</select>
<select id="listProduct_where" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat(‘%‘,#{name},‘%‘)
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
</mapper>
编写测试类
DynamicSQL类的main方法中追加如下代码：
System.out.println("多条件查询");
Map<String,Object> params3 = new HashMap<>();
// params.put("name","a");
params.put("price","10");
List<Product> ps3 = session.selectList("listProduct_where",params3);
for (Product p : ps3) {
System.out.println(p);
}
结果验证
正确结果应该如下所示：
多条件查询
Product [id=1, name=product a, price=88.88]
Product [id=2, name=product b, price=88.88]
Product [id=3, name=product c, price=88.88]
Product [id=4, name=product x, price=88.88]
Product [id=5, name=product y, price=88.88]
Product [id=6, name=product z, price=88.88]
set
修改配置文件
Product.xml中添加updateProduct方法，添加完毕如下所示：
<?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.nyf.pojo">
<resultMap type="Product" id="productBean">
<id column="pid" property="id" />
<result column="pname" property="name" />
<result column="price" property="price" />
<!-- 多对一的关系 -->
<!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
<association property="category" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<!-- 根据id查询Product, 关联将Orders查询出来 -->
<select id="listProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘ from category_ c left join product_ p on c.id = p.cid
</select>
<select id="getProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘
from category_ c
left join product_ p on c.id = p.cid
where p.id = #{id}
</select>
<!-- concat(‘%‘,#{name},‘%‘)的用途是拼接字符串 -->
<select id="listProduct_if" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat(‘%‘,#{name},‘%‘)
</if>
</select>
<select id="listProduct_where" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat(‘%‘,#{name},‘%‘)
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
<update id="updateProduct" parameterType="Product" >
update product_
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
where id=#{id}
</update>
</mapper>
编写测试类
DynamicSQL类的main方法中追加调用updateProduct方法的代码，再追加一个listAll方法，如下所示：
System.out.println("执行updateProduct");
Product p = new Product();
p.setId(6);
p.setName("product zz");
p.setPrice(99.99f);
session.update("updateProduct",p);
listAll(session);
private static void listAll(SqlSession session) {
Map<String,Object> params = new HashMap<>();
params.put("name","a");
params.put("price","10");
List<Product> ps2 = session.selectList("listProduct",params);
for (Product p : ps2) {
System.out.println(p);
}
}
结果验证
正确结果应该如下所示：
执行updateProduct
Product [id=1, name=product a, price=0.0]
Product [id=2, name=product b, price=0.0]
Product [id=3, name=product c, price=0.0]
Product [id=4, name=product x, price=0.0]
Product [id=5, name=product y, price=0.0]
Product [id=6, name=product zz, price=0.0]
trim自定义
修改配置文件
Product.xml的mapper中追加updateProduct2方法，追加完毕如下所示：
<?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.nyf.pojo">
<resultMap type="Product" id="productBean">
<id column="pid" property="id" />
<result column="pname" property="name" />
<result column="price" property="price" />
<!-- 多对一的关系 -->
<!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
<association property="category" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<!-- 根据id查询Product, 关联将Orders查询出来 -->
<select id="listProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘ from category_ c left join product_ p on c.id = p.cid
</select>
<select id="getProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘
from category_ c
left join product_ p on c.id = p.cid
where p.id = #{id}
</select>
<!-- concat(‘%‘,#{name},‘%‘)的用途是拼接字符串 -->
<select id="listProduct_if" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat(‘%‘,#{name},‘%‘)
</if>
</select>
<select id="listProduct_where" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat(‘%‘,#{name},‘%‘)
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
<update id="updateProduct" parameterType="Product" >
update product_
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
where id=#{id}
</update>
<update id="updateProduct2" parameterType="Product" >
update product_
<trim prefix="SET" suffixOverrides=",">
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</trim>
where id=#{id}
</update>
</mapper>
编写测试类
DynamicSQL类中追加调用updateProduct2方法的代码，如下所示：
System.out.println("执行updateProduct2");
Product p2 = new Product();
p2.setId(6);
p2.setName("product zzzzzz");
p2.setPrice(99.99f);
session.update("updateProduct2",p2);
listAll(session);
结果验证
正确结果应该如下所示：
执行updateProduct2
Product [id=1, name=product a, price=0.0]
Product [id=2, name=product b, price=0.0]
Product [id=3, name=product c, price=0.0]
Product [id=4, name=product x, price=0.0]
Product [id=5, name=product y, price=0.0]
Product [id=6, name=product zzzzzz, price=0.0]
choose
修改配置文件
Product.xml的mapper中追加listProduct_choose方法，追加完毕如下所示：
<?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.nyf.pojo">
<resultMap type="Product" id="productBean">
<id column="pid" property="id" />
<result column="pname" property="name" />
<result column="price" property="price" />
<!-- 多对一的关系 -->
<!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
<association property="category" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<!-- 根据id查询Product, 关联将Orders查询出来 -->
<select id="listProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘ from category_ c left join product_ p on c.id = p.cid
</select>
<select id="getProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘
from category_ c
left join product_ p on c.id = p.cid
where p.id = #{id}
</select>
<!-- concat(‘%‘,#{name},‘%‘)的用途是拼接字符串 -->
<select id="listProduct_if" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat(‘%‘,#{name},‘%‘)
</if>
</select>
<select id="listProduct_where" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat(‘%‘,#{name},‘%‘)
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
<update id="updateProduct" parameterType="Product" >
update product_
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
where id=#{id}
</update>
<update id="updateProduct2" parameterType="Product" >
update product_
<trim prefix="SET" suffixOverrides=",">
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</trim>
where id=#{id}
</update>
<select id="listProduct_choose" resultType="Product">
SELECT * FROM product_
<where>
<choose>
<when test="name != null">
and name like concat(‘%‘,#{name},‘%‘)
</when>
<when test="price !=null and price != 0">
and price > #{price}
</when>
<otherwise>
and id >1
</otherwise>
</choose>
</where>
</select>
</mapper>
编写测试类
DynamicSQL类的main方法中追加如下代码：
Map<String, Object> params = new HashMap<>();
params.put("name", "a");
// params.put("price","10");
List<Product> ps = session.selectList("listProduct_choose", params);
for (Product p3 : ps) {
System.out.println(p3);
}
结果验证
正确结果应该如下所示：
Product [id=1, name=product a, price=88.88]
foreach
修改配置文件
Product.xml的mapper中追加listProduct_foreach方法，追加完毕如下所示：
<?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.nyf.pojo">
<resultMap type="Product" id="productBean">
<id column="pid" property="id" />
<result column="pname" property="name" />
<result column="price" property="price" />
<!-- 多对一的关系 -->
<!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
<association property="category" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<!-- 根据id查询Product, 关联将Orders查询出来 -->
<select id="listProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘ from category_ c left join product_ p on c.id = p.cid
</select>
<select id="getProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘
from category_ c
left join product_ p on c.id = p.cid
where p.id = #{id}
</select>
<!-- concat(‘%‘,#{name},‘%‘)的用途是拼接字符串 -->
<select id="listProduct_if" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat(‘%‘,#{name},‘%‘)
</if>
</select>
<select id="listProduct_where" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat(‘%‘,#{name},‘%‘)
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
<update id="updateProduct" parameterType="Product" >
update product_
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
where id=#{id}
</update>
<update id="updateProduct2" parameterType="Product" >
update product_
<trim prefix="SET" suffixOverrides=",">
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</trim>
where id=#{id}
</update>
<select id="listProduct_choose" resultType="Product">
SELECT * FROM product_
<where>
<choose>
<when test="name != null">
and name like concat(‘%‘,#{name},‘%‘)
</when>
<when test="price !=null and price != 0">
and price > #{price}
</when>
<otherwise>
and id >1
</otherwise>
</choose>
</where>
</select>
<select id="listProduct_foreach" resultType="Product">
SELECT * FROM product_
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
编写测试类
DynamicSQL类的main方法中追加如下代码：
System.out.println("执行listProduct_foreach");
List<Integer> ids = new ArrayList();
ids.add(1);
ids.add(3);
ids.add(5);
List<Product> ps4 = session.selectList("listProduct_foreach",ids);
for (Product p : ps4) {
System.out.println(p);
}
结果验证
正确结果应该如下所示：
listProduct_foreach
Product [id=1, name=product a, price=88.88]
Product [id=3, name=product c, price=88.88]
Product [id=5, name=product y, price=88.88]
bind
修改配置文件
Product.xml的mapper中追加listProduct_bind方法，追加完毕如下所示：
<?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.nyf.pojo">
<resultMap type="Product" id="productBean">
<id column="pid" property="id" />
<result column="pname" property="name" />
<result column="price" property="price" />
<!-- 多对一的关系 -->
<!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
<association property="category" javaType="Category">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</association>
</resultMap>
<!-- 根据id查询Product, 关联将Orders查询出来 -->
<select id="listProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘ from category_ c left join product_ p on c.id = p.cid
</select>
<select id="getProduct" resultMap="productBean">
select c.id ‘cid‘, p.id ‘pid‘, c.name ‘cname‘, p.name ‘pname‘
from category_ c
left join product_ p on c.id = p.cid
where p.id = #{id}
</select>
<!-- concat(‘%‘,#{name},‘%‘)的用途是拼接字符串 -->
<select id="listProduct_if" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat(‘%‘,#{name},‘%‘)
</if>
</select>
<select id="listProduct_where" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat(‘%‘,#{name},‘%‘)
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
<update id="updateProduct" parameterType="Product" >
update product_
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
where id=#{id}
</update>
<update id="updateProduct2" parameterType="Product" >
update product_
<trim prefix="SET" suffixOverrides=",">
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</trim>
where id=#{id}
</update>
<select id="listProduct_choose" resultType="Product">
SELECT * FROM product_
<where>
<choose>
<when test="name != null">
and name like concat(‘%‘,#{name},‘%‘)
</when>
<when test="price !=null and price != 0">
and price > #{price}
</when>
<otherwise>
and id >1
</otherwise>
</choose>
</where>
</select>
<select id="listProduct_foreach" resultType="Product">
SELECT * FROM product_
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
<select id="listProduct_bind" resultType="Product">
<bind name="likename" value="‘%‘ + name + ‘%‘" />
select * from product_ where name like #{likename}
</select>
</mapper>
编写测试类
DynamicSQL类的main方法中追加如下代码：
System.out.println("执行listProduct_bind");
Map<String, String> params2 =new HashMap();
params.put("name", "product");
List<Product> ps5 = session.selectList("listProduct_bind",params);
for (Product p : ps5) {
System.out.println(p);
}
结果验证
正确结果应该如下所示：
执行listProduct_bind
Product [id=1, name=product a, price=88.88]
Product [id=2, name=product b, price=88.88]
Product [id=3, name=product c, price=88.88]
Product [id=4, name=product x, price=88.88]
Product [id=5, name=product y, price=88.88]
Product [id=6, name=product z, price=99.99]