QueryDSL intersection with Spring Boot Data JPA

Question

I am using QueryDSL within a Spring Boot, Spring Data JPA project. I have the following schema for a table called test:

| id | key  | value |
|----|------|-------|
| 1  | test | hello |
| 1  | test | world |
| 2  | test | hello |
| 2  | foo  | bar   |
| 3  | test | hello |
| 3  | test | world |

Now I want to write the following SQL in QueryDSL:

select id from test where key = 'test' and value = 'hello'
INTERSECT
select id from test where key = 'test' and value = 'world'

Which would give me all ids where key is 'test' and values are 'hello' and 'world'.

I did not find any way of declaring this kind of SQL in QueryDSL yet. I am able to write the two select statements but then I am stuck at combining them with an INTERSECT.

JPAQueryFactory queryFactory = new JPAQueryFactory(em); // em is an EntityManager

QTestEntity qTestEntity = QTestEntity.testEntity;

var q1 = queryFactory.query().from(qTestEntity).select(qTestEntity.id).where(qTestEntity.key("test").and(qTestEntity.value.eq("hello")));
var q2 = queryFactory.query().from(qTestEntity).select(qTestEntity.id).where(qTestEntity.key("test").and(qTestEntity.value.eq("world")));;

In the end I want to retrieve a list of ids which match the given query. In general the amount of intersects can be something around 20 or 30, depending on the number of key/value-pairs I want to search for.

Does anyone know a way how to do something like this with QueryDSL ?

EDIT:

Assume the following schema now, with two tables: test and 'user':

test:

| userId  | key  | value |
|---------|------|-------|
| 1       | test | hello |
| 1       | test | world |
| 2       | test | hello |
| 2       | foo  | bar   |
| 3       | test | hello |
| 3       | test | world |

user:

| id | name     |
|----|----------|
| 1  | John     |
| 2  | Anna     |
| 3  | Felicita |

The correspond java classes look like this. TestEntity has a composite key consisting of all of its properties.

@Entity
public class TestEntity {
    @Id
    @Column(name = "userId", nullable = false)
    private String pubmedId;

    @Id
    @Column(name = "value", nullable = false)
    private String value;

    @Id
    @Column(name = "key", nullable = false)
    private String key;
}

@Entity
class User {
  @Id 
  private int id;

  private String name;

  @ElementCollection
  private Set<TestEntity> keyValues;
}

How can I map the test table to the keyValues properties within the User class?

Answer 1

In this case I'd probably just use an OR expression:

queryFactory
    .query()
    .from(qTestEntity) .select(qTestEntity.id)
    .where(qTestEntity.key("test").and(
        qTestEntity.value.eq("hello")
       .or(qTestEntity.value.eq("world")));

However, you specifically mention wanting to use a set operation. I by the way think you want to perform an UNION operation instead of an INSERSECT operation, because the latter one would be empty with the example given.

JPA doesn't support set operations such as defined in ANSI SQL. However, Blaze-Persistence is an extension that integrates with most JPA implementations and does extend JPQL with set operations. I have recently written a QueryDSL extension for Blaze-Persistence. Using that extension, you can do:

List<Document> documents = new BlazeJPAQuery<Document>(entityManager, cbf)
    .union(
         select(document).from(document).where(document.id.eq(41L)),
         select(document).from(document).where(document.id.eq(42L))
    ).fetch();

For more information about the integration and how to set it up, the documentation is available at https://persistence.blazebit.com/documentation/1.5/core/manual/en_US/index.html#querydsl-integration

Answer 2

Your TestEntity is not really an Entity, since it's id is not a primary key, it's the foreign key to the user table.

If it's only identifiable by using all its properties, it's an @Embeddable, and doesn't have any @Id properties.

You can map a collection of Embeddables as an @ElementCollection part of another entity which has the id as primary key. The id column in your case is not a property of the Embeddable, it's just the foreign key to the main table, so you map it as a @JoinColumn:

@Embeddable
public class TestEmbeddable {

    @Column(name = "value", nullable = false)
    private String value;

    @Column(name = "key", nullable = false)
    private String key;
}

@Entity
class User {
  @Id 
  private int id;
  @ElementCollection
  @CollectionTable(
     name="test",
     joinColumns=@JoinColumn(name="id")
  )
  private Set<TestEmbeddable> keyValues;
}

In this case, the QueryDSL becomes something like this (don't know the exact api):

user.keyValues.any().in(new TestEmbeddable("test", "hello"))
  .and(user.keyValues.keyValues.any().in(new TestEmbeddable("test", "world"))