CODEWITHSUNDEEP
javahibernatehibernate-criteria
Matheus Cândido
Matheus Cândido

Reputation: 31

How to use CriteriaBuilder to join on selection

How to join a table with a selection using CriteriaBuilder?

Suppose I have this query:

SELECT 
    tbl1.*, 
    tbl2.total 
FROM 
    table_1 tbl1 
LEFT JOIN 
    (SELECT col_id AS id, SUM(value) AS total FROM table_2 WHERE active = 1 GROUP BY col_id) tbl2 
ON 
    tbl1.id = tbl2.id;

Where the definition of table_1 is:

CREATE TABLE table_1(
    id NUMBER(19, 0),
    -- other columns
)

... and table_2 ...

CREATE TABLE table_2(
    id NUMBER(19, 0),
    col_id NUMBER(19, 0),
    value NUMBER(14, 2),

    -- other columns
    FOREING KEY (col_id) REFERENCES table_1(id);
)

Upvotes: 0

Views: 2338

Answers (2)

Christian Beikov
Christian Beikov

Reputation: 16400

This is not possible with plain JPA or Hibernate. With Hibernate you can model this query if the subquery is static, but for dynamic subqueries you will need something like Blaze-Persistence which works on top of JPA/Hibernate and provides support for these things.

For the static query solution you can do this:

@Entity
@Table(name = "table1")
public class Table1 {
  @Column(name = "id")
  private Integer id;
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "id", insertable = false, updatable = false)
  private Table2 table2;
}

@Entity
@Subselect("SELECT col_id AS id, SUM(value) AS total FROM table_2 WHERE active = 1 GROUP BY col_id")
public class Table2Query {
  @Column(name = "id")
  private Integer id;
  @Column(name = "total")
  private BigDecimal total;
}

Here is a nice article by Vlad Mihalcea about Blaze-Persistence if you want a dynamic solution i.e. where the query structure isn't fixed: https://vladmihalcea.com/blaze-persistence-jpa-criteria-queries/

Upvotes: 1

Jade Gonçalves
Jade Gonçalves

Reputation: 1

Use the join method in root and use it to get the values from the other table. Note: you need to add the relation in the entity depending on the relationship of these tables (onetone, manytoone or onetomany). Something like this:

Entity code Table1:

@OneToOne
private Table2 table2;

Search code example:

(Root<Table1> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
           Join<Table1, Table2> joinTable2 = root.join("table2");
           cb.equal(joinTable2.get("active"), 1);
           .. other filters ..
};

Upvotes: 0

Related Questions