How to Write Many-To-Many Search Queries in MySQL and Hibernate
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 tag1, tag2, ..., tagn
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 tag1, tag2, ..., tagn
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 tag1, tag2, ..., tagn
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.
Guide to Selecting Appropriate Map/Collection in Java
Java API contains numerous Collection and Map implementations so it might be confusing to figure out which one to use. Here is a quick flowchart that might help with choosing from the most common implementations.
This cheat sheet doesn't include rarely used classes like WeakHashMap, LinkedList, etc. because they are designed for very specific or exotic tasks and shouldn't be chosen in 99% cases.
How to Iterate Over a Map in Java
There are several ways of iterating over a Map in Java. Lets go over the most common methods and review their advantages and disadvantages. Since all maps in Java implement Map interface, following techniques will work for any map implementation (HashMap, TreeMap, LinkedHashMap, Hashtable, etc.)
Method #1: Iterating over entries using For-Each loop.
This is the most common method and is preferable in most cases. Should be used if you need both map keys and values in the loop.
Map<Integer, Integer> map = new HashMap<Integer, Integer>();
for (Map.Entry<Integer, Integer> entry : map.entrySet()) {
System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
}
Note that For-Each loop was introduced in Java 5 so this method is working only in newer versions of the language. Also For-Each loop will throw NullPointerException if you try to iterate over a map that is null, so before iterating you should always check for null references.
Method #2: Iterating over keys or values using For-Each loop.
If you need only keys or values from the map, you can iterate over keySet or values instead of entrySet.
Map<Integer, Integer> map = new HashMap<Integer, Integer>();
//iterating over keys only
for (Integer key : map.keySet()) {
System.out.println("Key = " + key);
}
//iterating over values only
for (Integer value : map.values()) {
System.out.println("Value = " + value);
}
This method gives slight performance advantage over entrySet iteration (about 10% faster) and is more clean.
Method #3: Iterating using Iterator.
Using Generics:
Map<Integer, Integer> map = new HashMap<Integer, Integer>();
Iterator<Map.Entry<Integer, Integer>> entries = map.entrySet().iterator();
while (entries.hasNext()) {
Map.Entry<Integer, Integer> entry = entries.next();
System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
}
Without Generics:
Map map = new HashMap();
Iterator entries = map.entrySet().iterator();
while (entries.hasNext()) {
Map.Entry entry = (Map.Entry) entries.next();
Integer key = (Integer)entry.getKey();
Integer value = (Integer)entry.getValue();
System.out.println("Key = " + key + ", Value = " + value);
}
You can also use same technique to iterate over keySet or values.
This method might look redundant but it has its own advantages. First of all it is the only way to iterate over a map in older versions of Java. The other important feature is that it is the only method that allows you to remove entries from the map during iteration by calling iterator.remove(). If you try to do this during For-Each iteration you will get "unpredictable results" according to javadoc.
From performance point of view this method is equal to For-Each iteration.
Method #4: Iterating over keys and searching for values (inefficient).
Map<Integer, Integer> map = new HashMap<Integer, Integer>();
for (Integer key : map.keySet()) {
Integer value = map.get(key);
System.out.println("Key = " + key + ", Value = " + value);
}
This might look like a cleaner alternative for method #1 but in practice it is pretty slow and inefficient as getting values by a key might be time consuming (this method in different Map implementations is 20%-200% slower than method #1). If you have FindBugs installed, it will detect this and warn you about inefficient iteration. This method should be avoided.
Conclusion
If you need only keys or values from the map use method #2. If you are stuck with older version of Java (less than 5) or planning to remove entries during iteration you have to use method #3. Otherwise use method #1.