Hi,

I am sorry if similar question has been posted before. I tried looking it up, but couldnt find anything.

I am using a legacy database and hence had to use composite id as the identifier for the table.

I have verified my code with various examples given on the internet, but I am getting the following error when i try to retrive an object using the get method


FamilyMemberRecordDAO dao = new FamilyMemberRecordDAO();
FamilyMemberRecordId id = new FamilyMemberRecordId();
id.setRrn(new Long(1));
id.setUfid("00201490");
FamilyMemberRecord obj = dao.findById(id);
System.out.println(obj.getFirstName());

public FamilyMemberRecord findById( FamilyMemberRecordId id) throws HousingPersistenceException {
try {
FamilyMemberRecord instance = (FamilyMemberRecord) getSession()
.get(FamilyMemberRecord.class, id);
return instance;
} catch (RuntimeException re) {
log.error("get failed", re);
throw re;
}
}

I am sure that it must be a very simple problem, but I am not able to identify it. Any help would be greatly appreciated.

Hibernate version: 3.0

Mapping documents:
<hibernate-mapping>
<class name="persistence.gh.FamilyMemberRecord" table="GFHAPPSFM" schema="CVTDATA" catalog="S10F4ADF">
<composite-id name="id" class="persistence.gh.FamilyMemberRecordId">
<key-property name="ufid" type="java.lang.String" column="UFID"/>
<key-property name="rrn" type="java.lang.Long" column="RRN"/>
</composite-id>
<property name="memberType" type="java.lang.String">
<column name="FAMMBRTYPE" length="1" />
</property>
<property name="firstName" type="java.lang.String">
<column name="NAMEFIRST" length="20" />
</property>
<property name="nameinit" type="java.lang.String">
<column name="NAMEINIT" length="1" />
</property>
<property name="lastName" type="java.lang.String">
<column name="NAMELAST" length="20" />
</property>
<property name="birthDate" type="java.lang.Long">
<column name="BIRTHDATE" precision="8" scale="0" />
</property>
<property name="gender" type="java.lang.String">
<column name="GENDER" length="1" />
</property>
</class>
</hibernate-mapping>

POJO
public class FamilyMemberRecord implements java.io.Serializable {

// Fields

private FamilyMemberRecordId id;
private String memberType;
private String firstName;
private String nameinit;
private String lastName;
private Long birthDate;
private String gender;
...

getter and setter methods
...

}


public class FamilyMemberRecordId implements Serializable {

private String ufid;
private Long rrn;

public Long getRrn() {
return rrn;
}
public void setRrn(Long rrn) {
this.rrn = rrn;
}
public String getUfid() {
return ufid;
}
public void setUfid(String ufid) {
this.ufid = ufid;
}
public int hashCode() {
final int PRIME = 31;
int result = 1;
result = PRIME * result + ((rrn == null) ? 0 : rrn.hashCode());
result = PRIME * result + ((ufid == null) ? 0 : ufid.hashCode());
return result;
}
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
final FamilyMemberRecordId other = (FamilyMemberRecordId) obj;
if (rrn == null) {
if (other.rrn != null)
return false;
} else if (!rrn.equals(other.rrn))
return false;
if (ufid == null) {
if (other.ufid != null)
return false;
} else if (!ufid.equals(other.ufid))
return false;
return true;
}
}


Full stack trace of any exception that occurs:

org.hibernate.exception.SQLGrammarException: could not load an entity: [edu.ufl.housing.persistence.gh.FamilyMemberRecord#component[ufid,rrn]{ufid=00201490, rrn=1}]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1359)
at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:116)
at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:101)
at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersister.java:2471)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:351)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:332)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:113)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:167)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:79)
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:621)
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:614)
at edu.ufl.housing.persistence.gh.FamilyMemberRecordDAO.findById(FamilyMemberRecordDAO.java:56)
at edu.ufl.housing.persistence.gh.Test.main(Test.java:13)
Caused by: java.sql.SQLException: [SQL0199] Keyword AS not expected. Valid tokens: (.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:520)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1363)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:252)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1881)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1711)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:396)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.doQuery(Loader.java:390)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1345)
... 12 more

Name and version of the database you are using: AS400

The generated SQL (show_sql=true):
Hibernate: select familymemb0_.UFID as UFID, familymemb0_.RRN as RRN, familymemb0_.FAMMBRTYPE as FAMMBRTYPE2_, familymemb0_.NAMEFIRST as NAMEFIRST2_, familymemb0_.NAMEINIT as NAMEINIT2_, familymemb0_.NAMELAST as NAMELAST2_, familymemb0_.BIRTHDATE as BIRTHDATE2_, familymemb0_.GENDER as GENDER2_ from S10F4ADF.CVTDATA.GFHAPPSFM familymemb0_ where familymemb0_.NAMEFIRST=?

What dialect are you using?

Um...RRN is a reserved keyword in DB2/400 representing the relative record number.

I am using org.hibernate.dialect.DB2400Dialect.

This database has been there for a long time and many applications are depended on it. So i dont think i will be able to change the column name. Anyways, I dont feel that the problem is because of that. I feel the problem is somewhere in the mapping file or the way I have defined the id class. Not sure though.

Did you try to execute the query as it is in AS400.



The exception says the keyword AS not expected.

i get the same "Keyword AS not expected. Valid tokens: (." when i try to execute the query generated by Hibernate directly on AS400. Since Hibernate generated the query, I have no control over it. What can be done now?

Ok good, do one thing change the query to



and run this on AS400, if you get the same error i will think about something else. If it works you can hack hibernate by changing the object field name "rrn" in FamilyMemberRecordId class so that it generates sql with different alias.

Thanks for your response..

I tried changing the query as you told to RRN_HACK, but I am still getting the same error. I am totally out of ideas on what is causing this. Is it the mapping? Is it because of RRN? Is is because of the way I am trying to retrive the object? Somebody please help!

Remove all the "as" and run the plain SQL and see if it works.