mybatis常用sql汇总

select
    sum(case when ismm.smm_type = '1' and ismm .smm_status = '0' then ismm.smm_num else 0 end) as monthPurchaseNum,
    sum(case when ismm.smm_type = '2' and (ismm.smm_status = '0' or ismm.smm_status = '4') then ismm.smm_num else 0 end) as monthSelfNum,
    sum(case when ismm.smm_status = '1' or (ismm.smm_status = '4' and ismm.smm_inventory_status = '2') then ismm.smm_num else 0 end) as monthOutNum,
    sum(case when ismm .smm_status = '2' then ismm.smm_num else 0 end) as monthLoseNum,
    sum(case when ismm .smm_status = '3' then ismm.smm_num else 0 end) as monthReturnNum
from isp_stock_manage_message ismm
left join isp_general_purchase_apply_detail igpad on igpad.id = ismm.smm_apply_detail_id
left join isp_base_supplier ibs on ibs.lifnr = igpad.supplier_id
where ismm.delete_flg = '0' and ismm.smm_approve_status = '1'
<if test="smmVO.startTime != null and smmVO.endTime != null">
    and ismm.create_time between #{smmVO.startTime} and #{smmVO.endTime}
</if>
<if test="smmVO.startTime == null">
    and to_char(ismm.create_time,'yyyy-mm') = to_char(now(),'yyyy-mm')
</if>
<if test="smmVO.smmGoodsCode != null and smmVO.smmGoodsCode != ''">
    and ismm.smm_goods_code LIKE CONCAT('%', #{smmVO.smmGoodsCode},'%')
</if>
<if test="smmVO.applyDetailNo != null and smmVO.applyDetailNo != ''">
    and igpad.apply_detail_no LIKE CONCAT('%', #{smmVO.applyDetailNo},'%')
</if>
<if test="smmVO.supplierName != null and smmVO.supplierName != ''">
    and ibs.name LIKE CONCAT('%', #{smmVO.supplierName},'%')
</if>
<if test="smmVO.contractNo != null and smmVO.contractNo != ''">
    and igpad.contract_no LIKE CONCAT('%', #{smmVO.contractNo},'%')
</if>
<if test="smmVO.min != null and smmVO.min != ''">
    and abs(ismm.smm_num) &gt;= #{smmVO.min}
</if>
<if test="smmVO.max != null and smmVO.max != ''">
    and abs(ismm.smm_num) &lt;= #{smmVO.max}
</if>

===================================================================================
mapper层：
List<OOO> selectDisMsg(@Param(Constants.WRAPPER) QueryWrapper<OOO> ew);

xml层：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aaa.mapper.TableMapper">
    
    <resultMap id="ONUZdgh" type="com.eastcom_sw.cis.cam.entity.statistics.MsgSendRecordZdgh">
        <id column="ID_" property="id" />
    </resultMap>
    <select id="selectDisMsg" resultType="java.util.List" resultMap="ONUZdgh">
        select * from Table where ID_ in (select Max(ID_) from Table group by BATCH_ID)
        <if test="ew.sqlSegment != null and ew.sqlSegment != '' and ew.nonEmptyOfWhere">
            and ${ew.sqlSegment}
        </if>
    </select>
</mapper>

if 标签可以⾃动根据表达式的结果来决定是否将对应的语句添加到 SQL 中，如果条件不成⽴则不添加，
如果条件成⽴则添加。
where 标签可以⾃动判断是否要删除语句块中的 and 关键字，如果检测到 where 直接跟 and 拼接，则
⾃动删除 and，通常情况下 if 和 where 结合起来使⽤。
<select id="findByConditionWithIf" resultType="com.ykq.entity.Account">
         select * from account
        <where>
             <if test="name!=null and name!=''">
                 and name like concat('%',#{name},'%')
             </if>
             <if test="isdeleted!=null">
                  and isdeleted=#{isdeleted}
             </if>
        </where>
    </select>

<select id="findByConditionWithChoose" resultType="com.ykq.entity.Account">
         select * from account
         <where>
             <choose>
                 <when test="name!=null and name!=''">
                     and name like concat('%',#{name},'%')
                 </when>
                 <when test="isdeleted!=null">
                     and isdeleted=#{isdeleted}
                 </when>
                 <otherwise>
                     <![CDATA[and money <1000 ]]>
                 </otherwise>
             </choose>
         </where>
     </select>

=============================================================

<select id="qrySp" resultMap="BaseResultMap">
    select * from BBBB  where 1=1
    <if test="start_time != null and start_time != ''">
        and to_date(REQ_TIME,'yyyy-mm-dd hh24:mi:ss') &gt;= to_date(#{start_time,jdbcType=VARCHAR},'yyyy-mm-ddhh24miss')
    </if>
    <if test="end_time != null and end_time != ''">
        and to_date(REQ_TIME,'yyyy-mm-dd hh24:mi:ss') &lt;= to_date(#{end_time,jdbcType=VARCHAR},'yyyy-mm-ddhh24miss')
    </if>
    <if test="city != null and city != '' and city != '请选择地市'">
        and CITY = #{city,jdbcType=VARCHAR}
    </if>
    <if test="area != null and area != '' and area != '请选择区县'">
        and AREA = #{area,jdbcType=VARCHAR}
    </if>
    order by TIME desc
</select>

======================================================

<select id="queryBa" parameterType="java.lang.String" resultType="java.lang.String">
    select distinct BATCH_ID from BBBB where
    to_char(COLLECT_DATE,'yyyy-mm-dd hh24:mi:ss') like #{month,jdbcType=VARCHAR} and SCENE_ID = #{scene,jdbcType=VARCHAR}
    and SCENE_NAME like #{scene_name,jdbcType=VARCHAR}
</select>

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

  <!-- 下面association中的column:把第一次查询的某一个作为第二次查询的值
          select:的值是为下一个查询语句提供一名暗号，注意看第二个查询语句的id；
          就是这样将两次查询给关联起来
          -->
          <association property="teacher" javaType="Teacher" column="teacher_id" select="findTeacherById">
              <id column="t_id" property="tid"/>
              <result column="t_name" property="tname"/>
          </association>
      </resultMap>
      <select id="findClazzById" resultMap="ClazzMap">
            select * from class where c_id=#{cid}
      </select>

      <select id="findTeacherById" resultType="Teacher">
            select t_id tid,t_name tname from teacher where t_id=#{tid}
      </select>

 

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

 trim 标签中的 prefix 和 suffix 属性会被⽤于⽣成实际的 SQL 语句，会和标签内部的语句进⾏拼接，如果语句前后出现了 prefixOverrides 或者 suffixOverrides 属性中指定的值，MyBatis 框架会⾃动将其删除。
<select id="findByConditionWithChoose" resultType="com.ykq.entity.Account">
          select * from account
                 <trim prefix="where" prefixOverrides="or|and" >
                       <choose>
                           <when test="name!=null and name!=''">
                                and name like concat('%',#{name},'%')
                           </when>
                           <when test="isdeleted!=null">
                               and isdeleted=#{isdeleted}
                           </when>
                           <otherwise>
                             <![CDATA[or money <1000 ]]>
                           </otherwise>
                       </choose>
                 </trim>
    </select>

foreach 标签可以迭代⽣成⼀系列值，这个标签主要⽤于 SQL 的 in 语句。
<select id="findByConditionWithFor" resultType="com.ykq.entity.Account">
         select * from account
         <where>
              <if test="ids!=null and ids.length>0">
                  id in
                  <foreach collection="ids" open="(" close=")" separator="," item="id">
                      #{id}
                  </foreach>
              </if>
         </where>

    </select>

分页插件

<!--pageHelper的依赖-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.11</version>
        </dependency>

(2)使用分页插件 在mybatis配置文件中加入如下代码

 <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 -->
        </plugin>
    </plugins>

测试：

AccountDao accountDao = session.getMapper(AccountDao.class);
        PageHelper.startPage(2,2); //设置分页的条件
        List<Account> all = accountDao.findAll();
        PageInfo pageInfo=new PageInfo(all);
        System.out.println("总页码："+pageInfo.getPages());
        System.out.println("总条数:"+pageInfo.getTotal());
        System.out.println("当前显示的页码:"+pageInfo.getPageNum());
        System.out.println("当前页码的数据:"+pageInfo.getList());