Hibernate version: 3.2.3.ga / 3.2.4.ga
Name and version of the database you are using: postgreSQL 8.2.4
Description:
The problem it's that
hibernate it's omitting the second sql join to the Promotions table and
PostgreSQL it's generating sintax error. For me it
seens like an error in the Criteria API when using
Property.forName("id").in(criteria) . I've solved my problem by executing the second criteria resulting in two separated querys to the database(
Restrictions.in(criteria.getExecutableCriteria(getSession()).list())) and in this case the Criteria API works as desired. I wanted to post this behaviour here first before going to jira and see it somebody can help me with this.
org/hibernate/criteria/test/DemographicArea.java
Code:
/**
*
*/
package org.hibernate.criteria.test;
import java.io.Serializable;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Version;
import org.hibernate.annotations.Type;
@Entity
@Table(name="DemographicAreas")
public class DemographicArea implements Serializable {
private static final long serialVersionUID = 6583587093102315973L;
private Long id;
private int version;
private MarketArea marketArea;
private String description;
/**
* @return the description
*/
@Type(type="text")
public String getDescription() {
return description;
}
/**
* @return the id
*/
@Id
@Column(name="demopraphicAreaId")
public Long getId() {
return id;
}
/**
* @return the marketArea
*/
@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="marketAreaId")
public MarketArea getMarketArea() {
return marketArea;
}
/**
* @return the version
*/
@Version
public int getVersion() {
return version;
}
/**
* @param description the description to set
*/
public void setDescription(String description) {
this.description = description;
}
/**
* @param id the id to set
*/
public void setId(Long id) {
this.id = id;
}
/**
* @param marketArea the marketArea to set
*/
public void setMarketArea(MarketArea marketArea) {
this.marketArea = marketArea;
}
/**
* @param version the version to set
*/
public void setVersion(int version) {
this.version = version;
}
}
org/hibernate/criteria/test/Promotion.javaCode:
/**
*
*/
package org.hibernate.criteria.test;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Version;
/**
*
*/
@Entity
@Table(name="Promotions")
public class Promotion implements Serializable{
private static final long serialVersionUID = 3929844478350903342L;
private Long id;
private int version;
private Date startDate;
private Date endDate;
/**
* @return the id
*/
@Id
@Column(name="promotionId")
@GeneratedValue(strategy=GenerationType.IDENTITY)
public Long getId() {
return id;
}
/**
* @return the endDate
*/
@Temporal(value=TemporalType.DATE)
@Basic(optional=false)
public Date getEndDate() {
return endDate;
}
/**
* @return the startDate
*/
@Temporal(value=TemporalType.DATE)
@Basic(optional=false)
public Date getStartDate() {
return startDate;
}
/**
* @return the version
*/
@Version
public int getVersion() {
return version;
}
/**
* @param version the version to set
*/
public void setVersion(int version) {
this.version = version;
}
/**
* @param endDate the endDate to set
*/
public void setEndDate(Date endDate) {
this.endDate = endDate;
}
/**
* @param id the id to set
*/
public void setId(Long id) {
this.id = id;
}
/**
* @param startDate the startDate to set
*/
public void setStartDate(Date startDate) {
this.startDate = startDate;
}
}
org/hibernate/criteria/test/MarketArea.javaCode:
/**
*
*/
package org.hibernate.criteria.test;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Version;
/**
*
*/
@Entity
@Table(name="marketAreas")
public class MarketArea implements Serializable {
private static final long serialVersionUID = 2512785432105726917L;
private Long id;
private int version;
private String name;
private List<Promotion> promotions = new ArrayList<Promotion>();
private List<DemographicArea> demographicAreas=new ArrayList<DemographicArea>();
/**
* @return the id
*/
@Id
@Column(name="marketAreaId")
@GeneratedValue(strategy=GenerationType.IDENTITY)
public Long getId() {
return id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @return the promotions
*/
@ManyToMany
public List<Promotion> getPromotions() {
return promotions;
}
/**
* @return the version
*/
@Version
public int getVersion() {
return version;
}
/**
* @return the demographicArea
*/
@OneToMany(mappedBy="marketArea")
public List<DemographicArea> getDemographicAreas() {
return demographicAreas;
}
/**
* @param id the id to set
*/
public void setId(Long id) {
this.id = id;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @param promotions the promotions to set
*/
public void setPromotions(List<Promotion> promotions) {
this.promotions = promotions;
}
/**
* @param version the version to set
*/
public void setVersion(int version) {
this.version = version;
}
/**
* @param demographicAreas
*/
public void setDemographicAreas(List<DemographicArea> demographicAreas) {
this.demographicAreas = demographicAreas;
}
}
This it's the code that reproduce my scenario
org/hibernate/criteria/test/Scenario.javaCode:
/**
*
*/
package org.hibernate.criteria.test;
import java.util.Date;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.tool.hbm2ddl.SchemaExport;
/**
*
*/
public class Scenario {
public static void main(String[] args) {
AnnotationConfiguration cfg=new AnnotationConfiguration();
cfg.configure();
if(args.length>0)
new SchemaExport(cfg).create(false, true);
SessionFactory factory=cfg.buildSessionFactory();
Session session=factory.openSession();
Date toDate=new Date();
DetachedCriteria criteria=getPromotionsByEntityCriteria();
criteria.add(Restrictions.eq("da.description", "description"));
criteria.setProjection(Projections.distinct(Projections.property("promos.id")));
//Promotion Criteria
Criteria promotionCriteria = session.createCriteria(Promotion.class,"p")
.add(Property.forName("id").in(criteria));
//Add Date restrictions
promotionCriteria.add(
Restrictions.and(
Restrictions.ge("p.endDate", toDate),
Restrictions.le("p.startDate", toDate)
));
System.out.println(promotionCriteria.list());
}
private static DetachedCriteria getPromotionsByEntityCriteria()
{
DetachedCriteria criteria=DetachedCriteria.forClass(MarketArea.class,"ma");
criteria.createAlias("ma.promotions", "promos");
criteria.createAlias("ma.demographicAreas", "da");
return criteria;
}
}
This it's the configuration file used in this scenario
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="hibernate.cache.use_query_cache">false</property>
<property name="hibernate.connection.pool_size">1</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.DriverManagerConnectionProvider</property>
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>
<property name="hibernate.bytecode.use_reflection_optimizer">false</property>
<property name="hibernate.default_schema">public</property>
<property name="hibernate.connection.url">${dbURL}</property>
<property name="hibernate.connection.username">${userName}</property>
<property name="hibernate.connection.password">${password}</property>
<property name="hibernate.show_sql">true</property>
<mapping class="org.hibernate.criteria.test.Promotion"/>
<mapping class="org.hibernate.criteria.test.DemographicArea"/>
<mapping class="org.hibernate.criteria.test.MarketArea"/>
</session-factory>
</hibernate-configuration>
Code:
select this_.promotionId as promotio1_0_0_, this_.endDate as endDate0_0_, this_.startDate as startDate0_0_, this_.version as version0_0_ from public.Promotions this_ where this_.promotionId in (select distinct promos1_.promotionId as y0_ from public.marketAreas this0__ where da2_.description=?) and (this_.endDate>=? and this_.startDate<=?)
The desired generated SQLCode:
select this_.promotionId as promotio1_0_0_, this_.endDate as endDate0_0_, this_.startDate as startDate0_0_, this_.version as version0_0_ from public.Promotions this_ where this_.promotionId in (select distinct promos1_.promotionId as y0_ from public.marketAreas this_ inner join public.marketAreas_Promotions promotions4_ on this_.marketAreaId=promotions4_.marketAreas_marketAreaId inner join public.Promotions promos1_ on promotions4_.promotions_promotionId=promos1_.promotionId inner join public.DemographicAreas da2_ on this_.marketAreaId=da2_.marketAreaId where da2_.description=?) and (this_.endDate>=? and this_.startDate<=?)
stack trace:Code:
May 11, 2007 3:45:14 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
May 11, 2007 3:45:14 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: missing FROM-clause entry in subquery for table "promos1_"
Exception in thread "main" 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:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at org.hibernate.criteria.test.Scenario.main(Scenario.java:45)
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry in subquery for table "promos1_"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 6 more
Regards,
Login
FAQ
Search
