java criteria 生成_java – Hibernate Criteria查询在生成的SQL中以错误的顺序列出表...

我有一个Criteria查询使用几个连接,并且生成的SQL列出了这些表的顺序,以便ON子句引用尚未声明的表.

为了重现这个问题,我创建了一个具有三个表的小数据模型：Bill,Event和一个连接表BillEvent(我在列表中列出了一个可运行的JUnit测试,其中包含实体定义).以下Criteria查询失败并出现语法错误,因为在引用之后声明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

inner join test.event event1x1_ on this_.eventId=event1x1_.id

where

hibernatej2_.id=?

and event2x4_.id is null

JUnit测试使用Hibernate 5和H2：

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 billEvents = new HashSet(0);

public Bill() {

}

public Bill(String label) {

this.label = label;

}

public Bill(String label, Set 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 getBillEvents() {

return this.billEvents;

}

public void setBillEvents(Set 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 billEvents = new HashSet<>(0);

public Event() {

}

public Event(Timestamp time) {

this.time = time;

}

public Event(Timestamp time, Set 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 getBillEvents() {

return this.billEvents;

}

public void setBillEvents(Set 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 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(或事件后面的任何字母顺序)修复了此查询中的语法错误.虽然这是不可扩展的：如果要从连接表的另一侧进行查询,该查询将失败,因为它现在按字母顺序排列.