This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes EXCEPTION: org.hibernate.exception.SQLGrammarException: Unknown column 'FOLDER' in 'where clause' Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "EXCEPTION: org.hibernate.exception.SQLGrammarException: Unknown column Watch "EXCEPTION: org.hibernate.exception.SQLGrammarException: Unknown column New topic
Author

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

ajay mittal
Greenhorn

Joined: Nov 23, 2011
Posts: 24
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

Joined: Nov 23, 2011
Posts: 24
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();
}
}
}
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38472
    
  23
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

Joined: Nov 23, 2011
Posts: 24
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: EXCEPTION: org.hibernate.exception.SQLGrammarException: Unknown column 'FOLDER' in 'where clause'