- 多条件的矛盾
如果要进行多条件判断,就会写成这样:
<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>
这么写的问题是:当没有name参数,却有price参数的时候,执行的sql语句就会是:
这样执行就会报错select * from product_ and price > 10
<?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_ <if test="name!=null"> where name like concat('%',#{name},'%') </if> <if test="price!=0"> and price > #{price} </if> </select> </mapper>
package com.how2java; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; 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 com.how2java.pojo.Product; 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(); 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(); } }
- where标签
这个问题可以通过<where>标签来解决,如代码所示
<where>标签会进行自动判断<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>
如果任何条件都不成立,那么就在sql语句里就不会出现where关键字
如果有任何条件成立,会自动去掉多出来的 and 或者 or。
所以在测试代码里
这个参数map,无论是否提供值否都可以正常执行Map<String,Object> params = new HashMap<>(); //params.put("name","a"); params.put("price","10");
<?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>
package com.how2java; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; 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 com.how2java.pojo.Product; 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(); 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标签:
其效果与where标签类似,有数据的时候才进行设置。<set> <if test="name != null">name=#{name},</if> <if test="price != null">price=#{price}</if> </set>
<?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> <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>
package com.how2java; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; 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 com.how2java.pojo.Product; 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(); 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); } } }
- trim标签
trim 用来定制想要的功能,比如where标签就可以用
来替换<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
set标签就可以用
来替换<trim prefix="SET" suffixOverrides=","> ... </trim>
运行set标签中的代码,其效果是一样的。
<?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_ <trim prefix="WHERE" prefixOverrides="AND |OR "> <if test="name!=null"> and name like concat('%',#{name},'%') </if> <if test="price!=null and price!=0"> and price > #{price} </if> </trim> </select> <update id="updateProduct" 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>
- 注意
mybatis中 xml文件不支持 大于号 小于号,有两种方法可以解决
一种是用转译符: < 就是小于的意思, >就是大于的意思
还有一种是 使用<![CDATA[ ]]>符号进行说明
第一种:SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE 第二种:<![CDATA[ when min(starttime)<='12:00' and max(endtime)<='12:00' ]]>
Mybatis动态SQL-where标签、set标签、trim标签
最新推荐文章于 2023-10-16 16:58:34 发布