执行不同的条件限定,需要准备两条sql语句
Product.xml(两条sql语句,一个是查询所有,一个是根据名称模糊查询。)
<?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.how2java.pojo">
<select id="listProduct" resultType="Product">
select * from product_
</select>
<select id="listProductByName" resultType="Product">
select * from product_ where name like concat('%',#{name},'%')
</select>
</mapper>
Test1.java
public class Test1 {
public static void main(String[] args) throws IOException {
String resource = "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");
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("listProductByName",params);
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
}
执行后:
为什么要用标签?
如果Product的字段比较多的话,为了应付各个字段的查询,那么就需要写多条sql语句,这样就变得难以维护。这个时候,就可以使用Mybatis 动态SQL里的if标签,这样只需要定义一条sql语句即可应付多种情况了,在测试的时候,也只需要调用这么一条sql语句listProduct 即可。
if标签
注:if判断中必须用null(小的的n)不是Null !!!! 不然无法运行
Product.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.how2java.pojo">
<select id="listProduct" resultType="Product">
select * from product_
//test 是 <if>元素的属性
<if test="name!=null">
where name like concat('%',#{name},'%')
</if>
</select>
</mapper>
注:parameterType参数类型可以不指明,当没有设置parameterType的时候,mybatis会自动识别, 在这里传递的是Map类型的参数进去。
Test2.java
public class Test2 {
public static void main(String[] args) throws IOException {
String resource = "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");
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",params); //这里变了用的是同一条sql语句的id
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
}
效果与上面等同!!!
where标签
接着上面if标签的例子
如果要进行多条件判断,就会写成下面这样,但会产生多条件的矛盾:
<select id="listProduct" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat('%',#{name},'%')
</if>
<if test="price!=0">
and price > #{price}
</if>
</select>
因此执行就会报错
这个问题可以通过< where >标签来解决!!!
Product.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.how2java.pojo">
<select id="listProduct" 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>
注:if标签里的sql语句,and要加上去;
因为where标签会自动去掉前缀里面多余的and 或这是 or,但是where标签不能自动添加and 或者是 or;
Test3.java
public class Test3 {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("多条件查询");
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);
}
session.commit();
session.close();
}
}
set标签
与where标签类似的,在update语句里也会碰到多个字段相关的问题。 在这种情况下,就可以使用set标签:
Product.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.how2java.pojo">
<select id="listProduct" 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}
<!--
解决把where标签中的条件,> 号换为<号,编译阶段就报错,运行还会报错的问题:
> 位大于号, <位小于号,需要用到xml转义符
-->
</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>
Test4.java
public class TestMybatis {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
Product p = new Product();
//设置参数,update与select不一样,它传入的是一个Product实例
p.setId(6);
p.setName("product zz");
p.setPrice(99.99f);
session.update("updateProduct",p);
listAll(session);
session.commit();
session.close();
}
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);
}
}
}
choose标签
Mybatis里面没有else标签,但是可以使用when otherwise标签来达到 if else 这样的效果。
Product.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.how2java.pojo">
<select id="listProduct" 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里边那个 and 没有用,<where>会帮你去掉,写不写都行
</otherwise>
</choose>
</where>
</select>
</mapper>
当有多个when标签,判断when标签中的test条件是否成立,如果有一个成立,则 choose 结束。
Test5.java
public class Test5 {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
Map<String,Object> params = new HashMap<>();
// params.put("name","a");
// params.put("price","10");
List<Product> ps = session.selectList("listProduct",params);
for (Product p : ps) {
System.out.println(p);
}
session.commit();
session.close();
}
private static void listAll(SqlSession session) {
}
}
foreach标签(foreach标签通常用于in 这样的语法里)
list是个数组。
由sql语句select * from product_ where id in (‘1’,‘3’,‘5’)知道
【1,2,3,4】 open="(" separator=",“close=”)的意思:以“(”开始,中间用“,”分开,以“)”结束。
即是:(1,2,3,4),所以其目的是改格式。
Product.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.how2java.pojo">
<select id="listProduct" resultType="Product">
SELECT * FROM product_
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
item是collection里面的每一个元素,collection="list"指是列表类型
Test6.java
public class Test6 {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
//查询出id等于1,3,5的数据出来。
List<Integer> ids = new ArrayList();
ids.add(1);
ids.add(3);
ids.add(5);
List<Product> ps = session.selectList("listProduct",ids);
for (Product p : ps) {
System.out.println(p);
}
session.commit();
session.close();
}
}
疑惑1:关于解决<与>的显示问题
比如sql语句: select * from product_ where name=? and price>? and id in(?,?,?);
这样传参的时候试是怎么传入的?还有xml该怎么写?
解决方法:
把装有id的list放入map中:map.put(“idList”, list),map中还包括name和price。
疑惑2:关于解决传入的collection类型不是list而是map的问题
参考方案(参考地址):
上述collection的值为ids,是传入的参数Map的key
bind标签
bind标签就像是再做一次字符串拼接,下面是在模糊查询的基础上,把模糊查询改为bind标签。
Product.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.how2java.pojo">
<!-- 本来的模糊查询方式 -->
<!-- <select id="listProduct" resultType="Product"> -->
<!-- select * from product_ where name like concat('%',#{0},'%') -->
<!-- </select> -->
//因为Mybatis会智能识别,所以paramType="string",在这里可以不用加
<select id="listProduct" resultType="Product">
//name 就是引用时的name,其值可随便命名,value 指传入的参数处理后的值
<bind name="likename" value="'%' + name + '%'" />
select * from product_ where name like #{likename}
</select>
</mapper>
Test7.java
public class TestMybatis {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
Map<String, String> params =new HashMap();
params.put("name", "product");
List<Product> ps = session.selectList("listProduct",params);
for (Product p : ps) {
System.out.println(p);
}
session.commit();
session.close();
}
}
喜欢的朋友可以关注我的个人公众号,后台回复java资料可免费领取资源。