All geek questions in one place

Spring Loading: cannot deduce the type of SQL that will be used for the java.time.LocalDateTime instance

I have the following method (full JdbcInvoiceRepositoryclass below) executed in a Spring Boot project :

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;
}

I provided converters in:

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

COnverter for 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());
    }
}

However, when I execute it in a test, I keep getting an error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO invoices VALUES (?,?,?,?,?)]; org.postgresql.util.PSQLException nested exception: cannot call the SQL type to use for an instance of java.time.LocalDateTime. Use setObject () with an explicit type value to indicate the type to use.

org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662)    org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950)    org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40)    org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:335)    qbs.domain.model.persistance.JdbcInvoiceRepository.bulkSaveInvoices(JdbcInvoiceRepository.java:40)    qbs.domain.model.persistance.JdbcInvoiceRepository $$ FastClassBySpringCGLIB $$ 3c96bc2e.invoke()    org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)    org.springframework.aop.framework.CglibAopProxy $CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)    org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)    org.springframework.aop.framework.CglibAopProxy $DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)    qbs.domain.model.persistance.JdbcInvoiceRepository $$ EnhancerBySpringCGLIB $$ 20bcffdd.bulkSaveInvoices()    qbs.QbsApplicationTests.fillDB(QbsApplicationTests.java:33) sun.reflect.NativeMethodAccessorImpl.invoke0 ( ) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    java.lang.reflect.Method.invoke(Method.java:498) org.junit.runners.model.FrameworkMethod $1.runReflectiveCall(FrameworkMethod.java:50)    org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)    org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)    org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)    org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)    org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)    org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)    org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)    org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)    org.junit.runners.ParentRunner $3.run(ParentRunner.java:290) org.junit.runners.ParentRunner $1.schedule(ParentRunner.java:71) org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) org.junit.runners.ParentRunner.access $000 (ParentRunner.java:58) org.junit.runners.ParentRunner $2. (ParentRunner.java:268) org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)    org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)    org.junit.runners.ParentRunner.run(ParentRunner.java:363) org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)    org.junit.runner.JUnitCore.run(JUnitCore.java:137) com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)    com.intellij.rt.execution.junit.IdeaTestRunner $Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)    com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)    com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)   at sun.reflect.NativeMethodAccessorImpl.invoke0 ( ) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    java.lang.reflect.Method.invoke(Method.java:498) com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) : org.postgresql.util.PSQLException: SQL java.time.LocalDateTime. setObject() "" . org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1051)    org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:454)    org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:238)    org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:169)    org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:65)    org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.access $000 (NamedParameterBatchUpdateUtils.java:32)    org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils $1.setValues ​​(NamedParameterBatchUpdateUtils.java:48)    org.springframework.jdbc.core.JdbcTemplate $4.doInPreparedStatement(JdbcTemplate.java:960)    org.springframework.jdbc.core.JdbcTemplate $4.doInPreparedStatement(JdbcTemplate.java:950)    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)   ... 47

?

: pom.xml:

<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>

JdbcInvoiceRepository

@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
);
+4
1

1) JDBC, JPA

2) Spring Data JPA

3) Spring :

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

4) (Iterable iterable) JPA ORM .

. invoiceRepository.save(invoices)

+1

Source: https://habr.com/ru/post/1672465/

More articles:


All Articles