Mybatis动态sql和分页

俺叫牛爱芳

于 2022-04-19 14:16:26 发布

阅读量102

点赞数

文章标签： spring

本文链接：https://blog.csdn.net/l32273/article/details/124162901

版权

本文详细介绍了动态SQL的五个关键标签应用：If进行条件判断，trim处理SQL前后缀，foreach遍历集合，choose灵活选择条件，以及set和where的使用。同时涵盖了Mybatis参数传参技巧和分页、范围查询的实战案例。

摘要由CSDN通过智能技术生成

动态SQL

进行部分标签案例演示

1，If标签

<select id="queryBookIf" resultType="mybatis01.model.Book">
  select <include refid="Base_Column_List"/>
  from t_book where 1=1
  <if test="null!=bookType and ''!=bookType"> <!--book_type 为数据库字段名-->
    and book_type=#{bookType} <!--bookType为实体类属性名-->
 
  </if>
  </select>

这里where1=1使条件永远成立,text里面进行条件判断

2，trim标签

prefix	前缀
suffix	后缀
suffixOverride	去除后缀指定的字符
prefixOverrides	去除前缀指定的字符

<insert id="insertSelective" parameterType="mybatis01.model.Book" >
    insert into t_book
    
    <!--prefix 在sql语句前拼一个(
        insert into t_book (
    -->
    <!--suffix在sql语句后拼一个(
         insert into t_book ()
    -->
    <!-- suffixOverrides 去除最后一个逗号 
            insert into t_book (book_id,book_name,)
            取消这sql语句中最后一个逗号
     -->
    
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="bookId != null" >
        book_id,
      </if>
      <if test="bookName != null" >
        book_name,
      </if>
      <if test="bookNamePinyin != null" >
        book_name_pinyin,
      </if>
      <if test="bookPrice != null" >
        book_price,
      </if>
      <if test="bookType != null" >
        book_type,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="bookId != null" >
        #{bookId,jdbcType=INTEGER},
      </if>
      <if test="bookName != null" >
        #{bookName,jdbcType=VARCHAR},
      </if>
      <if test="bookNamePinyin != null" >
        #{bookNamePinyin,jdbcType=VARCHAR},
      </if>
      <if test="bookPrice != null" >
        #{bookPrice,jdbcType=REAL},
      </if>
      <if test="bookType != null" >
        #{bookType,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

判断实体类的属性是否为空,为空则不给予添加此对应的字段

3，foreach标签

<select id="queryBookByForeach" resultType="mybatis01.model.Book">
    select <include refid="Base_Column_List"/> from t_book where 1=1 and book_id in(
    <!--循环遍历此ids集合
        迭代获取的元素名称id自己定义
    -->
    
    <foreach collection="ids" item="id" separator=",">
      #{id}
    </foreach>
    )
  </select>

4，choose标签

<select id="queryBookChoose" resultType="mybatis01.model.Book">
    select <include refid="Base_Column_List"/> from t_book where 1=1
  <choose>
    <when test="bookName!=null and ''!=bookName">
      and  book_name like concat('%',#{bookName},'%')
    </when>
    <when test="bookName!=null and ''!=bookName">
      and  book_type like concat('%',#{bookType},'%')
    </when>
    <otherwise>
      and  bookId =#{bookId}
    </otherwise>
  </choose>
</select>

5、set

<update id="updateByPrimaryKeySelective" parameterType="mybatis01.model.Book" >
    update t_book
    <set >
      <if test="bookName != null" >
        book_name = #{bookName,jdbcType=VARCHAR},
      </if>
      <if test="bookNamePinyin != null" >
        book_name_pinyin = #{bookNamePinyin,jdbcType=VARCHAR},
      </if>
      <if test="bookPrice != null" >
        book_price = #{bookPrice,jdbcType=REAL},
      </if>
      <if test="bookType != null" >
        book_type = #{bookType,jdbcType=VARCHAR},
      </if>
    </set>
    where book_id = #{bookId,jdbcType=INTEGER}
  </update>

6，where

1) 如果没有条件, 不会生成 where 关键字
2) 如果有条件, 会自动添加 where 关键字
3) 如果第一个条件中有 and, 自动去除之

<select id="queryBookWhere" resultType="mybatis01.model.Book">
    select <include refid="Base_Column_List"/> from t_book
    <where>
      <if test="bookType!=null and bookType!=''">
        and book_Type=#{bookType}
      </if>
      <if test="bookName!=null and bookName!=''">
        and book_name=#{bookName}
      </if>
    </where>
  </select>

Mybatis传参

1，八大基础类型传参

<select id="queryBookId" resultType="mybatis01.model.Book">

select <include refid="Base_Column_List"/> from t_book where 1=1

<if test="null!=value and ''!=value">

         and book_id=#{undefinedvalue}

</if>

</select>

2，String类型传参

<select id="queryBookType" resultType="mybatis01.model.Book">
  select <include refid="Base_Column_List"/> from t_book where 1=1
  <if test="null!=bType and ''!=bType">
    and book_type=#{bType}
  </if>
</select>

3，传递对象或者集合

<!--对象-->

<select id="queryBookObject" resultType="mybatis01.model.Book">

select <include refid="Base_Column_List"/> from t_book where 1=1

        <if test="null!=bookType and ''!=bookType">

                and book_type=#{bookType}

        </if>

</select>

<!--集合-->

<select id="queryBookByForeach" resultType="mybatis01.model.Book">
  select <include refid="Base_Column_List"/> from t_book where 1=1 and book_id in(
  <foreach collection="ids" item="id" separator=",">
    #{id}
  </foreach>
  )
</select>

4，传递多个参数

<select id="queryBookByParams" resultType="mybatis01.model.Book">
  select <include refid="Base_Column_List"/> from t_book where 1=1
  <if test="null!=bookType and ''!=bookType">
    and book_type=#{bookType}
  </if>
  <if test="null!=bookId and ''!=bookId">
    and book_id=#{bookId}
  </if>

</select>

模糊查询

三种方式:

#{}：相当于占位符,参数传递时需要手动拼接%%

${}：使用$传递时,只传递参数本身, 例如： "zs"--> ${} --> zs

参数类型为字符串,#会在前后加单引号['],$则直接插入值

问题使用${...}代替#{...}(不建议使用该方式,有Sql注入风险)

concat()：为数据库的拼接方法

查询返回结果集

resultMap	适合使用返回值是自定义实体类的情况
resultType	适合使用返回值的数据类型是非自定义的，即jdk的提供的类型

Mapper接口 
 List<Book> queryMap();
Book queryBookByID(Integer bookId);
//Map和List<Map>集合适用于多表联查返回综合数据结果
Map<String,Object> querySingBookByMap(Integer bookId);
List<Map<String,Object>> querySingBookByMaps();
 
 
实现对应Mapper接口的xml
 <select id="queryMap" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/> from t_book
  </select>
  <select id="queryBookByID" resultType="mybatis01.model.Book">
    select <include refid="Base_Column_List"/> from t_book where 1=1 and book_id=#{value}
  </select>
  <select id="querySingBookByMap" resultType="java.util.Map">
    select <include refid="Base_Column_List"/> from t_book where 1=1 and book_id=#{bookId}
 
  </select>
  <select id="querySingBookByMaps" resultType="java.util.Map">
    select <include refid="Base_Column_List"/> from t_book
  </select>

分页查询

为什么要重写mybatis的分页？
Mybatis的分页功能很弱，它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果)，在大数据量的情况下这样的分页基本上是没有用的

PageBean pageBean=new PageBean();
        pageBean.setPage(2);
 
        //判断是否分页
        if (null != pageBean && pageBean.isPagination()) {
            // 参数1:当前页码,参数2:每页条数
            PageHelper.startPage(pageBean.getPage(), pageBean.getRows());
        }
 
 
        List<Book> bookList = bookService.queryBookPager(Book.builder().bookName("西游").build(), pageBean);
        System.out.println(bookList.getClass());
 
        if (null != pageBean && pageBean.isPagination()) {
            PageInfo pageInfo = new PageInfo(bookList);
            System.out.println("页码：" + pageInfo.getPageNum());
            System.out.println("页大小：" + pageInfo.getPageSize());
            System.out.println("总记录：" + pageInfo.getTotal());
            List list = pageInfo.getList();
            list.forEach(System.out::println);
        }

范围查询,转义字符处理

>(>)
<(<)
&(&)
空格( )

<select id="queryBookRange" resultType="mybatis01.model.Book">
  select <include refid="Base_Column_List"/> from t_book where 1=1
  <if test="null!=min and ''!=min">
    and book_price &gt;  #{min}
  </if>
  <if test="null!=max and ''!=max">
    and book_price &lt; #{max}
  </if>
</select>

这次的动态sql和分页就介绍到这里!!!

俺叫牛爱芳

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis动态sql和分页

动态SQL进行部分标签案例演示1，If标签<select id="queryBookIf" resultType="mybatis01.model.Book"> select <include refid="Base_Column_List"/> from t_book where 1=1 <if test="null!=bookType and ''!=bookType"> 
复制链接

扫一扫