MyBatis 的强大特性之一便是它的动态 SQL。可以根据不同条件拼接 SQL 语句。
动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。主要由以下几种元素。
- if
- where
- trim
- choose (when, otherwise)
- set
- foreach
- bind
1、if （判断）
实例：根据条件查询酒店列表
hotelMapper接口
package com.pjf.mybatis.dao; import java.util.List;
import com.pjf.mybatis.po.Hotel; public interface HotelMapper { public List<Hotel> getHotel(Hotel hotel); }
hotelMapper.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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <!-- resultMap使用 --> <select id="getHotel" resultMap="myHotel"> select* from hotel where <if test="id!=null"> id=#{id} </if> <if test="hotelName!=null && hotelName!="""> and hotel_name like concat('%',#{hotelName},'%') </if> <if test="price!=null"> and price> #{price} </if> </select> </mapper>
测试类
package com.pjf.mybatis; import java.io.IOException; import java.io.InputStream; import java.util.List;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 org.junit.Test; import com.pjf.mybatis.dao.HotelMapper;import com.pjf.mybatis.po.Hotel; public class TestHotel { public SqlSessionFactory sqlSessionFactory() throws IOException { // mybatis的配置文件 String resource = "mybatis_config.xml"; // 使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）TestHotel.class.getClassLoader() InputStream is = Resources.getResourceAsStream(resource); // 构建sqlSession的工厂 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); return sessionFactory; } @Test public void getHotel() throws IOException { SqlSessionFactory sessionFactory = sqlSessionFactory(); SqlSession session = sessionFactory.openSession(); HotelMapper hotelMapper = session.getMapper(HotelMapper.class); System.out.println(hotelMapper.getClass()); Hotel hotel = new Hotel(1001, "南京", "", 800); List<Hotel> list = hotelMapper.getHotel(hotel); for (Hotel hotel1 : list) { System.out.println(hotel1); } session.close(); } }
查看结果
需要注意的是特殊字符需要转义，
<if test="hotelName!=null && hotelName!=""">
常用的有以下字符
2、where
对于上面的查询条件，如果在测试类中
Hotel hotel = new Hotel(1001, "南京", "", 800);改成 Hotel hotel = new Hotel(null,"南京","", 800);
结果会怎么样
sql多了一个and
解决：
a、增加默认条件1=1
<?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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <!-- resultMap使用 --> <select id="getHotel" resultMap="myHotel"> select* from hotel where 1=1 <if test="id!=null"> id=#{id} </if> <if test="hotelName!=null && hotelName!="""> and hotel_name like concat('%',#{hotelName},'%') </if> <if test="price!=null"> and price> #{price} </if> </select> </mapper>
结果
b、增加where元素
将所有的元素放在where标签内，where元素会自动去除首位的and或or。注意是首位的and或者or。
<?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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <!-- resultMap使用 --> <select id="getHotel" resultMap="myHotel"> select* from hotel <where> <if test="id!=null"> id=#{id} </if> <if test="hotelName!=null && hotelName!="""> and hotel_name like concat('%',#{hotelName},'%') </if> <if test="price!=null"> and price> #{price} </if> </where> </select> </mapper>
3、trim （字符串截取）
如果and写在语句的末尾，该怎么解决呢
<?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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <!-- resultMap使用 --> <select id="getHotel" resultMap="myHotel"> select* from hotel <where> <if test="id!=null"> id=#{id} and </if> <if test="hotelName!=null && hotelName!="""> hotel_name like concat('%',#{hotelName},'%') and </if> <if test="price!=null"> price> #{price} </if> </where> </select> </mapper>
测试类
package com.pjf.mybatis; import java.io.IOException; import java.io.InputStream; 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 org.junit.Test; import com.pjf.mybatis.dao.HotelMapper; import com.pjf.mybatis.po.City; import com.pjf.mybatis.po.Hotel; public class TestHotel { public SqlSessionFactory sqlSessionFactory() throws IOException { // mybatis的配置文件 String resource = "mybatis_config.xml"; // 使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）TestHotel.class.getClassLoader() InputStream is = Resources.getResourceAsStream(resource); // 构建sqlSession的工厂 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); return sessionFactory; } @Test public void getHotel() throws IOException { SqlSessionFactory sessionFactory = sqlSessionFactory(); SqlSession session = sessionFactory.openSession(); HotelMapper hotelMapper = session.getMapper(HotelMapper.class); Hotel hotel = new Hotel(1001,"南京","",null); System.out.println(hotel); List<Hotel> list = hotelMapper.getHotel(hotel); for (Hotel hotel1 : list) { System.out.println(hotel1); } session.close(); } }
结果：末尾就会多一个and
这时候就可以通过trim标签来解决：
trim 对trim标签体中拼接后的字符串进行操作
prefix 对拼接后的字符串加前缀 prefixOverrides 对拼接后的字符串去前缀 suffix 对拼接后的字符串加后缀 suffixOverrides 对拼接后的字符串去后缀
<?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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <select id="getHotel" resultMap="myHotel"> select* from hotel <!-- trim 对trim标签体中拼接后的字符串进行操作 prefix 对拼接后的字符串加前缀 prefixOverrides 对拼接后的字符串去前缀 suffix 对拼接后的字符串加后缀 suffixOverrides 对拼接后的字符串去后缀 --> <trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and"> <if test="id!=null"> id=#{id} and </if> <if test="hotelName!=null && hotelName!="""> hotel_name like concat('%',#{hotelName},'%') and </if> <if test="price!=null"> price> #{price} </if> </trim> </select> </mapper>
这样就解决了
4、choose （分支选择）
MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。,但不同的是mybatis进入了一个分支后就不再进入其他分支。
<?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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <select id="getHotel" resultMap="myHotel"> select* from hotel <where> <choose> <when test="id!=null"> id=#{id} </when> <when test="hotelName!=null && hotelName!="""> hotel_name like concat('%',#{hotelName},'%') </when> <when test="hotelAddress!=null && hotelAddress!="""> hotel_address=#{hotelAddress} </when> <when test="price!=null"> price>#{price} </when> <otherwise> 1=1 </otherwise> </choose> </where> </select> </mapper>
5、set
前面我们使用update可以进行全字段更新，但是如果是带了什么字段就更新什么字段，该怎么做呢。
可是使用set和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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <update id="updateHotel"> update hotel <set> <if test="hotelName!=null"> hotel_name=#{hotelName}, </if> <if test="price!=null"> price=#{price} </if> </set> <where> id=#{id} </where> </update> </mapper>
6、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.pjf.mybatis.dao.HotelMapper"> <resultMap type="com.pjf.mybatis.po.Hotel" id="myHotel"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="hotel_name" property="hotelName" jdbcType="VARCHAR" /> <result column="hotel_address" property="hotelAddress" jdbcType="VARCHAR" /> <result column="price" property="price" jdbcType="INTEGER" /> </resultMap> <!-- resultMap使用 --> <select id="getHotel" resultMap="myHotel"> select* from hotel where id in <!-- collection 指要遍历的集合 1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list 2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array 3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key item 将遍历出的元素赋值给指定的变量 separator 每个元素之间的分隔符 open 遍历出的结果拼接出一个开始符 close 遍历出的结果拼接一个结束符 index 索引 --> <foreach collection="list" item="ids" separator="," open="(" close=")"> #{ids} </foreach> </select> </mapper>
测试类
package com.pjf.mybatis; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; 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 org.junit.Test; import com.pjf.mybatis.dao.HotelMapper; import com.pjf.mybatis.po.City; import com.pjf.mybatis.po.Hotel; public class TestHotel { public SqlSessionFactory sqlSessionFactory() throws IOException { // mybatis的配置文件 String resource = "mybatis_config.xml"; // 使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）TestHotel.class.getClassLoader() InputStream is = Resources.getResourceAsStream(resource); // 构建sqlSession的工厂 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); return sessionFactory; } @Test public void getHotel() throws IOException { SqlSessionFactory sessionFactory = sqlSessionFactory(); SqlSession session = sessionFactory.openSession(true); HotelMapper hotelMapper = session.getMapper(HotelMapper.class); List<Integer> id =new ArrayList<Integer>(); id.add(1001); id.add(1002); id.add(1003); List<Hotel> list= hotelMapper.getHotel(id); for (Hotel hotel : list) { System.out.println(hotel); } session.close(); } }
7、bind
bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。
<select id="getHotel" resultMap="myHotel"> <bind name="_hotelName" value="'%' + hotelName + '%'" /> SELECT * FROM hotel WHERE hotel_name LIKE #{_hotelName} </select>