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();
  });
}

Lesiak · Accepted Answer · 2019-04-15 07:28:45Z

2

+50

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

edited Apr 15, 2019 at 7:28

answered Apr 15, 2019 at 7:03

Lesiak

26.5k2 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

slashdottir Over a year ago

Thank you, I tried this but when we get to List<Foo> baz = typedQuery.getResultList(); the result is an empty array...

slashdottir Over a year ago

How can I see the ddl?

slashdottir Over a year ago

Interestingly, though, if I comment out the conditions logic, e.g. I run: Join<Foo, Bar> foobars = (Join) fromFoo.fetch("bars"); TypedQuery<Foo> 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 Over a year ago

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.

Lesiak Over a year ago

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

|

Syed Khalid Ahmed · Accepted Answer · 2019-04-15 06:41:22Z

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, 2019 at 6:41

answered Apr 15, 2019 at 5:26

Syed Khalid Ahmed

3,2423 gold badges23 silver badges39 bronze badges

9 Comments

slashdottir Over a year ago

Thank you, I really want this to work but with this line: Join<Foo, Bar> foobars = (Join<Foo, Bar>) fromFoo.fetch("fooId"); I get a compile error: Inconvertible types; cannot cast 'javax.persistence.criteria.Fetch<java.lang.Object,java.lang.Object> to 'javax.persistence.criteria.Join<Foo,Bar>'

Syed Khalid Ahmed Over a year ago

@slashdottir which hibernate version you are using?

slashdottir Over a year ago

Looks like its 5.1.0.Final

Syed Khalid Ahmed Over a year ago

@slashdottir Why don’t you use the latest version?

Syed Khalid Ahmed Over a year ago

@slashdottir sorry that was 5.3.5 final.

|

Collectives™ on Stack Overflow

How to construct this join query with JPA API?

2 Answers 2

11 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related