Spring Boot：无法推断用于java.time.LocalDateTime实例的SQL类型

Question

public int[] bulkSaveInvoices(List<Invoice> invoices){

    String insertSQL = "INSERT INTO invoices VALUES (:id, :exactIssueTime, :finalIssueTime, :issuer, :groupID)";
    SqlParameterSource[] sqlParams = SqlParameterSourceUtils.createBatch(invoices.toArray());

    int[] insertCounts = namedParameterJdbcTemplate.batchUpdate(insertSQL, sqlParams);

    return insertCounts;
}

├── Invoice.java
├── InvoiceRepository.java
└── persistance
    ├── converters
    │   ├── LocalDateAttributeConverter.java
    │   └── LocalDateTimeAttributeConverter.java
    └── JdbcInvoiceRepository.java

LocalDateTime得转换器：

@Converter(autoApply = true)
public class LocalDateTimeAttributeConverter implements AttributeConverter<LocalDateTime, Timestamp> {

    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime locDateTime) {
        return (locDateTime == null ? null : Timestamp.valueOf(locDateTime));
    }

    @Override
    public LocalDateTime convertToEntityAttribute(Timestamp sqlTimestamp) {
        return (sqlTimestamp == null ? null : sqlTimestamp.toLocalDateTime());
    }
}

然而，当我在测试中执行它时，我一直得到错误：

org.springframework.jdbc.badsqlgrammarexception：PreparedStatementCallback；错误的SQL语法[插入发票值（？,？,？,？,？）]；嵌套的异常是org.postgresql.util.psqlexception：无法推断用于java.time.LocalDateTime实例的SQL类型。使用带有显式Types值的setObject（）来指定要使用的类型。

问题可能是什么？如何解决？

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.2.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-rest</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <!--For less boilerplate code-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

    <!--JSR 330 for DI in Java - @Inject-->
    <dependency>
        <groupId>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>

</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>


<repositories>
    <repository>
        <id>spring-releases</id>
        <url>https://repo.spring.io/libs-release</url>
    </repository>
</repositories>
<pluginRepositories>
    <pluginRepository>
        <id>spring-releases</id>
        <url>https://repo.spring.io/libs-release</url>
    </pluginRepository>
</pluginRepositories>

@Repository
public class JdbcInvoiceRepository {

    @Inject
    private JdbcTemplate jdbcTemplate;
    @Inject
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private SimpleJdbcInsert simpleJdbcInsert;

    @PostConstruct
    public void setupSimpleJdbcInsert(){
        simpleJdbcInsert =
                new SimpleJdbcInsert(jdbcTemplate).withTableName("invoices").usingGeneratedKeyColumns("id");
    }

    public int[] bulkSaveInvoices(List<Invoice> invoices){

        String insertSQL = "INSERT INTO invoices VALUES (:id, :exactIssueTime, :finalIssueTime, :issuer, :groupID)";
        SqlParameterSource[] sqlParams = SqlParameterSourceUtils.createBatch(invoices.toArray());

        int[] insertCounts = namedParameterJdbcTemplate.batchUpdate(insertSQL, sqlParams);

        return insertCounts;
    }

    public Long getInvoiceCount() {
        return jdbcTemplate.queryForObject("select count(*) from invoices",
                Long.class);

    }

}

编辑invoice.java

@Data
@AllArgsConstructor

@Entity
@Table(name = "invoices")
public class Invoice {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long id;

    @Column(name = "exact_iss_time")
    private LocalDateTime exactIssueTime;

    @Column(name = "final_iss_time")
    private LocalDateTime finalIssueTime;

    @Column(name = "issuer")
    private String issuer;

    @Column(name = "groupid")
    private Integer groupID;

    protected Invoice() {
    }

}

发票表定义：

    CREATE TABLE invoices (
  id      SERIAL PRIMARY KEY,
  exact_iss_time TIMESTAMP NOT NULL,
  actual_iss_time TIMESTAMP NOT NULL,
  issuer    TEXT NOT NULL,
  groupid   INTEGER NOT NULL
);

Answer 1

1）不要使用JDBC尝试批量保存JPA实体

2）改用Spring Data JPA

3）定义Spring数据存储库如下：

public interface InvoiceRepository  extends JpaRepository<Invoice, Long> {}