Mybatis动态Sql的作用

逃离我推掉我的手 2024-03-24 20:08 74阅读 0赞

**目录**

一、mybatis动态SQL

1、if

2、foreach

3、choose、when、otherwise

二、mybatis模糊查询

1、mybatis模糊查询的三种方式

2、mybatis中\#与$的区别

一, 分页

一、PageHelper介绍

PageHelper插件的优点：

二、PageHelper使用

1. 导入pom依赖

2. Mybatis.cfg.xml 配置拦截器

模拟测试

三、特殊字符处理

1. 使用转义字符

2. 使用CDATA 区段

--------------------

## 一、mybatis动态SQL ##

>     update 表名 set name=?,age=? where id=?
> 
> 如果我们的前台没有传参，比如没有传入我们的name值，name就会把字段值改为null，这就违背了我们编码的初衷。
> 
> 许多人会使用类似于`where 1 = 1` 来作为前缀，在代码中会用**if判断是否为null**，再用and进行一个sql拼接。
> 
> 我们可以用常用的几个标签：

### 1、if ###

> 再if标签里面，test里面的条件满足了，才会把后面的sql语句进行拼接

<select id="updateByPrimaryKeySelective" parameterType="com.xzs.model.Book">
            SELECT * FROM Book WHERE price>= 9.9
            <if test="name != null and name != ''">
                AND name like '%${name}%'
            </if>
    </select>

### 2、foreach ###

> 我们再调用删除的方法的时候，通常会与SQL语句中的IN查询条件结合使用。
> 
>     delete from student where id in（）
> 
>   
> parameterType为List（链表）或者Array（数组），后面在引用时，参数名必须为list或者array。如在foreach标签中，collection属性则为需要迭代的集合，由于入参是个List，所以参数名必须为list。
> 
>     <select id="selectById" resultType="com.xzs.model.Book" parameterType="java.util.List" >
>           select
>           <include refid="Base_Column_List"/>
>           from t_mvc_book
>           where bid in
>           <foreach collection="bids" item="bid" open="(" separator="," close=")">
>               #{bid}
>           </foreach>
>       </select>
> 
> 测试一下结果
> 
> ![d8ab4d1a68414b21aa07f91e2143d9ad.png][]

### 3、choose、when、otherwise ###

> 你可以使用**choose**、**when**和**otherwise**元素来实现类似于switch语句的逻辑
> 
>     <select id="getUser" parameterType="int" resultType="User">
>       SELECT * FROM users
>       WHERE 1=1
>       <choose>
>         <when test="id != null">
>           AND id = #{id}
>         </when>
>         <when test="name != null">
>           AND name = #{name}
>         </when>
>         <otherwise>
>           AND status = 'ACTIVE'
>         </otherwise>
>       </choose>
>     </select>

> **如果传入的id不为空，则会在SQL语句中包含`AND id = #{id}`这个条件；如果传入的name不为空，则会在SQL语句中包含`AND name = #{name}`这个条件；否则，会在SQL语句中包含`AND status = 'active'`这个条件。**

## 二、mybatis模糊查询 ##

> 我们写入一个xml文件，**log4j2.xml 用于打印出来你的sql语句**
> 
>     <?xml version="1.0" encoding="UTF-8"?>
>      
>     
>     <Configuration status="WARN" monitorInterval="30">
>         <Properties>
>             
>             <Property name="LOG_HOME">/root/workspace/lucenedemo/logs</Property>
>             <property name="ERROR_LOG_FILE_NAME">/root/workspace/lucenedemo/logs/error</property>
>             <property name="WARN_LOG_FILE_NAME">/root/workspace/lucenedemo/logs/warn</property>
>             <property name="PATTERN">%d{yyyy-MM-dd HH:mm:ss.SSS} [%t-%L] %-5level %logger{36} - %msg%n</property>
>         </Properties>
>      
>         <Appenders>
>             
>             <Console name="Console" target="SYSTEM_OUT">
>                 
>                 <ThresholdFilter level="trace" onMatch="ACCEPT"
>                                  onMismatch="DENY" />
>                 
>                 
>                 <PatternLayout pattern="${PATTERN}" />
>             </Console>
>      
>             
>             
>             <File name="log" fileName="logs/test.log" append="false">
>                 <PatternLayout
>                         pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
>             </File>
>             
>             <RollingFile name="RollingFileInfo" fileName="${LOG_HOME}/info.log"
>                          filePattern="${LOG_HOME}/$${date:yyyy-MM}/info-%d{yyyy-MM-dd}-%i.log">
>                 
>                 <ThresholdFilter level="info" onMatch="ACCEPT"
>                                  onMismatch="DENY" />
>                 <PatternLayout
>                         pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
>                 <Policies>
>                     
>                     
>                     
>                     <TimeBasedTriggeringPolicy interval="1"
>                                                modulate="true" />
>                     
>                     
>                 </Policies>
>             </RollingFile>
>      
>             <RollingFile name="RollingFileWarn" fileName="${WARN_LOG_FILE_NAME}/warn.log"
>                          filePattern="${WARN_LOG_FILE_NAME}/$${date:yyyy-MM}/warn-%d{yyyy-MM-dd}-%i.log">
>                 <ThresholdFilter level="warn" onMatch="ACCEPT"
>                                  onMismatch="DENY" />
>                 <PatternLayout
>                         pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
>                 <Policies>
>                     <TimeBasedTriggeringPolicy />
>                     <SizeBasedTriggeringPolicy size="2 kB" />
>                 </Policies>
>                 
>                 <DefaultRolloverStrategy max="20" />
>             </RollingFile>
>      
>             <RollingFile name="RollingFileError" fileName="${ERROR_LOG_FILE_NAME}/error.log"
>                          filePattern="${ERROR_LOG_FILE_NAME}/$${date:yyyy-MM}/error-%d{yyyy-MM-dd-HH-mm}-%i.log">
>                 <ThresholdFilter level="error" onMatch="ACCEPT"
>                                  onMismatch="DENY" />
>                 <PatternLayout
>                         pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
>                 <Policies>
>                     
>                     <TimeBasedTriggeringPolicy interval="1"
>                                                modulate="true" />
>                     
>                 </Policies>
>             </RollingFile>
>      
>         </Appenders>
>      
>         
>         <Loggers>
>             
>             <logger name="org.springframework" level="INFO"></logger>
>             <logger name="org.mybatis" level="INFO"></logger>
>      
>             
>             <logger name="org.springframework" level="ERROR" />
>             <logger name="org.hibernate" level="ERROR" />
>             <logger name="org.apache.struts2" level="ERROR" />
>             <logger name="com.opensymphony.xwork2" level="ERROR" />
>             <logger name="org.jboss" level="ERROR" />
>      
>      
>             
>             <root level="all">
>                 <appender-ref ref="Console" />
>                 <appender-ref ref="RollingFileInfo" />
>                 <appender-ref ref="RollingFileWarn" />
>                 <appender-ref ref="RollingFileError" />
>             </root>
>      
>         </Loggers>
>      
>     </Configuration>
> 
> ![ba1c2fc73fa04a85ac4c3b12c3b0297a.png][]

### 1、mybatis模糊查询的三种方式 ###

1.第一种

<select id="like01" resultType="com.xzs.model.Book" parameterType="java.util.List">
            select
            <include refid="Base_Column_List"/>
            from t_mvc_book
            where bname like #{bname}
        </select>

测试的结果

@Test
        public void like01() {
            bookbiz.like01("%圣%").forEach(System.out::println);
        }

![ba1c2fc73fa04a85ac4c3b12c3b0297a.png][]

第二种：$需要单引号，$传参是占位符的形式

<select id="like02" resultType="com.xzs.model.Book" parameterType="java.util.List">
            select
            <include refid="Base_Column_List"/>
            from t_mvc_book
            where bname like '${bname}'
        </select>

测试的结果

@Test
        public void like02() {
            bookbiz.like02("%圣%").forEach(System.out::println);
        }

![09e0073189ae441dbb9cda5cae606485.png][]

第三种

<select id="like03" resultType="com.xzs.model.Book" parameterType="java.util.List">
            select
            <include refid="Base_Column_List"/>
            from t_mvc_book
            where bname like concat('%',#{bname},'%')
        </select>

测试的结果

@Test
        public void like03() {
            bookbiz.like03("%圣%").forEach(System.out::println);
        }

![957d6cfc663447fcb9809ced55d745a4.png][]

### 2、mybatis中\#与$的区别 ###

> 1.  $是占位符赋值，\#是预处理SQL。
> 2.  外在形式，$传参不带引号''，**\#**传参自带引号''。
> 3.  $传参存在sql注入，\#不存在。
> 4.  $可以用来做动态列，完成动态sql的开发。

三、MyBatis结果映射（单表多表查询）

>     <resultMap id="BaseResultMap" type="com.xzs.model.Book">
>             <constructor>
>                 <idArg column="bid" jdbcType="INTEGER" javaType="java.lang.Integer"/>
>                 <arg column="bname" jdbcType="VARCHAR" javaType="java.lang.String"/>
>                 <arg column="price" jdbcType="REAL" javaType="java.lang.Float"/>
>             </constructor>
>         </resultMap>
> 
> 这个代码中，column属性写的是**数据库里面的字段**，里面有一个name的属性，是**实体类里面的字段**，当数据库里面的字段喝实体类里面的字段是一样的可以不用写name属性；
> 
> 如果数据库里面出现了下划线，比如**data\_time**，就需要在
> 
>     <arg column="data_time",name="DataTime" jdbcType="VARCHAR" javaType="java.lang.String"/>

**使用mybatis的各种场景**

> **使用mybatis的各种场景，返回的结果是多样的，resultType/resultMap？**
> 
> 1.  返回单表的对应的实体类，仅有一个查询结果，可以用resultType/resultMap。
> 2.  使用了resultType
>     
>         <select id="selectresultType" resultType="com.xzs.model.Book" parameterType="java.lang.Integer">
>                 select
>                 <include refid="Base_Column_List"/>
>                 from t_mvc_book
>                 where bid = #{bid,jdbcType=INTEGER}
>             </select>
>     
>     2.编写接口实现接口  
>     
>     
>         Book selectresultType(Integer bid);
>     
>         @Override
>             public Book selectresultType(Integer bid) {
>                 return bookMapper.selectresultType(bid);
>             }
>     
>     3.测试方法  
>     
>     
>         @Test
>             public void selectresultType() {
>                 System.out.println("测试resultType查询");
>                 Book book = bookbiz.selectByPrimaryKey(12);
>                 System.out.println(book);
>             }
>     
>     4.测试结果  
>       
>     
>     
>     ![d96e763fe446482f8b95772dc3e1ef71.png][]
>     
>     2. 返回单表的对应的实体类，有多个查询结果，可以用resultType/resultMap  
>     1.resultType/resultMap。resultType一个或多个都用实体对象  
>     
>     
>         <select id="selectresultType01" resultType="com.xzs.model.Book">
>                 select
>                 <include refid="Base_Column_List"/>
>                 from t_mvc_book
>             </select>
>          
>             <select id="selectresultMap01" resultMap="BaseResultMap">
>                 select
>                 <include refid="Base_Column_List"/>
>                 from t_mvc_book
>             </select>
>     
>     2.编写接口方法和实现接口  
>     
>     
>         List<Book> selectresultType01();
>          
>             List<Book> selectresultMap01();
>     
>         @Override
>             public List<Book> selectresultType01() {
>                 return bookMapper.selectresultType01();
>             }
>          
>             @Override
>             public List<Book> selectresultMap01() {
>                 return bookMapper.selectresultMap01();
>             }
>     
>     3.测试方法  
>     
>     
>         @Test
>             public void selectresultType01() {
>                 bookbiz.selectresultType01().forEach(System.out::println);
>             }
>          
>             @Test
>             public void selectresultMap01() {
>                 bookbiz.selectresultMap01().forEach(System.out::println);
>             }
>     
>     4.测试结果是一样的![f669b07efe53410b8c172e140b674a83.png][]
>     
>     3.返回多表的对应的实体类，仅有一个查询结果，通常用resultType，也可以用resultMap  
>     4.返回多表的对应的实体类，有多个查询结果，通常用resultType，也可以用resultMap  
>     1.编写配置，我们可以不用配置的表，可以调用其他的表|  
>     
>     
>         <select id="select01" resultType="java.util.Map" parameterType="java.util.Map">
>                 select s.*, sc.cid, sc.score
>                 from t_mysql_student s,
>                      t_mysql_score sc
>                 where s.sid = sc.sid
>                   and sc.sid = #{sid}
>                   and sc.cid = #{cid}
>             </select>
>          
>             <select id="select02" resultType="java.util.Map" parameterType="java.util.Map">
>                 select s.*, sc.cid, sc.score
>                 from t_mysql_student s,
>                      t_mysql_score sc
>                 where s.sid = sc.sid
>             </select>
>     
>     2.编写接口和实现类  
>     
>     
>         Map select01(Map map);
>          
>             List<Map> select02(Map map);
>     
>         @Override
>             public Map select01(Map map) {
>                 return bookMapper.select01(map);
>             }
>          
>             @Override
>             public List<Map> select02(Map map) {
>                 return bookMapper.select02(map);
>             }
>     
>     3.测试方法  
>     
>     
>         @Test
>             public void select01() {
>                 Map map = new HashMap();
>                 map.put("sid", "01");
>                 map.put("cid", "01");
>                 System.out.println(bookbiz.select01(map));
>             }
>          
>             @Test
>             public void select02() {
>                 Map map = new HashMap();
>                 map.put("sid", "01");
>                 map.put("cid", "01");
>                 bookbiz.select02(map).forEach(System.out::println);
>             }
>     
>     4.测试结果![248baac4fde24c0d9a0cd21beab19dbe.png][]![b3896eec37d042f2b124374042e0b42a.png][]
>     
>     5.返回单个列表，仅有一个查询结果，就用resultType  
>     6.返回单个列表，有多个查询结果，就用resultType。  
>     1.配置xml  
>     
>     
>         
>             <select id="selectByString01" resultType="java.lang.String" parameterType="java.lang.Integer">
>                 select bname
>                 from t_mvc_book
>                 where bid = #{bid,jdbcType=INTEGER}
>             </select>
>             
>             <select id="selectByString02" resultType="java.lang.String" parameterType="java.lang.String">
>                 select bname
>                 from t_mvc_book
>                 where bname like concat('%', #{bname}, '%')
>             </select>
>     
>     2.编写接口和实现接口  
>     
>     
>         String selectByString01(Integer bid);
>          
>             List<String> selectByString02(String bname);
>     
>         @Override
>             public String selectByString01(Integer bid) {
>                 return bookMapper.selectByString01(bid);
>             }
>          
>             @Override
>             public List<String> selectByString02(String bname) {
>                 return bookMapper.selectByString02(bname);
>             }
>     
>     3.测试方法  
>     
>     
>         @Test
>             public void selectByString01() {
>                 System.out.println(bookbiz.selectByString01(60));
>             }
>          
>             @Test
>             public void selectByString02() {
>                 Map map = new HashMap();
>                 map.put("sid", "01");
>                 map.put("cid", "01");
>                 bookbiz.selectByString02("圣墟").forEach(System.out::println);
>             }
>     
>     4.测试结果![a4ae24db98c9452aa8e959d0ab88698a.png][]
>     
>     ![82803b8a0b14482abbe39cec70fb1a08.png][]

> **resultType：**对应返回类型。
> 
> **resultMap：**对应返回映射关系，指的是实体类与数据表字段关系。
> 
> 单表查询，返回单列，返回多表查询结果，是用resultType
> 
> 查询的结果，需要有关属性的体现，那么就用resultMap

## 一, 分页 ##

### 一、PageHelper介绍 ###

PageHelper 是 Mybatis 的一个插件，这里就不扯了，就是为了更加便捷的进行[分页查询][Link 1]。

**官方网址：[MyBatis 分页插件 PageHelper][MyBatis _ PageHelper]**

#### PageHelper插件的优点： ####

物理分页：支持常见的 12 种数据库Oracle,MySql,MariaDB,SQLite,DB2,PostgreSQL,SqlServer 等...  
支持多种分页方式：支持常见的RowBounds(PageRowBounds)，PageHelper.startPage 方法调用，Mapper 接口参数调用。  
[QueryInterceptor 规范][QueryInterceptor] ：使用 QueryInterceptor 规范，开发插件更轻松。

### 二、PageHelper使用 ###

#### 1. 导入pom依赖 ####

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

#### 2. Mybatis.cfg.xml 配置拦截器 ####

> **必须配置在<environments>上面，不然会报错。**
> 
> 正确实例：
> 
> ![818fa65e4082436b9a88da4becda9508.png][]

#### 模拟测试 ####

mayBatis.xml配置

<select id="like4" resultType="com.xzs.model.Book"  parameterType="java.util.List">
        select
        <include refid="Base_Column_List" />
        from t_mvc_book
        where bname like Concat ('%',#{bname},'%')
      </select>

接口

List<Book> like4(String bname, PageBean pageBean);

实现接口

public List<Book> like4(String bname, PageBean pageBean) {
            if (pageBean!=null&&pageBean.isPagination()){
                PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
            }
            List<Book> books = bookmapper.like4(bname);
            if (pageBean!=null&&pageBean.isPagination()){
                PageInfo<Book> bookPageInfo = new PageInfo<>(books);
                System.out.println("当前页" + bookPageInfo.getPageNum());
                System.out.println("展示记录数：" + bookPageInfo.getPageSize());
                System.out.println("符合查询条件的总记录数" + bookPageInfo.getTotal());
                pageBean.setTotal((int) bookPageInfo.getTotal());
            }
            return books ;
        }

测试类：

public void like4() {
            PageBean pageBean = new PageBean();
            pageBean.setPage(2);
            pageBean.setRows(10);
            BookBiz.like4("%圣%",pageBean).forEach(System.out::println);
        }

结果：

![9a87864ec5314e1b9aced7c738456638.png][]

### 三、特殊字符处理 ###

众所周知 在mybatis中我们经常用到特殊字符动态拼接语句，如经常使用到 大于(>,>=)、小于(<,<=)、不等于(<> ,!=)符号。Mybatis使用的 \*.xml文件格式，需要在尖括号进行相关的转义或者使用 CDATA 区段。

#### 1. 使用转义字符 ####

列如：

> <select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">  
> select \* from t\_mvc\_book where price < 9 and price > 20  
> </select>

### 2. 使用CDATA 区段 ###

<!\[CDATA\[ \]\]>是xml语法，在<!\[CDATA\[ \]\]>内部的所有内容都会被解析器忽略，不进行转义。所以在xml中这是一种通用方案。

<table> 
 <thead> 
  <tr> 
   <th>特殊字符</th> 
   <th>&lt;![CDATA[ ]]&gt;</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&lt;</td> 
   <td>&lt;![CDATA[&lt;]]&gt;</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>&lt;![CDATA[&gt;]]&gt;</td> 
  </tr> 
  <tr> 
   <td>&amp;</td> 
   <td>&lt;![CDATA[&amp;]]&gt;</td> 
  </tr> 
  <tr> 
   <td>"</td> 
   <td>&lt;![CDATA["]]&gt;</td> 
  </tr> 
  <tr> 
   <td>’</td> 
   <td>&lt;![CDATA[']]&gt;</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>&lt;![CDATA[&lt;=]]&gt;</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>&lt;![CDATA[&gt;=]]&gt;</td> 
  </tr> 
  <tr> 
   <td>!=</td> 
   <td>&lt;![CDATA[!=]]&gt;</td> 
  </tr> 
 </tbody> 
</table>

例如：

> <select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">  
> select \* from t\_mvc\_book where <!\[CDATA\[ price > \#\{price\} and \#\{price\} != 0 \]\]>  
> </select>

[d8ab4d1a68414b21aa07f91e2143d9ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/0a5eeb8b6e0e4a07a12c847f039b4ac4.png
[ba1c2fc73fa04a85ac4c3b12c3b0297a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/40195a69de0a48b8bca76d3bb82a0ec5.png
[09e0073189ae441dbb9cda5cae606485.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/61a98c26e5bf4554829a3b402807c888.png
[957d6cfc663447fcb9809ced55d745a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/ec456cdd00ff4e92a575097e0147385f.png
[d96e763fe446482f8b95772dc3e1ef71.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/dccdcfa74fde41c3b27ab0a7fafffb02.png
[f669b07efe53410b8c172e140b674a83.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/9db926984e0a491b9ccfafc66366cd3e.png
[248baac4fde24c0d9a0cd21beab19dbe.png]: https://img-blog.csdnimg.cn/248baac4fde24c0d9a0cd21beab19dbe.png
[b3896eec37d042f2b124374042e0b42a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b845ec991c1142f58a5e96f6c3b32c54.png
[a4ae24db98c9452aa8e959d0ab88698a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b923216c652240d79f4c618ece2d0d79.png
[82803b8a0b14482abbe39cec70fb1a08.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/70c1cf55b6404f35b9924fdea6a6092f.png
[Link 1]: http://t.csdn.cn/7oHMe
[MyBatis _ PageHelper]: https://pagehelper.github.io/
[QueryInterceptor]: https://pagehelper.github.io/docs/interceptor/
[818fa65e4082436b9a88da4becda9508.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/5a2915d51a8748bead2fee1f6d3b5220.png
[9a87864ec5314e1b9aced7c738456638.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/9266fb1a87b843b9ab09112c3df65ceb.png