mybatis之动态SQL

where标签

当我们拼接动态SQL时，如果一个查询条件都没有，那我们就不需要where子句，而如果有至少一个条件我们就需要where子句。
示例

<select id="findActiveBlogLike" resultType="Blog">
	SELECT * FROM BLOG
	<where>
		state = #{state}
	</where>
</select>

if标签

示例：

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

如果我们的state字段也是动态拼接的，那这里就有问题了，比如我三个条件都没有时，拼出来的sql语句就是SELECT * FROM BLOG WHERE显然是无法执行的，这就要用到我们的where标签：

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

当一个查询条件都没有拼接时， mybatis会自动将where关键字和拼接多个条件之间的诸如AND、OR这些多余的关键字去掉

choose\when\otherwise标签

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

使用示例：

<select id="findActiveBlogLike" resultType="Blog">
  	SELECT * FROM BLOG WHERE state = 'ACTIVE'
	<choose>
    	<when test="title != null">
      		AND title like #{title}
    	</when>
    	<when test="author != null and author.name != null">
      		AND author_name like #{author.name}
    	</when>
    	<otherwise>
      		AND featured = 1
    	</otherwise>
  	</choose>
</select>

set标签

set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号(如：语句最后的逗号)
使用示例：

<update id="updateAuthorIfNecessary">
  	update Author
    <set>
      	<if test="username != null">
      		username=#{username},
      	</if>
      	<if test="password != null">
      		password=#{password},
      	</if>
      	<if test="email != null">
      		email=#{email},
      	</if>
      	<if test="bio != null">
     	 	bio=#{bio}
      	</if>
    </set>
  	where id=#{id}
</update>

trim标签

trim标签常用属性有：

• prefix: 添加指定前缀

• prefixOverrides: 删除指定前缀

• suffixOverrides: 删除指定后缀
  示例一：用标签实现标签功能

  <trim prefix="WHERE" prefixOverrides="AND |OR ">
    ...
  </trim>
  

  prefixOverrides的作用是移除字符串开头的内容中所有指定在 prefixOverrides 属性中的内容，并且插入 prefix 属性中指定的内容。

  示例二： 用标签实现标签功能

  <trim prefix="SET" suffixOverrides=",">
    ...
  </trim>
  

  注意这里我们删去的是后缀值，同时添加了前缀值。

foreach

foreach标签用于通过循环的方式动态拼接SQL，使用方法如下：

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

• item: 指定在循环中可以用的item属性名称。当循环的对象是List、Set、数组时item是当前迭代的对象的元素值；而当循环对象是Map时，item是key对应的值
• index: 指定在循环中可以用的index属性名称。当循环的对象是List、Set、数组时index是当前迭代的次数；而当循环对象是Map时，index是key

动态SQL示例

jdbc.properties数据源

jdbc.url=jdbc:mysql://localhost:3306/yanfa5
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
jdbc.characterEncoding=utf8

CityMapper.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">

<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.CityDao">
	<!-- where、if、choose标签示例 -->
    <select id="queryCity" resultType="com.lanou3g.mybatis.bean.City">
        select * from city
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <choose>
                <when test="cname != null">
                    and cname = #{cname}
                </when>
                <otherwise>
                    and pid = #{pid}
                </otherwise>
            </choose>
        </where>
    </select>

	<!-- set标签示例 -->
	<update id="updateCity">
        update city
        <set>
            <if test="id != null">
                pid = #{pid}
            </if>
        </set>
        where id = #{id}
    </update>

	<!-- trim标签示例 -->
	<update id="updateCityByTrim">
        update city
        <!-- 删除set pid = #{pid},句首的‘set’和句尾的‘，’
        <trim prefix="set" prefixOverrides="set" suffixOverrides=",">
            set pid = #{pid},
        </trim>
        where id = #{id}
    </update>

	<!-- foreach标签示例 -->
	<select id="selectByForEach" resultType="com.lanou3g.mybatis.bean.City">
        select * from city where id in
        <foreach collection="list" item="city" open="(" separator="," close=")">
            #{city.id}
        </foreach>
    </select>
</mapper>

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties" />
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/CityMapper.xml" />
    </mappers>
</configuration>

对应数据库表的java类

@Getter
@Setter
public class City {
    private Integer id;
    private String cname;
    private Integer pid;

    @Override
    public String toString() {
        return "City{" +
                "id=" + id +
                ", cname='" + cname + '\'' +
                ", pid=" + pid +
                "}\n";
    }
}

dao层接口（操作数据库）

public interface CityDao {
	//where、if、choose标签示例
    List<City> queryCity(City city);
	//set标签示例
	int updateCity(City city);
	//trim标签示例
	int updateCityByTrim(City city);
	//foreach标签示例
	List<City> selectByForEach(List<City> cityList);
}

程序运行入口

public class App {
    public static void main(String[] args) throws IOException {
        //1、读入配置文件
        String confPath = "mybatis.xml";
        InputStream is = Resources.getResourceAsStream(confPath);

        //2、构建SqlSessionFactory(用于获取sqlSession)
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is,"development");

        //3、获取sqlSession对象（用于具体的RUID)
        SqlSession sqlSession = sessionFactory.openSession(true);

        //4、具体的RUID
        CityDao cityDao = sqlSession.getMapper(CityDao.class);
        City city = new City();

		//where、if、choose标签示例
        city.setId(6);
        city.setPid(2);
        System.out.println(cityDao.queryCity(city));

		//set标签示例
		city.setId(10);
        city.setPid(6);
        System.out.println("影响行数:" + cityDao.updateCity(city));

		//trim标签示例
		city.setId(10);
        city.setPid(12);
        System.out.println("影响行数:" + cityDao.updateCityByTrim(city));

		//foreach标签示例
		List<City> cityList = new ArrayList<>();
        for(int i = 0;i < 10 ;i++){
            City c = new City();
            c.setId(i + 1);
            cityList.add(c);
        }
        System.out.println(cityDao.selectByForEach(cityList));
    }
}