2021 June 02 Java后端开发

SSM-MyBatis(二)-动态SQL、druid连接池、配置文件、分页查询

动态SQL

  1. 什么是动态SQL？
    1. 多条件查询，当满足n个条件时，查询数据库

if标签

  1. 通常用来做判断，判断根据判断是否添加某段sql语句

     //skill.xml
 <select id="dynamicSQL" parameterType="Map" resultType="com.zh.bean.Skill">
     SELECT * FROM skill WHERE 1 = 1
     <if test="id != null">
         AND id > #{id}
     </if>
     <if test="name != null">
         AND name LIKE #{name}
     </if>
     <if test="level != null">
         AND level &lt; #{level}
     </if>
 </select>

  2. SkillTest测试代码

     public void dynamicSQL() throws Exception {
     try (SqlSession session = MyBatises.openSession()) {
         Map<String, Object> param = new HashMap<>();
         param.put("id", 3);
         param.put("name", "%s%");
         param.put("level", 900);
         /*
         SELECT * FROM skill WHERE 1 = 1
         AND id > #{id}
         AND name LIKE #{name}
         AND level < #{level}
          */
         List<Skill> skills = session.selectList("skill.dynamicSQL2", param);
         for (Skill skill : skills) {
             System.out.println(skill);
         }
     }
 }

  3. 更多标签，可以参考：https://mybatis.org/mybatis-3/zh/dynamic-sql.html

where标签

//skill.xml代码
<select id="dynamicSQL2" parameterType="Map" resultType="com.zh.bean.Skill">
    SELECT * FROM skill
    <where>
        <if test="id != null">
            AND id > #{id}
        </if>
        <if test="name != null">
            AND name LIKE #{name}
        </if>
        <if test="level != null">
            AND level &lt; #{level}
        </if>
    </where>
</select>
  1. 与上面相比，不用在WHERE后面添加1=1了,之前添加目的是因为后序拼接的语句会多余一个AND
  2. 如果用了where会自动删除多余的AND

批量处理

1.批量添加

  1. skill.xml

     <insert id="batchInsert"
         useGeneratedKeys="true"
         keyProperty="id"
         parameterType="List">
     INSERT INTO skill(name, level) VALUES
     <!-- 要遍历的集合collection、skill：集合中的元素、separator之间用什么分割-->
     <foreach collection="list" item="skill" separator=",">
         (#{skill.name}, #{skill.level})
     </foreach>
 </insert>

  2. SkillTest测试代码

     @Test
 public void batchInsert() throws Exception {
     try (SqlSession session = MyBatises.openSession()) {
         List<Skill> skills = new ArrayList<>();
         skills.add(new Skill("Java1", 111));
         skills.add(new Skill("Java2", 222));
         skills.add(new Skill("Java3", 333));
         skills.add(new Skill("Java4", 444));
         session.insert("skill.batchInsert", skills);
         //获取插入的所有id
         for (Skill skill : skills) {
             System.out.println(skill.getId());
         }
         session.commit();
     }
 }

2.批量删除

  1. skill.xml（2种写法）

     <delete id="batchDelete" parameterType="List">
     DELETE FROM skill WHERE id IN (
     <foreach collection="list" item="id" separator=",">
         #{id}
     </foreach>
     )
 </delete>

 <delete id="batchDelete2" parameterType="List">
     DELETE FROM skill WHERE id IN
     <foreach collection="array"
              item="id"
              open="("
              close=")"
              separator=",">
         #{id}
     </foreach>
 </delete>

  2. SkillTest测试代码

     @Test
 public void batchDelete() throws Exception {
     try (SqlSession session = MyBatises.openSession()) {
            
         //传参为List，collection需要设置为list
         // List<Integer> ids = new ArrayList<>();
         // ids.add(23);
         // ids.add(24);
         // ids.add(25);
         // ids.add(26);
            
         //传参为数组，collection需要设置为array
         Integer[] ids = {7, 8, 9};
         session.insert("skill.batchDelete2", ids);
         session.commit();
     }
 }

3.注意事项

  1. 批量添加的效率比【多次单个添加】要高，但是它无法使用<selectedKey>获取新插入记录的主键
    1. 但可以使用useGeneratedKeys获取主键
    2. useGeneratedKeys：代表使用自动生成的主键
    3. keyProperty：告诉主键属性对应的bean对象的那个属性名称
  2. 批量操作生成的SQL语句可能会比较长，有可能会超过数据库的限制
  3. 如果传进来的参数是List,collection属性值为list就可以遍历这个List
  4. 如果传进来的参数是数组，collection属性值为array就可以遍历这个数组

其他标签

typeAliases

  1. 用于设置类型的别名（不区分大小写）
  2. 添加到mybatis-config.xml的configuration标签中
    1. 注意添加在settings标签与environments标签之间
     <!-- 别名 -->
 <typeAliases>
     <!-- 一旦设置了别名，它是不区分大小写的 -->
     <!-- 给这个类com.zh.bean.Skill设置一个别名，以后所有的bean映射文件中所有出现这个类的地方都可以换成skill-->
     <typeAlias type="com.zh.bean.Skill" alias="skill" />
     <!-- 这个包下的所有类，都会起一个别名：全类名的最后一个单词
         这么一设置，等于给com.zh.bean包下的所有类都设置了一个别名，别名名称为最后一个单词
         比如com.zh.bean.Skill的别名就是Skill
      -->
     <package name="com.zh.bean" />
 </typeAliases>

  3. 那么之前skill.xml的查询语句可以这么写

     //resultType不用再写com.zh.bean.Skill，直接写skill即可，SKILL/SkiLL...都可以
 <select id="list" resultType="skill">
     SELECT * FROM skill
 </select>
  4. 参考：https://mybatis.org/mybatis-3/zh/configuration.html#typeAliases
  5. 注意： 那么我们可以猜测mybatis-config.xml文件中所有type的值看起来不像类的应该都是别名，比如POOLED、JDBC等，只是mybatis自己内部定义的别名

sql标签

  1. 添加到mapper标签中，用于抽取公共的SQL语句
  2. 比如skill.xml中有的SQL语句很多这条语SELECT * FROM skill

  3. 那么抽取如下

     <sql id="sqlListAll">
     SELECT * FROM skill
 </sql>

  4. 其他的语句如下

     <select id="list2" parameterType="com.zh.bean.Skill" resultType="com.zh.bean.Skill">
     <include refid="sqlListAll" /> WHERE id > #{id} OR level > #{level} OR name LIKE #{name}
 </select>

集成druid连接池

  1. mybatis-config.xml数据库配置如下

     <environment id="production">
     <!--使用JDBC来管理事务-->
     <transactionManager type="JDBC"/>
     <!--POOLED 采用连接池的方式管理数据库连接 -->
     <dataSource type="POOLED">
         <property name="driver" value="com.mysql.jdbc.Driver"/>
         <property name="url" value="jdbc:mysql://localhost:3306/test-mybatis"/>
         <property name="username" value="root"/>
         <property name="password" value="root"/>
     </dataSource>
 </environment>
    1. 只要将dataSource的type的值设置为POOLED，那么就使用了mybatis内置的连接池
    2. 也可以通过property设置内置连接池的常用属性

  2. 但是通常不使用mybatis内置的连接池，通常使用其他第三方封装的连接池更好用，那么如何使用呢？比如比较流行的连接池druid

使用步骤

  1. 在pom.xml添加maven依赖导入druid

     <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.2.3</version>
 </dependency>

  2. 新建一个继承UnpooledDataSourceFactory的连接池类(com.zh.common.DruidDataSourceFactory)，在构造方法中设置数据源

     package com.zh.common;
 import com.alibaba.druid.pool.DruidDataSource;
 import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;
 public class DruidDataSourceFactory extends UnpooledDataSourceFactory {
     public DruidDataSourceFactory() {
         this.dataSource = new DruidDataSource();
     }
 }
    1. mybatis要求第三方如果想要替换自己的连接池，必须实现UnpooledDataSourceFactory类，而且实现DruidDataSourceFactory构造方法，并返回第三方的连接池数据源方法

  3. 在environment标签中配置连接池的类名

     <environment id="development1">
     <!-- 采用JDBC的事务管理方法 -->
     <transactionManager type="jdbc" />
     <!-- type为设置其他的连接池对象 -->
     <!-- <dataSource type="com.zh.common.DruidDataSourceFactory"> -->
     <!-- 可以设置一个别名DRUID，取代com.zh.common.DruidDataSourceFactory-->
     <dataSource type="DRUID">
         <!-- 这里面的值跟之前JAVAEE项目中的druid.properties文件夹中的一样 -->
         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
         <property name="url" value="jdbc:mysql://localhost:3306/test_mybatis"/>
         <property name="username" value="root"/>
         <property name="password" value="root"/>
         <property name="initialSize" value="5"/>
         <property name="maxActive" value="10"/>
         <property name="maxWait" value="5000"/>
     </dataSource>
 </environment>

原理分析

  1. mybatis会根据dataSource标签设置的连接池类型，创建那个对象
  2. 然后去读取property标签设置的所有属性
  3. 然后将这些属性值设置给第一步创建的对象

数据库信息可配置化

  1. 上面的配置将数据库以及连接池的信息直接写死在environment标签中了，那么是否可跟之前一样可配置化呢？

  2. 在resources中导入druid.properties文件

     //测试
 dev.driverClass=com.mysql.jdbc.Driver
 dev.url=jdbc:mysql://localhost:3306/test-mybatis
 dev.username=root
 dev.password=root
 dev.initialSize=5
 dev.maxActive=10
 dev.maxWait=5000
    
 //生产
 pdr.driverClass=com.mysql.jdbc.Driver
 pdr.url=jdbc:mysql://localhost:3306/xr
 pdr.username=root
 pdr.password=root
 pdr.initialSize=5
 pdr.maxActive=10
 pdr.maxWait=500

  3. 在mybatis-config.xml的configuration标签中添加导入druid.properties文件

     <!-- 这样写就相当于把druid.properties文件中所有的变量导入-->
 <properties resource="druid.properties" />

  4. environments动态设置

     <!-- 开发环境（调试阶段） -->
 <environment id="development">
 <!-- 采用JDBC的事务管理方法 -->
 <transactionManager type="JDBC" />
 <!-- DRUID是别名 -->
 <dataSource type="DRUID">
     <!-- 值都是动态设置的 -->
     <property name="driverClass" value="${dev.driverClass}"/>
     <property name="url" value="${dev.url}"/>
     <property name="username" value="${dev.username}"/>
     <property name="password" value="${dev.password}"/>
     <property name="initialSize" value="${dev.initialSize}"/>
     <property name="maxActive" value="${dev.maxActive}"/>
     <property name="maxWait" value="${dev.maxWait}"/>
 </dataSource>

PageHelper-分页查询

  1. 数据库的分页查询
    1. 不同数据库的分页查询SQL可能是不一样的，比如：每页10条数据，查询第2页（第11-20条记录）
     #MySQL,编号从0开始，所以第二页从10开始
 SELECT * FROM student LIMIT 10,10;
    
 #SQL Server 微软提供
 SELECT * FROM student
 OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY；
    
 #Oracle
 SELECT * FROM
 (SELECT
     s.*, ROWNUM rn
     FROM
     (SELECT *FROM student) S
     WHERE ROWNUM <= 20
 )
 WHERE rn>= 11;
  2. 所以，在MyBatis中使用不同的数据库，就需要编写不同的分页查询SQL

PageHelper简介

  1. PageHelper是由国人开发的MyBatis分页插件，支持多种常用的数据库，可以极大简化分页的业务逻辑
  2. 源码：https://github.com/pagehelper/Mybatis-PageHelper
  3. 使用方法：https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md

使用步骤

  1. 环境配置

    1. 首先要增加Maven依赖

       <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>5.2.0</version>
 </dependency>

    2. 在mybatis-config.xml中配置插件

       <!--    注意plugins标签添加在typeAlias与environments之间-->
 <plugins>
     <plugin interceptor="com.github.pagehelper.PageInterceptor">
         <!-- true代表分页合理化： pageNum <= 0 就会自动获取第1页，如果pageNum>pages时就会自动获取最后1页 -->
         <property name="reasonable" value="true"/>
     </plugin>
 </plugins>

    3. reasonable设置true

      1. pageNum <= 0 时会查询第一页，pageNum > pages(超过总页数时),会查询最后一页
      2. pageNum：查询的页码
  2. 代码使用

    1. skill.xml中查询语句没有任何变化，PageHelper会自动拼接查询语句

       <select id="list" resultType="com.zh.bean.Skill">
     SELECT * FROM skill
 </select>

    2. SkillTest中测试代码如下：

       @Test
 public void page() throws Exception {
     try (SqlSession session = MyBatises.openSession()) {
         //第三页开始，每页5条数据，不需要管是什么类型数据库，也不用写分页查询SQL语句
         PageHelper.startPage(3, 5);
         List<Skill> skills = session.selectList("skill.list");
         for (Skill skill : skills) {
             System.out.println(skill);
         }
     }
 }

All content is licensed under CC BY-NC-SA

Buit with Jekyll and 3-Jekyll theme • Hosted on Github

Table of Contents