How to construct this join query with JPA API?

Question

Two tables:

CREATE TABLE `foo` (
  `foo_id` bigint(20)  not null auto_increment,
  `name` varchar(32) not null,
  `_deleted_` tinyint(1) default '0',
   PRIMARY KEY (`foo_id`)
) ;

CREATE TABLE `bar` (
  `bar_id` bigint(20)  not null auto_increment,
   `foo_id` bigint(20)  not null,
   `key` varchar(32) not null,
   `value` varchar(125) not null,
   `_deleted_` tinyint(1) default '0',
   PRIMARY KEY (`bar_id`)
);

Table contents:

select * from foo;
+--------+-------+-----------+
| foo_id | name  | _deleted_ |
+--------+-------+-----------+
|      1 | cat   |         0 |
|      2 | dog   |         0 |
|      3 | mouse |         0 |
|      4 | rat   |         1 |
+--------+-------+-----------+
3 rows in set (0.00 sec)

select * from bar;
+--------+--------+-------+--------+-----------+
| bar_id | foo_id | key   | value  | _deleted_ |
+--------+--------+-------+--------+-----------+
|      1 |      1 | sound | meow   |         0 |
|      2 |      1 | ears  | pointy |         0 |
|      3 |      2 | sound | ruff   |         0 |
|      4 |      2 | nose  | long   |         0 |
|      5 |      3 | sound | squeak |         0 |
|      6 |      3 | tail  | long   |         0 |
|      7 |      3 | legs  | two    |         1 |
+--------+--------+-------+--------+-----------+
6 rows in set (0.00 sec)

The query I want to create:

select f.foo_id, f.name, b.key, b.value from foo f, bar b 
  where f.foo_id = b.foo_id and f._deleted_ = 0 and b._deleted_ = 0;

+--------+-------+-------+--------+
| foo_id | name  | key   | value  |
+--------+-------+-------+--------+
|      1 | cat   | sound | meow   |
|      1 | cat   | ears  | pointy |
|      2 | dog   | sound | ruff   |
|      2 | dog   | nose  | long   |
|      3 | mouse | sound | squeak |
|      3 | mouse | tail  | long   |
+--------+-------+-------+--------+
6 rows in set (0.01 sec)

Foo class:

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "foo")
public class Foo {

  @Id
  @Column(name = "foo_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long fooId;
  private String name;
  @Column(name = "_deleted_")
  private Short deleted;

  @OneToMany
  @JoinTable(name="bar",
      joinColumns=@JoinColumn(name="foo_id"))
  private List<Bar> bars;
}

Bar class:

@Data 
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "bar")
public class Bar {

  @Id
  @Column(name = "bar_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long barId;
  private Long fooId;
  private String key;
  private String value;
  @Column(name = "_deleted_")
  private Short deleted;
}

Attempt to Join them:

protected Stream<Foo> getFoosWithBars() {
  return this.jpaApi.withTransaction(entityManager -> {
    final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    final CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
    Root<Foo> fromFoo = criteria.from(Foo.class);
    Join<Foo, Bar> foobars = fromFoo.join("fooId");
    List<Predicate> conditions = new ArrayList();
    conditions.add(builder.notEqual(fromFoo.get("deleted"), 1));
    #  what goes here?
    conditions.add(builder.notEqual(???Bar???.get("deleted"), 1));

    TypedQuery<Foo> typedQuery = entityManager.createQuery(criteria
        .select(fromFoo)
      .where(conditions.toArray(new Predicate[] {})));
    return typedQuery.getResultList().stream();
  });
}

Khalid Shah · Answer 1 · 2019-04-15T06:41:22.490

2

You are missing Join condition for tables in this JPA query and use Join Object to match condition for Bar object. Have a look at this query and tell me if you have any further query.

protected Stream<Foo> getFoosWithBars() {
  return this.jpaApi.withTransaction(entityManager -> {
    final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    final CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
    Root<Foo> fromFoo = criteria.from(Foo.class);
    Join<Foo, Bar> foobars = (Join<Foo, Bar>) fromFoo.fetch("fooId");

    List<Predicate> conditions = new ArrayList();
    conditions.add(builder.equal(fromFoo.get("fooId"),foobars.get("fooId"))); // You are missing join Condition
    conditions.add(builder.equal(fromFoo.get("deleted"), 0));
    conditions.add(builder.equal(foobars.get("deleted"), 0));

    TypedQuery<Pod> typedQuery = entityManager.createQuery(criteria.select(fromFoo)
      .where(conditions.toArray(new Predicate[] {})));
    return typedQuery.getResultList().stream();
  });
}

edited Apr 15 '19 at 06:41

answered Apr 15 '19 at 05:26

Khalid Shah

3,132
3
20
39

Thank you, I really want this to work but with this line: Join foobars = (Join) fromFoo.fetch("fooId"); I get a compile error: Inconvertible types; cannot cast 'javax.persistence.criteria.Fetch to 'javax.persistence.criteria.Join' – slashdottir Apr 15 '19 at 16:23
@slashdottir which hibernate version you are using? – Khalid Shah Apr 15 '19 at 16:26
Looks like its 5.1.0.Final – slashdottir Apr 15 '19 at 16:28
@slashdottir Why don’t you use the latest version? – Khalid Shah Apr 15 '19 at 16:32
I can try. This is for my job, where changing things requires going through a ticket process, but if necessary, I can see about upgrading Hibernate. – slashdottir Apr 15 '19 at 16:47
@slashdottir I am using 5.2.10 final in my project. And that statement running as expected in my own query. – Khalid Shah Apr 15 '19 at 16:48
I just updated to 5.2.10.Final and clean rebuilt. Same error about Inconvertible types.. incompatible types: javax.persistence.criteria.Fetch cannot be converted to javax.persistence.criteria.Join – slashdottir Apr 15 '19 at 17:53
There are 3 ways to get this generic right: 1) Use raw type in cast 2) Join foobars = (Join)fromFoo.fetch("bars"); 3) Use 2 variables: one for Fetch, then cast it to Join – Lesiak Apr 15 '19 at 20:15
1

@slashdottir sorry that was 5.3.5 final. – Khalid Shah Apr 16 '19 at 03:48

Lesiak · Accepted Answer · 2019-04-15T07:28:45.090

I am strongly convinced that the problem lies in the mapping of your entities. With an incorrect model, you will have hard time generating a proper query.

Lets look at what gets generated by ddl from the initial code: org.hibernate.DuplicateMappingException: Table [bar] contains physical column name [foo_id] referred to by multiple physical column names: [foo_id], [fooId]

Let's try to correct it:

@Column(name = "foo_id")
private Long fooId;

Now the following ddl is generated:

create table foo (foo_id bigint(20) generated by default as identity,
_deleted_ smallint,
name varchar(255),
primary key (foo_id))

create table bar (bar_id bigint(20) generated by default as identity,
_deleted_ smallint,
foo_id bigint,
key varchar(255),
value varchar(255),
bars_bar_id bigint(20) not null,
primary key (bar_id))

The problem

bars_bar_id is a result of your @JoinTable, and will be problematic.

The query proposed in another answer, using
Join<Foo, Bar> foobars = (Join<Foo, Bar>) fromFoo.fetch("fooId");

fails with hibernate.jpa.criteria.BasicPathUsageException: Cannot join to attribute of basic type See a hint that you need a properly mapped association to make a join

Note that only changing the:

@Column(name = "foo_id")
private Long fooId;

to

@ManyToOne
@JoinColumn(name = "foo_id")
Foo foo;

is not sufficient: any a from foo to bar will cause 2 joins in the SQL (as mentioned earlier, a FK on unexpected field bars_bar_id):

final CriteriaBuilder builder = em.getCriteriaBuilder();
final CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
Root<Foo> fromFoo = criteria.from(Foo.class);
Join<Foo, Bar> foobars = (Join) fromFoo.fetch("bars");

select
    foo0_.foo_id as foo_id1_2_0_,
    bar2_.bar_id as bar_id1_1_1_,
    foo0_._deleted_ as _deleted2_2_0_,
    foo0_.name as name3_2_0_,
    bar2_._deleted_ as _deleted2_1_1_,
    bar2_.foo_id as foo_id3_1_1_,
    bar2_.key as key4_1_1_,
    bar2_.value as value5_1_1_,
    bars1_.foo_id as foo_id3_1_0__,
    bars1_.bars_bar_id as bars_bar6_1_0__ 
from
    foo foo0_ 
inner join
    bar bars1_ 
        on foo0_.foo_id=bars1_.foo_id 
inner join
    bar bar2_ 
        on bars1_.bars_bar_id=bar2_.bar_id

The correct mapping

The best way to map a @OneToMany relationship with JPA and Hibernate

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "foo")
public class Foo {

    @Id
    @Column(name = "foo_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long fooId;
    private String name;
    @Column(name = "_deleted_")
    private Short deleted;

    @OneToMany(mappedBy = "foo")
    private List<Bar> bars;
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "bar")
public class Bar {

    @Id
    @Column(name = "bar_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long barId;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "foo_id")
    Foo foo;

    private String key;
    private String value;
    @Column(name = "_deleted_")
    private Short deleted;
}

criteria query

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
Root<Foo> fromFoo = criteria.from(Foo.class);
Join<Foo, Bar> foobars = (Join) fromFoo.fetch("bars");

List<Predicate> conditions = new ArrayList<>();
conditions.add(builder.equal(fromFoo.get("deleted"), 0));
conditions.add(builder.equal(foobars.get("deleted"), 0));

TypedQuery<Foo> typedQuery = entityManager.createQuery(
        criteria.select(fromFoo)
                .where(conditions.toArray(new Predicate[]{})));

generated SQL

select
    foo0_.foo_id as foo_id1_2_0_,
    bars1_.bar_id as bar_id1_1_1_,
    foo0_._deleted_ as _deleted2_2_0_,
    foo0_.name as name3_2_0_,
    bars1_._deleted_ as _deleted2_1_1_,
    bars1_.foo_id as foo_id5_1_1_,
    bars1_.key as key3_1_1_,
    bars1_.value as value4_1_1_,
    bars1_.foo_id as foo_id5_1_0__,
    bars1_.bar_id as bar_id1_1_0__ 
from
    foo foo0_ 
inner join
    bar bars1_ 
        on foo0_.foo_id=bars1_.foo_id 
where
    foo0_._deleted_=0 
    and bars1_._deleted_=0

Thank you, I tried this but when we get to List baz = typedQuery.getResultList(); the result is an empty array... — slashdottir, Apr 15 '19 at 17:48
Interestingly, though, if I comment out the conditions logic, e.g. I run: Join foobars = (Join) fromFoo.fetch("bars"); TypedQuery typedQuery = entityManager.createQuery(criteria.select(fromFoo)); I get 7 results. Two for cat (each cat has two bars), 2 for dog (each having two bars), 3 for mouse (each having 3 bars). But with the conditions added, zero results. — slashdottir, Apr 15 '19 at 19:31
I found my problem. The table was getting created with the _deleted_ column defaulting to NULL, not '0'. Changing condition to: conditions.add(builder.isNull(fromFoo.get("deleted"))) gives me 6 results. However, the 'mouse' result shows 3 child bars, instead of 2. — slashdottir, Apr 15 '19 at 19:55
Probably not the most helpful comment - works fine for me, both if we use 0 or null. Two tips: 1) you may want to de-duplicate the result with .distinct(true) on your criteria query 2) You don't want toString() from Lombok on your entities if they form a cycle - you'll end up with Stack Overflow as long as you try to print them — Lesiak, Apr 15 '19 at 20:08
To see the ddl, I setup a test configuration: I use in memory database (h2), then set spring.jpa.hibernate.ddl-auto=create-drop, and force sql to be logged — Lesiak, Apr 15 '19 at 20:20
Getting there, with the distinct added, I get three results. However, the mouse result still has 3 bars. The 2nd condition is not eliminating deleted bars (conditions.add(builder.isNull(foobars.get("deleted"))); — slashdottir, Apr 15 '19 at 20:27
I'm going to go with this and just post-process the results to delete the undeleted bars. Thank you — slashdottir, Apr 15 '19 at 20:32
Does the bar you receive in your query have something different than null in deleted field? — Lesiak, Apr 15 '19 at 20:50
suddenly now it's doing the right thing, I dunno why it changed, but it works. thanks! — slashdottir, Apr 15 '19 at 21:00

How to construct this join query with JPA API?

2 Answers2