返回myBatis中已删除行的列表

Question

我想将已删除的行详细信息列表返回给控制器

<delete id = "delete-record", parameter-type = "hash-map">
DELETE FROM <Table Name>
WHERE id = #{id};

</delete>

在上面的段中，我找不到任何返回类型。

Answer 1

使用RETURNING子句和select映射与flushCache

<select id="removeSomeStuff" parameterType="map" resultType="WhateverType" flushCache="true">
    delete from some_stuff where id = #{id}
    RETURNING *
</select>

上面的查询返回等价于select * from some_stuff的结果集，您可以使用任何映射选项(例如，resultMap或resultType)来获取对象。

Answer 2

如果使用Mariadb > 10.0.5，则可以使用关键字返回，如下所示：

DELETE FROM `data` 
WHERE `timestamp` BETWEEN '2019-03-08 02:11:45' AND '2019-03-18 03:20:45'
RETURNING id,`name`,`state`;

请参阅：https://mariadb.com/kb/en/library/delete/

样本

MariaDB [test]> CREATE TABLE `data` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `timestamp` timestamp NULL DEFAULT NULL,
    ->   `name` varchar(16) DEFAULT NULL,
    ->   `state` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.023 sec)

MariaDB [test]> INSERT INTO `data` (`id`, `timestamp`, `name`, `state`)
    -> VALUES
    -> (1, '2019-03-07 11:16:32', 'Light_A', 0),
    -> (2, '2019-03-07 12:36:32', 'Light_A', 1),
    -> (3, '2019-03-07 13:15:12', 'Light_A', 0),
    -> (4, '2019-03-08 02:11:45', 'Light_A', 1),
    -> (5, '2019-03-08 02:18:45', 'Light_A', 1),
    -> (6, '2019-03-08 02:22:45', 'Light_A', 0),
    -> (7, '2019-03-18 03:14:45', 'Light_B', 0),
    -> (8, '2019-03-18 03:16:45', 'Light_B', 1),
    -> (9, '2019-03-18 03:18:45', 'Light_B', 1),
    -> (10, '2019-03-18 03:20:45', 'Light_B', 0),
    -> (11, '2019-03-19 17:20:12', 'Light_B', 0),
    -> (12, '2019-03-19 17:22:12', 'Light_B', 1),
    -> (13, '2019-03-19 17:23:12', 'Light_B', 0);
Query OK, 13 rows affected (0.009 sec)
Records: 13  Duplicates: 0  Warnings: 0

现在删除

MariaDB [test]> DELETE FROM `data` WHERE
`timestamp` BETWEEN '2019-03-08 02:11:45' AND '2019-03-18 03:20:45'
RETURNING id,`name`,`state`;
+----+---------+-------+
| id | name    | state |
+----+---------+-------+
|  4 | Light_A |     1 |
|  5 | Light_A |     1 |
|  6 | Light_A |     0 |
|  7 | Light_B |     0 |
|  8 | Light_B |     1 |
|  9 | Light_B |     1 |
| 10 | Light_B |     0 |
+----+---------+-------+
7 rows in set (0.001 sec)

MariaDB [test]> 

Answer 3

铁线莲可以帮助我们自动完成这件事

<delete id="deleteDemo">
  DELETE FROM Table_Name
  WHERE ID = #{id}
</delete>

请参见这样的Mapper.java：

int deleteDemo(@Param("id") String id);

它将返回受影响的记录数量。

从jdbc中可以找到这个数字。

<update>也是如此。

如果您想提高一点，请学习jdbc中的useAffectedRows。