Lets review basic many-to-many relationship between tables and build common search queries in MySQL and Hibernate HQL. We will take this site’s database schema which has article-tag many-to-many relationship as an example and will try to build search queries to find articles by specific tags.

Here is our database schema that implements many-to-many relationship using intermediate “article_tag” table:

 

 

Corresponding Hibernate mappings:

<hibernate-mapping>     <class name=ca.sergiy.model.Tag" table="tag">         <cache usage="read-write"/>         <id name="id" column="id" type="long">             <generator class="native"/>         </id>         <property name="name" column="name"/>     </class> </hibernate-mapping> 

<hibernate-mapping>     <class name="ca.sergiy.model.Article" table="article">         <cache usage="read-write" />         <id name="id" column="id" type="long">             <generator class="native" />         </id>         <property name="title" column="title" />         <set name="tags" table="article_tag" lazy="false">             <key column="articleid" />             <many-to-many class="ca.sergiy.model.Tag" column="tagid" />         </set>     </class> </hibernate-mapping> 

#1. Find all articles that are tagged with any of tag₁, tag₂, …, tag_n

MySQL query to select all articles that have “Java” or “Hibernate” among their assigned tags:

SELECT DISTINCT a.* FROM   `article` a        INNER JOIN article_tag at          ON at.articleid = a.id        INNER JOIN tag t          ON t.id = at.tagid WHERE  t.name IN ("Java", "Hibernate") 

Corresponding Hibernate HQL:

String[] tags = {"Java", "Hibernate"}; String hql = "select distinct a from Article a " +                 "join a.tags t " +                 "where t.name in (:tags)"; Query query = session.createQuery(hql); query.setParameterList("tags", tags); List<Article> articles = query.list(); 

This query will also work for a single tag (find all articles tagged with “Java”)

#2. Find all articles that have no tags assigned

MySQL query:

SELECT   a.* FROM     `article` a          LEFT JOIN article_tag at            ON at.articleid = a.id GROUP BY a.id HAVING   Count(at.tagid) = 0 

Corresponding Hibernate HQL:

String hql = "select a from Article a " +                 "left join a.tags t " +                 "group by a " +                 "having count(t)=0"; Query query = session.createQuery(hql); List<Article> articles = query.list(); 

Note that this query uses LEFT JOIN.

#3. Find all articles that are tagged with at least tag₁, tag₂, …, tag_n

MySQL query to select all articles that have at least both “Java” and “Hibernate” among their assigned tags:

SELECT a.* FROM   article a        INNER JOIN (SELECT   at.articleid                    FROM     article_tag at                             INNER JOIN article a                               ON a.id = at.articleid                             INNER JOIN tag t                               ON t.id = at.tagid                    WHERE    t.name IN ("Java","Hibernate")                    GROUP BY at.articleid                    HAVING   Count(at.articleid) = 2) aa          ON a.id = aa.articleid 

Hibernate HQL, looks much cleaner:

String[] tags = {"Java", "Hibernate"}; String hql = "select a from Article a " +                 "join a.tags t " +                 "where t.name in (:tags) " +                 "group by a " +                 "having count(t)=:tag_count"; Query query = session.createQuery(hql); query.setParameterList("tags", tags); query.setInteger("tag_count", tags.length); List<Article> articles = query.list(); 

#4. Find all articles that are tagged with exactly tag₁, tag₂, …, tag_n

MySQL query to select all articles that are tagged with exactly “Java” and “Hibernate” tags (no other tags assigned):

SELECT a.* FROM   article a        INNER JOIN (SELECT   at.articleid                    FROM     article_tag at                    WHERE    at.articleid IN (SELECT   at2.articleid                                              FROM     article_tag at2                                                       INNER JOIN article a2                                                         ON a2.id = at2.articleid                                              GROUP BY at2.articleid                                              HAVING   Count(at2.articleid) = 2)                             AND at.tagid IN (SELECT id                                              FROM   tag t                                              WHERE  t.name IN ("Java","Hibernate"))                    GROUP BY at.articleid                    HAVING   Count(at.articleid) = 2) aa          ON a.id = aa.articleid 

Hibernate HQL:

String[] tags = {"Java", "Hibernate"}; String hql = "select a from Article a " +                 "join a.tags t " +                 "where t.name in (:tags) " +                 "and a.id in (" +                     "select a2.id " +                     "from Article a2 " +                     "join a2.tags t2 " +                     "group by a2 " +                     "having count(t2)=:tag_count) " +                 "group by a " +                 "having count(t)=:tag_count"; Query query = session.createQuery(hql); query.setParameterList("tags", tags); query.setInteger("tag_count", tags.length); List<Article> articles = query.list(); 

Basically it is query #3 with extra condition applied: total number of tags should be n.