[hibernate-issues] [Hibernate-JIRA] Created: (ANN-515) Fields are not correctly quoted in @OneToMany relationships when specified
Vincent Jenks (JIRA)
noreply at atlassian.com
Fri Dec 22 18:32:06 EST 2006
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
Priority: Critical
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