mybatis第三天 动态SQL,配置文件,分页查询

148 阅读4分钟

这是我参与11月更文挑战的第7天,活动详情查看:2021最后一次更文挑战

动态SQL

概述

动态SQL是MYbatis的强大功能之一, 动态SQL就是同一个方法根据不同的条件表示不同的SQL语句,查询不同的结果,主要是where的条件变化,使用mybatis提供的标签实现动态SQL的功能,主要有if,where ,foreach,sql等,

动态SQL 第一个if标签

语法

<if test="boolean判断结果">
    sql语句
</if>
<--在mapper中-->
<select id="findUserListVo" resultMap="UserVo">
    select * from user
    <if test="条件">
    sql 语句
    </if>
</select>

第一条Sql语句叫主sql,一旦if条件成立,就把if里的部分sql语句拼接到主sql上,形成一个完整的sql,if条件可以有多个,条件为真都会拼接到主sql上

接口

List<User> SelectUserIf(User user);

mapper

<select id="SelectUserIf" resultType="user">
    select * from user
        where 1=1
<if test="name !=null and name!='' ">
    and name = #{name}
</if>

    <if test="pwd !=null and name!='' ">
      or  pwd = #{pwd}
    </if>
</select>

测试类

@Test
public void test1(){
    //获取sqlsession
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    User user = new User();
    user.setName("李四");
    user.setPwd("12345");
    List<User> users =  mapper.SelectUserIf(user);
    System.out.println(users);
    session.close();
}

结果

image.png

为了看到Mybatis到底执行了什么,我们加上打印SQL语句的配置,在主配置文件李加上如下配置

  <settings>
        <!-- 打印查询语句 -->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>

第一个先看两个条件都成立

image.png

我们先把where 1=1 删除掉,然后值给name成立,语法不正确了,为了防止这个问题,我们一般加上where 1=1 避免这个错误,

image.png 再把第二个条件放开,name不要了,看一下输出语句,与预期的结果一样,我们就实现了动态的sql,还有一个注意点,动态sql的入参要是一个对象类型。

image.png 我们可以看出 if条件多用于多条件查询的情况下

在mapper的动态SQL中如果出现大于小于符号<>,特别是小于< 符号跟xml的开始标签重合,解析就会报错,所以就要使用实体符号来代替,表如下

image.png

动态SQL第二个 where标签

在使用if时,不加永真条件的话,拼接sql语句时会报错,引发sql语法错误,使用where标签就可以解决这个问题,在使用where标签时,里边是一个个或多个if标签,当if为true时,where标签会转变为where关键字加到sql语句的后边,如果没有真的条件则没有条件,

语法

<where>
    <if test="条件">部分sql</if>
    <if test="条件">部分sql</if>
    <if test="条件">部分sql</if>
</where>

接口

List<User> SelectUserwhere(User user);

mapper

这里两个条件都成立的话,sql语句应该长这个样子,select * from user where and name = #{name} or pwd = #{pwd},但实际运行结果却是第一个and不见了,Mybatis自动帮我们剔除掉了,第一个标签中 and可写可不写,写了Mybatis会帮我们剔除掉,但其余的if标签里部分sql前必须要写上,要不然就会报错


    <select id="SelectUserwhere" resultType="user">
        select * from user
<where>
    <if test="name !=null and name!='' "> and name = #{name}</if>
    <if test="pwd !=null and name!='' ">  or  pwd = #{pwd}</if>
</where>
    </select>

image.png

我们不给name值看一下执行结果,where依然帮我们删除掉了or标签,语句结果也是对的

image.png

动态SQL第三个foreach标签

foreach用来实现对传入的集合的循环取值使用,一般使用在in里边

语法

<foreach collection="集合类型" open="开始" close="结束" item="集合一个值" separator="分隔符" index="序号或者key">
    #{item}
</foreach>
List简单类型

接口

List<User> SelectUserForeach(ArrayList<Integer> user);

mapper

<select id="SelectUserForeach" resultType="user">
    select  * from user 
    <if test="list != null and list.size > 0">
            where id in
        <foreach collection="list" separator="," open="(" close=")" item="id">
            #{id}
        </foreach>
    </if>
</select>

测试类

@Test
public void testForeach(){
    //获取sqlsession
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    ArrayList<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    List<User> users =  mapper.SelectUserForeach(ids);
    System.out.println(users);
    session.close();
}
总结

我们在写foreach标签的时候必须搭配if where一起使用,上边的例子,如果没有if标签,当list喂null时或者为空我们的语法就有错误(select * from user where id in),所以要灵活应用,根据标签来拼接语句。

List对象类型

接口

List<User> SelectUserForeach1(ArrayList<User> user);

mapper

<select id="SelectUserForeach1" resultType="user">
    select  * from user
    <if test="list != null and list.size > 0">
       where  id in
        <foreach collection="list" separator="," open="(" close=")" item="user">
            #{user.id}
        </foreach>
    </if>
</select>

测试类

@Test
public void testForeach1(){
    //获取sqlsession
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    ArrayList<User> users = new ArrayList<>();
    User user = new User();
    user.setId(3);
    User user1 = new User();
    user1.setId(4);
    users.add(user1);
    users.add(user);
    List<User> userList =  mapper.SelectUserForeach1(users);
    System.out.println(users);
    session.close();
}

结果

image.png

动态SQL第四个 SQL标签

标签用于定义 SQL 片断,以便其它 SQL 标签复用。而其它标签使用该 SQL 片断,需要使用子标签。该标签可以定义 SQL 语句中的任何部分,所以子标签可以放在动态 SQL的任何位置

我们把刚才的代码使用SQL标签来表示下

<sql id="SelectUser">
    select * from user
</sql>
<select id="SelectUserForeach1" resultType="user">
    <include refid="SelectUser"></include>
    <if test="list != null and list.size > 0">
       where  id in
        <foreach collection="list" separator="," open="(" close=")" item="user">
            #{user.id}
        </foreach>
    </if>
</select>

查询结果跟之前是一样的,实现了代码的复用

配置文件

Mybatis的配置文件有两种,主配置文件,还有各个mapper

  1. 主配置文件,提供mybatis全局设置的,日志,数据源,各个mapper文件
  2. mapper文件,对应写sql语句的

settings部分

全局设置,可以不配置使用默认的,我们刚才的sql语句打印就是在settings里开启的

image.png 配置如下

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

类型别名(typeAliases)

这个我们上边讲过了,一种配置一个的,一个配置多个的

环境配置(environments)

可以配置多个,数据库的连接信息

  • 默认使用的环境 ID(比如:default="development")。
  • 每个 environment 元素定义的环境 ID(比如:id="development")。
  • 事务管理器的配置(比如:type="JDBC")。
  • 数据源的配置(比如:type="POOLED")。
<environments default="development">
  <environment id="development">
    <transactionManager type="JDBC">
      <property name="..." value="..."/>
    </transactionManager>
    <dataSource type="POOLED">
      <property name="driver" value="${driver}"/>
      <property name="url" value="${url}"/>
      <property name="username" value="${username}"/>
      <property name="password" value="${password}"/>
    </dataSource>
  </environment>
</environments>

类型处理器(typeHandlers)

分页查询PageHelper

PageHelper做数据分页,在select语句加上分页的sql内容 使用步骤

  1. 加入依赖
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.10</version>
</dependency>
  1. 加入 plugin 配置 在之前加入
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor" />
</plugins>

使用查询所有

@Test
public void testAll(){
    //获取sqlsession
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    PageHelper.startPage(1,1);
    List<User> userList =  mapper.SelectAll();
    System.out.println(userList);
    session.close();
}

控制台输出

image.png