CODEWITHSUNDEEP
javamysqlhibernate
Crayl
Crayl

Reputation: 1911

What is the Hibernate equivalent to this MySQL query?

There are two Models: Page & Tag. One Page can have many Tags.

Page:

public class Page {
    @Id  
    @GeneratedValue  
    @Column(name="page_id")
    private Integer id; 

    // TAGS
    @OneToMany(orphanRemoval=true, mappedBy="pageId", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private Set<Tag> tag = new HashSet<Tag>();
}

Tag:

public class Tag {

    // GENERIC FIELDS
    @Id  
    @GeneratedValue  
    private Integer id; 

    @Index(name = "tag")
    @Column
    private String tag;

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name="page_id")
    private Page pageId;
}

The task is now to select all Pages which have a specific Tag. With plain MySQL I would do (which works fine):

SELECT * from Page join Tag on Page.page_id = Tag.page_id where Tag.tag = 'sport'

But Hibernate complains if I do it like that:

List<Page> pageList = (List<Page>) session.createQuery("from Page join Tag on Page.page_id = Tag.page_id where Tag.tag = 'sport'").list();
// ERROR: [QuerySyntaxException: unexpected token: on near line 1, column 27 [from models.Page join Tag on Page.page_id = Tag.page_id where tag = 'sport']]

When I delete the "unexpected token" then Hibernate complains again:

List<Page> pageList = (List<Page>) session.createQuery("from Page join Tag where tag = 'sport'").list();
[TypeMismatchException: left and right hand sides of a binary logic operator were incompatibile [java.util.Set(models.Page.tag) : string]]

Problem with the second approach seems to be, that Page.tag is a Set and Hibernate seems unable to create it with the result from that query.

So how can I solve this? Is there something wrong with the query or is there a way to solve the TypeMismatchException?

BONUS QUESTION: I found the following solution which somehow works.. a lil bit at least:

List<Page> pageList = (List<Page>) session.createQuery("from Page where id in (from Tag where tag = 'sport')").list();

It works.. but it only delivers half of the Pages which have the Tag "sport". Can someone explain why?

Any suggestion is highly appreciated!

Upvotes: 0

Views: 340

Answers (2)

guido
guido

Reputation: 19224

In HQL and JPQL, if you want to refer to entity properties after the FROM clause, you need to assign aliases; so your query would be:

from Page p join Tag t on p.id = t.pageId where t.tag = 'sport'

there is also an alternative implicit join syntax:

from Page p where p.tag.tag = 'sport'

To be fully compliant with JPQL, you would also specify the select clause:

select p from Page p where p.tag.tag = 'sport'

Upvotes: 1

Julien
Julien

Reputation: 1097

In HQL you simply join using collection name: 

from Page as page left join tag as tags where tags.tag = 'sport'

Your collection tag has a confusing name, I would name it 'tagList' to avoid confusion with the tag property (the String one).

Upvotes: 1

Related Questions