[hibernate-issues] [Hibernate-JIRA] Commented: (ANN-515) Fields are not correctly quoted in @OneToMany relationships when specified
Emmanuel Bernard (JIRA)
noreply at atlassian.com
Thu Jan 25 19:49:20 EST 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/ANN-515?page=comments#action_25930 ]
Emmanuel Bernard commented on ANN-515:
--------------------------------------
pub should really be in either schema or catalog, not in the table name.
But besides that it should work, Hibernate quotes the table name accordingly (esp since it works for one database).
> Fields are not correctly quoted in @OneToMany relationships when specified
> --------------------------------------------------------------------------
>
> Key: ANN-515
> URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-515
> Project: Hibernate Annotations
> Type: Bug
> Versions: 3.2.1
> Environment: Any platform, any database using Hibernate 3.2.1, HB Annotations 3.2.1, and EntityManager 3.2.1. Tested in the context of JPA as persistence engine in Glassfish V1 UR1 (b14). Should be trivial to re-create in JBoss AS using attached project. Was tested with MSSQL 2000, PostgreSQL 8.1, and Progress 10.1A. Attached project configured for MSSQL 2000.
> Reporter: Vincent Jenks
> Assignee: Emmanuel Bernard
> Priority: Critical
> Fix For: 3.2.2
> Attachments: HibernateQuoteBug.zip
>
>
> When tables have hyphenated names, the database requires standard SQL syntax using double-quotes (or some other form of escape) to execute queries w/o exceptions.
> Hibernate Annotations 3.2.1 cannot handle this scenario properly when used in JPA for @OneToMany relationships. The obverse side of the join does not get quoted, nor do the foreign keys within the select clause. This causes a org.hibernate.exception.SQLGrammarException to be thrown.
> A simple example, taken from the attached Netbeans 5.5/Glassfish project is as follows.
> The entities:
> [code]
> @Entity
> @Table(name="`car-lot`")
> public class CarLot implements Serializable
> {
> @Id @GeneratedValue(strategy=GenerationType.AUTO)
> @Column(name="`lot-id`", nullable=false)
> private Integer lotId;
> @Column(name="`lot-name`", nullable=false)
> private String lotName;
> @Column(name="`lot-location`", nullable=false)
> private String lotLocation;
> @OneToMany(mappedBy="carLot", fetch=FetchType.LAZY, cascade=CascadeType.ALL)
> private List<Car> cars;
> ............................
> }
> @Entity
> @Table(name="`cars-for-sale`")
> public class Car implements Serializable
> {
> @Id @GeneratedValue(strategy=GenerationType.AUTO)
> @Column(name="`car-id`", nullable=false)
> private Integer carId;
> @Column(name="`car-make`", nullable=false)
> private String carMake;
> @Column(name="`car-model`", nullable=false)
> private String carModel;
> @Column(name="`year-manufactured`", nullable=false)
> @Temporal(TemporalType.TIMESTAMP)
> private Date yearManufactured;
>
> @ManyToOne
> @JoinColumn(name="`lot-id`")
> private CarLot carLot;
> ............................
> }
> [/code]
> Queried like so:
> [code]
> @Stateless
> public class CarBean implements CarLocal
> {
> @PersistenceContext
> private EntityManager em;
> public List<CarLot> getAllLotsAndCars()
> {
> return this.em.createQuery("select cl from CarLot cl left join fetch cl.cars").getResultList();
> }
> }
> [/code]
> Results in the following SQL being generated:
> [code]
> Hibernate:
> /* select
> cl
> from
> CarLot cl
> left join
> fetch cl.cars */ select
> carlot0_.[lot-id] as lot1_16_0_,
> cars1_.[car-id] as car1_15_1_,
> carlot0_.[lot-name] as lot2_16_0_,
> carlot0_.[lot-location] as lot3_16_0_,
> cars1_.[car-make] as car2_15_1_,
> cars1_.[car-model] as car3_15_1_,
> cars1_.[year-manufactured] as year4_15_1_,
> cars1_.[lot-id] as lot5_15_1_,
> cars1_.lot-id as lot5_0__, --lot-id FK is not [bracketed]!!
> cars1_.[car-id] as car1_0__
> from
> [car-lot] carlot0_
> left outer join
> [cars-for-sale] cars1_
> on carlot0_.[lot-id]=cars1_.lot-id --lot-id FK is not [bracketed]!!
> [/code]
> The entire stack trace in this test:
> [code]
> EJB5018: An exception was thrown during an ejb invocation on [CarBean]
> javax.ejb.EJBException
> at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3730)
> at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3630)
> at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3431)
> at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1247)
> at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:192)
> at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:118)
> at $Proxy54.getAllLotsAndCars(Unknown Source)
> at hqb.servlet.CarServlet.doGet(CarServlet.java:32)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
> at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:397)
> at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:278)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536)
> at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:240)
> at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:179)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:73)
> at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:182)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at com.sun.enterprise.web.VirtualServerPipeline.invoke(VirtualServerPipeline.java:120)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939)
> at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:137)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939)
> at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:239)
> at com.sun.enterprise.web.connector.grizzly.ProcessorTask.invokeAdapter(ProcessorTask.java:667)
> at com.sun.enterprise.web.connector.grizzly.ProcessorTask.processNonBlocked(ProcessorTask.java:574)
> at com.sun.enterprise.web.connector.grizzly.ProcessorTask.process(ProcessorTask.java:844)
> at com.sun.enterprise.web.connector.grizzly.ReadTask.executeProcessorTask(ReadTask.java:287)
> at com.sun.enterprise.web.connector.grizzly.ReadTask.doTask(ReadTask.java:212)
> at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:252)
> at com.sun.enterprise.web.connector.grizzly.WorkerThread.run(WorkerThread.java:75)
> Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
> at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
> at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
> at hqb.session.CarBean.getAllLotsAndCars(CarBean.java:56)
> 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 com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1050)
> at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:165)
> at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2766)
> at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3847)
> at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:184)
> ... 29 more
> Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
> at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
> at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at org.hibernate.loader.Loader.doList(Loader.java:2214)
> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
> at org.hibernate.loader.Loader.list(Loader.java:2090)
> at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)
> at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
> at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
> at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
> at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
> ... 39 more
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'lot'.
> at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
> at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
> at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
> at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
> at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
> 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:2211)
> ... 47 more
> StandardWrapperValve[CarServlet]: Servlet.service() for servlet CarServlet threw exception
> javax.ejb.EJBException
> at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3730)
> at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3630)
> at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3431)
> at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1247)
> at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:192)
> at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:118)
> at $Proxy54.getAllLotsAndCars(Unknown Source)
> at hqb.servlet.CarServlet.doGet(CarServlet.java:32)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
> at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:397)
> at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:278)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536)
> at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:240)
> at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:179)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:73)
> at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:182)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at com.sun.enterprise.web.VirtualServerPipeline.invoke(VirtualServerPipeline.java:120)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939)
> at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:137)
> at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566)
> at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536)
> at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939)
> at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:239)
> at com.sun.enterprise.web.connector.grizzly.ProcessorTask.invokeAdapter(ProcessorTask.java:667)
> at com.sun.enterprise.web.connector.grizzly.ProcessorTask.processNonBlocked(ProcessorTask.java:574)
> at com.sun.enterprise.web.connector.grizzly.ProcessorTask.process(ProcessorTask.java:844)
> at com.sun.enterprise.web.connector.grizzly.ReadTask.executeProcessorTask(ReadTask.java:287)
> at com.sun.enterprise.web.connector.grizzly.ReadTask.doTask(ReadTask.java:212)
> at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:252)
> at com.sun.enterprise.web.connector.grizzly.WorkerThread.run(WorkerThread.java:75)
> Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
> at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
> at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
> at hqb.session.CarBean.getAllLotsAndCars(CarBean.java:56)
> 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 com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1050)
> at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:165)
> at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2766)
> at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3847)
> at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:184)
> ... 29 more
> Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
> at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
> at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at org.hibernate.loader.Loader.doList(Loader.java:2214)
> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
> at org.hibernate.loader.Loader.list(Loader.java:2090)
> at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)
> at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
> at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
> at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
> at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
> ... 39 more
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'lot'.
> at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
> at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
> at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
> at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
> at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
> 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:2211)
> ... 47 more
> [/code]
> Some other things I have tested & tried but did not work as a work-around:
> 1. Replacing the tick (`) characters with escaped quotes (\") in table & field names in the entities.
> 2. EAGER loading the child collection of Car entities and removing the "left join fetch" clause from the JPQL.
> ...when eagerly loaded, the additional auto-generated queries are broken, e.g.
> [code]
> Hibernate:
> /* load one-to-many hqb.model.CarLot.cars */ select
> cars0_.lot-id as lot5_1_, --oops!
> cars0_.[car-id] as car1_1_,
> cars0_.[car-id] as car1_23_0_,
> cars0_.[car-make] as car2_23_0_,
> cars0_.[car-model] as car3_23_0_,
> cars0_.[year-manufactured] as year4_23_0_,
> cars0_.[lot-id] as lot5_23_0_
> from
> [cars-for-sale] cars0_
> where
> cars0_.lot-id=? --oops!
> [/code]
> The attached zip file is a very simple Netbeans 5.5 Enterprise project targeted for Glassfish...however it would be easy enough to throw the sources into an eclipse webtools project (or whatever) and test it against JBoss or anything else.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list