我有一个使用多个联接的Criteria查询，并且生成的SQL乱序列出了表，以便ON子句引用尚未声明的表。
为了重现该问题，我创建了一个包含三个表的小型数据模型:Bill，Event和一个联结表BillEvent(我在问题末尾列出了带有实体定义的可运行JUnit测试)。以下条件查询因语法错误而失败，因为event1在引用后被声明。 如何重写此查询，以便以正确的顺序声明表？
// Get the most recent BillEvent for a bill
final Criteria criteria = session.createCriteria(BillEvent.class, "be1")
.createCriteria("event", "event1")
.createCriteria("be1.bill")
.add(Restrictions.eq("id", billId))
.createCriteria("billEvents", "be2")
.createCriteria("event", "event2", JoinType.LEFT_OUTER_JOIN,
Restrictions.ltProperty("event1.time", "time"))
.add(Restrictions.isNull("event2.id"));
错误:
Caused by: org.h2.jdbc.JdbcSQLException: Column "EVENT1X1_.TIME" not found; SQL statement:
select
this_.id as id1_1_4_,
this_.billId as billId3_1_4_,
this_.eventId as eventId4_1_4_,
this_.note as note2_1_4_,
hibernatej2_.id as id1_0_0_,
hibernatej2_.label as label2_0_0_,
be2x3_.id as id1_1_1_,
be2x3_.billId as billId3_1_1_,
be2x3_.eventId as eventId4_1_1_,
be2x3_.note as note2_1_1_,
event2x4_.id as id1_2_2_,
event2x4_.time as time2_2_2_,
event1x1_.id as id1_2_3_,
event1x1_.time as time2_2_3_
from
test.billEvent this_
inner join test.bill hibernatej2_ on this_.billId=hibernatej2_.id
inner join test.billEvent be2x3_ on hibernatej2_.id=be2x3_.billId
left outer join test.event event2x4_
on be2x3_.eventId=event2x4_.id
and ( event1x1_.time<event2x4_.time )
inner join test.event event1x1_ on this_.eventId=event1x1_.id
where
hibernatej2_.id=?
and event2x4_.id is null
使用Hibernate 5和H2进行JUnit测试:
package com.stackoverflow.repro;
import static javax.persistence.GenerationType.IDENTITY;
import java.sql.Timestamp;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
import org.h2.Driver;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.spi.MetadataImplementor;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.criterion.Restrictions;
import org.hibernate.dialect.H2Dialect;
import org.hibernate.sql.JoinType;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.Assert;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.TestName;
public class HibernateJoinTest {
private static final String TEST_CATALOG = "test";
@Rule public TestName name = new TestName();
@Entity
@Table(name = "bill", catalog = TEST_CATALOG)
public static class Bill implements java.io.Serializable {
private Integer id;
private String label;
private Set<BillEvent> billEvents = new HashSet<BillEvent>(0);
public Bill() {
}
public Bill(String label) {
this.label = label;
}
public Bill(String label, Set<BillEvent> billEvents) {
this.label = label;
this.billEvents = billEvents;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id", unique = true, nullable = false)
public Integer getId() {
return this.id;
}
public void setId(Integer id) {
this.id = id;
}
@Column(name = "label", unique = true, nullable = false, length = 45)
public String getLabel() {
return this.label;
}
public void setLabel(String label) {
this.label = label;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "bill", cascade = { CascadeType.ALL })
public Set<BillEvent> getBillEvents() {
return this.billEvents;
}
public void setBillEvents(Set<BillEvent> billEvents) {
this.billEvents = billEvents;
}
}
@Entity
@Table(name = "event", catalog = TEST_CATALOG)
public static class Event implements java.io.Serializable {
private Integer id;
private Timestamp time;
private Set<BillEvent> billEvents = new HashSet<>(0);
public Event() {
}
public Event(Timestamp time) {
this.time = time;
}
public Event(Timestamp time, Set<BillEvent> billEvents) {
this.time = time;
this.billEvents = billEvents;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id", unique = true, nullable = false)
public Integer getId() {
return this.id;
}
public void setId(Integer id) {
this.id = id;
}
@Column(name = "time", nullable = false)
public Timestamp getTime() {
return this.time;
}
public void setTime(Timestamp time) {
this.time = time;
}
@OneToMany(fetch = FetchType.LAZY, mappedBy = "event", cascade = { CascadeType.ALL })
public Set<BillEvent> getBillEvents() {
return this.billEvents;
}
public void setBillEvents(Set<BillEvent> billEvents) {
this.billEvents = billEvents;
}
}
@Entity
@Table(name = "billEvent", catalog = TEST_CATALOG, uniqueConstraints = @UniqueConstraint(columnNames = {"billId", "eventId"}) )
public static class BillEvent implements java.io.Serializable {
private Integer id;
private Bill bill;
private Event event;
private String note;
public BillEvent() {
}
public BillEvent(Bill bill, Event event) {
this.bill = bill;
this.event = event;
}
public BillEvent(Bill bill, Event event, String note) {
this.bill = bill;
this.event = event;
this.note = note;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id", unique = true, nullable = false)
public Integer getId() {
return this.id;
}
public void setId(Integer id) {
this.id = id;
}
@ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.ALL })
@JoinColumn(name = "billId", nullable = false)
public Bill getBill() {
return this.bill;
}
public void setBill(Bill bill) {
this.bill = bill;
}
@ManyToOne(fetch = FetchType.LAZY, cascade = { CascadeType.ALL })
@JoinColumn(name = "eventId", nullable = false)
public Event getEvent() {
return this.event;
}
public void setEvent(Event event) {
this.event = event;
}
@Column(name = "note", unique = true, nullable = false, length = 120)
public String getNote() {
return this.note;
}
public void setNote(String note) {
this.note = note;
}
}
@Test
public void testOuterJoin() {
final SessionFactory sessionFactory = createSessionFactory();
final String label = "B0001";
final Timestamp ts = new Timestamp(System.currentTimeMillis());
final Timestamp ts2 = new Timestamp(ts.getTime() + 1000);
final String note1 = "First note";
final String note2 = "Second note";
final int billId;
try (final Session session = sessionFactory.openSession();) {
final Transaction tx = session.beginTransaction();
final Bill bill = new Bill(label);
session.save(bill);
billId = bill.getId();
final Event event1 = new Event(ts);
session.save(event1);
final Event event2 = new Event(ts2);
session.save(event2);
session.save(new BillEvent(bill, event1, note1));
session.save(new BillEvent(bill, event2, note2));
session.flush();
tx.commit();
}
try (final Session session = sessionFactory.openSession()) {
final Criteria criteria = session.createCriteria(BillEvent.class, "be1")
.createCriteria("event", "event1")
.createCriteria("be1.bill")
.add(Restrictions.eq("id", billId))
.createCriteria("billEvents", "be2")
.createCriteria("event", "event2", JoinType.LEFT_OUTER_JOIN,
Restrictions.ltProperty("event1.time", "time"))
.add(Restrictions.isNull("event2.id"));
@SuppressWarnings("unchecked")
final List<BillEvent> results = criteria.list();
Assert.assertEquals(1, results.size());
final BillEvent billEvent = results.get(0);
Assert.assertEquals(note2, billEvent.getNote());
Assert.assertEquals(ts2, billEvent.getEvent().getTime());
}
}
private SessionFactory createSessionFactory() {
final String dialectClassName = H2Dialect.class.getName();
final Configuration config =
new Configuration()
.addAnnotatedClass(Bill.class)
.addAnnotatedClass(Event.class)
.addAnnotatedClass(BillEvent.class);
final String dbName = name.getMethodName();
config.setProperty(Environment.DIALECT, dialectClassName);
config.setProperty(Environment.DRIVER, Driver.class.getName());
config.setProperty(Environment.URL, "jdbc:h2:mem:"+dbName+";DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS TEST\\; SET SCHEMA TEST");
config.setProperty(Environment.USER, "SA");
config.setProperty(Environment.PASS, "");
config.setProperty(Environment.SHOW_SQL, "true");
config.setProperty(Environment.FORMAT_SQL, "true");
final StandardServiceRegistry serviceRegistry = config.getStandardServiceRegistryBuilder().applySettings(config.getProperties()).build();
final MetadataSources sources =
new MetadataSources(serviceRegistry)
.addAnnotatedClass(Bill.class)
.addAnnotatedClass(Event.class)
.addAnnotatedClass(BillEvent.class);
final Metadata metadata = sources.buildMetadata();
final SchemaExport export = new SchemaExport((MetadataImplementor) metadata);
export.create(false, true);
final SessionFactory sessionFactory = config.buildSessionFactory();
return sessionFactory;
}
}
编辑:这里的问题似乎是Hibernate用属性名称按字母顺序枚举表。因此，如果存在以下联接:
from root
inner join root.z
inner join root.z.b
inner join root.z.a
inner join root.a on (... and root.z.prop = root.a.prop)
生成的订单将是
from root
inner join root.a on (... and root.z.prop = root.a.prop)
inner join root.z
inner join root.z.a
inner join root.z.b
将
BillEvent.bill重命名为BillEvent.zBill(或event之后按字母顺序排列的任何内容)可修复此查询中的语法错误。但是，这是不可扩展的:如果您想从联结表的另一侧进行查询，该查询将失败，因为它现在按字母顺序是乱序的。 最佳答案
使用条件时，hibernate实际上会根据深度优先搜索的方式遍历实体树，以根据配置中的字段定义建立连接。在您的情况下，首先遍历 BillEvent ，然后遍历 Bill 类的子字段。因此，从帐单关联创建所有联接之后，基本上就创建了事件实体联接。您可以在hbm.xml中定义顺序，但是正如您提到的那样，它的伸缩性不是很高。
因此，您在这里至少有两个选择:
最终标准条件= session.createCriteria(Event.class，“event1”)
.createCriteria(“event1.billEvents”，“be1”)
.createCriteria(“be1.bill”，“bill1”)
.createCriteria(“bill1.billEvents”，“be2”)
.createCriteria(“be2.event”，“event2”，JoinType.LEFT_OUTER_JOIN，
Restrictions.ltProperty(“event1.time”，“event2.time”))
.add(Restrictions.eq(“be1.id”，billId))
.add(Restrictions.isNull(“event2.id”))
.setProjection(Projections.projectionList()
.add(Projections.property(“be1.event”)，“事件”)
.add(Projections.property(“be1.note”)，“note”))
.setResultTransformer(Transformers.aliasToBean(BillEvent.class));