java - 数据转换错误转换

Question

我遇到了奇怪的错误:

INFO: Rolled back transaction after test execution for test context [TestContext@1b1cfb87 testClass = DaoTest, testInstance = test.DaoTest@821330f, testMethod = testQuery@DaoTest, testException = javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Data conversion error converting "2016-04-25"; SQL statement:
 SELECT state, count(*)    FROM table   WHERE created_on >= ? - 1 AND created_on < ? + 1   GROUP BY state  [22018-191]
Error Code: 22018
Call:  SELECT state, count(*)    FROM table   WHERE created_on >= ? - 1 AND created_on < ? + 1   GROUP BY state 
    bind => [2 parameters bound]
Query: DataReadQuery(sql=" SELECT state, count(*)    FROM table   WHERE created_on >= ? - 1 AND created_on < ? + 1   GROUP BY state "), mergedContextConfiguration = [MergedContextConfiguration@6f43c82 testClass = DaoTest, locations = '{classpath:test.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]

我的查询是:

SELECT state, count(*) 
  FROM table 
 WHERE created_on >= ?1 - 1 AND created_on < ?2 + 1 
 GROUP BY state 

<小时/>

要重现的其他详细信息:

我有 pom.xml 的 Maven 项目:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>net.betlista</groupId>
    <artifactId>tests.so.eclipselink.date</artifactId>
    <version>1.0.0-SNAPSHOT</version>

    <dependencies>
        <!-- Persistence -->
        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>javax.persistence</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>eclipselink</artifactId>
            <version>2.5.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>3.2.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>3.2.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>3.2.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>3.2.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>3.2.6.RELEASE</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.191</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

</project>

我的Dao.class是

package test;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TemporalType;

import org.springframework.stereotype.Repository;

@Repository
public class Dao {

    @PersistenceContext
    protected EntityManager em;

    public void getCount(Date fromDate, Date toDate) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT state, count(*) ");
        sb.append("   FROM table ");
        sb.append("  WHERE created_on >= ?1 - 1 AND created_on < ?2 + 1 ");
        sb.append("  GROUP BY state ");

        Query q = em.createNativeQuery(sb.toString());
        q.setParameter(1, fromDate, TemporalType.DATE);
        q.setParameter(2, toDate, TemporalType.DATE);
        List list = q.getResultList();
    }

}

我的 DaoTest 类是:

package test;

import java.util.Date;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:test.xml"})
public class DaoTest extends AbstractTransactionalJUnit4SpringContextTests {

    @Autowired
    Dao dao;

    @Test
    public void testQuery() {
        Date now = new Date();
        dao.getCount(now, now);
    }
}

我的配置文件如下:

persistence.xml (src/test/resources/META-INF/)

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0">
    <persistence-unit name="pu" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
        <properties>
            <property name="eclipselink.weaving" value="false" />
        </properties>
    </persistence-unit>
</persistence>

ddl.sql

SET MODE Oracle;

drop ALL OBJECTS;

  CREATE TABLE TABLE 
   (    
    ID NUMBER, 
    CREATED_ON TIMESTAMP (6) DEFAULT SYSTIMESTAMP, 
    STATE VARCHAR2(25 CHAR), 
   );

dml.sql

insert into TABLE (ID,CREATED_ON,state)
    values (1111, dateadd('DAY', -1, CURRENT_DATE), 'STARTED');
insert into TABLE (ID,CREATED_ON,state)
    values (1112, dateadd('DAY', -2, CURRENT_DATE), 'NONE');

test.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">

    <context:component-scan base-package="test" />
    <context:annotation-config />

    <jdbc:embedded-database id="dataSource" type="H2">
        <jdbc:script location="classpath:ddl.sql" />
        <jdbc:script location="classpath:dml.sql" />
    </jdbc:embedded-database>

    <bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="persistenceUnitName" value="pu" />
        <property name="jpaVendorAdapter">
            <bean
                class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
                <property name="generateDdl" value="false" />
                <property name="databasePlatform"
                    value="org.eclipse.persistence.platform.database.H2Platform" />
                <property name="showSql" value="false" />
            </bean>
        </property>
    </bean>

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

</beans>

<小时/>

此外，我添加了新测试(没有 em 并使用 JdbcTempltate)，并且我有类似的问题:

@Autowired
DataSource dataSource;

public void getCountJdbc(Date fromDate, Date toDate) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.queryForList(getQuery(), fromDate, toDate);
}

其中 getQuery() 返回相同的查询，错误为:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [ SELECT state, count(*)    FROM table   WHERE created_on >= ?1 - 1 AND created_on < ?2 + 1   GROUP BY state ]; Data conversion error converting "2016-04-26 09:14:46.663"; SQL statement:
 SELECT state, count(*)    FROM table   WHERE created_on >= ?1 - 1 AND created_on < ?2 + 1   GROUP BY state  [22018-191]; nested exception is org.h2.jdbc.JdbcSQLException: Data conversion error converting "2016-04-26 09:14:46.663"; SQL statement:
 SELECT state, count(*)    FROM table   WHERE created_on >= ?1 - 1 AND created_on < ?2 + 1   GROUP BY state  [22018-191]
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:249)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:676)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:716)
    at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:803)
    at test.Dao.getCountJdbc(Dao.java:34)
    at test.DaoTest.testJdbc(DaoTest.java:28)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "2016-04-26 09:14:46.663"; SQL statement:
 SELECT state, count(*)    FROM table   WHERE created_on >= ?1 - 1 AND created_on < ?2 + 1   GROUP BY state  [22018-191]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.value.Value.convertTo(Value.java:953)
    at org.h2.expression.Operation.getValue(Operation.java:110)
    at org.h2.expression.Comparison.getValue(Comparison.java:248)
    at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:86)
    at org.h2.expression.Expression.getBooleanValue(Expression.java:178)
    at org.h2.command.dml.Select.queryGroup(Select.java:337)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:649)
    at org.h2.command.dml.Query.query(Query.java:341)
    at org.h2.command.dml.Query.query(Query.java:309)
    at org.h2.command.dml.Query.query(Query.java:36)
    at org.h2.command.CommandContainer.query(CommandContainer.java:110)
    at org.h2.command.Command.executeQuery(Command.java:201)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:110)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:646)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
    ... 35 more
Caused by: java.lang.NumberFormatException: For input string: "2016-04-26 09:14:46.663"
    at java.lang.NumberFormatException.forInputString(Unknown Source)
    at java.lang.Integer.parseInt(Unknown Source)
    at java.lang.Integer.parseInt(Unknown Source)
    at org.h2.value.Value.convertTo(Value.java:901)
    ... 49 more

Answer 1

我发现了问题:

我已经习惯了 Oracle，以至于我没有意识到语法 created_on >= ?1 - 1 在其他数据库中可能不可用。

我修正了这个问题，用 Java 进行了这个计算。我没有使用 h2 的 dataeadd 函数，因为我的代码在生产中运行在 Oracle 上，我仅将 h2 用于 JUnit。