[eclipselink-dev] JPA queries and basic collection mapping

[Dennis Fuglsang <dennis.fuglsang@xxxxxxxxxx>]

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 KEY (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]