Meaningless Drivel is fun!*
The moose likes Object Relational Mapping and the fly likes Hibernate .list() throwing SQLGrammarException Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate .list() throwing SQLGrammarException" Watch "Hibernate .list() throwing SQLGrammarException" New topic
Author

Hibernate .list() throwing SQLGrammarException

Seema Sajnani
Greenhorn

Joined: Sep 14, 2011
Posts: 8
Here are my DTO classes and the mapping files
public class TopicDTO implements java.io.Serializable {


// Fields

private Long topicId;
private String topicDescription;
private TopicTypeDTO topicType;
private Set<SubTopicDTO> subTopics;
getter/setters
}


public class SubTopicDTO implements java.io.Serializable {


// Fields

private Long subTopicId;
private String subTopicDescription;
private Long topic;
private Set<SpecialSubTopicDTO> specialSubTopics;
getter/setters
}

public class SpecialSubTopicDTO implements java.io.Serializable {


// Fields

private Long specialSubTopicId;
private String specialSubTopicDescription;
private Long subTopic;
}
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.callDisposition.dto.TopicDTO" table="DSPS_TPC_TYP" lazy="false">
<id name="topicId" type="java.lang.Long">
<column name="DSPS_TPC_TYP_ID" />
</id>
<property name="topicDescription" type="java.lang.String">
<column name="DSPS_TPC_TYP_DSC" length="100" />
</property>
<many-to-one name="topicType" class="com.callDisposition.dto.TopicTypeDTO" >
<column name="DSPS_PRVG_TYP_CDE" length="6" not-null="true" />
</many-to-one>
<property name="active" type="java.lang.String">
<column name="ACTV_FLG" length="1" />
</property>
<property name="rlseNum" type="java.lang.Long">
<column name="RLSE_NUM" length="22" not-null="true" />
</property>
<property name="cratUserId" type="java.lang.String">
<column name="CRAT_USER_ID" length="8" not-null="true" />
</property>
<property name="cratTsp" type="java.sql.Timestamp">
<column name="CRAT_TSP" length="11" not-null="true" />
</property>
<property name="expireRlseNum" type="java.lang.String">
<column name="EXPIRE_RLSE_NUM" length="22" />
</property>
<property name="updUserId" type="java.lang.String">
<column name="UPD_USER_ID" length="8" not-null="true" />
</property>
<property name="updTsp" type="java.sql.Timestamp">
<column name="UPD_TSP" length="11" not-null="true" />
</property>
<set name="subTopics" fetch="select" table="DSPS_SBTPC_TYP">
<key>
<column name="DSPS_TPC_TPY_ID" not-null="true" />
</key>
<one-to-many class="com.callDisposition.dto.SubTopicDTO" />
</set>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.callDisposition.dto.SubTopicDTO" table="DSPS_SBTPC_TYP" lazy="false">
<id name="subTopicId" type="java.lang.Long">
<column name="DSPS_SBTPC_TYP_ID" />
</id>
<property name="subTopicDescription" type="java.lang.String">
<column name="DSPS_SBTPC_TYP_DSC" length="100" />
</property>
<property name="topic" type="java.lang.Long">
<column name="DSPS_TPC_TYP_ID" length="6" />
</property>
<property name="active" type="java.lang.String">
<column name="ACTV_FLG" length="1" />
</property>
<property name="rlseNum" type="java.lang.Long">
<column name="RLSE_NUM" length="22" not-null="true" />
</property>
<property name="cratUserId" type="java.lang.String">
<column name="CRAT_USER_ID" length="8" not-null="true" />
</property>
<property name="cratTsp" type="java.sql.Timestamp">
<column name="CRAT_TSP" length="11" not-null="true" />
</property>
<property name="expireRlseNum" type="java.lang.Long">
<column name="EXPIRE_RLSE_NUM" length="22" />
</property>
<property name="updUserId" type="java.lang.String">
<column name="UPD_USER_ID" length="8" not-null="true" />
</property>
<property name="updTsp" type="java.sql.Timestamp">
<column name="UPD_TSP" length="11" not-null="true" />
</property>
<set name="specialSubTopics" fetch="join">
<key>
<column name="DSPS_SBTPC_TPY_ID" not-null="false" />
</key>
<one-to-many class="com.callDisposition.dto.SpecialSubTopicDTO" />
</set>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.callDisposition.dto.SpecialSubTopicDTO" table="DSPS_SPCL_SBTPC_TYP" lazy="false">
<id name="specialSubTopicId" type="java.lang.Long">
<column name="DSPS_SPCL_SBTPC_TYP_ID" />
<generator class="assigned" />
</id>
<property name="specialSubTopicDescription" type="java.lang.String">
<column name="DSPS_SPCL_SBTPC_TYP_DSC" length="100" />
</property>
<property name="subTopic" type="java.lang.Long">
<column name="DSPS_SBTPC_TYP_ID" length="6" />
</property>
<property name="active" type="java.lang.String">
<column name="ACTV_FLG" length="1" />
</property>
<property name="rlseNum" type="java.lang.Long">
<column name="RLSE_NUM" length="22" not-null="true" />
</property>
<property name="cratUserId" type="java.lang.String">
<column name="CRAT_USER_ID" length="8" not-null="true" />
</property>
<property name="cratTsp" type="java.sql.Timestamp">
<column name="CRAT_TSP" length="11" not-null="true" />
</property>
<property name="expireRlseNum" type="java.lang.Long">
<column name="EXPIRE_RLSE_NUM" length="22" />
</property>
<property name="updUserId" type="java.lang.String">
<column name="UPD_USER_ID" length="8" not-null="true" />
</property>
<property name="updTsp" type="java.sql.Timestamp">
<column name="UPD_TSP" length="11" not-null="true" />
</property>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.callDisposition.dto.TopicTypeDTO" table="DSPS_PRVG_TYP" lazy="false">
<id name="typeCode" type="java.lang.Long">
<column name="DSPS_PRVG_TYP_CDE" />
</id>
<property name="typeDescription" type="java.lang.String">
<column name="DSPS_PRVG_TYP_DSC" length="100" />
</property>
<property name="active" type="java.lang.String">
<column name="ACTV_FLG" length="1" />
</property>
<property name="rlseNum" type="java.lang.Long">
<column name="RLSE_NUM" length="22" not-null="true" />
</property>
<property name="cratUserId" type="java.lang.String">
<column name="CRAT_USER_ID" length="8" not-null="true" />
</property>
<property name="cratTsp" type="java.sql.Timestamp">
<column name="CRAT_TSP" length="11" not-null="true" />
</property>
<property name="expireRlseNum" type="java.lang.Long">
<column name="EXPIRE_RLSE_NUM" length="22" />
</property>
<property name="updUserId" type="java.lang.String">
<column name="UPD_USER_ID" length="8" not-null="true" />
</property>
<property name="updTsp" type="java.sql.Timestamp">
<column name="UPD_TSP" length="11" not-null="true" />
</property>
</class>
</hibernate-mapping>

This is my DAO class
public class CallDispositionDAO {


public CallDispositionVO getTopicsForType(Long type) throws RuntimeException {
String METHOD_NAME = "getTopicsForType";
CallDispositionVO callVO = new CallDispositionVO();
LOG.debug(LOG_AREA+ "methodName = "+METHOD_NAME+" | Entry");
try{

LOG.debug(LOG_AREA+ "methodName = "+METHOD_NAME+" | Initializing the transaction");
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

LOG.debug(LOG_AREA+ "methodName = "+METHOD_NAME+" | Calling the query");
//String SQL_QUERY = "from TopicDTO topic left join topic.topicType topicType where topicType.typeCode = "+type;
String SQL_QUERY = "from TopicDTO";
Query resultSet = session.createQuery(SQL_QUERY);


List resultList = resultSet.list();

tx.commit();
session.close();
LOG.debug(LOG_AREA+ "methodName = "+METHOD_NAME+" | Session closed");
}catch(Exception e){
LOG.error(LOG_AREA+ "methodName = "+METHOD_NAME+" | Exception : "+e);
throw new RuntimeException();
}
LOG.debug(LOG_AREA+ "methodName = "+METHOD_NAME+" | Exit");
return callVO;
}
}

This is exception i get when .list() is executed
[7/12/13 11:16:32:490 CDT] 00000020 SystemOut O Hibernate: select topicdto0_.DSPS_TPC_TYP_ID as DSPS1_15_, topicdto0_.DSPS_TPC_TYP_DSC as DSPS2_15_, topicdto0_.DSPS_PRVG_TYP_CDE as DSPS3_15_, from CRS3DEVL.DSPS_TPC_TYP topicdto0_
[7/12/13 11:16:32:523 CDT] 00000020 DMAdapter I com.ibm.ws.ffdc.impl.DMAdapter getAnalysisEngine FFDC1009I: Analysis Engine using data base: C:\Program Files (x86)\ibm\SDP\runtimes\base_v7\profiles\AppSrv01\properties\logbr\ffdc\adv\ffdcdb.xml
[7/12/13 11:16:32:538 CDT] 00000020 WSRdbManagedC W DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: com.ibm.db2.jcc.am.SqlSyntaxErrorException: CURRENT SQLID(*PRIOR FAILURE*);DBT5
at com.ibm.db2.jcc.am.fd.a(fd.java:676)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.o.f(o.java:540)
at com.ibm.db2.jcc.am.o.a(o.java:495)
at com.ibm.db2.jcc.am.Sqlca.getJDBCMessage(Sqlca.java:334)
at com.ibm.db2.jcc.am.SqlExceptionContainer.getMessage(SqlExceptionContainer.java:78)
at com.ibm.db2.jcc.am.SqlSyntaxErrorException.getMessage(SqlSyntaxErrorException.java:52)
at java.lang.Throwable.getLocalizedMessage(Throwable.java:131)
at java.lang.Throwable.toString(Throwable.java:247)
at java.lang.Throwable.printStackTrace(Throwable.java:332)
at java.lang.Throwable.printStackTrace(Throwable.java:230)
at com.ibm.ws.rsadapter.AdapterUtil.stackTraceToString(AdapterUtil.java:1365)
at com.ibm.ws.rsadapter.AdapterUtil.stackTraceToString(AdapterUtil.java:1375)
at com.ibm.ws.rsadapter.AdapterUtil.stackTraceToString(AdapterUtil.java:1350)
at com.ibm.ws.rsadapter.DiagnosticModuleForAdapter.ffdcDumpDefaultAdapter(DiagnosticModuleForAdapter.java:129)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:600)
at com.ibm.ws.ffdc.DiagnosticModule.getDataForDirective(DiagnosticModule.java:305)
at com.ibm.ws.ffdc.DiagnosticModule.getDataForDirectives(DiagnosticModule.java:279)
at com.ibm.ws.ffdc.DiagnosticModule.dumpComponentData(DiagnosticModule.java:144)
at com.ibm.ws.ffdc.impl.DMAdapter.processDM(DMAdapter.java:123)
at com.ibm.ws.ffdc.impl.DMAdapter.formatTo(DMAdapter.java:114)
at com.ibm.ffdc.util.provider.IncidentLogger.writeIncidentTo(IncidentLogger.java:63)
at com.ibm.ws.ffdc.impl.FfdcProvider.logIncident(FfdcProvider.java:161)
at com.ibm.ws.ffdc.impl.FfdcProvider.logIncident(FfdcProvider.java:95)
at com.ibm.ffdc.util.provider.FfdcProvider.log(FfdcProvider.java:239)
at com.ibm.ws.ffdc.impl.FfdcProvider.log(FfdcProvider.java:103)
at com.ibm.ffdc.util.provider.IncidentEntry.log(IncidentEntry.java:96)
at com.ibm.ffdc.util.provider.Ffdc.log(Ffdc.java:90)
at com.ibm.ws.ffdc.FFDCFilter.processException(FFDCFilter.java:114)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:735)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.callDisposition.dao.CallDispositionDAO.getTopicsForType(CallDispositionDAO.java:54)
at com.callDisposition.business.CallDispositionBusinessImpl.GetTopicsforType(CallDispositionBusinessImpl.java:36)
at com.callDisposition.pa.CallDispositionPA.execute(CallDispositionPA.java:38)
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30383
    
150

What happens when you run that SQL at the database command line?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Seema Sajnani
Greenhorn

Joined: Sep 14, 2011
Posts: 8
There was a issue related to the data source set up. It got fixed
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Hibernate .list() throwing SQLGrammarException