整形医院手机网站模版/seo建站还有市场吗

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 hotelwhere<if test="id!=null">id=#{id} </if><if test="hotelName!=null &amp;&amp; hotelName!=&quot;&quot;">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;}@Testpublic 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 &amp;&amp; hotelName!=&quot;&quot;">

常用的有以下字符

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 hotelwhere 1=1<if test="id!=null">id=#{id} </if><if test="hotelName!=null &amp;&amp; hotelName!=&quot;&quot;">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 &amp;&amp; hotelName!=&quot;&quot;">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 &amp;&amp; hotelName!=&quot;&quot;">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;}@Testpublic 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 &amp;&amp; hotelName!=&quot;&quot;">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 &amp;&amp; hotelName!=&quot;&quot;">hotel_name like concat('%',#{hotelName},'%')</when><when test="hotelAddress!=null &amp;&amp; hotelAddress!=&quot;&quot;">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属性值为list2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个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;}@Testpublic 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 hotelWHERE hotel_name LIKE #{_hotelName}
</select>