MyBatis学习(四)

前言

　　最近比较松懈，下班回家后也懒得学习了。今晚实在是看不下去了，争取时间学习。社会上有这么多的资源，就看谁能抢的多吧。今天就说说MyBatis的动态SQL吧

正文

　　动态 SQL 通常要做的事情是有条件地包含 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>

</select>

如果这些条件没有一个能匹配上将会怎样？最终这条 SQL 会变成这样：

SELECT * FROM BLOG

WHERE

这会导致查询失败。如果仅仅第二个条件匹配又会怎样？这条 SQL 最终会是这样:

SELECT * FROM BLOG

WHERE

AND title like ‘someTitle’

这个查询也会失败。这个问题不能简单的用条件句式来解决，如果你也曾经被迫这样写过，那么你很可能从此以后都不想再这样去写了。

MyBatis 有一个简单的处理，这在90%的情况下都会有用。而在不能使用的地方，你可以自定义处理方式来令其正常工作。一处简单的修改就能得到想要的效果：

<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>

where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。

好了，上面都是摘自MyBatis官网，说明了动态sql在使用中存在的问题，那我就以where为例，跑一个小程序，还是基于前面几节中使用到的表。

<select id="getUserByObjInDynamicMethod" parameterType="org.tonny.entity.User" resultType="org.tonny.entity.User">

        SELECT * FROM users

        <where>

            <if test="id != null">

                id=#{id}

            </if>

            <if test="name != null">

                and name like #{name}

            </if>

        </where>

    </select>

    <select id="getUserByMapInDynamicMethod" parameterType="org.tonny.entity.User" resultType="org.tonny.entity.User">

        SELECT * FROM users

        <where>

            <if test="id != null">

                id=#{id}

            </if>

            <if test="name != null">

                and name like #{name}

            </if>

        </where>

    </select>

这部分配置与上面的一样，使用了where标签。对应的Java代码如下：

@Test

    public void getUserByObjInDynamicMethod()

    {

        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 映射sql的标识字符串

        String sql = "org.tonny.mapper.UsersMapper.getUserByObjInDynamicMethod";

        User user = new User();

        user.setId(1);

        user.setName("%nny%");

        user.setAge(30);

        List<User> userList = sqlSession.selectList(sql, user);

        sqlSession.close();

        System.out.println(userList);

    }

    @Test

    public void getUserByMapInDynamicMethod()

    {

        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 映射sql的标识字符串

        String sql = "org.tonny.mapper.UsersMapper.getUserByObjInDynamicMethod";

        Map<String,Object> map = new HashMap<String, Object>();

        map.put("id", 1);

        map.put("name", "%nny%");

        List<User> userList = sqlSession.selectList(sql, map);

        sqlSession.close();

        System.out.println(userList);

    }

这样就可以查出来了。对了，为了可以在控制台查看MyBatis的SQL，需要在mybatis.xml增加配置信息：

<!-- 设置日志打印 -->

    <settings>

        <setting name="logImpl" value="STDOUT_LOGGING"/>

    </settings>

这样就可以看到输出的sql语句了。测试结果：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.

PooledDataSource forcefully closed/removed all connections.

PooledDataSource forcefully closed/removed all connections.

PooledDataSource forcefully closed/removed all connections.

PooledDataSource forcefully closed/removed all connections.

Opening JDBC Connection

Created connection 25193812.

Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1806d54]

==>  Preparing: SELECT * FROM users WHERE id=? and name like ?

==> Parameters: 1(Integer), %nny%(String)

<==    Columns: id, NAME, age

<==        Row: 1, Tonny Chien, 25

<==      Total: 1

Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1806d54]

Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1806d54]

Returned connection 25193812 to pool.

[User [id=1, name=Tonny Chien, age=25]]

还要介绍一下set标签，它对应update中的set

<update id="updateUserByMapInDynamicMethod" parameterType="java.util.Map">

        UPDATE users

        <set>

            <if test="name != null">

                name = #{name},

            </if>

            <if test="age != null">

                age = #{age}

            </if>

        </set>

        <where>

            <if test="id != null">

                id=#{id}

            </if>

            <if test="age != null">

                <![CDATA[

                    and age > #{age}

                ]]>

            </if>

        </where>

    </update>

Java测试代码如下：

@Test

    public void updateUserByMapInDynamicMethod()

    {

        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 映射sql的标识字符串

        String sql = "org.tonny.mapper.UsersMapper.updateUserByMapInDynamicMethod";

        Map<String,Object> map = new HashMap<String, Object>();

        map.put("id", 1);

        map.put("age", 20);

        map.put("name", "北堂一刀");

        int result = sqlSession.update(sql, map);

        sqlSession.commit();

        sqlSession.close();

        System.out.println(result);

    }

执行结果：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.

PooledDataSource forcefully closed/removed all connections.

PooledDataSource forcefully closed/removed all connections.

PooledDataSource forcefully closed/removed all connections.

PooledDataSource forcefully closed/removed all connections.

Opening JDBC Connection

Created connection 30587315.

Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3]

==>  Preparing: UPDATE users SET name = ?, age = ? WHERE id=? and age > ?

==> Parameters: 北堂一刀(String), 20(Integer), 1(Integer), 20(Integer)

<==    Updates: 1

Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3]

Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3]

Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3]

Returned connection 30587315 to pool.

1

后记

就写到这儿吧，下次再详细些，休息了。

巴特西

MyBatis学习(四)

最新文章

热门文章