Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I map entity association using a query instead of a @JoinColumn with JPA?

I’m using Hibernate 4.1.3.Final with JPA 2.1 and MySQL 5.5.37. I have an entity with the following field:

@Entity
@Table(name = "category",
       uniqueConstraints = { @UniqueConstraint(columnNames = { "NAME" })}
)
public class Category implements Serializable, Comparable<Category> {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @NotEmpty
    private Set<Subject> subjects;

    ...
}

There is no simple join table to link up the subjects field, instead, there is a slightly more complex MySQL query. Here is an example of figuring out the subjects given a particular category id:

SELECT DISTINCT e.subject_id 
FROM category c, resource_category rc, product_resource pr, 
     sb_product p, product_book pe, book e 
WHERE c.id = rc.category_id 
  AND rc.resource_id = pr.resource_id 
  AND pr.product_id = p.id 
  AND p.id = pe.product_id 
  AND pe.ebook_id = e.id 
  AND c.id = ‘ABCEEFGH‘;

What is the simplest way to wire up the above field using the query below when loading categories?

This question concerns dealing with Java to accomplish this so building a view or doing some other type of MySQL madness is not an option, at least as an answer for this question.

Edit:

Added the notation per the suggestion (replacing '="ABCDEFG"' with '=id') but Hibernate is generating this invalid SQL when I do queries for items tied to the Category entity. Here is the SQL Hibernate spits out

SELECT categories0_.resource_id AS RESOURCE1_75_0_,
       categories0_.category_id AS CATEGORY2_76_0_,
       category1_.id            AS ID1_29_1_,
       category1_.NAME          AS name2_29_1_,SELECT DISTINCT e.subject_id
FROM            category c,
                resource_category rc,
                product_resource pr,
                product p,
                product_ebook pe,
                book e
WHERE           c.id = rc.category_id
AND             rc.resource_id = pr.resource_id
AND             pr.product_id = p.id
AND             p.id = pe.product_id
AND             pe.ebook_id = e.id
AND             c.id = category1_.id as formula1_1_,
                subject2_.id         AS id1_102_2_,
                subject2_.NAME       AS name2_102_2_
FROM            resource_category categories0_
INNER JOIN      category category1_
ON              categories0_.category_id=category1_.id
LEFT OUTER JOIN subject subject2_
ONSELECT DISTINCT e.subject_id
FROM            category c,
                resource_category rc,
                product_resource pr,
                product p,
                product_ebook pe,
                book e
WHERE           c.id = rc.category_id
AND             rc.resource_id = pr.resource_id
AND             pr.product_id = p.id
AND             p.id = pe.product_id
AND             pe.ebook_id = e.id
AND             c.id = category1_.id=subject2_.id
where           categories0_.resource_id=?

Notice the "left outer join subject subject2_ on SELECT DISTINCT e.subject_id" and "AND c.id = category1_.id=subject2_.id" towards the end.

Edit 2:

Here is the entity involved in the above query

@Entity
@Table(name="resource")
public class Resource implements Serializable, Comparable<Resource>
{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    …    
    @Column(name = "FILE_NAME")
    private String fileName;

    @Column(name = "URI")
    private String uri;

    …

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "resource_category", joinColumns = { @JoinColumn(name = "RESOURCE_ID") }, inverseJoinColumns = { @JoinColumn(name = "CATEGORY_ID") })
    private Set<Category> categories;

and here is the query itself …

CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteria = builder.createQuery(Resource.class);
Root<T> rootCriteria = criteria.from(Resource.class);
criteria.select(rootCriteria).where(builder.equal(rootCriteria.get(“uri”),uri));
Resource ret = null;
try {
        final TypedQuery<T> typedQuery = m_entityManager.createQuery(criteria);
        ret = typedQuery.getSingleResult();
} catch (NoResultException e) {
        LOG.warn(e.getMessage());
}
return ret; 
like image 995
Dave A Avatar asked Apr 10 '15 16:04

Dave A


People also ask

Is @JoinColumn mandatory?

It is not necessary to have @JoinColumn annotation. You can always override it. If you won't provide it in your code then Hibernate will automatically generate one for you i.e. default name for your column. Could you please be more specific?

How do I map OneToMany?

Create an entity class Student. java under com. javatpoint. mapping package that contains student id (s_id), student name (s_name) with @OneToMany annotation that contains Library class object of List type.

What is association mapping in JPA?

Association mappings are one of the key features of JPA and Hibernate. They model the relationship between two database tables as attributes in your domain model. That allows you to easily navigate the associations in your domain model and JPQL or Criteria queries.


1 Answers

You need to use Hibernate specific JoinColumnOrFormula:

public class Category implements Serializable, Comparable<Category> {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @NotEmpty
    @ManyToOne
    @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(
            formula = @JoinFormula(
                value = 
                    "SELECT DISTINCT e.subject_id " +
                    "FROM category c, resource_category rc, product_resource pr, " +
                    "     sb_product p, product_book pe, book e " +
                    "WHERE c.id = rc.category_id " +
                    "  AND rc.resource_id = pr.resource_id " +
                    "  AND pr.product_id = p.id " +
                    "  AND p.id = pe.product_id " +
                    "  AND pe.ebook_id = e.id " +
                    "  AND c.id = ‘ABCEEFGH‘", 
                referencedColumnName="id"
            )
        )
    })
    private Set<Subject> subjects;

    ...
}

Or, you can include this query in a stored procedure:

CREATE FUNCTION join_book(text) RETURNS text
    AS  'SELECT DISTINCT e.subject_id ' +
        'FROM category c, resource_category rc, product_resource pr, ' +
        '     sb_product p, product_book pe, book e ' +
        'WHERE c.id = rc.category_id ' +
        '  AND rc.resource_id = pr.resource_id ' +
        '  AND pr.product_id = p.id ' +
        '  AND p.id = pe.product_id ' +
        '  AND pe.ebook_id = e.id ' +
        '  AND c.id = $1;'  
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

And, then your mapping becomes:

public class Category implements Serializable, Comparable<Category> {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    @NotEmpty
    @ManyToOne
    @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(
            formula = @JoinFormula(
                value = "join_book(id)", 
                referencedColumnName="id"
            )
        )
    })
    private Set<Subject> subjects;

    ...
}
like image 159
Vlad Mihalcea Avatar answered Sep 20 '22 00:09

Vlad Mihalcea