Insert a row with Instant-DATETIME column to MySQL using JOOQ

huangapple 未分类评论59阅读模式
英文:

Insert a row with Instant-DATETIME column to MySQL using JOOQ

问题

我试图使用JOOQ将一行插入到MySQL 8中,其中Instant <--> DATETIME映射,但我得到了org.jooq.exception.DataAccessException,根本原因是com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2020-05-02 02:45:38.134463+00:00' for column 'created' at row 1

由于某种原因,JOOQ在DATETIME列中添加了+00:00,而MySQL不支持该格式。

任何关于JOOQ是否可以直接处理此问题的想法吗?

CREATE TABLE `user` (
    `id`       BIGINT       NOT NULL AUTO_INCREMENT,
    `version`  BIGINT       NOT NULL DEFAULT 0,
    `name`     VARCHAR(255) NOT NULL,
    `created`  DATETIME     NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ind_name`(`name`)
);
@Getter
@Setter
public class User {
    @NonNull
    @Id
    private Long id;
    @NonNull
    @Version
    private Long version;
    @NonNull
    @Column(name = "name")
    private String name;
    @NonNull
    @Column(name = "created")
    private Instant created;
}
dslContext.insertInto( DSL.table( "user" ), field( "name", String.class ),
                //or field( "created", Instant.class), the result is the same
                field( "created", SQLDataType.INSTANT ) ) 
                .values( name, Instant.now() )
                .returningResult( DSL.asterisk() )
                .fetchOne()
                .into( User.class );
英文:

I'm trying to use JOOQ to insert a row to MySQL 8 with Instant <--> DATETIME mapping, but I'm getting org.jooq.exception.DataAccessException with root cause com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: &#39;2020-05-02 02:45:38.134463+00:00&#39; for column &#39;created&#39; at row 1

For some reason JOOQ adds +00:00 to DATETIME column and that format is not supported by MySQL.

Any ideas if JOOQ can manage that out-of-the-box?

CREATE TABLE `user` (
    `id`       BIGINT       NOT NULL AUTO_INCREMENT,
    `version`  BIGINT       NOT NULL DEFAULT 0,
    `name`     VARCHAR(255) NOT NULL,
    `created`  DATETIME     NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ind_name`(`name`)
);
@Getter
@Setter
public class User {
    @NonNull
    @Id
    private Long id;
    @NonNull
    @Version
    private Long version;
    @NonNull
    @Column(name = &quot;name&quot;)
    private String name;
    @NonNull
    @Column(name = &quot;created&quot;)
    private Instant created;
}
dslContext.insertInto( DSL.table( &quot;user&quot; ), field( &quot;name&quot;, String.class ),
                //or field( &quot;created&quot;, Instant.class), the result is the same
                field( &quot;created&quot;, SQLDataType.INSTANT ) ) 
                .values( name, Instant.now() )
                .returningResult( DSL.asterisk() )
                .fetchOne()
                .into( User.class );

huangapple
  • 本文由 发表于 2020年5月2日 12:21:17
  • 转载请务必保留本文链接:https://java.coder-hub.com/61554418.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定