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) >= #{smmVO.min}
</if>
<if test="smmVO.max != null and smmVO.max != ''">
and abs(ismm.smm_num) <= #{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') >= 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') <= 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());