作用

• 可以定义代码片段
• 可以进行逻辑判断
• 可以进行循环处理(批量处理)，使条件判断更为简单

使用方式

• 通过mybatis中与动态sql有关的标签来实现

< sql >标签 + < include >标签

作用

• < sql >标签：可以用来定义代码片段
• < include >标签：对定义的代码片段进行引用

用法

UsersMapper.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.example.mapper.UsersMapper">

    <!--

        自定义代码片段

    -->

    <sql id="allColumns">

            id, username, birthday, sex, address

    </sql>

    <!--

        //获取全部用户信息

        List<Users> getAll();

    -->

    <select id="getAll" resultType="user">

        select

            <include refid="allColumns"/>

        from

            users

    </select>

</mapper>

< if > 标签 + < where >标签

作用

• < if >标签：进行条件判断
• < where >标签：进行多条件拼接，在查询，删除，更新中使用

用法

UsersMapper.java接口代码

package com.example.mapper;

import com.example.pojo.User;

import java.util.List;

/**

 * 数据访问层的接口，定义对数据库完成的CRUD的操作

 */

public interface UsersMapper {

    //根据给定的查询条件进行多条件查询

    List<User> selectOnCondition(User user);

}

UsersMapper.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.example.mapper.UsersMapper">

    <!--

        //根据给定的查询条件进行多条件查询

        List<User> selectOnCondition(User user);

        private Integer id;

        private String userName;

        private Date birthday;

        private String sex;

        private String address;

    -->

    <select id="selectOnCondition" parameterType="user" resultType="user">

        select

            <include refid="allColumns"/>

        from

            users

        <where>

            <if test="userName != null and userName != ''">

                and username like concat('%', #{userName},'%')

            </if>

            <if test="birthday != null">

                and birthday=#{birthday}

            </if>

            <if test="sex != null and sex != ''">

                and sex=#{sex}

            </if>

            <if test="address != null and address != ''">

                and address=#{address}

            </if>

        </where>

    </select>

</mapper>

• < where >标签相当于在原sql语句后补加了一个：where关键字，从底层输出的结果可以证实

==>  Preparing: select id, username, birthday, sex, address from users WHERE username like concat('%', ?,'%')

• 注意：至少有一个< if >标签通过，where关键字才会被追加，不然不会追加

    @Test

    public void testSelectOnCondition(){

        User u = new User();

        List<User> users = usersMapper.selectOnCondition(u);

        users.forEach(System.out::println);

    }

Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]

Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]

Opening JDBC Connection

Created connection 1340848245.

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4febb875]

==>  Preparing: select id, username, birthday, sex, address from users        //这时sql语句后面没有追加where

==> Parameters:

<==    Columns: id, username, birthday, sex, address

<==        Row: 1, 荷包蛋, 2002-08-23, 女, 黑河

<==        Row: 2, 小王, 2001-07-12, 1, 芜湖市

<==        Row: 3, 小张, 1999-02-22, 1, 长沙

<==        Row: 5, 段, 2001-03-10, 1, 太原

<==        Row: 6, 范成群, 2002-01-19, 1, 鲅鱼圈

<==        Row: 7, 学委, 2001-05-13, 2, 平顶山市

<==        Row: 28, 逻辑, 2010-05-18, 男, 上海

<==        Row: 29, 小昕, 2001-03-14, 女, 忻州

<==        Row: 30, 小青, 1996-11-22, 女, 沈阳市

<==      Total: 9

Users{id=1, userName='荷包蛋', birthday=Fri Aug 23 00:00:00 CST 2002, sex='女', address='黑河'}

Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='芜湖市'}

Users{id=3, userName='小张', birthday=Mon Feb 22 00:00:00 CST 1999, sex='1', address='长沙'}

Users{id=5, userName='段', birthday=Sat Mar 10 00:00:00 CST 2001, sex='1', address='太原'}

Users{id=6, userName='范成群', birthday=Sat Jan 19 00:00:00 CST 2002, sex='1', address='鲅鱼圈'}

Users{id=7, userName='学委', birthday=Sun May 13 00:00:00 CST 2001, sex='2', address='平顶山市'}

Users{id=28, userName='逻辑', birthday=Tue May 18 00:00:00 CST 2010, sex='男', address='上海'}

Users{id=29, userName='小昕', birthday=Wed Mar 14 00:00:00 CST 2001, sex='女', address='忻州'}

Users{id=30, userName='小青', birthday=Fri Nov 22 00:00:00 CST 1996, sex='女', address='沈阳市'}

Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4febb875]

Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4febb875]

Returned connection 1340848245 to pool.

Process finished with exit code 0

• 在< if >标签中的test属性中，用于条件判断的是入参的属性
  • 如果该属性不为空(对于String类型的属性，既要不等于null，又要是非空串)，标签中的条件追加到原sql语句后
  • 注意：如果只是追加了一个条件，mybatis框架会自动去除该条件所带的and

测试代码

package com.example.mapper;

import com.example.pojo.User;

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.After;

import org.junit.Before;

import org.junit.Test;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

public class TestUsersMapper {

    //SqlSession对象

    SqlSession sqlSession;

    //mybatis动态代理对象

    UsersMapper usersMapper;

    //获取SqlSession

    @Before

    public void getSqlSession() throws IOException {

        //读取核心配置文件

        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

        //创建SqlSessionFactory对象

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

        //获取SqlSession

        sqlSession = factory.openSession();

        //获取mybatis动态代理对象

        usersMapper = sqlSession.getMapper(UsersMapper.class);

    }

    //归还SqlSession

    @After

    public void closeSession(){

        sqlSession.close();

    }

    @Test

    public void testSelectOnCondition(){

        User u = new User("小", null, null, null);

        List<User> users = usersMapper.selectOnCondition(u);

        users.forEach(System.out::println);

    }

}

输出结果

Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]

Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]

Opening JDBC Connection

Created connection 2082509879.

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7c209437]

==>  Preparing: select id, username, birthday, sex, address from users WHERE username like concat('%', ?,'%')

==> Parameters: 小(String)

<==    Columns: id, username, birthday, sex, address

<==        Row: 2, 小王, 2001-07-12, 1, 芜湖市

<==        Row: 3, 小张, 1999-02-22, 1, 长沙

<==        Row: 29, 小昕, 2001-03-14, 女, 忻州

<==        Row: 30, 小青, 1996-11-22, 女, 沈阳市

<==      Total: 4

Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='芜湖市'}

Users{id=3, userName='小张', birthday=Mon Feb 22 00:00:00 CST 1999, sex='1', address='长沙'}

Users{id=29, userName='小昕', birthday=Wed Mar 14 00:00:00 CST 2001, sex='女', address='忻州'}

Users{id=30, userName='小青', birthday=Fri Nov 22 00:00:00 CST 1996, sex='女', address='沈阳市'}

Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7c209437]

Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7c209437]

Returned connection 2082509879 to pool.

Process finished with exit code 0

< set >标签

作用

• 对选中的记录的字段进行有选择的更新
  • 注意：但是至少更新一个字段

原先update标签的缺点

    <!--

        //根据id更新用户信息

        int update(User user);

    -->

    <update id="update" parameterType="user">

        update

            users

        set

            username=#{userName}, birthday=#{birthday}, sex=#{sex}, address=#{address}

        where

            id=#{id}

    </update>

    @Test

    public void testUpdate() throws ParseException {

        //时间字符串

        String dateStr = "2002-8-23";

        int num = usersMapper.update(new User(1, "荷包蛋", date.parse(dateStr), "女", "黑河"));

        sqlSession.commit();

        if (num == 1) {

            System.out.println("更新成功!");

        }else{

            System.out.println("更新失败!");

        }

    }

• 缺点分析
  • 上述标签之所以可以按照预期正常工作，是因为我们更新了该条记录处id以外的所有字段
  • 如果只更新一个字段，例如更新birthday字段，那么除了id和birthday字段，该条记录的其他字段值全部被赋值为null
  • 除非把其他不需要更改的字段赋值为与原先一样的值，该操作十分没有必要

使用< set >标签优化

UsersMapper.java

package com.example.mapper;

import com.example.pojo.User;

/**

 * 数据访问层的接口，定义对数据库完成的CRUD的操作

 */

public interface UsersMapper {

    //根据给定条件有选择性的更新记录的字段

    int updateBetter(User user);

}

UsersMapper.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.example.mapper.UsersMapper">

    <!--

        //根据给定条件有选择性的更新记录的字段

        int updateBetter(User user);

        private Integer id;

        private String userName;

        private Date birthday;

        private String sex;

        private String address;

    -->

    <update id="updateBetter" parameterType="user">

        update

            users

        <set>

            <if test="userName != null and userName != ''">

                username=#{userName},

            </if>

            <if test="birthday != null">

                birthday=#{birthday},

            </if>

            <if test="sex != null and sex != ''">

                sex=#{sex},

            </if>

            <if test="address != null and address != ''">

                address=#{address},

            </if>

        </set>

        where

            id=#{id}

    </update>

</mapper>

• < set > 标签相当于在原先sql语句后面补加了关键字，set，此时sql语句相当于："update XXX set"

==>  Preparing: update users SET address=? where id=?

• 注意：至少有一个< if >标签通过测试，set才会被追加
  • 这里也可以解释为什么至少要更新一个字段：如果测试条件都没有通过，也就是不更新字段
  • 此时sql语句被解析为："update XXX where id = ?"，缺少set条件，sql语句出错

• < set >标签里的< if >标签测试条件通过后，将需要修改的字段条件拼接到原sql语句后面
  • 注意：新拼接的字段条件后面要跟上一个逗号，防止有多个条件成立，要添加多个字段，当只有一个条件成立时：mybatis框架会自动去除该逗号

测试代码

package com.example.mapper;

import com.example.pojo.User;

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.After;

import org.junit.Before;

import org.junit.Test;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

public class TestUsersMapper {

    //SqlSession对象

    SqlSession sqlSession;

    //mybatis动态代理对象

    UsersMapper usersMapper;

    //获取SqlSession

    @Before

    public void getSqlSession() throws IOException {

        //读取核心配置文件

        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

        //创建SqlSessionFactory对象

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

        //获取SqlSession

        sqlSession = factory.openSession();

        //获取mybatis动态代理对象

        usersMapper = sqlSession.getMapper(UsersMapper.class);

    }

    //归还SqlSession

    @After

    public void closeSession(){

        sqlSession.close();

    }

    @Test

    public void testUpdateBetter(){

        User u = new User();

        u.setId(1);

        u.setAddress("哈尔滨");

        int num = usersMapper.updateBetter(u);

        if(num == 1){

            System.out.println("更新成功!");

        }else{

            System.out.println("更新失败!");

        }

        sqlSession.commit();

    }

}

输出结果

Checking to see if class com.example.mapper.TestUsersMapper matches criteria [is assignable to Object]

Checking to see if class com.example.mapper.UsersMapper matches criteria [is assignable to Object]

Opening JDBC Connection

Created connection 544966217.

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]

==>  Preparing: update users SET address=? where id=?

==> Parameters: 哈尔滨(String), 1(Integer)

<==    Updates: 1

更新成功!

Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]

Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]

Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@207b8649]

Returned connection 544966217 to pool.

Process finished with exit code 0

最新文章

1. mvc4 自定义HtmlHelper
2. Jedis测试redis
3. 有关try..catch..finally处理异常的总结
4. Marven笔记贴
5. 异步I/O
6. Data Flow -&gt;&gt; Pivot
7. httpsClient抓取证书
8. Android_listView
9. json-lib-2.4-jdk15.jar maven
10. vs 2013 编译zlib
11. 【数学】HDU 5761 Rower Bo
12. Spark算子--join
13. ado.net的简单数据库操作（二）之封装SqlHelperl类
14. MRC-block与ARC-block
15. 关于Java面试
16. 跟踪调试JDK源码时遇到的问题及解决方法
17. java 通信
18. Two Sum LT1
19. lua -- 点击关闭窗口中的子界面
20. (网络流 模板 Dinic) Drainage Ditches --POJ --1273

热门文章

1. 面试突击51：为什么单例一定要加 volatile？
2. ML第2周学习小结
3. Java - 六原则一法则
4. Base64编码知识详解
5. BUUCTF-snake
6. Nginx防御CC攻击
7. Java去除字符串中 除数字和逗号以外的符号
8. maven编译 出现Process terminated
9. sql server2016 数据库日志 清空语句
10. 关于 k 进制线性基