3. MyBatis - 深度学习 - SQL语句

╰+哭是因爲堅強的太久メ 2023-07-03 06:18 4阅读 0赞

## 前言 ##

整理一下MyBatis中常使用的SQL说明

## 介绍 ##

通过原生JDBC写DAO的年代 ，程序员最怕莫过于拼接SQL语句，拼接参数与设置返回结果集。

Hibernate 将拼接SQL时代成为过去，通过ORM映谢，完全不需要处理任何SQL，但这又带来了新的问题就是。无法编写自定义SQL从而丧失了灵活活及更好的性能。

MyBatis 通过 mapper 映射SQL很好解决了这一点，它无需在JAVA代码中拼接SQL，而是将其移至mapper 文件集中处理SQL节约了大量的开发时间

### Mapper 中的元素 ###

<table> 
 <thead> 
  <tr> 
   <th>元素</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>cache</td> 
   <td>对给定命名空间的缓存配置</td> 
  </tr> 
  <tr> 
   <td>resultMap</td> 
   <td>结果集映射</td> 
  </tr> 
  <tr> 
   <td>sql</td> 
   <td>可被其他语句引用的可重用语句块</td> 
  </tr> 
  <tr> 
   <td>insert</td> 
   <td>插入语句</td> 
  </tr> 
  <tr> 
   <td>update</td> 
   <td>更新语句</td> 
  </tr> 
  <tr> 
   <td>delete</td> 
   <td>删除语句</td> 
  </tr> 
  <tr> 
   <td>select</td> 
   <td>查询语句</td> 
  </tr> 
 </tbody> 
</table>

### 常见属性说明 ###

<select id="selectPerson" 
    parameterType="int"
    
    parameterMap="paramsMap"
    
    resultType="Person"
    
    resultMap="StudentMap"
    
    flushCache="false"
    
    useCache="true"
    
    timeout="10"
    
    fetchSize="256"
    
    statementType="PREPARED">
    </select>

### StatementType详解 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>STATEMENT</td> 
   <td>直接执行SQL，不进行预编译</td> 
   <td>select * from student where id = ${id}</td> 
  </tr> 
  <tr> 
   <td>PREPARED</td> 
   <td>预处理后，执行SQL</td> 
   <td>select * from student where id = #{id}</td> 
  </tr> 
  <tr> 
   <td>CALLABLE</td> 
   <td>执行存储过程</td> 
   <td>{call test(#{id,jdbcType=INTEGER,mode=IN})}</td> 
  </tr> 
 </tbody> 
</table>

## 预备数据库 ##

> 如果你使用docker的话，推荐看看我的文章把 \[Docker 实战系列 - MySQL环境\](…/…/Docker/实战系列/Docker 实战系列 - MySQL环境.md)

创建数据库

# 创建库
    create database mybatis_demo;

创建表

CREATE TABLE `student` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `cls_id` int(10) NOT NULL COMMENT '班级ID',
      `name` varchar(32) NOT NULL COMMENT '名字',
      `age` int(3) NOT NULL COMMENT '年龄',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

CREATE TABLE `cls` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) NOT NULL COMMENT '班级名称',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

插入测试数据

INSERT INTO `student`(`id`, `cls_id`, `name`, `age`, `create_time`) VALUES (1, 1, '张三', 13, now());
    INSERT INTO `student`(`id`, `cls_id`, `name`, `age`, `create_time`) VALUES (2, 2, '李四', 14, now());

INSERT INTO `cls`(`id`, `name`, `create_time`) VALUES (1, '初一1班', now());
    INSERT INTO `cls`(`id`, `name`, `create_time`) VALUES (2, '初一2班', now());

Java类配置信息

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class Student { 
        private Integer id;
        private String name;
        private Integer clsId;
        private Date createTime;
        // 班级信息
        private Cls cls;
    }

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class Cls { 
        private Integer id;
        private String name;
    }

## 查询 ##

单个简单参数引用 `方法中只有一个参数可通过任意名称进行引用`

<select id="selectStudentXml" resultMap="StudentMap">
      select * from student where id = #{id}
    </select>

int insertStudent(Integer clsId,String name,Integer age);

多个简单参数引用 `通过参数下标引用 #{param1} #{param2}`

<insert id="insertStudent" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
      INSERT INTO student(cls_id,`name`,age,create_time) 
      VALUES (#{param1}, #{param2}, #{param3}, now());
    </insert>

int insertStudent2(Student s);

对像属性引用: `直接通过对象属性名称引用，嵌套对像通过.号进行引用`

<insert id="insertStudent2" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
      INSERT INTO student(cls_id,`name`,age,create_time)
      VALUES (#{clsId}, #{name}, #{age}, now());
    </insert>

int insertStudent2(Student s);

map方式

<insert id="insertStudent3" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
      INSERT INTO student(cls_id,`name`,age,create_time)
      VALUES (#{clsId}, #{name}, #{age}, now());
    </insert>

int insertStudent3(Map<String, Object> map);

变量名称引用(需要jdk1.8支持) `编译时加上-parameters`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]

<build>
      
      <plugins>
        <plugin>
          <groupId>org.apache.maven.plugins</groupId>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.3</version>
          <configuration>
            <source>1.8</source>
            <target>1.8</target>
            <compilerArgs>
              <arg>-parameters</arg>
            </compilerArgs>
          </configuration>
        </plugin>
      </plugins>
    </build>

## 关联查询 ##

简单查询

<resultMap id="StudentMap" type="com.xm.chapter1.Student">
      <id property="id" column="id"/>
      <result property="clsId" column="cls_id"/>
      <result property="createTime" column="create_time"/>
    
    </resultMap>
    
    <select id="selectStudentXml" resultMap="StudentMap">
      select * from student where id = #{id}
    </select>

关联查询 - 推荐 **进行一次select**

<resultMap id="StudentMap" type="com.xm.chapter1.Student">
      <id property="id" column="id"/>
      <result property="name" column="name"/>
      <result property="clsId" column="cls_id"/>
      <result property="createTime" column="create_time"/>
      <association property="cls" javaType="com.xm.chapter1.Cls">
        <id property="id" column="cls_id"/>
        <result property="name" column="cls_name"/>
      </association>
    </resultMap>
    
    <select id="selectStudentAssociation" resultMap="StudentMap">
      SELECT s.*,c.id as cls_id,c.name as cls_name from student s
      left join cls c on s.cls_id = c.id
      where s.id = #{id}
    </select>

引入外部进行关联查询 - 不推荐 **会进行两次select，然后mybatis关联**

<resultMap id="StudentMap" type="com.xm.chapter1.Student">
      <id property="id" column="id"/>
      <result property="name" column="name"/>
      <result property="clsId" column="cls_id"/>
      <result property="createTime" column="create_time"/>
      <association property="cls" javaType="com.xm.chapter1.Cls" select="selectByClsId" column="cls_id"/>
    </resultMap>
    <select id="selectStudentAssociation2" resultMap="StudentMap">
      SELECT * from student where id = #{id}
    </select>

## 动态SQL ##

where标签会自动加入SQL `where`，并去除无用的符号

<select id="selectWhere" resultMap="StudentMap">
      SELECT * from student where id = #{id} 
      <where>
        <if test="title != null and title != ''">
          and name = #{name}
        </if>
        <if test="age != null">
          and age = #{age}
        </if>
      </where>
      order by id
    </select>

使用when + 默认值

<select id="selectUserListSortBy" resultMap="StudentMap">
      SELECT * FROM student 
     <choose>
        <when test="sortBy == 'name' or sortBy == 'age'">
          order by ${sortBy}
        </when>
       
        <otherwise>
          order by name
        </otherwise>
     </choose>
    </select>

只想选择一项，任选一。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>

若语句的开头为“AND”或“OR”，*where* 元素也会将它们去除

删除某尾部“AND”或“OR”，*where* 元素也会将它们去除

update语句中的set

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

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>

动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="selectStudentListSortBy" resultMap="StudentMap">
      SELECT * FROM student where id in
      <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
    		#{item}
      </foreach>
    </select>

List<Student> selectStudentListSortBy(@Param("list") List<Integer> list);

StudentMapper mapper = session.getMapper(StudentMapper.class);
    List<Student> code = mapper.selectStudentListSortBy(Arrays.asList(1,2,3));
    System.out.println(code);

在同一个mapper 多个statement 存在多个相同的sql 片段时，可以通过`<sql>`元素声明，在通过 `<include>`元素进行引用

<sql id="files">
        id ,name ,createTime
    </sql>

## 扩展动态SQL ##

以上逻辑符号都是MyBatis中自带的XMLLanguageDriver所提供的解释语言，除非此之外我们还可以使用MyBatis-Velocity 或 mybatis-freemarker 等外部解释器来编写动态脚本

引入依赖

<dependency>
        <groupId>org.mybatis.scripting</groupId>
        <artifactId>mybatis-freemarker</artifactId>
        <version>1.1.2</version>
    </dependency>

定义

List<Student> selectByIds(@Param("ids") List<Integer> ids);

<select id="selectByIds" resultMap="StudentMap" lang="org.mybatis.scripting.freemarker.FreeMarkerLanguageDriver">
      SELECT * FROM student where id in(${ids?join(',')})
    </select>

查询

@Test
    public void language(){ 
      StudentMapper mapper = session.getMapper(StudentMapper.class);
      List<Student> code = mapper.selectByIds(Arrays.asList(1,2,3));
      System.out.println(code);
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200203133548559.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly94aWFtZW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70