常用mybatis中sql写法

最新推荐文章于 2024-06-02 14:01:58 发布

学会专注

最新推荐文章于 2024-06-02 14:01:58 发布

阅读量807

点赞数

本文链接：https://blog.csdn.net/qq_30869501/article/details/68485935

版权

1.时间处理

2.in操作包含大于1000

3.oracle批量处理

4.orcle模糊查询 (左模糊,全模糊)

5.oracle递归查询

1.时间处理

springmvc 默认采用jackson处理json,处理时间需要

@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")

private Date startDate;

可以把默认处理改为fastJson

//将一个物理类变成一个配置文件
@Configuration
//适配器
public class WebMvcConfig extends WebMvcConfigurerAdapter {
    /**
     * 利用fastjson替换掉jackson,且解决中文乱码问题
     * @param  converters
     */
    @Override
    public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
        //1.构建了一个HttpMessageConverter  FastJson   消息转换器
        FastJsonHttpMessageConverter fastConverter = new FastJsonHttpMessageConverter();
        //2.定义一个配置，设置编码方式，和格式化的形式
        FastJsonConfig fastJsonConfig = new FastJsonConfig();
        //3.设置成了PrettyFormat格式
        fastJsonConfig.setSerializerFeatures(SerializerFeature.PrettyFormat);
        //4.处理中文乱码问题
        List<MediaType> fastMediaTypes =  new ArrayList<>();
        fastMediaTypes.add(MediaType.APPLICATION_JSON_UTF8);
        fastConverter.setSupportedMediaTypes(fastMediaTypes);

        //5.将fastJsonConfig加到消息转换器中
        fastConverter.setFastJsonConfig(fastJsonConfig);
        converters.add(fastConverter);
    }
}

步骤三：
日期格式设定：
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;

注意：这里的pattern里的日期格式也可以设置成"yyyy/MM/dd HH:mm:ss"
      后面的时分秒的设置也可以没有，可以直接设置成：
      "yyyy-MM-dd" 或 "yyyy/MM/dd"

<if test="timeLimitStart != null and timeLimitEnd != null">
AND to_date(TIME_LIMIT,'yyyy-mm-dd') BETWEEN #{timeLimitStart,jdbcType=TIMESTAMP} AND
#{timeLimitEnd,jdbcType=TIMESTAMP}
</if>

<if test="creationTimeStart != null and creationTimeEnd != null">
AND CREATION_TIME BETWEEN #{creationTimeStart,jdbcType=TIMESTAMP} AND
#{creationTimeEnd,jdbcType=TIMESTAMP}
</if>

<if test="startTime != null">
and CREATION_TIME >= #{startTime}
</if>
<if test="endTime != null">
and CREATION_TIME <= #{endTime}
</if>

<if test="type != null and type != ''">
    and type = #{type,jdbcType=INTEGER}
</if>
<if test="startTime != null ">
    and valid_time &gt;= #{startTime,jdbcType=TIMESTAMP}
</if>
<if test="endTime != null ">
    and valid_time &lt;= #{endTime,jdbcType=TIMESTAMP}
</if>

<if test="status == '0'.toString()">
    and DATE_SUB(CURDATE(), INTERVAL 7 DAY) &lt;= create_time
</if>
注意：xml中判断startTime，endTime 是否为空不能判断 !=’’，不然报错。如果一条sql用到了两个以上where条件，xml里不能用《where》标签。

时间
DATE_FORMAT(l.valid_time,'%Y-%m-%d %H:%i:%S') validTime,
年龄

TIMESTAMPDIFF(YEAR,b.birthday,CURDATE()) AS age

2.in操作包含大于1000

<if test="idCardBranchList !=null and idCardBranchList.size>0">
    AND ( ID_CARD IN
    <foreach collection="idCardBranchList" index="index" item="item" open="(" separator="," close=")">
        <if test="(index % 999) == 998">NULL ) OR ID_CARD IN (</if>#{item,jdbcType=VARCHAR}
    </foreach>
    )
</if>

<if test="IdList !=null and IdList.size>0">
AND ID NOT IN
<foreach collection="IdList" index ="index" item="item" open="(" separator="," close=")">
<if test="(index % 999) == 998"> NULL ) OR ID NOT IN (</if>#{item}
</foreach>
</if>

3.oracle批量处理

<insert id ="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
insert into SUPPLIER_UNIT_PRICE
(ID,SUPPLIER_ID)
SELECT SEQ_S_UINT_PRICE.NEXTVAL,t.* from(
<foreach collection="list" item="item" index="index" separator="union all">
SELECT
#{item.supplierId}
FROM DUAL
</foreach>
)t
</insert>

如果无效的字符则是空格错误超过1000条可以考虑java分批提交
<update id="updateBatch" parameterType="java.util.List">
update SUPPLIER_UNIT_PRICE
<trim prefix="set" suffixOverrides=",">
<trim prefix="UNIT = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.unit!=null">
when id=#{item.id} then #{item.unit}
</if>
</foreach>
</trim>
<trim prefix="PRICE = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.price!=null">
when id=#{item.id} then #{item.price}
</if>
</foreach>
</trim>
</trim>
where ID in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
  #{item.id}
</foreach>
and (
<foreach item="item" index="index" collection="list" open="(" separator="or" close=")">
  ID in #{item.id}
</foreach>
)
</update>

<!-- 批量更新 -->
<update id="updateImportInfo" parameterType="java.util.List">
  <foreach collection="list" item="item" index="index" separator=";"  open="begin" close=";end;">
    update supplier_unit_price
      <set>
        <if test="item.unit!=null">
        UNIT = #{item.unit},
      </if>
      <if test="item.price!=null">
        PRICE = #{item.price},
      </if>
      <if test="item.modifiedBy!=null">
        MODIFIED_BY = #{item.modifiedBy},
      </if>
      <if test="item.modificationTime!=null">
        MODIFICATION_TIME = #{item.modificationTime}
      </if>
    </set>
    WHERE ID = #{item.id}
 </foreach>
</update>

<!-- 插入操作日志日志 -->
<insert id="addOperateLog" useGeneratedKeys="false"
   parameterType="java.util.List">
   INSERT INTO E_OPERATE_LOG(LOG_ID, DESCRIPTIONE, OPERATOR, IP,MR_PLAN_NO,TABLE_NAME, OPERATE_TIME,IMPORT_RECORD_ID,SUCCESS_NUMBER,FAILED_NUMBER)
   <foreach collection="list" index="" item="item" separator="union all">
      (SELECT
      #{item.logId},
      #{item.desciption},
      #{item.operator},
      #{item.ip},
      #{item.planNo},
      #{item.tableName},
       current_timestamp,
      #{item.importRecordID},
      #{item.successNumber},
      #{item.failedNumber}
      FROM dual  )
   </foreach>
</insert>