mysql 选择、插入和删除在 java 程序中有效，但更新不起作用

我有一个带有主键的表id，选择，插入和删除查询都可以在java程序中工作，但是更新查询不起作用，以便“重复更新时插入”（仅在记录不存在时有效，当记录存在时更新将不起作用）。

所有查询均已提交，我的 mariadb 版本是 10.1.14。

预先感谢您的任何帮助！

所有查询都可以在 mysql-cli 中正常运行。

表模式

+------------------+----------------------+------+-----+---------------------+-----------------------------+
| Field            | Type                 | Null | Key | Default             | Extra                       |
+------------------+----------------------+------+-----+---------------------+-----------------------------+
| id               | int(10) unsigned     | NO   | PRI | NULL                |                             |
| posng_valid_type | tinyint(3) unsigned  | YES  |     | NULL                |                             |
| longitude        | double(9,6)          | NO   |     | 0.000000            |                             |
| latitude         | double(9,6)          | NO   |     | 0.000000            |                             |
| heading          | smallint(6)          | NO   |     | 0                   |                             |
| altitude         | float(7,3)           | NO   |     | 0.000               |                             |
| gps_speed        | smallint(5) unsigned | NO   |     | 0                   |                             |
| sample_time      | timestamp            | NO   |     | 0000-00-00 00:00:00 |                             |
| update_time      | timestamp            | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+------------------+----------------------+------+-----+---------------------+-----------------------------+

1.更新查询

update `status_position` 
set `status_position`.`id` = 3007,  
    `status_position`.`posng_valid_type` = 0, 
    `status_position`.`longitude` = 121.1921, 
    `status_position`.`latitude` = 31.2797, 
    `status_position`.`heading` = -1, 
    `status_position`.`altitude` = 0.0, 
    `status_position`.`gps_speed` = 0, 
    `status_position`.`sample_time` = timestamp '2017-02-15 03:52:23.0'   
where `status_position`.`id` = 3007;

2. 更新重复查询

insert into `status_position` (
    `id`, 
    `posng_valid_type`, 
    `longitude`, 
    `latitude`, 
    `heading`, 
    `altitude`, 
    `gps_speed`, 
    `sample_time`
 ) values (
    2001, 
    0, 
    121.1921, 
    31.2797, 
    -1, 
    0.0, 
    0, 
    timestamp '2017-02-15 03:52:23.0'
) on duplicate key update 
    `status_position`.`id` = 2001, 
    `status_position`.`posng_valid_type` = 0, 
    `status_position`.`longitude` = 121.1921, 
    `status_position`.`latitude` = 31.2797, 
    `status_position`.`heading` = -1, 
    `status_position`.`altitude` = 0.0, 
    `status_position`.`gps_speed` = 0, 
    `status_position`.`sample_time` = timestamp '2017-02-15 03:52:23.0';

使用 JOOQ 生成查询 2 的 java 代码

public <R extends Record> void batchUpsertRecord(Table<R> table, List<R> records) throws PersistenceException {
        Connection conn = ConnectionPoolManager.INSTANCE.getConnection();

            try (DSLContext dslContext = DSL.using(conn, SQLDialect.MARIADB)) {
                List<InsertQuery> insertQueryList = new ArrayList<>();
                for (R record : records) {
                    InsertQuery<R> insert = dslContext.insertQuery(table);
                    insert.addRecord(record);
                    insert.onDuplicateKeyUpdate(true);
                    insert.addValuesForUpdate(mapOfChangedValues(record));
                    insertQueryList.add(insert);
                }
                dslContext.batch(insertQueryList).execute();
                conn.commit();
            } catch (SQLException e) {
                logger.error("Failed to upsert record into table({}).", table.getName(), e);
            } finally {
                ConnectionPoolManager.INSTANCE.closeConnection(conn, logger);
            }
    }