Java框架学习：MyBatis之动态Sql

环境搭建

导入依赖

<dependency>
   	<groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.18</version>
</dependency>

mybatis-config.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="db.properties"/>
    <typeAliases>
        <package name="com.young.model"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${db.driverClassName}"/>
                <property name="url" value="${db.url}"/>
                <property name="username" value="${db.username}"/>
                <property name="password" value="${db.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.young.mapper.GoodMapper"/>
    </mappers>

</configuration>

db.properties配置文件

db.url=jdbc:mysql://localhost:3306/mybatis2_db?serverTimezone=UTC
db.username=root
db.password=123
db.driverClassName=com.mysql.jdbc.Driver

Good实体类

public class Good {

    private Integer gid;
    private String gname;
    private Float gprice;
    private String gdecs;

	//以下省略了所有成员变量的get和set方法......
	
}

传统的Mapper映射文件

GoodMapper接口

public interface GoodMapper {
   Good selectByGid(Integer gid);
}

GoodMapper.xml映射文件
注意：xml配置文件的第一行必须为该文件的版本号等信息，即：<?xml version="1.0" encoding="UTF-8" ?>，否则会报错

<?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.young.mapper.GoodMapper">
    <select id="selectByGid" resultType="Good">
        SELECT
            gid,
            gname,
            gprice,
            gdecs
        FROM
            goods
        WHERE
            gid=#{gid};
    </select>
</mapper>

问题：
在之前的开发中，我们会在<select>标签中书写Sql语句，比如上述根据gid查询商品信息。假设我们还需要根据gname或者gprice等其他列查询商品信息，就需要再写更多的<select>标签来实现这些功能，而且查询的信息都是相同的，也会造成代码冗余。

动态Sql

<if>标签、<sql>标签、<where>标签

GoodMapper.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.young.mapper.GoodMapper">
    <sql id="baseColumn">
        gid,gname,gprice,gdecs
    </sql>
	<!--根据传入的参数Good对象来判断，查询的条件-->
    <select id="query" parameterType="Good" resultType="Good">
        SELECT
           <include refid="baseColumn"/>
        FROM
            goods
        WHERE
            <if test="gid!=null">
                gid=#{gid}
            </if>
            <if test="gname!=null">
                and gname=#{gname}
            </if>
    </select>
</mapper>

GoodMapper接口

public interface GoodMapper {
   Good query(Good good);
}

使用<sql>标签将需要查询的列包裹起来，然后使用<include>标签在Sql语句中引用。
使用<if>标签进行条件的判断，如果传入的Good类对象的gid属性不为空，那么将gid=#{gid}拼接在WHERE后；同理，如果如果传入的Good类对象的gname属性不为空，将and gname=#{gname}继续拼接。

细节： 在设计Good实体类时，gid的类型为Integer，此时gid的默认值为null，如果类型为int，那么默认值为0，<if>标签在进行判断时会将sql语句拼接

测试方法

 @Test
public void testMapper() throws IOException {
    InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
    SqlSession session = sessionFactory.openSession();
    GoodMapper goodMapper = session.getMapper(GoodMapper.class);
    Good good = new Good();
    good.setGid(1);
    good.setGname("补兵的艺术");
    Good result = goodMapper.query(good);
    System.out.println(JSON.toJSONString(result,true));
    session.close();
}

运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods WHERE gid=? and gname=?

问题：
如果Good类对象的gid属性为null，即将good.setGid(1);这一行注释，那么执行的sql语句为：SELECT gid,gname,gprice,gdecs FROM goods WHERE and gname=?

所以可以使用<where>标签，如下所示：

<select id="query" parameterType="Good" resultType="Good">
    SELECT
       <include refid="baseColumn"/>
    FROM
        goods
    <where>
        <if test="gid!=null">
            and gid=#{gid}
        </if>
        <!--优化功能，如果用户输入空字符串或者空格不会查询-->
        <if test="gname!=null and gname.trim!='' ">
            and gname=#{gname}
        </if>
    </where>
</select>

<where>标签会自动删除where关键字后的and关键字
测试方法

GoodMapper goodMapper = session.getMapper(GoodMapper.class);
Good good = new Good();
good.setGname("补兵的艺术");
Good result = goodMapper.query(good);
System.out.println(JSON.toJSONString(result,true));

运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods WHERE gname=?

<trim>标签

可以使用<trim>标签代替 <where>标签

<select id="query" parameterType="Good" resultType="Good">
    SELECT
       <include refid="baseColumn"/>
    FROM
        goods
    <!--prefix:增加的前缀
            prefixOverrides:删除的前缀
            suffix:增加的后缀
            suffixOverrides:删除的后缀
            在动态生成的语句中，删除前缀"and"，然后增加前缀"where"-->
    <trim prefix="where" prefixOverrides="and">
        <if test="gid!=null">
            and gid=#{gid}
        </if>
        <if test="gname!=null and gname.trim!=''">
            and gname=#{gname}:
        </if>
    </trim>
</select>

测试方法

GoodMapper goodMapper = session.getMapper(GoodMapper.class);
Good good = new Good();
good.setGname("补兵的艺术");
Good result = goodMapper.query(good);
System.out.println(JSON.toJSONString(result,true));

运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods where gname=?

<trim>标签通常用于插入和更新
GoodMapper.xml映射文件中<update>标签内容

<update id="update" parameterType="Good">
   UPDATE
        goods
    SET
    <trim prefixOverrides="," >
        <if test="gname!=null and gname.trim!=''">
            gname=#{gname}
        </if>
        <if test="gprice!=null">
            ,gprice=#{gprice}
        </if>
        <if test="gdecs!=null and gdecs.trim!=''">
            ,gdecs=#{gdecs}
        </if>
    </trim>
    where gid=#{gid}
</update>

GoodMapper接口

public interface GoodMapper {
   Good query(Good good);
   void update(Good good);
}

测试方法

GoodMapper goodMapper = session.getMapper(GoodMapper.class);
Good good = new Good();
good.setGid(4);
good.setGname("英雄图鉴");
good.setGdecs("英雄百科全书");
goodMapper.update(good);
session.commit();

运行方法通过日志可以得知执行的sql语句：UPDATE goods SET gname=? ,gdecs=? where gid=?

<foreach>标签

<foreach>标签通常用于构建IN条件语句时
GoodMapper.xml映射文件

<select id="queryIn" parameterType="list" resultType="Good">
    SELECT
        <include refid="baseColumn"/>
    FROM
    goods
    where gid in
    <!--collection:该属性的值为"list"，代表传入的参数List集合
        item:表示集合中的元素
        open:表示前缀
        close:表示后缀
        separator:表示每个元素的分割符
        index:如果参数时List集合或者数组表示下标，如果参数是Map集合，则表示key-->
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
</select>

GoodMapper接口

public interface GoodMapper {
   Good query(Good good);
   void update(Good good);
   List<Good> queryIn(List<Integer> ids);
}

测试方法

GoodMapper goodMapper = session.getMapper(GoodMapper.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(3);
List<Good> goods = goodMapper.queryIn(ids);
System.out.println(JSON.toJSONString(goods,true));

运行方法通过日志可以得知执行的sql语句：SELECT gid,gname,gprice,gdecs FROM goods where gid in ( ? , ? )