我有一个使用多个连接的 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<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，然后是 Bill 类的子字段。所以基本上它在创建来自 bill 关联的所有连接之后创建 event 实体连接。您可以在 hbm.xml 中定义顺序，但正如您所提到的，它的可扩展性不是很好。

所以你在这里至少有两个选择:

1. 更改标准使根实体不同，然后添加投影和结果转换器以获取 BillEvent 实体。例如:

   final Criteria criteria = 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"), "event")
                       .add(Projections.property("be1.note"), "note"))
                   .setResultTransformer(Transformers.aliasToBean(BillEvent.class));
   

2. 另一个可能的选择是使用 hql 而不是 criteria api。由于它使用不同的 sql 构建机制，因此您可以直接根据请求控制连接顺序。

关于java - Hibernate Criteria 查询在生成的 SQL 中以错误的顺序列出表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33095911/