Hello!
I have been working with Hibernate2/3 for over a year now, but i am currently stuck which a problem, which seems to be caused by invalid sql.
I am trying to build a kind of hierarchical map-structure with two tables data_nodes/data_node_children (see below for details). Every node can have subnodes which are associated with a string key.
The node contains a ValueHolder, which is mapped as a component. So the ValueHolder's type and value field are part of the node's table, but in Java the ValueHolder appears as separate object.
When i issue the following query in the hql editor of the HibernateTools
from LiquidDataNode co WHERE co.children['year'].valueHolder.value LIKE '%200%'
the following SQL is generated:
select liquiddata0_.id as id, liquiddata0_.is_value_node as is2_405_, liquiddata0_.value as value405_, liquiddata0_.value_type as value4_405_
from data_nodes liquiddata0_, data_node_children children1_, data_nodes liquiddata2_
where children1_.childId=liquiddata2_.id and liquiddata0_.id=children1_.id and children1_.dataNodeKey = 'year' and (children1_.value like '%200%')
which causes a Error 1054 Unknown column 'children1_.value' (as children1_ is an alias of table data_node_children)
The correct SQL would end in
... and (liquiddata2_.value like '%200%')
as liquiddata2_ is the alias to the table data_nodes in which the component valueHolder is stored.
As reference, the schema generated by Hibernate is like
data_nodes: id, is_value_node, value, value_type
data_node_children: id, childId, dataNodeKey
The java objects are very basic with setter/getter generated by hbm2java.
To me everything seems to be correct, and all other operations work as expected. Only this query fails because of the wrong sql...
Can anybody give me a hint? Did i make a mistake or why does Hibernate generate this SQL?
Thank you very much for your help!
Sebastian
Hibernate version: 3.0.5
Mapping documents:
<class name="net.sus.liquid.contentobject.LiquidDataNode" table="data_nodes"
dynamic-update="true">
<id name="id" column="id" type="java.lang.Long">
<generator class="hilo"/>
</id>
<property name="isValueNode" column="is_value_node" type="boolean"
not-null="true"/>
<component name="valueHolder"
class="net.sus.liquid.contentobject.LiquidValueHolder">
<property name="value" column="value" type="java.lang.String"/>
<property name="valueType" column="value_type" type="java.lang.String"/>
</component>
<map name="children" lazy="true" table="data_node_children"
cascade="all-delete-orphan">
<key column="id" />
<index column="dataNodeKey" type="string" />
<many-to-many column="childId" unique="true"
class="net.sus.liquid.contentobject.LiquidDataNode"/>
</map>
</class>
Full stack trace of any exception that occurs:
From within HibernateTools HQL Editor:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.console.HQLQueryPage.getList(HQLQueryPage.java:30)
at org.hibernate.eclipse.console.views.QueryPageViewer$ContentProviderImpl.getElements(QueryPageViewer.java:80)
at org.eclipse.jface.viewers.StructuredViewer.getRawChildren(StructuredViewer.java:765)
at org.eclipse.jface.viewers.TableViewer.getRawChildren(TableViewer.java:1046)
at org.eclipse.jface.viewers.StructuredViewer.getFilteredChildren(StructuredViewer.java:707)
at org.eclipse.jface.viewers.StructuredViewer.getSortedChildren(StructuredViewer.java:822)
at org.eclipse.jface.viewers.TableViewer.internalRefreshAll(TableViewer.java:762)
at org.eclipse.jface.viewers.TableViewer.internalRefresh(TableViewer.java:712)
at org.eclipse.jface.viewers.TableViewer.internalRefresh(TableViewer.java:701)
at org.eclipse.jface.viewers.StructuredViewer$7.run(StructuredViewer.java:1171)
at org.eclipse.jface.viewers.StructuredViewer.preservingSelection(StructuredViewer.java:1108)
at org.eclipse.jface.viewers.StructuredViewer.refresh(StructuredViewer.java:1169)
at org.eclipse.jface.viewers.StructuredViewer.refresh(StructuredViewer.java:1128)
at org.eclipse.jface.viewers.TableViewer.inputChanged(TableViewer.java:662)
at org.eclipse.jface.viewers.ContentViewer.setInput(ContentViewer.java:248)
at org.eclipse.jface.viewers.StructuredViewer.setInput(StructuredViewer.java:1324)
at org.hibernate.eclipse.console.views.QueryPageViewer.createTable(QueryPageViewer.java:174)
at org.hibernate.eclipse.console.views.QueryPageViewer.createControl(QueryPageViewer.java:153)
at org.hibernate.eclipse.console.views.QueryPageViewer.<init>(QueryPageViewer.java:111)
at org.hibernate.eclipse.console.views.QueryPageTabView.rebuild(QueryPageTabView.java:83)
at org.hibernate.eclipse.console.views.QueryPageTabView$1.contentsChanged(QueryPageTabView.java:52)
at org.hibernate.eclipse.console.views.QueryPageTabView$1.intervalAdded(QueryPageTabView.java:57)
at javax.swing.AbstractListModel.fireIntervalAdded(AbstractListModel.java:130)
at org.hibernate.console.QueryPageModel.add(QueryPageModel.java:50)
at org.hibernate.console.KnownConfigurations$3.queryPageCreated(KnownConfigurations.java:167)
at org.hibernate.console.ConsoleConfiguration.fireQueryPageCreated(ConsoleConfiguration.java:266)
at org.hibernate.console.ConsoleConfiguration.executeHQLQuery(ConsoleConfiguration.java:247)
at org.hibernate.eclipse.console.actions.ExecuteHQLAction.execute(ExecuteHQLAction.java:99)
at org.hibernate.eclipse.console.actions.ExecuteHQLAction.run(ExecuteHQLAction.java:94)
at org.eclipse.jface.action.Action.runWithEvent(Action.java:996)
at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:538)
at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:488)
at org.eclipse.jface.action.ActionContributionItem$6.handleEvent(ActionContributionItem.java:441)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:82)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1012)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:2778)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2472)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1570)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1534)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:306)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:143)
at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:103)
at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:228)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:156)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.eclipse.core.launcher.Main.invokeFramework(Main.java:315)
at org.eclipse.core.launcher.Main.basicRun(Main.java:268)
at org.eclipse.core.launcher.Main.run(Main.java:942)
at org.eclipse.core.launcher.Main.main(Main.java:926)
Caused by: java.sql.SQLException: Unknown column 'children1_.value' in 'where clause'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 58 more
Name and version of the database you are using: MySql 4.1.11a-4 (Debian)
The generated SQL (show_sql=true):
select liquiddata0_.id as id, liquiddata0_.is_value_node as is2_405_, liquiddata0_.value as value405_, liquiddata0_.value_type as value4_405_ from data_nodes liquiddata0_, data_node_children children1_, data_nodes liquiddata2_ where children1_.childId=liquiddata2_.id and liquiddata0_.id=children1_.id and children1_.dataNodeKey = 'year' and (children1_.value like '%200%')
Debug level Hibernate log excerpt:
n.a. (how can i set the debug level within hibernate tools for eclipse?)
Login
FAQ
Search
