Mybatis以及mybatis-plus特殊用法

mybtais:

      传入参数为集合的情况使用：--注意最好mapper.java传入的参数一定要用注解形式绑定，防止出现找不到参数的问题

     1、传入参数是List

  List<String> selectTaskIdStatus(@Param("taskIdList") List<String> taskIdList);

  <select id="selectTaskIdStatus" resultType="java.lang.String" >
    SELECT
    ta.task_id
    FROM
    exp_tax_voucher_taskIds ta
    WHERE
    ta.status = "success"
    AND
    ta.task_id in
    <foreach collection="taskIdList" item="taskId" separator="," open="(" close=")">
        #{taskId}
    </foreach>
  </select>
2、传入参数是set

 List<ExpTaxReportWithKeyDTO> selectByMyKey(@Param(value = "keySet") Set<String> keySet);

 <select id="selectByMyKey" resultType="com.hand.hcf.app.ant.taxreimburse.dto.ExpTaxReportWithKeyDTO">
   SELECT
   CONCAT( company_id, tax_category_name, request_period ) my_key,
   etr.*
   FROM
   exp_tax_report etr
   WHERE
   etr.blend_status="10002"
   AND CONCAT( company_id, tax_category_name, request_period )
   IN
   <foreach open="(" close=")" collection="keySet" item="item" separator=",">
      #{item}
   </foreach>
 </select>
3、查询后返回值为拼接的字符串--同上

 SELECT
   CONCAT( company_id, tax_category_name, request_period ) my_key,
   etr.*
   FROM
   exp_tax_report etr
   WHERE
   etr.blend_status="10002"
   AND CONCAT( company_id, tax_category_name, request_period )
   IN
   <foreach open="(" close=")" collection="keySet" item="item" separator=",">
      #{item}
   </foreach>
 </select>
4、截取日期进行查询

   SELECT company_id, currency_code,date_format(pay_date,'%Y-%m') pay_peroid, sum( flow_amount ) flow_amount_sum FROM exp_bank_flow WHERE blend_status = "10001" AND STATUS = "10001" GROUP BY company_id, pay_peroid

5、自定义resultMap里面使用<collection>标签

<resultMap id="queryInfoMap" type="com.hand.hcf.app.core.web.dto.ImportResultDTO">
    <result column="successEntities" property="successEntities"/>
    <result column="failureEntities" property="failureEntities"/>
    <collection property="errorData" select="queryErrorData"
                column="{transactionId=transaction_id}"/>
</resultMap>
<select id="queryInfo" resultMap="queryInfoMap">
    SELECT (SELECT
    COUNT(0) AS successEntities
    FROM exp_tax_calculation_temp
    WHERE batch_number = #{transactionId}
    AND error_flag = 0) AS successEntities,
    (SELECT
    COUNT(0) AS failureEntities
    FROM exp_tax_calculation_temp
    WHERE batch_number = #{transactionId}
    AND error_flag = 1) AS failureEntities,
    #{transactionId} AS transaction_id
    FROM dual
</select>

<resultMap id="errorDataMap" type="com.hand.hcf.app.core.web.dto.ImportErrorDTO">
    <result column="row_number" property="index"/>
    <result column="error" property="error"/>
</resultMap>
<select id="queryErrorData" resultMap="errorDataMap">
    select t.row_number AS row_number,
    t.error_detail AS error
    FROM exp_tax_calculation_temp t
    WHERE t.batch_number = #{transactionId}
    AND t.error_flag = 1
</select>
6、先GROUP BY后再ORDER BY

<select id="queryByAuthority" resultMap="ExpenseCategory">
    select * from (
    select
    e.*,
    eaeg.id as eaeg_id,
    eaeg.category_id as eaeg_category_id

    from  ant_exp_expense_category e

    left join  ant_exp_expense_category_employee_group eaeg on e.id = eaeg.category_id

    left join  ant_exp_expense_category_authority ea on e.id = ea.category_id

    left join  ant_coa acb on acb.document_id = e.id and acb.type = "Debit"

    left join  ant_coa acc on acc.document_id = e.id and acc.type = "Credit"
    <where>
        <if test="categoryType != null">
           and e.category_type =  #{categoryType}
        </if>
        <if test="code != null">
           and e.code like concat(concat('%', #{code}), '%')
        </if>
        <if test="name != null">
           and e.name like concat(concat('%', #{name}), '%')
        </if>
        and e.is_enabled =  1
    </where>
    GROUP BY e.id) result
    ORDER BY result.last_updated_date desc
</select>

Mybatis-plus:
1、or的用法

   or(boolean condition, String sqlOr, Object... params)
2、拼接SQL--andNew

   andNew(boolean condition, String sqlAnd, Object... params) {

3、排序

  .orderBy("CONVERT(row_number,SIGNED)", true);

查询方式 说明
setSqlSelect 设置 SELECT 查询字段
where WHERE 语句，拼接 + WHERE 条件
and AND 语句，拼接 + AND 字段=值
andNew AND 语句，拼接 + AND (字段=值)
or OR 语句，拼接 + OR 字段=值
orNew OR 语句，拼接 + OR (字段=值)
eq 等于=
allEq 基于 map 内容等于=
ne 不等于<>
gt 大于>
ge 大于等于>=
lt 小于<
le 小于等于<=
like 模糊查询 LIKE
notLike 模糊查询 NOT LIKE
in IN 查询
notIn NOT IN 查询
isNull NULL 值查询
isNotNull IS NOT NULL
groupBy 分组 GROUP BY
having HAVING 关键词
orderBy 排序 ORDER BY
orderAsc ASC 排序 ORDER BY
orderDesc DESC 排序 ORDER BY
exists EXISTS 条件语句
notExists NOT EXISTS 条件语句
between BETWEEN 条件语句
notBetween NOT BETWEEN 条件语句
addFilter 自由拼接 SQL
last 拼接在最后，例如：last(“LIMIT 1”)