SQL 中删除超出时间限制的数据，并返回删除数据信息（Mybatis+postgresql）

前言

前一阵子，接到一个活，主要内容是这样的，数据库中存在一些过期的日志（可能是一天前的数据，或者是一个月前的数据，等等），将这些数据删除掉，并且返回这些数据的信息（就是说我得知道自己到底删了哪些数据呀？）。苦于在网上找了好久，终无果，于是清明小长假，便在自己的mac上，简单的搭了个环境，试了一下，谁让自己是初出茅庐的小菜鸟呢。因为看到网上很多都是关于sql如何操作的，但是并没有结合Mybatis的，所以在这里记录一下，方便自己日后使用，也希望可以帮到那些一直寻找的人😁。

正文

先给大家看一下我自拟的表结构：

-- ----------------------------
-- Table structure for eventLog
-- ----------------------------
DROP TABLE IF EXISTS "public"."eventLog";
CREATE TABLE "public"."eventLog" (
  "id" int4 NOT NULL DEFAULT nextval('"eventLog_id_seq"'::regclass),
  "start_time" timestamp(6),
  "log_detail" varchar(255) COLLATE "pg_catalog"."default"
)
;
ALTER TABLE "public"."eventLog" OWNER TO "harry";

-- ----------------------------
-- Primary Key structure for table eventLog
-- ----------------------------
ALTER TABLE "public"."eventLog" ADD CONSTRAINT "eventLog_pkey" PRIMARY KEY ("id");

表很简单，一共有三个字段，id为自增主键， start_time 为需要看是否过期的字段，log_detail 为这个日志的详细内容。

接下来贴出大家关心的 Mybatis 中的代码：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.example.harry.dao.EventLogMapper">
    <resultMap id="BaseMap" type="com.example.harry.entity.EventLog">
      <id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/>
      <result column="start_time" property="time" jdbcType="TIMESTAMP" javaType="java.sql.Timestamp"/>
      <result column="log_detail" property="logDetail" jdbcType="VARCHAR" javaType="java.lang.String"/>
    </resultMap>
        <insert id="insertEventLog"  parameterType="EventLog" useGeneratedKeys="true" keyProperty="id">
            insert into "eventLog" (start_time,log_detail) values (now(),#{logDetail});
        </insert>

        <select id="deleteOverdueLog" resultType="map">
            DELETE
            FROM "eventLog"
            <![CDATA[ WHERE start_time < now()::TIMESTAMP + '-${overdueDays} days']]>
            RETURNING id, start_time, log_detail;
        </select>
    
    </mapper>

最重要的就是哪个select方法，很多人就要问了，为什么是delete的语句，你却要用select的标签呢？
这个问题问的好，刚开始我也是用的delete，但是不管sql怎么写，返回的都是一个Integer，然后转念一想，Mybatis是基于JDBC的，在JDBC中增删改执行的都是 executeUpdate方法，这个方法返回整数就是受影响的行数，所以如果想在删除的同时，返回删除行的信息，就一定要用select。
在这里，我想要强调四点：
1.我这里让它返回的是个List<Map<String,Object>>所以使用的是

resultType="map"

如果想要装载进EventLog的实体类中，我们可以使用

resultMap="BaseMap"

关于resultType和resultMap的区别，我就不在这里赘述了，详细的可以查看官网（赞一下官网👍，写的真的很好）。
2. overdueDays是我传递进来的Integer的参数，代表的是具体的天数，可以配置在application.properties中，方便由于业务修改造成的变化。这里使用的是 ${},而不是#{}。是因为#{}会做防注入处理，把参数换为 ‘?’,并且将参数自动的加上引号，而$ {} 就不会只是单纯的做替换。
3. <![CDATA[ something]]> 这里可以把something换成任意语句，并且something的语句不会被转义，因为我们这里用到了'<',' ' '(就是小于号和单引号)，所以我们不希望它被转义。
4. RETURNING 后面接的就是我们具体想要返回删除行的哪些字段。

Tips:
SQL除了以上写法，还有另一种写法：

WITH deleteInfo AS (DELETE
            FROM "eventLog"
            <![CDATA[ WHERE start_time < now()::TIMESTAMP + '-${overdueDays} days']]>
            RETURNING id, start_time, log_detail)
SELECT * FROM deleteInfo;

后记

具体的项目我已经上传到了 github上，如果有需要可以进行下载https://github.com/luckypoison/postgresBootLearn.git
谢谢大家

最后编辑于：2019.04.07 22:34:27