Hibernate如何解决Teradata数据库的模式以进行初始验证？

Question

Hibernate如何解决Teradata数据库的模式以进行初始验证？

4

环境:

Java/Spring应用程序使用JPA/Hibernate进行持久化，连接到在应用程序容器（Tomcat）中配置的Teradata数据源，并通过JNDI访问。

我使用的版本:

java: 6
spring: 3.2.4.RELEASE
hibernate.core: 4.2.4.Final
hibernate.entitymanager: 4.2.4.Final
hibernate.validator: 5.0.1.Final
springdata: 1.3.4.RELEASE
javax.validation: 1.1.0.Final

问题：

在同一台服务器上有两个Teradata数据库，它们拥有同名但列不同的表：

DatDe001.SFITEM
Columns: [iipcst, iidesc, iivend, updated_at, iisku#, created_at, item_expdt, item_effdt]

DEV_DIG_UMT.SFITEM
Columns: [iipcst, iidesc, iivend, row_updt_tms, iisku#, row_insrt_tms, item_expdt, item_effdt]

您可以看到不同的列已更新_at -> row_updt_tms 和created_at -> row_insrt_tms 。

我正在使用JNDI数据源，该数据源使用此jdbc url进行配置：

jdbc:teradata://<server_ip>/DATABASE=DEV_DIG_UMT,DBS_PORT=1025,COP=OFF,CHARSET=UTF8,TMODE=ANSI

假定jdbc连接将使用jdbc url中的DATABASE值解析表的位置。但是，当Hibernate执行初始模式验证时，即在Spring尝试创建EntityManagerFactory bean时进行上下文初始化的时候，它似乎选择了错误的一个：DatDe001.SFITEM。

2013-08-15 13:32:03,635 INFO localhost-startStop-1 org.hibernate.tool.hbm2ddl.TableMetadata - HHH000261: Table found: DatDe001.SFITEM
2013-08-15 13:32:03,635 INFO localhost-startStop-1 org.hibernate.tool.hbm2ddl.TableMetadata - HHH000037: Columns: [iipcst, iidesc, iivend, updated_at, iisku#, created_at, item_expdt, item_effdt]

因为我的JPA实体（请参见下面帖子中的实体）没有那些列，所以Hibernate验证会抛出异常（请参见摘要堆栈跟踪）：

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor#0': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in file [C:\APP\springsource\vfabric-tc-server-developer-2.9.2.RELEASE\base-instance\wtpwebapps\profile-items\WEB-INF\classes\META-INF\spring\applicationContext.xml]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:529)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
...
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in file [C:\APP\springsource\vfabric-tc-server-developer-2.9.2.RELEASE\base-instance\wtpwebapps\profile-items\WEB-INF\classes\META-INF\spring\applicationContext.xml]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1482)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:521)
...
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
            at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:924)
            at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:899)
...
Caused by: org.hibernate.HibernateException: Missing column: row_updt_tms in DatDe001.SFITEM
at org.hibernate.mapping.Table.validateColumns(Table.java:366)
at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1305)
at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:155)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:508)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1790)
at org.hibernate.ejb.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:96)
at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:914)

看到这个，我就想知道当通过JPA / hibernate执行查询语句到数据库时，这种行为是否会持续存在，或者在那种情况下它是否会指向正确的表。

然后仅出于调查目的，我将我的JPA实体更改为具有与DatDe001.SFITEM表相同的列：

@Entity
public class Sfitem implements Serializable {
    private static final long serialVersionUID = 1L;

    @EmbeddedId
    private SfitemPK id;

    @Column(name="\"iidesc\"")
    private String iidesc;

    @Column(name="\"iipcst\"")
    private BigDecimal iipcst;

    @Column(name="\"iivend\"")
    private BigDecimal iivend;

    @Temporal
    @Column(name="\"item_expdt\"")
    private Date itemExpdt;

    @Temporal
    @Column(name="\"created_at\"")
    private Date createdAt;

    @Temporal
    @Column(name="\"updated_at\"")
    private Date updatedAt;

    ...
}

我已经启动了应用程序，并且它成功加载了。现在日志看起来很好，而不是显示异常：

...
2013-08-15 14:42:52,056 INFO localhost-startStop-1 org.hibernate.tool.hbm2ddl.TableMetadata - HHH000261: Table found: DatDe001.SFITEM
2013-08-15 14:42:52,056 INFO localhost-startStop-1 org.hibernate.tool.hbm2ddl.TableMetadata - HHH000037: Columns: [iipcst, iidesc, iivend, updated_at, iisku#, created_at, item_expdt, item_effdt]
2013-08-15 14:42:52,061 DEBUG localhost-startStop-1 org.hibernate.internal.SessionFactoryImpl - Checking 0 named HQL queries
2013-08-15 14:42:52,061 DEBUG localhost-startStop-1 org.hibernate.internal.SessionFactoryImpl - Checking 0 named SQL queries
2013-08-15 14:42:52,063 TRACE localhost-startStop-1 org.hibernate.service.internal.AbstractServiceRegistryImpl - Initializing service [role=org.hibernate.service.config.spi.ConfigurationService]
2013-08-15 14:42:52,113 TRACE localhost-startStop-1 org.hibernate.service.internal.AbstractServiceRegistryImpl - Initializing service [role=org.hibernate.stat.spi.StatisticsImplementor]
...

我尝试执行对表的查询，惊讶地发现此时Hibernate指向了正确的数据库/模式：DEV_DIG_UMT，但是由于该实体现在具有其他数据库的列：DatDe001，因此查询失败。请参见日志：

2013-08-15 14:50:05,731 TRACE tomcat-http--4 org.hibernate.engine.query.spi.QueryPlanCache - Located HQL query plan in cache (SELECT o FROM Sfitem o WHERE o.id.iisku = :iisku AND o.id.itemEffdt <= :date AND coalesce(o.itemExpdt, cast('9999-12-31' as date)) >= :date)
2013-08-15 14:50:05,766 TRACE tomcat-http--4 org.hibernate.engine.query.spi.QueryPlanCache - Located HQL query plan in cache (SELECT o FROM Sfitem o WHERE o.id.iisku = :iisku AND o.id.itemEffdt <= :date AND coalesce(o.itemExpdt, cast('9999-12-31' as date)) >= :date)
2013-08-15 14:50:05,768 TRACE tomcat-http--4 org.hibernate.engine.query.spi.HQLQueryPlan - Find: SELECT o FROM Sfitem o WHERE o.id.iisku = :iisku AND o.id.itemEffdt <= :date AND coalesce(o.itemExpdt, cast('9999-12-31' as date)) >= :date
2013-08-15 14:50:05,772 TRACE tomcat-http--4 org.hibernate.engine.spi.QueryParameters - Named parameters: {iisku=387671, date=2013-08-08}
2013-08-15 14:50:05,810 DEBUG tomcat-http--4 org.hibernate.SQL - select sfitem0_."iisku#" as iisku1_0_, sfitem0_."item_effdt" as item_eff2_0_, sfitem0_."created_at" as created_3_0_, sfitem0_."iidesc" as iidesc4_0_, sfitem0_."iipcst" as iipcst5_0_, sfitem0_."iivend" as iivend6_0_, sfitem0_."item_expdt" as item_exp7_0_ from sfitem sfitem0_ where sfitem0_."iisku#"=? and sfitem0_."item_effdt"<=? and coalesce(sfitem0_."item_expdt", cast('9999-12-31' as DATE))>=?
2013-08-15 14:50:05,832 DEBUG tomcat-http--4 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - could not prepare statement [select sfitem0_."iisku#" as iisku1_0_, sfitem0_."item_effdt" as item_eff2_0_, sfitem0_."created_at" as created_3_0_, sfitem0_."iidesc" as iidesc4_0_, sfitem0_."iipcst" as iipcst5_0_, sfitem0_."iivend" as iivend6_0_, sfitem0_."item_expdt" as item_exp7_0_ from sfitem sfitem0_ where sfitem0_."iisku#"=? and sfitem0_."item_effdt"<=? and coalesce(sfitem0_."item_expdt", cast('9999-12-31' as DATE))>=?]
com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 14.00.00.21] [Error 3810] [SQLState 42S22] Column/Parameter 'DEV_DIG_UMT.sfitem0_.created_at' does not exist.
    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:307)
    at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:102)
    at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:320)
    at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:201)
    at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:121)
    at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:112)
...
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1859)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
        at org.hibernate.loader.Loader.doQuery(Loader.java:900)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
        at org.hibernate.loader.Loader.doList(Loader.java:2526)
        at org.hibernate.loader.Loader.doList(Loader.java:2512)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
        at org.hibernate.loader.Loader.list(Loader.java:2337)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)

这意味着Hibernate验证和查询执行程序的行为不同。具有正确字段的实体：

@Entity
public class Sfitem implements Serializable {
    private static final long serialVersionUID = 1L;

    @EmbeddedId
    private SfitemPK id;

    @Column(name="\"iidesc\"")
    private String iidesc;

    @Column(name="\"iipcst\"")
    private BigDecimal iipcst;

    @Column(name="\"iivend\"")
    private BigDecimal iivend;

    @Column(name="\"item_expdt\"")
    private Date itemExpdt;

    @Column(name="\"row_insrt_tms\"")
    private Timestamp rowInsrtTms;

    @Column(name="\"row_updt_tms\"")
    private Timestamp rowUpdtTms;

    ...
}

Persistence.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.TeradataDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <!-- Uncomment the following two properties for JBoss only -->
            <!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
            <!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
        </properties>
    </persistence-unit>
</persistence>

数据源和实体管理器Bean：

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
  <property name="jndiName" value="${datasource.jndiName}"/>
  <property name="lookupOnStartup" value="true"/>
  <property name="resourceRef" value="true" />
</bean>

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

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
    <property name="persistenceUnitName" value="persistenceUnit"/>
    <property name="dataSource" ref="dataSource"/>
</bean>

这是一个bug还是配置问题？有人遇到过同样的问题吗？

我不想在持久化单元或实体中配置默认模式，因为我们采用的方法是将数据源配置保留在应用程序外部，并且通过使用容器上下文中定义的JNDI数据源在单个位置。这样，当部署到不同的环境（Dev、QA、Prod等）时，我们就不需要担心了。

- raspacorp

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rob Paller · Accepted Answer

如果您提交给Teradata的SELECT查询中需要全名限定表名。

select sfitem0_."iisku#" as iisku1_0_, sfitem0_."item_effdt" as item_eff2_0_,
       sfitem0_."created_at" as created_3_0_, sfitem0_."iidesc" as iidesc4_0_,
       sfitem0_."iipcst" as iipcst5_0_, sfitem0_."iivend" as iivend6_0_,
       sfitem0_."item_expdt" as item_exp7_0_ 
 from DatDe001.SFITEM sfitem0_ /* Notice database name is included here */
where sfitem0_."iisku#"=? 
  and sfitem0_."item_effdt"<=? 
  and coalesce(sfitem0_."item_expdt", cast('9999-12-31' as DATE))>=?

编辑

您还可以构造一个字符串，在每个SELECT语句之前执行，指定您希望用作默认数据库的模式/数据库，以用于查找未在SQL中完全限定的对象：

DATABASE=?

然后可能使用参数来提供该值，就像您为WHERE子句的值一样。

编辑2

对于给定的连接字符串，您只能指定单个DATABASE参数。如果您的要求是允许应用程序前端支持的数据库具有不同的名称，则需要为应用程序需要在后端与之通信的每个数据库参数化连接字符串。