Mybatis的动态SQL使用和Mybatis的分页

Mybatis框架

Mybatis的动态SQL的使用

• if语句的使用

<!--改造订单表查询功能，使用动态SQL功能-->
  <select id="selectLikeBill" resultType="com.csi.smbms.domain.Bill" parameterType="map">
    SELECT * FROM smbms_bill WHERE 1=1 //这个条件必须要有
      <if test="productName != null">
        AND productName like CONCAT('%',#{productName},'%')
      </if>
      <if test="providerId != null">
        AND providerId = #{providerId}
      </if>
      <if test="isPayment != null">
        AND isPayment = #{isPayment}
      </if>
  </select>

多个条件可以使用多个if

• where-if的使用

<select id="selectLikeProvider" resultType="com.csi.smbms.domain.Provider" parameterType="map">
    SELECT * FROM smbms_provider
    <where>
      <if test="proCode != null">
        AND proCode like CONCAT('%',#{proCode},'%')
      </if>
      <if test="proName != null">
        AND proName like CONCAT('%',#{proName},'%')
      </if>
    </where>
  </select>

尽量使用第二种方式，相比第一种方式这种更简单易读

• set的使用

<update id="updateProvider" parameterType="map">
    update smbms_provider
    <set>
      <if test="proCode != null">
        proCode = #{proCode},
      </if>
      <if test="proName != null">
        proName = #{proName},
      </if>
      <if test="proDesc != null">
        proDesc = #{proDesc},
      </if>
      <if test="proContact != null">
        proContact = #{proContact},
      </if>
      <if test="proPhone != null">
        proPhone = #{proPhone},
      </if>
      <if test="proAddress != null">
        proAddress = #{proAddress},
      </if>
      <if test="proFax != null">
        proFax = #{proFax},
      </if>
      <if test="createdBy != null">
        createdBy = #{createdBy},
      </if>
      <if test="creationDate != null">
        creationDate = #{creationDate},
      </if>
      <if test="modifyDate != null">
        modifyDate = #{modifyDate},
      </if>
      <if test="modifyBy != null">
        modifyBy = #{modifyBy},
      </if>
    </set>
    where id = #{id}
  </update>

修改数据库数据的时候可以使用set语句

• trim(set)的使用

<update id="updateProviderTrim" parameterType="map">
    update smbms_provider
    <trim prefix="set" suffixOverrides=",">
      <if test="proCode != null">
        proCode = #{proCode},
      </if>
      <if test="proName != null">
        proName = #{proName},
      </if>
      <if test="proDesc != null">
        proDesc = #{proDesc},
      </if>
      <if test="proContact != null">
        proContact = #{proContact},
      </if>
      <if test="proPhone != null">
        proPhone = #{proPhone},
      </if>
      <if test="proAddress != null">
        proAddress = #{proAddress},
      </if>
      <if test="proFax != null">
        proFax = #{proFax},
      </if>
      <if test="createdBy != null">
        createdBy = #{createdBy},
      </if>
      <if test="creationDate != null">
        creationDate = #{creationDate},
      </if>
      <if test="modifyDate != null">
        modifyDate = #{modifyDate},
      </if>
      <if test="modifyBy != null">
        modifyBy = #{modifyBy},
      </if>
    </trim>
    where id = #{id}
  </update>

• trim(where)的使用

<select id="selectLikeProvider" resultType="com.csi.smbms.domain.Provider" parameterType="map">
    SELECT * FROM smbms_provider
    <trim  prefix="where" suffixOverrides="AND">
      <if test="proCode != null">
        AND proCode like CONCAT('%',#{proCode},'%')
      </if>
      <if test="proName != null">
        AND proName like CONCAT('%',#{proName},'%')
      </if>
    </trim>
  </select>

• choose (when, otherwise)

<select id="selectChooseCondition" resultType="com.csi.smbms.domain.Provider" parameterType="map">
    SELECT * FROM smbms_provider
    <where>
      <choose>
        <when test="proCode != null">
          proCode LIKE CONCAT('%',#{proCode},'%')
        </when>
        <when test="proName != null">
          proName LIKE CONCAT('%',#{proName},'%')
        </when>
        <when test="proContact">
          proContact LIKE CONCAT('%',#{proContact},'%')
        </when>
        <otherwise>
          YEAR(creationDate)=YEAR(NOW());
        </otherwise>
      </choose>
    </where>
  </select>

使用方式跟java的Switch一样

• foreach的使用

<select id="selectProviderId" resultType="com.csi.smbms.domain.Bill" parameterType="list">
    SELECT * FROM smbms_bill
    WHERE providerId IN
    <foreach collection="list" item="item" separator="," open="(" close=")">
      #{item}
    </foreach>
  </select>

foreach的元素属性主要有item，index，collection，open，separator，close

Mybatis实现分页

• 使用原生MyBatis框架提供的RowBounds方法

@Test
    public void selectBill1(){

        SqlSession sqlSession = MyBatisUtils.getSqlSession ();

        BillMapper billMapper = sqlSession.getMapper ( BillMapper.class );

       /* PageHelper.startPage ( 1,5 );*/

        List<Bill> bills = billMapper.selectBill (new RowBounds (0,5));

        for (Bill bill : bills) {
            System.out.println (bill);
        }
    }

但是RowBounds是假分页，RowBounds会把数据库里面的数据全拿出来，然后把对应的条数展示出来

[DEBUG] 2022-09-20 14:21:59,696 com.csi.smbms.mapper.BillMapper.selectBill - ==>  Preparing: SELECT * FROM smbms_bill

可以看到RowBounds是查找全部出来的数据

• 使用PageHelper插件实现分页

先导入依赖

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

        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>4.2</version>
        </dependency>

实现分页

@Test
    public void selectBill(){

        SqlSession sqlSession = MyBatisUtils.getSqlSession ();

        BillMapper billMapper = sqlSession.getMapper ( BillMapper.class );

        PageHelper.startPage ( 1,5 );

        List<Bill> bills = billMapper.selectBill ();

        for (Bill bill : bills) {
            System.out.println (bill);
        }

        PageInfo<Provider> pageInfo = new PageInfo (bills);

        System.out.println ( pageInfo.getPageSize () );

        System.out.println (pageInfo.getPageNum ());

        System.out.println (pageInfo.getEndRow ());

        System.out.println (pageInfo.getNavigateFirstPage ());

        MyBatisUtils.close ();
    }

PageHelper实现的是真分页

[DEBUG] 2022-09-20 14:21:59,696 com.csi.smbms.mapper.BillMapper.selectBill - ==>  Preparing: SELECT * FROM smbms_bill LIMIT ?

日志里面可以看出来SQL语句是通过LIMIT查询出来的数据