Hibernate: How to get a field from a join table using @SecondaryTable with predicate

Question

I have 3 tables, but I'm only interested in

• table A
• the 'amount' field from A_Cat (this is the join table between "A" and "Category") for a well defined value of cat_Id. This predicate makes the relation a OneToOne instead of a OneToMany.

This is the simplified structure:

CREATE TABLE A
(
    a_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL
);

CREATE TABLE Category
(
    cat_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL
);

CREATE TABLE A_Category
(
    a_id INT PRIMARY KEY,
    cat_id INT PRIMARY KEY,
    amount INTEGER NOT NULL
);

Java code:

@Entity
@Table(name = "A")
@SecondaryTable(name = "A_Category", pkJoinColumns = {@PrimaryKeyJoinColumn(name = "a_id")}))
public class ModelA {

    @Id
    @Column(name = "a_id")
    private Long id;

    @Column(name = "amount", table = "A_Category")
    private Integer amount;
}

This generates a join in Hibernate between "A" and "A_Category" for all the possible values.

So, here's my question, how to add a predicate to the SecondaryTable (eg: cat_id = 1).

Can it be done with an annotation? I tried it with @WhereJoinTable but no luck.

PS: I can obtain what I want if I use @Formula, but if I want more fields from A_Category then the generated sql is not that performant and like to avoid writing sql in my POJO.

Answer 1

JPA Entities

JPA entities must reflect the database structure and should not directly involve business logic. From your simplified structure, I assume you could use these entities:

@Entity
@Table(name = "A")
public class A{

    @Id
    @Column(name = "a_id")
    private Long id;

    @Column(name = "title")
    private String title;

    @OneToMany(mappedBy = "a")
    private List<ACategory> categoryList;

    // no-arg constructor
    public A(){
        // initialise the list in the no-arg constructor to avoid bad surprises...
        this.categoryList = new ArrayList<>();
    }

    // getters & setters
}

As the ManyToMany relationship has an extra column, you'll need the ACategory entity:

@Entity
@Table(name = "A_Category")
public class ACategory{

    // dedicated id column, see note(*) below
    @Id
    @Column(name = "id")
    private Long id;

    // foreign key to "A" table
    @ManyToOne
    @JoinColumn(name = "a_id")
    private A a;

    // foreign key to "Category" table
    @ManyToOne
    @JoinColumn(name = "cat_id")
    private Category category;

    @Column(name = "amount")
    private Integer amount;

    // no-arg constructor

    // getters & setters
}

^{(*): Notice that ACategory has a dedicated id column. It'll avoid some issue such as this question.}

For completeness sake, the Category entity looks like

@Entity
@Table(name = "Category")
public class Category{

        @Id
        @Column(name = "cat_id")
        private Long id;

        @Column(name = "title")
        private String title;

        @OneToMany(mappedBy = "category")
        private List<ACategory> aList;

        // no-arg constructor
        public Category(){
            this.aList = new ArrayList<>();
        }

        // getters & setters
}

NamedQueries

Then a way to handle your issue is to use JPQL @NamedQuery where you can have business logic. An example could be:

@Entity
@Table(name = "A")
@NamedQuery(
    name="A.findAllByCategoryId"
    query="SELECT a FROM A a "
        + "INNER JOIN a.categoryList relACat "
        + "INNER JOIN relACate.category c"
        + "WHERE c.id = :categoryId"
)
public class A{
    // cf. above
}

I used named parameters but you can also use ordinal parameters.

Querying

Then, in your service associated to A entities, you can use the named query in this way:

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

@Stateless
public class AService{

    // @PersistenceContext properly filled
    private EntityManager em;

    public List<A> fetchAEntities(){
        TypedQuery<A> query = em.createNamedQuery("A.findAllByCategoryId", A.class);
        // it seems you can afford hardcoding it
        query.setParameter("categoryId", 1);

        return query.getResultLis();
    }
}

If instead of a A list, you wish to have specific A entities, just create another NamedQuery with additional parameter in the WHERE clause.

---

^{As a non-Hibernate user, this is a pure JPA solution}