mybatis 增删改查实例

查询列表

Dao,返回实体类列表，参数Dto类

List<OrderOperateDto> needList(InventoryPlSelectParamDto param);

SQL

<select id="needList" parameterType="com.liby.inventory.export.dto.InventoryPlSelectParamDto"
        resultMap="OrderOperateDtoMap" >
            SELECT
                  T.so2,T.posnr2,T.pstyv,T.vstel,T.vmeng,T.omeng,
                  n.ebeln,n.so1,n.so3,n.bstnk,n.chan,n.posnr,n.vgbel,n.vgpos,n.matnr,n.matnr_name matnrName,n.wei,n.vol,
                  n.unit,n.barcode,n.matkle,n.mtype,n.matkle_name matkleName,n.bigclass,n.category,n.vstelx,n.kweng,
                  n.werks,n.lgort,n.charg,n.date1,n.date2,n.date3,n.lprio,n.prio,n.zbulk,n.ihrez,n.pay,n.vbtyp,
                  n.stpcode,n.stpname,n.kunnr,n.name1,n.lifnr,n.zzunmark,n.order_status orderStatus,n.cmgst,n.vsart,n.name2,n.vstel_name vstelName
            FROM(
              SELECT
                  MAX( id ) id,so2,posnr2,pstyv,vstel,SUM( vmeng ) vmeng,SUM( omeng ) omeng
              FROM
                inventory_need
              WHERE
                vbtyp = 'C'
                AND chan = 'GT'
                AND stpcode = #{stpcode,jdbcType=VARCHAR}
                AND so2 IS NOT NULL
                AND posnr2 IS NOT NULL
              GROUP BY
                so2,
                posnr2,
                pstyv,
                vstel) T
            LEFT JOIN inventory_need n ON T.id = n.id
            where 1=1
             AND T.vmeng > 0
            <if test="startDate2 != null and startDate2 != ''.toString()">
                AND DATE_FORMAT(n.date2,'%Y-%m-%d') <![CDATA[ >= ]]> #{startDate2}
            </if>
            <if test="endDate2 != null and endDate2 != ''.toString() ">
                AND DATE_FORMAT(n.date2,'%Y-%m-%d') <![CDATA[ <= ]]> #{endDate2}
            </if>
            <if test="so2 != null and so2.size() > 0 ">
                and
                <foreach collection="so2" index="index" item="val" open="(" separator=" or " close=")">
                    n.so2 like '%${val}%'
                </foreach>
            </if>
            <if test="barcode != null and barcode.size() > 0 ">
                and
                <foreach collection="barcode" index="index" item="val" open="(" separator=" or " close=")">
                    n.barcode like '%${val}%'
                </foreach>
            </if>
            <if test="matkle != null and matkle.size() > 0 ">
                and
                <foreach collection="matkle" index="index" item="val" open="(" separator=" or " close=")">
                    n.matkle like '%${val}%'
                </foreach>
            </if>
            <if test="matnr_name != null and matnr_name.size() > 0 ">
                and
                <foreach collection="matnr_name" index="index" item="val" open="(" separator=" or " close=")">
                    n.matnr_name like '%${val}%'
                </foreach>
            </if>
            <choose>
                <when test="orderBy != null and orderBy !='' and  orderByType != null and orderByType != ''">
                    ORDER BY ${orderBy} ${orderByType}
                </when>
                <otherwise>
                    ORDER BY T.so2 asc
                </otherwise>
            </choose>
</select>

查询一条数据，注意如果查询出的结果有多条mybatis会报错，只能用list接收

Dao

InventoryNeed selectOneByVbelnAndPosnr(InventoryNeed inventoryNeed);

SQL

<select id="selectOneByVbelnAndPosnr" resultMap="BaseResultMap" parameterType="com.liby.inventory.service.entity.InventoryNeed">
  select  <include refid="Base_Column_List" /> from inventory_need
  where vbeln = #{vbeln,jdbcType=VARCHAR} and posnr = #{posnr,jdbcType=VARCHAR}
</select>

 

查询行数

Dao

int countByySo3AndPosnr(InventoryNeed need);

SQL

<select id="countByySo3AndPosnr" parameterType="com.liby.inventory.service.entity.InventoryNeed"
resultType="java.math.BigDecimal">
  select count(1) from inventory_need where so3 = #{so3,jdbcType=VARCHAR} and posnr = #{posnr,jdbcType=VARCHAR}
</select>

 

ids查询

Dao

List<OrderOperateDto> needList(InventoryPlSelectParamDto param);

SQL

<select id="queryBanch" parameterType="java.util.Map" resultType="java.util.HashMap">
    select  CONCAT(material_code,factory_code,whare_house,stock_point_code,banch_num) ke,e.* from  edi_material_banch  e where   operation_type NOT IN ('D')  and deleted not in ('1')
    <choose>
    <when test="_parameter.containsKey('ids') and ids != null and ids.length >0 ">
        AND  e.id IN (
        <foreach collection="ids" item="id" separator=",">
            #{id,jdbcType=VARCHAR}
        </foreach>
        )
    </when>
    <otherwise>

    </otherwise>
    </choose>
</select>

 

QueryWrapper

QueryWrapper<InventoryDcStock> queryWrapper = new QueryWrapper();
queryWrapper.lambda().in(InventoryDcStock::getId, ids);