java - JPQL/HQL : select with OR fails

Question

我的查询遇到一些问题，但不知 Prop 体问题在哪里。
例如，此查询返回 3 个 Change 对象:

select c from Change c where c.entityOne.serial = 'TEST3'

例如，此查询返回 2 个更改对象:

select c from Change c where c.entityTwo.serial = 'TEST3'

但是这个查询返回 0，而我期望的是 3 到 5 之间的值。

select c from Change c where (c.entityOne.serial = 'TEST3' or c.entityTwo.serial = 'TEST3')

我哪里错了？如果您需要更多代码，请询问。

测试重现:
抱歉，有很多代码，但需要重现此问题。

更改.java

@Entity
public class Change {
    private long id;
    private EntityOne entityOne;
    private EntityTwo entityTwo;

    @Id
    @GeneratedValue
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @ManyToOne
    public EntityOne getEntityOne() {
        return entityOne;
    }

    public void setEntityOne(EntityOne entityOne) {
        this.entityOne = entityOne;
    }

    @ManyToOne
    public EntityTwo getEntityTwo() {
        return entityTwo;
    }

    public void setEntityTwo(EntityTwo entityTwo) {
        this.entityTwo = entityTwo;
    }
}

EntityOne.java 和 EntityTwo.java(只需重命名类)

@Entity
public class EntityOne {
    private long id;
    private String serial;

    @Id
    @GeneratedValue
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getSerial() {
        return serial;
    }

    public void setSerial(String serial) {
        this.serial = serial;
    }
}

Test.java - 运行它。

public class Test {
    private static EntityManagerFactory buildFactory() {
        try {
            return Persistence.createEntityManagerFactory("migration-tool");
        } catch (Throwable ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        EntityManagerFactory emf = buildFactory();
        EntityManager em = emf.createEntityManager();
        Query q = em.createQuery("select c from Change c where (c.entityOne.serial = 'TEST3' or c.entityTwo.serial = 'TEST3')");
        List<Change> changeRecords =  q.getResultList();
        System.out.println(changeRecords.size());
        emf.close();
    }
}

持久性.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0">
    <persistence-unit name="migration-tool" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <class>test.Change</class>
        <class>test.EntityOne</class>
        <class>test.EntityTwo</class>
        <properties>
            <property name="hibernate.search.autoregister_listeners" value="false"/>
            <property name="hibernate.cache.region.factory_class"
                      value="net.sf.ehcache.hibernate.EhCacheRegionFactory"/>
            <property name="hibernate.cache.use_query_cache" value="true"/>
            <property name="hibernate.connection.url" value="jdbc:postgresql://127.0.0.1:5434/test"/>
            <property name="hibernate.connection.username" value="postgres"/>
            <property name="hibernate.connection.password" value="123456"/>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
        </properties>
    </persistence-unit>
</persistence>

pom.xml 中的依赖项

<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.1-901.jdbc4</version>
</dependency>
<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>persistence-api</artifactId>
    <version>1.0</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-annotations</artifactId>
    <version>3.4.0.GA</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>3.4.0.GA</version>
</dependency>
<dependency>
    <groupId>net.sf.ehcache</groupId>
    <artifactId>ehcache-core</artifactId>
    <version>2.6.0</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.4.2</version>
</dependency>

以及用于填写数据的 SQL 脚本。

CREATE TABLE entityone
(
  id bigint NOT NULL,
  serial character varying(255),
  PRIMARY KEY (id)
);

CREATE TABLE entitytwo
(
  id bigint NOT NULL,
  serial character varying(255),
  PRIMARY KEY (id)
);

CREATE TABLE change
(
  id bigint NOT NULL,
  entityone_id bigint,
  entitytwo_id bigint,
  PRIMARY KEY (id),
  FOREIGN KEY (entityone_id) REFERENCES entityone(id),
  FOREIGN KEY (entitytwo_id) REFERENCES entitytwo(id)
);

insert into entityone values (1, 'TEST3');
insert into entityone values (2, 'TEST1');
insert into entityone values (3, 'TEST2');
insert into entityone values (4, 'TEST3');

insert into entitytwo values (1, 'TEST3');
insert into entitytwo values (2, 'TEST3');
insert into entitytwo values (3, 'TEST4');
insert into entitytwo values (4, 'TEST3');

insert into change values (1, 1, NULL);
insert into change values (2, 2, NULL);
insert into change values (3, 3, NULL);
insert into change values (4, 4, NULL);
insert into change values (5, NULL, 1);
insert into change values (6, NULL, 2);
insert into change values (7, NULL, 3);
insert into change values (8, NULL, 4);

Answer 1

根据评论写出答案:

问题是NULL值(value)观。 Chapter 16.4 Hibernate 文档 (v 4.3) 状态:

16.4. Forms of join syntax

HQL supports two forms of association joining: implicit and explicit.

The queries shown in the previous section all use the explicit form, that is, where the join keyword is explicitly used in the from clause. This is the recommended form.

The implicit form does not use the join keyword. Instead, the associations are "dereferenced" using dot-notation. implicit joins can appear in any of the HQL clauses. implicit join result in inner joins in the resulting SQL statement.

from Cat as cat where cat.mate.name like '%s%'

我已将最相关的部分加粗。这意味着您的点符号将展开为内部联接，并且由于键为空 - 您的实体将不会出现在联接中。
Hibernate执行的SQL也显示了这一点(为了可读性，下面进行了美化):

select change  
from Change change, EntityOne entityone, EntityTwo entitytwo
where 
change.entityOne_id=entityone.id and
change.entityTwo_id=entitytwo.id and  
(
    ((change.entityOne_id is not null) and entityone.serial='TEST3') or 
    ((change.entityTwo_id is not null) and entitytwo.serial='TEST3')
)

不幸的是，点符号不允许您获得所需的结果。您必须自己创建连接，如上述文档第 16.3 章中所示。示例:

SELECT c
FROM Change AS c
    LEFT JOIN Change.entityOne as entityOne
    LEFT JOIN Change.entityTwo as entityTwo
WHERE
    entityOne.serial = "TEST3" OR entityTwo.serial = "TEST3"

注意:我没有 Hibernate 的工作设置，您可能需要在上面添加额外的 null 检查