业务 sql 和 Mybatis

一、sql

1.1 时间

时间戳

某天

-- 某天时间时间戳
SELECT unix_timestamp(FROM_UNIXTIME(1637287200000/1000,"%Y-%m-%d %H:%i:%s")) * 1000;
-- 某天日期时间戳
SELECT unix_timestamp(FROM_UNIXTIME(1637287200000/1000,"%Y-%m-%d")) * 1000;

当天

-- 当前时间时间戳
unix_timestamp(now()) * 1000
-- 当前日期时间戳
unix_timestamp(CURRENT_DATE()) * 1000

测试

SELECT now(), timestamp(now()), unix_timestamp(now()), unix_timestamp(now()) * 1000, unix_timestamp(timestamp(now())) * 1000

增减天数时间戳

当天

-- 当前时间7天前时间戳
unix_timestamp(date_add(now(), interval -7 day)) * 1000
-- 当前日期7天前时间戳
unix_timestamp(date_add(CURRENT_DATE(), interval -7 day)) * 1000

某天

-- 某天时间
unix_timestamp(date_add(FROM_UNIXTIME(1637287200000/1000,"%Y-%m-%d %H:%i:%s"), interval -7 day)) * 1000
-- 某天日期
unix_timestamp(date_add(FROM_UNIXTIME(1637287200000/1000,"%Y-%m-%d"), interval -7 day)) * 1000

1.2 排序

多条件排序

ORDER BY
CASE jf.`status` WHEN 0 THEN 0 WHEN 1 THEN 2 WHEN 2 THEN 1 WHEN 3 THEN 3 END ASC,
jf.`start_timestamp` ASC

1.3 判断

CASE WHEN count(*) > 0 THEN count(*) ELSE NULL END
IF(1>2,2,3)

二、Mybatis

choose

choose 标签是按顺序判断其内部 when 标签中的 test 条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的 sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

<choose>
    <when test="details != null and details.contains('mini')">
        CASE ct.`status` WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5
        THEN 5 END ASC, ct.`start_timestamp` ASC
    </when>
    <otherwise>
        ct.`create_at` DESC
    </otherwise>
</choose>

resultMap

<resultMap id="CareerTalkMap" type="com.qst.ourea.portal.context.model.recruitment.CareerTalkModel">
    <id property="id" column="id"/>
    <result property="companyId" column="company_id"/>
    <collection property="occupations" columnPrefix="occupation_"
                resultMap="com.qst.ourea.portal.database.mapper.recruitment.CareerTalkOccupationMapper.CareerTalkOccupationMap"/>
</resultMap>

include

<sql id="sqlExtraColumns">

</sql>

<include refid="sqlExtraColumns"/>

bind

<bind name="searchLike" value="'%' + search + '%'"/>
 #{searchLike}

collection

<collection property="occupations" columnPrefix="occupation_"
            resultMap="com.qst.ourea.portal.database.mapper.recruitment.InternProgramOccupationMapper.internProgramOccupationMap"/>

<collection property="cities" ofType="java.lang.String">
    <constructor>
        <arg column="cities"/>
    </constructor>
</collection>

<>

<![CDATA[<= ]]>