I think I have found a problem with the PostgreSQL Dialect when restricting a query by using a parent property and a restriction in a child collection.
Hibernate version: 3.2
Name and version of the database you are using: Postgres 8.2
Here are the persistent classes:
I have four persistent class.
Code:
@Entity
@Table(name = "REGION")
public class Region {
private Integer id;
private String name;
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "REGION_ID")
public Integer getId() { return id; }
void setId(Integer id) { this.id = id; }
@Column(name = "NAME")
public String getName() { return name; }
void setName(String name) { this.name = name; }
}
Code:
@Entity
@Table(name = "SCHOOL")
public class School {
private Integer id;
private String name;
private Region region;
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "SCHOOL_ID")
public Integer getId() { return id; }
void setId(Integer id) { this.id = id; }
@Column(name = "NAME")
public String getName() { return name; }
void setName(String name) { this.name = name; }
@ManyToOne @JoinColumn(name = "REGION_ID")
Region getRegion() { return region; }
void setRegion(Region region) { this.region = region; }
}
Code:
@Entity
@Table(name = "PUPIL")
public class Pupil {
private Integer id;
private Set<ExamResult> results;
private String name;
private School school;
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "PUPIL_ID")
public Integer getId() { return id; }
void setId(Integer id) { this.id = id; }
@CollectionOfElements @OneToMany(mappedBy = "pupil")
public Set<ExamResult> getResults() { return results; }
void setResults(Set<ExamResult> results) { this.results = results; }
@ManyToOne @JoinColumn(name = "SCHOOL_ID")
School getSchool() { return school; }
void setSchool(School school) { this.school = school; }
@Column(name = "NAME")
public String getName() { return name; }
void setName(String name) { this.name = name; }
}
Code:
@Entity
@Table(name = "EXAM_RESULT")
public class ExamResult {
private Integer id;
private Pupil pupil;
private String course;
private Integer mark;
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "EXAM_RESULT_ID")
public Integer getId() { return id; }
void setId(Integer id) { this.id = id; }
@ManyToOne @JoinColumn(name = "PUPIL_ID")
public Pupil getPupil() { return pupil; }
void setPupil(Pupil pupil) { this.pupil = pupil; }
@Column(name = "MARK")
public Integer getMark() { return mark; }
void setMark(Integer mark) { this.mark = mark; }
@Column(name = "COURSE")
public String getCourse() { return course; }
void setCourse(String course) { this.course = course; }
}
I try to create a criteria to retrieve the pupils in a Region with a given ExamResult mark:
Code between sessionFactory.openSession() and session.close():Code:
sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
s = sessionFactory.openSession();
Criteria c = s.createCriteria(Pupil.class);
c.createCriteria("results").add(Restrictions.eq("mark", 10));
c.createCriteria("school.region").add(Restrictions.eq("name", "Western Australia"));
c.list();
The generated SQL (show_sql=true):Code:
select this_.PUPIL_ID as PUPIL1_2_4_, this_.NAME as NAME2_4_, this_.SCHOOL_ID as SCHOOL3_2_4_, examresult1_.EXAM_RESULT_ID as EXAM1_3_0_, examresult1_.COURSE as COURSE3_0_, examresult1_.MARK as MARK3_0_, examresult1_.PUPIL_ID as PUPIL4_3_0_, pupil5_.PUPIL_ID as PUPIL1_2_1_, pupil5_.NAME as NAME2_1_, pupil5_.SCHOOL_ID as SCHOOL3_2_1_, school6_.SCHOOL_ID as SCHOOL1_1_2_, school6_.NAME as NAME1_2_, school6_.REGION_ID as REGION3_1_2_, region7_.REGION_ID as REGION1_0_3_, region7_.NAME as NAME0_3_ from PUPIL this_ inner join EXAM_RESULT examresult1_ on this_.PUPIL_ID=examresult1_.PUPIL_ID left outer join PUPIL pupil5_ on examresult1_.PUPIL_ID=pupil5_.PUPIL_ID left outer join SCHOOL school6_ on pupil5_.SCHOOL_ID=school6_.SCHOOL_ID left outer join REGION region7_ on school6_.REGION_ID=region7_.REGION_ID where this_.NAME=? and examresult1_.MARK=? and region2_.NAME=?
Full stack trace of any exception that occurs:Code:
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 net.timcarpenter.hibtest.Test.main(Test.java:24)
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "region2_"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
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
The hibernate.cfg.xml file is :
Code:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration
PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibtest</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property> -->
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="connection.url">jdbc:postgresql://localhost/hibtest</property>
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="connection.username">root</property>
<property name="connection.password">root</property>
<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>
<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">create</property>
<property name="show_sql">true</property>
<property name="hibernate.query.substitutions">true 1, false 0</property>
<property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
<property name="hibernate.generate_statistics">true</property>
<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.connection.release_mode">after_transaction</property>
<mapping class="net.timcarpenter.hibtest.Region"/>
<mapping class="net.timcarpenter.hibtest.School"/>
<mapping class="net.timcarpenter.hibtest.Pupil"/>
<mapping class="net.timcarpenter.hibtest.ExamResult"/>
</session-factory>
</hibernate-configuration>
If I use a MySQL database, dialect and driver, then all is fine.
Am I doing something wrong, or is this a defect?
Login
FAQ
Search