Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-dev] JPA queries and basic collection mapping

Please do not send usage questions to the dev mailing list.  Use the user mailing list or forums.

 

You need to use an outer join to avoid the join filtering the results.

 

SELECT a FROM Zinstance a left join a.groupUUIDs g WHERE a.groupUUIDs IS EMPTY OR   g IN (?1 )

 

This may even work and avoid the subselect,

 

SELECT a FROM Zinstance a left! join a.groupUUIDs g WHERE g is null OR  g = ?1 )

 

From: Dennis Fuglsang
Sent: November-21-11 3:15 PM
To: eclipselink-dev@xxxxxxxxxxx
Subject: [eclipselink-dev] JPA queries and basic collection mapping

 

I have been having problems getting JPA queries to work with a basic collection mapping for Set<String>.   To illustrate one of the problems I am wrestling with I created a simple test case involving a single Java class, two database tables, a basic collection mapping and JPA queries.  I am using EclipseLink 2.3.1. 

 

The SQL for the DB schema is shown below

 

CREATE TABLE Z_INSTANCES (

  INSTANCE_UUID CHAR(36) NOT NULL

, NAME VARCHAR2(256)

, CONSTRAINT Z_INSTANCES_PK PRIMARY KEY (INSTANCE_UUID) using index tablespace &INDEX_TABLESPACE ENABLE) tablespace &DATA_TABLESPACE;

 

CREATE TABLE Z_GROUPS(

  INSTANCE_UUID CHAR(36) NOT NULL

, GROUP_UUID CHAR(36) NOT NULL

, CONSTRAINT Z_GROUPS_UK1 UNIQUE (INSTANCE_UUID,GROUP_UUID) using index tablespace &INDEX_TABLESPACE ENABLE

, CONSTRAINT Z_INSTANCES_FK1 FOREIGN K! EY (INSTANCE_UUID)

    REFERENCES Z_INSTANCES(INSTANCE_UUID) ON DELETE CASCADE ENABLE) tablespace &DATA_TABLESPACE;

 

The Java class used is very simple

 

public class Zinstance {

    private String uuid;

    private String name;

    private Set<String> groupUUIDs;

    public Zinstance() {

        this.uuid = UUID.randomUUID().toString();

        this.groupUUIDs = new HashSet<String>();

    }

}

 

The OR mapping is shown below:

 

<entity class="my.temp.Zinstance">

      <table name="Z_INSTANCES" />

      <attributes>

            <id name="uuid"><column name="INSTANCE_UUID" updatable="false" /></id>

            <basic name="name"><column name="NAME" /></basic>

            <basic-collection name="groupUUIDs">

                  <value-column name="GROUP_UUID" />

                  <collection-table name="Z_GROUPS">

                        <primary-key-join-column>INSTANCE_UUID</primary-key-join-column>

                  </collection-table>

            </basic-collection>

      </attributes>

</entity>

 

 

I attempted the following JPA queries

 

(1)    SELECT a FROM Zinstance a WHERE a.groupUUIDs IS EMPTY         <-Success

(2)    SELECT a FROM Zinstance a WHERE a.groupUUIDs IN (?1 )            <-Success

(3)    SELECT a FROM Zinstance a WHERE a.groupUUIDs IS EMPTY OR   a.groupUUIDs IN (?1 )      <-Returns same results as (2)

 

Is there a problem with my basic collection mapping????   The native SQL for the three JPA queries above is shown below.  The third SQL statement seems like a logical combination of (1) and (2) but I believe that the (t1.INSTANCE_UUID = t0.INSTANCE_UUID) join in (3) is preventing instances with empty groupUUIDs from being returned.  Any suggestions, workarounds, or alternatives?

 

(1)SELECT t0.INSTANCE_UUID, t0.NAME FROM Z_INSTANCES t0 WHERE ((SELECT COUNT(t1.GROUP_UUID) FROM Z_GROUPS t1 WHERE (t1.INSTANCE_UUID = t0.INSTANCE_UUID)) = ?)

      bind => [0]

 

(2)SELECT t0.INSTANCE_UUID, t0.NAME FROM Z_INSTANCES t0, Z_GROUPS t1 WHERE ((t1.GROUP_UUID IN (?)) AND (t1.INSTANCE_UUID = t0.INSTANCE_UUID))

      bind => [652823a0-0c0d-42b8-a8de-60f421d30bf3]

 

(3)SELECT t0.INSTANCE_UUID, t0.NAME FROM Z_INSTANCES t0, Z_GROUPS t1 WHERE ((((SELECT COUNT(t2.GROUP_UUID) FROM Z_GROUPS t2 WHERE (t2.INSTANCE_UUID = t0.INSTANCE_UUID)) = ?) OR (t1.GROUP_UUID IN (?))) AND (t1.INSTANCE_UUID = t0.INSTANCE_UUID))

      bind => [0, 652823a0-0c0d-42b8-a8de-60f421d30bf3]

 


Back to the top