mybatis
日期操作 <if test="kprqBegin != null"> and DATE_FORMAT(kprq, '%Y-%m-%d') >= DATE_FORMAT(#{kprqBegin}, '%Y-%m-%d') </if> <if test="kprqEnd != null"> and DATE_FORMAT(kprq, '%Y-%m-%d') <= DATE_FORMAT(#{kprqEnd}, '%Y-%m-%d') </if> order by str_to_date(kprq, '%Y-%m-%d') desc
select date_format(o.end_date, '%Y-%m-%d %H-%I-%S')
select from_unixtime(1507000209033/1000) from dual bigint转换日期
批量插入,主键冲突就更新
<insert id="insertFpxxBatch" parameterType="java.util.List"> <![CDATA[ insert into BM_FPXX ( fpzl,fphm,gfmc,gfsh.... ) values ]]> <foreach collection="list" item="item" separator=","> <![CDATA[ (#{item.fpzl,jdbcType=VARCHAR},#{item.fphm,jdbcType=VARCHAR},SYSDATE(),SYSDATE()。。。。) ]]> </foreach> on duplicate key update fpzl=VALUES(fpzl),fphm=VALUES(fphm),gfmc=VALUES(gfmc), update_time=current_timestamp </insert>
自增主键插入
<insert id="insertRole" parameterType="com.holytax.common.data.role.entity.Role"> <selectKey resultType="java.lang.Long" keyProperty="id"> SELECT LAST_INSERT_ID() as id </selectKey> INSERT INTO bm_role <trim prefix="(" suffix=")" suffixOverrides=","> <if test="roleName != null"> ROLE_NAME, </if> <if test="description != null"> DESCRIPTION, </if> <if test="status != null"> STATUS, </if> <if test="createTime != null"> CREATE_TIME, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="roleName != null"> #{roleName,jdbcType=VARCHAR}, </if> <if test="description != null"> #{description,jdbcType=VARCHAR}, </if> <if test="status != null"> #{status,jdbcType=CHAR}, </if> <if test="createTime != null"> #{createTime,jdbcType=TIMESTAMP}, </if> </trim> </insert>
<!-- 分页查询数据头文件 把头和尾加到正常sql语句上-->
<sql id="queryHeader">
select * from (
</sql>
<!-- 分页查询数据尾文件 -->
<sql id="queryFooter">
<![CDATA[) innerresult limit #{start}, #{end}]]>
</sql>