JPA composite foriegn key error

Question

In my code there is a one-to-one relation between an employer and its rate table where the composite primary key of employer is composite foreign (logically)+primary key of rate . I am using JPA+hibernate+DB2. here is my employer.hbm file.

<hibernate-mapping>
    <class name="com.adg.ems.domain.Employer" table="EMPLOYER">
  </composite-id class="com.adg.ems.domain.EmployerIDClass" name="employerIDClass" >
   <key-property name="empID" type="java.lang.String" length="4" column="EMPLOYER_ID"></key-property>
   <key-many-to-one name="healthPlan" column="CUSTOMER_ID" class="com.adg.ems.domain.HealthPlan" lazy="false" access="property" >
   </key-many-to-one>
   <generator class="assigned" ></generator>
  </composite-id>  
  <property name="employerName" type="java.lang.String" length="50" not-null="true">
            <column name="EMP_NAME" />
        </property>        
  <property name="employerAddress1" type="java.lang.String" length="30" not-null="true">
            <column name="ADDR1" />
        </property>
        <one-to-one name="rate" class="com.adg.ems.domain.Rate"  cascade="all" lazy="false"/>

    </class>
</hibernate-mapping>



Rate.java

@Entity
@Table(name="RATES")
public class Rate extends IDomain {

 private static final long serialVersionUID = 123456698L;

 @Transient
 private Logger logger = LoggerFactory.getLogger(Rate.class);

 @EmbeddedId
 @GeneratedValue(generator = "foreign")
 @GenericGenerator(name = "foreign", strategy = "foreign", parameters = { @Parameter(name = "property", value = "employer") })
 @AuditNotRequired
 private EmployerIDClass employerIDClass;

 @OneToOne(optional = false, cascade = CascadeType.ALL)
 @JoinColumns(value = {
   @JoinColumn(name = "EMPLOYER_ID", referencedColumnName = "EMPLOYER_ID", nullable = false, insertable = false, updatable = false),
   @JoinColumn(name = "CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID", nullable = false, insertable = false, updatable = false)}) 
 @AuditNotRequired  
 private Employer employer;

 @Digits(fraction=5, integer=1, message="Must be number and of the form (+/-)#.#####, where '#' defines a numeric value.")
 @Column(name="RATE_APY")
    private Double rateAPY = 0.0;


 @Override
 public boolean equals(Object obj) {
  logger.debug("Inside EmployerRate equals method");
  if (obj != null && obj instanceof Rate) {
   Rate employerRate = (Rate) obj;
   EmployerIDClass employerIDClass = employerRate.getEmployerIDClass();
   logger.debug("employerIDClass ---->" + employerIDClass);
   if (employerIDClass != null) {
    if (employerIDClass.equals(this.employerIDClass)) {
     return true;
    }
   }
  }
  return false;
 }

 public int hashCode() {
  logger.debug("Inside EmployerRate hashcode method");
  return new HashCodeBuilder().append(getEmployerIDClass()).toHashCode();
 }


PKCLass

@Embeddable
public class EmployerIDClass extends IDomain {

 private static final long serialVersionUID = 123455793L;

 public EmployerIDClass() {
 }

 /**
  * Constructor
  * 
  * @param empID
  * @param healthPlan
  */
 public EmployerIDClass(String empID, HealthPlan healthPlan) {
  this.empID = empID;
  this.healthPlan = healthPlan;
 }

    @NotNull
    @Size(max = 3)
    @Column(name="EMPLOYER_ID", insertable=true, updatable=true, nullable=false)
    @AuditNotRequired
    private String empID;


    @NotNull
    @ManyToOne
    @JoinColumn(name="CUSTOMER_ID", insertable=true, updatable=true, nullable=false)
    @AuditNotRequired
    private HealthPlan healthPlan;
} 

Healthplan.hbm


<hibernate-mapping>
 <class name="com.adg.ems.domain.HealthPlan" table="CUSTOMER" >

  <id name="healthPlanID" type="java.lang.String" length="3" >
   <column name="CUSTOMER_ID" />
   <generator class="assigned" />
  </id>

  <timestamp name="addDate" column="ADDDATE" unsaved-value="null"></timestamp>

  <property name="healthPlanName" type="java.lang.String" not-null="true" length="50">
   <column name="NAME" />
  </property>

 </class>
</hibernate-mapping>

My DAO class has findDomain method

public Employer findDomain(IDomain iDomain) {
  EmployerIDClass employerIDClass = ((Employer) iDomain)
    .getEmployerIDClass();
  if (employerIDClass == null)
   return null;

try {
   Employer employer3 = entityManagerDB2JTA.find(Employer.class, employerIDClass);
   logger.debug("embeddedid workeddd."+employer3);
  } catch (Exception e) {
   logger.debug("embeddedid didntk work.", e);
  }

}

The exception I am getting is:

INFO WebContainer : 1 org.hibernate.type.StringType - could not read column value from result set: employer1_28_1_; [IBM][CLI Driver] CLI0125E  Function sequence error. SQLSTATE=HY010


org.hibernate.util.JDBCExceptionReporter - could not load an entity: [com.adg.ems.domain.Employer#component[empID,healthPlan]{healthPlan=com.adg.ems.domain.HealthPlan#***, empID=***}] [select employer0_.employer_id as employer1_21_5_, employer0_.customer_id as customer2_21_5_, employer0_.emp_name as emp3_21_5_,  rate2_.employer_id as employer1_28_1_, rate2_.customer_id as customer8_28_1_, rate2_.rate_apy as rate2_28_1_ from dhs01.employer employer0_ left left outer join dhs01.rates rate2_ on employer0_.employer_id=rate2_.employer_id and employer0_.customer_id=rate2_.customer_id  where employer0_.employer_id=? and employer0_.customer_id=?]
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E  Function sequence error. SQLSTATE=HY010 at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
 at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
 at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
 at COM.ibm.db2.jdbc.app.DB2ResultSet.getString2(Unknown Source)
 at COM.ibm.db2.jdbc.app.DB2ResultSet.getString(Unknown Source)
 at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getString(WSJdbcResultSet.java:1848)
 at org.hibernate.type.StringType.get(StringType.java:41)
 at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
 at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)
 at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
 at org.hibernate.type.ComponentType.hydrate(ComponentType.java:583)
 at org.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:298)
 at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1121)
 at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:588)
 at org.hibernate.loader.Loader.doQuery(Loader.java:724)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
 at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
 at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:71)
 at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:65)
 at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3072)
 at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:434)
 at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:415)
 at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:165)
 at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:223)
 at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:126)
 at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:905)
 at org.hibernate.impl.SessionImpl.get(SessionImpl.java:842)
 at org.hibernate.impl.SessionImpl.get(SessionImpl.java:835)
 at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:182)
 at sun.reflect.GeneratedMethodAccessor382.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:618)
 at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:365)
 at $Proxy528.find(Unknown Source)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:618)
 at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:224)
 at $Proxy506.find(Unknown Source)
 at com.adg.ems.dao.EmployerDAOImpl.findDomain_aroundBody26(EmployerDAOImpl.java:393)





WARN WebContainer : 1 org.hibernate.util.JDBCExceptionReporter - SQL Error: -99999, SQLState: HY010
ERROR WebContainer : 1 org.hibernate.util.JDBCExceptionReporter - [IBM][CLI Driver] CLI0125E  Function sequence error. SQLSTATE=HY010
INFO WebContainer : 1 org.hibernate.event.def.DefaultLoadEventListener - Error performing load command
org.hibernate.exception.GenericJDBCException: could not load an entity: [com.adg.ems.domain.Employer#component[empID,healthPlan]{healthPlan=com.adg.ems.domain.HealthPlan#002, empID=77V}]
 at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.loader.Loader.loadEntity(Loader.java:1895)
 at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:71)
 at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:65)
 at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3072)
 at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:434)
 at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:415)
 at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:165)
 at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:223)
 at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:126)
 at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:905)
 at org.hibernate.impl.SessionImpl.get(SessionImpl.java:842)
 at org.hibernate.impl.SessionImpl.get(SessionImpl.java:835)
 at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:182)
 at sun.reflect.GeneratedMethodAccessor382.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:618)
 at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:365)
 at $Proxy528.find(Unknown Source)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:618)

Please help!! I am badly stuck..

Answer 1

Hi the problem was with DB2 as DB2( or hibernate as I suspect) was returning Character for String and Bigdecimal for Long. I had to use the cast() function of DB2 in the native query to fetch the records. In the beginning I tried modifying the DB2dialect to make hibernate return string for character types but it didn't work. One more thing if someone is using the DB2 V8.x(AIX) with JPA getSingleresult() method then they will have to use the DB2400Dialect as rownumber() and over() function dont work with that DB2 version.