Seam + Hibernate + MS SQL

Seam 2.1, use Hibernate as JPA provider with MS SQL 2005, SQL driver complaints syntax error

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'user'.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)

"User" is keyword in SQL Server, so need to escape it in @Table definition. Also the Entity which names a property by using "user" may causes problem too.

Seam 2.1, use Hibernate as JPA provider with MS SQL 2005 and its JDBC driver 2.0. A EJB remote interface is implemented for a Seam EntityHome. After a remote client (A plain Java main class) obtains the EntityHome by JNDI look up and try to get an instance of the entity via getInstance(), it causes ClassCastException.

@Name("measurementDataHome")
@Stateful
public class MeasurementDataHome extends EntityHome implements MeasurementData {
......
}

@Remote
public interface MeasurementData {
 
 public void setId(Object Id);
 
 public Object getId();
 
 public void setInstance(MeasurementDataEntity instance);
 
 public MeasurementDataEntity getInstance();
 
 public boolean isManaged();
 
 public String remove();
}

Exception in thread "main" java.lang.ClassCastException: java.lang.Long
 at $Proxy2.getInstance(Unknown Source)
 at Main.main(Main.java:24)

The cause is unknown. But the fix is add Override getInstance() inside EntityHome implementation:

    @Override 
    public MeasurementDataEntity getInstance() {
     return super.getInstance();
    }

Seam 2.1, use Hibernate as JPA provider with MS SQL 2005 and its JDBC driver 2.0. When User entity is trying to load a list of its associated MeasurementData entities, it throws exceptions.

javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.PropertyAccessException: Null value was assigned to a property of primitive type setter of org.unsw.bsl.dss.measurement.MeasurementData.valueNumeric
 at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:77)
 at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:83)
 at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:190)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.security.RoleBasedAuthorizationInterceptorv2.invoke(RoleBasedAuthorizationInterceptorv2.java:201)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)

The problems was that the value in the database was NULL so when Hibernate try to set the value NULL from the DB to the set method which accept a primitive value it throw an error. So in Entity, change primitive type to Object type so it accepts null.

Seam 2.1, use Hibernate as JPA provider with MS SQL 2005 and its JDBC driver 2.0. Association between MeasurementData and User is Lazy fetching. When trying to get list of measurementData which associates with a user, it throws LIE.

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: org.unsw.bsl.dss.user.User.measurementData, no session or session was closed
 at org.hibernate.collection.AbstractPersistentCollection.throwLazyInitializationException(AbstractPersistentCollection.java:380)
 at org.hibernate.collection.AbstractPersistentCollection.throwLazyInitializationExceptionIfNotConnected(AbstractPersistentCollection.java:372)
 at org.hibernate.collection.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:119)
 at org.hibernate.collection.PersistentBag.size(PersistentBag.java:248)
 at DomainTest.testReadUser(DomainTest.java:77)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

Using EntityHome for entities in long-running contexts

How to avoid LazyInitializationException

Seam 2.1, use Hibernate as JPA provider with MS SQL 2005 and its JDBC driver 2.0. Hibernate generates wrong SQL, thus causes MS SQL driver throws exception. For example, ClinicalData and Patient are two entities associate with, the generated SQL mistakes clinicalData and patient (property field name) as column names.

Hibernate: 
    select
        measuremen0_.Id as Id4_0_,
        measuremen0_.addDateTime as addDateT2_4_0_,
        measuremen0_.clinicalData as clinical3_4_0_, <--- Wrong
        measuremen0_.patient as patient4_0_, <--- Wrong
        measuremen0_.valueNumeric as valueNum5_4_0_,
        measuremen0_.voidedDateTime as voidedDa6_4_0_ 
    from
        UserClinicalData measuremen0_ 
    where
        measuremen0_.Id=?
ERROR [org.hibernate.util.JDBCExceptionReporter] Invalid column name 'clinicalData'.

Hibernate: 
    select
        measuremen0_.UserId as UserId3_,
        measuremen0_.Id as Id3_,
        measuremen0_.Id as Id4_2_,
        measuremen0_.AddDateTime as AddDateT2_4_2_,
        measuremen0_.ClinicalDataId as Clinical6_4_2_, <--- After fixed
        measuremen0_.UserId as UserId4_2_, <--- After fixed
        measuremen0_.ValueNumeric as ValueNum3_4_2_,
        measuremen0_.VoidedDateTime as VoidedDa4_4_2_,
        clinicalda1_.Id as Id5_0_,
        clinicalda1_.ClinicalDataCodeId as Clinical3_5_0_,
        clinicalda1_.Type as Type5_0_,
        codedictio2_.Id as Id1_1_,
        codedictio2_.CodeDictionaryCode as CodeDict2_1_1_ 
    from
        UserClinicalData measuremen0_ 
    left outer join
        ClinicalData clinicalda1_ 
            on measuremen0_.ClinicalDataId=clinicalda1_.Id 
    left outer join
        CodeDictionary codedictio2_ 
            on clinicalda1_.ClinicalDataCodeId=codedictio2_.Id 
    where
        measuremen0_.UserId=?

The problem occurred when mapping notations i.e. @ManyToOne applied in getter/setter methods. The problem disappear after moving all notations to property field level.