Seam-gen, Hibernate catalog and Oracle "schema"

[almeidap]

Hi all,

We are developing a Seam 2.2.0.GA web application, which connects to an Oracle 10g database server. As this application needs to run on different environments, it should remain database independent, specially concerning SID, user and schema information.

When seam-gen is used to generate entities (using JBossTools 3.1.1 under Eclipse), it automatically includes a schema specification, which is in fact the name of the current DB user (as I read here):

@Entity
@Table(name = "MY_OBJECT", schema = "DBUSER")
public class MyObject implements java.io.Serializable {
     ...
     @Column(name = "MY_COLUMN", length = 32)
     @Length(max = 32)
     public String getMyColumn() {
          return this.myColumn;
     }
     ...
}

... with the following datasource input:

<datasources>
   <local-tx-datasource>
      <jndi-name>appDS</jndi-name>
      <use-java-context>true</use-java-context>
      <connection-url>jdbc:oracle:thin:@localhost:1521:xe</connection-url>
      <driver-class>oracle.jdbc.OracleDriver</driver-class>
      <user-name>dbuser</user-name>
      <password>dbpass</password>
   </local-tx-datasource>
</datasources>

Unfortunately, we need to remove the schema specification on entities as the user to connect on validation and production environments will be different. So, I tried to simply remove 'schema = "DBUSER"' but now, I receive an Hibernate validation error that did NOT happen when the schema was specified:

Caused by: javax.persistence.PersistenceException: org.hibernate.HibernateException: Wrong column type: MY_COLUMN, expected: varchar2(32 char)

So, when we do not specify schema on entities, where does Hibernate connect to?!? I tried to specify the default catalog for Hibernate but it does not solve the HibernateException neither the database independence:

<persistence-unit name="oflow" transaction-type="JTA">
   <provider>org.hibernate.ejb.HibernatePersistence</provider>
   <jta-data-source>java:/appDS</jta-data-source>
   <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
      <property name="hibernate.hbm2ddl.auto" value="validate"/>
      <property name="hibernate.default_catalog" value="MYUSER"/>
      ...
   </properties>
</persistence-unit>

Revelant logs:

12:24:41,162 INFO  [Version] Hibernate EntityManager 3.2.1.GA
12:24:41,175 INFO  [Version] Hibernate Annotations 3.2.1.GA
12:24:41,181 INFO  [Environment] Hibernate 3.2.4.sp1
12:24:41,186 INFO  [Environment] hibernate.properties not found
12:24:41,188 INFO  [Environment] Bytecode provider name : javassist
12:24:41,191 INFO  [Environment] using JDK 1.4 java.sql.Timestamp handling
12:24:41,561 INFO  [AnnotationBinder] Binding entity from annotated class: com.company.MyObject
12:24:41,562 INFO  [EntityBinder] Bind entity com.company.MyObject on table MY_OBJECT
...
12:24:41,718 INFO  [DatasourceConnectionProvider] Using datasource: java:/appDS
12:24:41,911 INFO  [SettingsFactory] RDBMS: Oracle, version: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
12:24:41,911 INFO  [SettingsFactory] JDBC driver: Oracle JDBC driver, version: 10.2.0.4.0
12:24:41,929 INFO  [Dialect] Using dialect: org.hibernate.dialect.Oracle10gDialect
12:24:41,934 INFO  [TransactionFactoryFactory] Transaction strategy: org.hibernate.ejb.transaction.JoinableCMTTransactionFactory
12:24:41,937 INFO  [TransactionManagerLookupFactory] instantiating TransactionManagerLookup: org.hibernate.transaction.JBossTransactionManagerLookup
12:24:41,939 INFO  [TransactionManagerLookupFactory] instantiated TransactionManagerLookup
12:24:41,939 INFO  [SettingsFactory] Automatic flush during beforeCompletion(): disabled
12:24:41,939 INFO  [SettingsFactory] Automatic session close at end of transaction: disabled
12:24:41,939 INFO  [SettingsFactory] JDBC batch size: 15
12:24:41,939 INFO  [SettingsFactory] JDBC batch updates for versioned data: disabled
12:24:41,941 INFO  [SettingsFactory] Scrollable result sets: enabled
12:24:41,941 INFO  [SettingsFactory] JDBC3 getGeneratedKeys(): disabled
12:24:41,941 INFO  [SettingsFactory] Connection release mode: auto
12:24:41,942 INFO  [SettingsFactory] Default catalog: OFLOW
12:24:41,942 INFO  [SettingsFactory] Default batch fetch size: 1
12:24:41,942 INFO  [SettingsFactory] Generate SQL with comments: disabled
12:24:41,942 INFO  [SettingsFactory] Order SQL updates by primary key: disabled
12:24:41,943 INFO  [SettingsFactory] Order SQL inserts for batching: disabled
12:24:41,943 INFO  [SettingsFactory] Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
12:24:41,945 INFO  [ASTQueryTranslatorFactory] Using ASTQueryTranslatorFactory
12:24:41,946 INFO  [SettingsFactory] Query language substitutions: {}
12:24:41,946 INFO  [SettingsFactory] JPA-QL strict compliance: enabled
12:24:41,946 INFO  [SettingsFactory] Second-level cache: enabled
12:24:41,946 INFO  [SettingsFactory] Query cache: disabled
12:24:41,946 INFO  [SettingsFactory] Cache provider: org.hibernate.cache.NoCacheProvider
12:24:41,946 INFO  [SettingsFactory] Optimize cache for minimal puts: disabled
12:24:41,946 INFO  [SettingsFactory] Structured second-level cache entries: disabled
12:24:41,952 INFO  [SettingsFactory] Echoing all SQL to stdout
12:24:41,952 INFO  [SettingsFactory] Statistics: disabled
12:24:41,952 INFO  [SettingsFactory] Deleted entity synthetic identifier rollback: disabled
12:24:41,952 INFO  [SettingsFactory] Default entity-mode: pojo
12:24:41,953 INFO  [SettingsFactory] Named query checking : enabled
12:24:42,276 INFO  [SessionFactoryImpl] building session factory
12:24:42,508 INFO  [SessionFactoryObjectFactory] Not binding factory to JNDI, no JNDI name configured
12:24:42,511 INFO  [SchemaValidator] Running schema validator
12:24:42,511 INFO  [SchemaValidator] fetching database metadata
12:24:42,676 INFO  [TableMetadata] table found: MYUSER.MY_OBJECT
12:24:42,676 INFO  [TableMetadata] columns: [my_column]
12:24:42,678 ERROR [[/oflow]] Exception lors de l'envoi de l'évènement contexte initialisé (context initialized) à l'instance de classe d'écoute (listener) org.jboss.seam.servlet.SeamListener
org.jboss.seam.InstantiationException: Could not instantiate Seam component: entityManagerFactory
     at org.jboss.seam.Component.newInstance(Component.java:2144)
     ...
Caused by: javax.persistence.PersistenceException: org.hibernate.HibernateException: Wrong column type: MY_COLUMN, expected: varchar2(32 char)
     at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:720)
     ...

I saw that this problem as already been discussed here but no real solution for it. Do you have any suggestion? Is there a way to specify Oracle schema on JDBC URL? Looked at Oracle documentation but didn't find anything...

Thanks a lot!

[almeidap]

Don't bother with the log line:

12:24:41,942 INFO  [SettingsFactory] Default catalog: OFLOW

...it's a copy/paste mistake and should be:

12:24:41,942 INFO  [SettingsFactory] Default catalog: MYUSER

Sorry! (BTW, is there a way to edit forum posts?)

[almeidap]

Ok, i found a strange behavior, with the hibernate.hbm2ddl.auto property value specified in the persistence.xml file:

• if update, Hibernate correctly maps the DB schema, without validation errors and it does not update or create new columns


• if validate, Hibernate fails validating CHAR columns

A solution could be to add columnDefinition="char" to the CHAR column annotation:

@Column(name = "MY_COLUMN", length = 32, columnDefinition="char")
@Length(max = 32)
public String getMyColumn() {
     return this.myColumn;
}

But this is not usefull as seam-gen does not generates this annotation and you will have to modify them by hand after every generation of entities.

In fact, it seems that the problem comes from the Hibernate Oracle (but not only) dialect that does not support fixed-length strings:

• http://opensource.atlassian.com/projects/hibernate/browse/HHH-2304


• http://opensource.atlassian.com/projects/hibernate/browse/HHH-2429

Tried to extend default Hibernate Oracle dialect but it didn't resolve the validation problem:

import java.sql.Types;

import org.hibernate.Hibernate;

public class FixedOracle10gDialect extends org.hibernate.dialect.Oracle10gDialect {

     /**
     * Hibernate's Oracle dialect does not support fixed-length strings.
     * See Hibernate bugs
     * <a href="http://opensource.atlassian.com/projects/hibernate/browse/HHH-2304">HHH-2304</a>
     * (unfixed since December 2006) and
     * <a href="http://opensource.atlassian.com/projects/hibernate/browse/HHH-2429">HHH-2429</a>.
     */
    public void registerCharacterTypeMappings(){
         super.registerCharacterTypeMappings();
         registerColumnType(Types.CHAR, "char($l)" );
          registerHibernateType(Types.CHAR, Hibernate.STRING.getName() );
    }
}

(credits to http://rita.wfplogistics.org/trac/browser/rita/src/org/wfp/rita/db/FixedMysqlDialect.java)

We are switching to VARCHAR2 column types but if someone has a solution for resolving the CHAR mapping problem between Hibernate and Oracle, feel free to share it! ;)

[daxxy]

The mapping problem could be remedied by use of a seam-gen.reveng.xml file.  Unfortunately jboss development studio doesn't support the seam-gen.reveng.xml file.  At least last time I asked it did not.

TDR