• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

EXCEPTION: org.hibernate.exception.SQLGrammarException: Unknown column 'FOLDER' in 'where clause'

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

Please let me know the fix for this exception.

EXCEPTION:

ERROR: Unknown column 'FOLDER' in 'where clause'
org.hibernate.exception.SQLGrammarException: Unknown column 'FOLDER' in 'where clause'
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at $Proxy5.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
at org.hibernate.loader.Loader.doQuery(Loader.java:899)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doList(Loader.java:2516)
at org.hibernate.loader.Loader.doList(Loader.java:2502)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
at org.hibernate.loader.Loader.list(Loader.java:2327)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1247)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
at com.onmobile.doa.ActionDetailsDOA.fetchActionDetailsByCircleIdProfileIdElementTypeAndactionId(ActionDetailsDOA.java:24)
at com.onmobile.doa.ActionDetailsDOA.main(ActionDetailsDOA.java:41)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'FOLDER' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 18 more

The DataModel sample is :

public class ActionDetails implements java.io.Serializable {

private static final long serialVersionUID = 1L;

private AId id; // containing composite primary key
private int parentId;
}

public class AId implements Serializable{
private int fldCircleId;
private int fldProfileId;
private ElementType elementType;
}

class Folder extends ActionDetails {
// contains attributes provided in ActionDetails.hbm.xml
}

class Item extends ActionDetails {
// contains attributes provided in ActionDetails.hbm.xml
}

The sample of ActionDetails.hbm.xml:

<hibernate-mapping>
<class name="com.onmobile.model.ActionDetails" table="action_details"catalog="selene2">
<composite-id name="id" class="model.ActionDetailsId">
<key-property name="fldCircleId" type="int">
<column name="fld_circle_id" />
</key-property>
<key-property name="fldProfileId" type="int">
<column name="fld_profile_id" />
</key-property>
<key-property name="elementType">
<column name="element_type" length="7" />
<type name="org.hibernate.type.EnumType">
<param name="enumClass">com.onmobile.model.ElementType</param>
<param name="type">12</param> <!-- 12 corresponds to java.sql.Types.VARCHAR -->
</type>
</key-property>
</composite-id>

<discriminator column="element_type" insert="false"/>


<property name="parentId" type="int">
<column name="parent_id" not-null="true" />
</property>
<property name="elementIndex" type="int">
<column name="element_index" not-null="true" />
</property>
<property name="level" type="int">
<column name="level" not-null="true" />
</property>
<property name="oldActionId" type="java.lang.Integer">
<column name="old_action_id" />
</property>
<property name="elementDetails" type="string">
<column name="element_details" length="1000" />
</property>

<set name="menuHighestActionIds" table="menu_highest_action_id" inverse="true" lazy="true" fetch="select">
<key>
<column name="fld_circle_id" not-null="true" />
<column name="fld_profile_id" not-null="true" />
<column name="element_type" length="7" not-null="true" />
<column name="highest_action_id" not-null="true" />
</key>
<one-to-many class="com.onmobile.model.MenuHighestActionId" />
</set>
<!-- Attributes of Folder.java -->

<subclass name="com.onmobile.model.Folder" discriminator-value="FOLDER">
<property name="folderId" type="java.lang.Integer">
<column name="folder_id" />
</property>
<property name="title" type="string">
<column name="title" length="20" />
</property>
</subclass>

<!-- Attributes of Item.java -->
<subclass name="com.onmobile.model.Item" discriminator-value="ITEM">
<property name="softId" type="java.lang.Integer">
<column name="softid" />
</property>
<property name="title" type="string">
<column name="title" length="20" />
</property>
<property name="description" type="string">
<column name="description" length="50" />
</property>
<property name="subscriptionInfo">
<column name="subscriptioninfo" length="35" />
<type name="org.hibernate.type.EnumType">
<param name="enumClass">com.onmobile.model.SubscriptionInfo</param>
<param name="type">12</param><!-- 12 corresponds to java.sql.Types.VARCHAR -->
</type>
</property>
<property name="commandType">
<column name="commandtype" length="13" />
<type name="org.hibernate.type.EnumType">
<param name="enumClass">com.onmobile.model.CommandType</param>
<param name="type">12</param> <!-- 12 corresponds to java.sql.Types.VARCHAR -->
</type>
</property>
<property name="keyword" type="string">
<column name="keyword" length="10" />
</property>
<property name="shortcode" type="java.lang.Integer">
<column name="shortcode" />
</property>
</subclass>

</class>
</hibernate-mapping>

The sample od ActionDetailsDOA.java :

public List <MenuActionIdList> fetchMenuActionIdList(int circleId, int profileId, int majorVersion, int minorVersion) throws Exception{
try{
hibSession = HibernateUtil.getSessionFactory().openSession();
tx = hibSession.beginTransaction();

Query query = hibSession.createQuery("from MenuActionIdList as maidl where maidl.id.fldCircleId="+circleId+" and maidl.id.fldProfileId="+profileId+" and maidl.id.majorVersion="+majorVersion+" and maidl.id.minorVersion="+minorVersion);
List<com.onmobile.model.MenuActionIdList> mAIdList = query.list();
tx.commit();

return mAIdList;
}catch(Exception e){
tx.rollback();
throw e;
}finally{
if(hibSession != null){
hibSession.close();
}
}
}

While using the above fetch API i am getting the exception mentioned above. The database i have used is mysql. I have mapped java Enumeration to varchar of mysql and it is inserting the rows properly. But while obtaining value from db it gives above exception. I think it is not able to map mysql varchar to java enumeration.
 
ajay mittal
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry the DOA contains following application :

public List<ActionDetails> fetchActionDetailsByCircleIdProfileIdElementTypeAndactionId(int circleId, int profileId, ElementType elementType, int actionId) throws Exception{
try{
hibSession = HibernateUtil.getSessionFactory().openSession();
tx = hibSession.beginTransaction();
Query query = hibSession.createQuery("from ActionDetails as ad where ad.id.fldCircleId="+circleId+" and ad.id.fldProfileId="+profileId+" and ad.id.elementType="+elementType+" and ad.id.actionId="+actionId);
System.out.println("AJAY");
List list = query.list();
System.out.println("MITTAL");
System.out.println(list);
tx.commit();
return null;
}catch(Exception exception){
tx.rollback();
throw exception;
}finally{
if(hibSession != null){
hibSession.close();
}
}
}
 
Marshal
Posts: 64642
225
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You would appear to have landed in the wrong forum. You would appear to have written an SQL query wrongly. I think a trip to a databases forum would help.
 
ajay mittal
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I resolved the issue, it was due to the HQL behaviour while dealing with EnumType in where clause.

I updated my HQL query to

Query query = hibSession.createQuery("from ActionDetails as ad where ad.id.fldCircleId="+circleId+" and ad.id.fldProfileId="+profileId+" and ad.id.elementType='"+elementType+"' and ad.id.actionId="+actionId);

from:
Query query = hibSession.createQuery("from ActionDetails as ad where ad.id.fldCircleId="+circleId+" and ad.id.fldProfileId="+profileId+" and ad.id.elementType="+elementType+" and ad.id.actionId="+actionId);

and the things were done for me.

Ajay
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!