mybatis生成mapper.xml文件的一些sql的用法

第一个，可选择的插入数据insertSelective

   <font size=5 >记得逗号莫忘
<insert id="insertSelective" parameterType="com.epay.ibas.dao.model.PartnerTable" >
    insert into PARTNER_TABLE
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="partnerId != null" >
        PARTNER_ID,
      </if>
      <if test="partnerName != null" >
        PARTNER_NAME,
      </if>
      <if test="partnerNm != null" >
        PARTNER_NM,
      </if>
      <if test="partnerState != null" >
        PARTNER_STATE,
      </if>
      <if test="cashierType != null" >
        CASHIER_TYPE,
      </if>
        CREATE_TIME,
        LAST_UPDATE_TIME,
        <if test="productType !=null">
        PRODUCT_TYPE,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="partnerId != null" >
        #{partnerId,jdbcType=VARCHAR},
      </if>
      <if test="partnerName != null" >
        #{partnerName,jdbcType=VARCHAR},
      </if>
      <if test="partnerNm != null" >
        #{partnerNm,jdbcType=VARCHAR},
      </if>
      <if test="partnerState != null" >
        #{partnerState,jdbcType=VARCHAR},
      </if>
      <if test="cashierType != null" >
        #{cashierType,jdbcType=VARCHAR},
      </if>
      sysdate,
      sysdate,
      <if test="productType !=null">
        #{productType,jdbcType=VARCHAR},
      </if>
    </trim>

运行的sql语句是

insert into PARTNER_TABLE ( PARTNER_ID, PARTNER_NAME, PARTNER_NM, PARTNER_STATE, CASHIER_TYPE,
 CREATE_TIME, LAST_UPDATE_TIME, PRODUCT_TYPE ) values ( ?, ?, ?, ?, ?, sysdate, sysdate, ? ) 

第二个多个条件可选择的查询

<select id="selectByPartner" parameterType="com.epay.cu.ibas.common.model.PartnerQueryReq" resultMap="partnerInfoDTO">
    select * from PARTNER_TABLE
    where 1 = 1
    <if test="partnerName != null" >
      and PARTNER_NAME LIKE   concat(concat('%',#{partnerName,jdbcType=VARCHAR}),'%')
    </if>
    <if test="partnerNm != null" >
      and PARTNER_NM = #{partnerNm,jdbcType=VARCHAR}
    </if>
    <if test="partnerState != null" >
      and PARTNER_STATE = #{partnerState,jdbcType=VARCHAR}
    </if>
    <if test="productType !=null">
      and PRODUCT_TYPE = #{productType,jdbcType=VARCHAR}
    </if>
    Order By CREATE_TIME Desc
  </select>

<select id="selectByProductIdInfo" parameterType="com.epay.cu.ibas.common.model.ProductInfoQueryReq" resultMap="ProductDTO">
   select n.*,i.partner_id,i.partner_mn from
    (select t.*,p.pc_name city_name,
     (select q.pc_name from pc_table q where t.province_no=q.pc_no ) as province_name
    from product_table t left join pc_table p on t.city_no= p.pc_no) n
    left join product_installment_table i on n.product_id = i.product_id
    where 1=1
    <if test="productId != null" >
      and n.PRODUCT_ID = #{productId,jdbcType=VARCHAR}
    </if>
    <if test="provinceNo != null" >
      and n.PROVINCE_NO = #{provinceNo,jdbcType=VARCHAR}
    </if>
    <if test="cityNo != null" >
      and n.CITY_NO = #{cityNo,jdbcType=VARCHAR}
    </if>
    <if test="subsidyType != null" >
      and n.SUBSIDY_TYPE = #{subsidyType,jdbcType=VARCHAR}
    </if>
    <if test="productType != null">
      and n.PRODUCT_TYPE = #{productType,jdbcType=VARCHAR}
    </if>
    <if test="state != null" >
      and n.STATE = #{state,jdbcType=VARCHAR}
    </if> Order By n.CREATE_TIME Desc
  </select>

上边此种复杂预计条件加上n.CITY_NO,n.SUBSIDY_TYPE,n.CREATE_TIME 等前边加上n.。。。即n表和t表有相同的列，不加的话会不知道取哪个表中的值，会出现未明确定义的列的错误，加上指定是哪个表

运行语句

          此时是只有PRODUCT_TYPE一个参数查询
select * from PARTNER_TABLE where 1 = 1 and PRODUCT_TYPE = ? Order By CREATE_TIME Desc 
    通过product_type和partner_name名字模糊查询，两个条件时
select * from PARTNER_TABLE where 1 = 1 and PARTNER_NAME LIKE concat(concat('%',?),'%') and PRODUCT_TYPE = ? Order By CREATE_TIME Desc 

第三个selectByExample

<sql id="Base_Column_List" >
    PRODUCT_INSTALLMENT_ID, PRODUCT_ID, INSTALLMENT_ID, RATE, PARTNER_ID, PARTNER_MN, 
    INSTALLMENT_NUM, INSTALLMENT_STATE, CASHIER_TYPE, CREATE_TIME, LAST_UPDATE_TIME, 
    REMAKE1, REMAKE2, REMAKE3, PROVINCE_NO,PRODUCT_TYPE
  </sql>
  
<select id="selectByExample" resultMap="BaseResultMap" parameterType="com.epay.ibas.dao.model.ProductInstallmentTableExample" >
    select
    <if test="distinct" >
      distinct
    </if>
    'true' as QUERYID,
    <include refid="Base_Column_List" />///见上
    from PRODUCT_INSTALLMENT_TABLE
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null" >
      order by ${orderByClause}
    </if>
  </select>

ProductInstallmentTableExample example1 = new ProductInstallmentTableExample();
ProductInstallmentTableExample.Criteria criteria1 = example1.createCriteria();
/
criteria1.andProductIdEqualTo(req.getProductId()).andProvinceNoEqualTo(req.getProvinceNo());
List<ProductInstallmentTable> productInstallmentList = productInstallmentTableMapper.selectByExample(example1);
        条件是productId和provinceNo

运行语句是：

 select 'true' as QUERYID, PRODUCT_INSTALLMENT_ID, PRODUCT_ID, INSTALLMENT_ID, RATE, PARTNER_ID, PARTNER_MN, INSTALLMENT_NUM, INSTALLMENT_STATE, 
 CASHIER_TYPE, CREATE_TIME, LAST_UPDATE_TIME, REMAKE1, REMAKE2, REMAKE3, PROVINCE_NO,PRODUCT_TYPE 
 from PRODUCT_INSTALLMENT_TABLE WHERE ( PRODUCT_ID = ? and PROVINCE_NO = ? ) 

orderByClause(“FINISH_TIME” DESC);按finish_time 降序排序

更新：updateByExampleSelective or
example,or()函数，执行语句等如图所示。

( xx and xx) or ( xx and xx)