• mybatis 09: 动态sql --- part1

    作用

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

    使用方式

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

    < sql >标签 + < include >标签

    作用

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

    用法

    UsersMapper.xml映射文件

    
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>
    
    
    
    <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 selectOnCondition(User user);

}

    UsersMapper.xml映射文件

    
mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.UsersMapper">

    
    <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 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 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标签的缺点

        
    <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

    
mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.UsersMapper">
    
    
    
    <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语句出错

    image

    • < 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
  • 相关阅读:
    【C++】之内存管理 - new/delete
    神经网络训练数据集大小,神经网络输入图片大小
    用通俗易懂的大白话彻底搞明白mysql的数据类型以及mysql中的int(11)，这个11到底是啥？
    java 中集合与数组的相互转换
    计算机网络复习第一章 概述
    计算机毕业设计Java无人值守台球厅智能管理监控系统（源码+系统+mysql数据库+Lw文档）
    使用windows自带的网络命令工具抓包
    10.12 校招 实习 内推 面经
    学习笔记-java代码审计-文件操作
    Nodejs初体验
  • 原文地址：https://www.cnblogs.com/nefu-wangxun/p/16585328.html