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.tagidWHERE 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.idGROUP BY a.idHAVING 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.
分享到:
相关推荐
how-to-write-a-great-research-paper-wiley-keys-to-success.pdf
How to write a CHIP-8 emulator: - EBook (epub format) - Source code (C++)
how-to-write-injection-proof-plsql
Hibernate Search is a library providing full-text search capabilities to Hibernate. It opens doors to more human friendly and efficient search engines while still following the Hibernate and Java ...
How to Write a Good Scientific Paper by Chris A. Mack is a valuable resource for all students and faculty in science and engineering. SPIE is pleased to make it available here at no charge as a PDF ...
微软大牛教你怎样写一篇好文章,内容丰富,翔实,可操作性强。
How to write and publish a scientific paper ContentsChapter 1 What Is Scientific Writing? Chapter 2 Origins of Scientific Writing Chapter 3 What Is a Scientific Paper? Chapter 4 How to Prepare the ...
Invasive Computing for Mapping Parallel Programs to Many-Core Architectures (Computer Architecture and Design Methodologies) By 作者: Andreas Weichslgartner – Stefan Wildermann – Michael Glaß – J...
How to Write & Publish a Scientific Paper,教你怎样发布期刊,硕博士极力推荐
How to Define and Use Formatted Search in SAP business one
Bioinformatics-with-Python-Cookbook-Learn-how-to-use-modern-Python-bioinformatics-libraries-and-applications-to-do-cutting-edge-research-in-computational-biology.pdf
Grounded in decades of experience with research training and supervision, this fully updated and revised edition takes an integrated, down-to-earth approach drawing on case studies and examples to ...
主要为关于论文写作的建议和方法,内部包含有三个文档,分别为:《How to Write Good Reviews for CVPR》、《Teaching Graduate Students How to Write Clearly》以及《How write a Paper》三个文档,十分具有借鉴...
convincingly, and how to run valid and successful A/B and multivariate testing of various designs. These articles are a selection of the best from Smashing Magazine in 2009 and 2010 dealing with ...
How to Write and Publish a Scientific Paper 非影印版,可以复制粘贴
A special feature of this edition is a new appendix on NoSQL and relational theory.Could you write an SQL query to find employees who have worked at least once in every programming department in the ...
This book provides practical, proven techniques for making writing for ... All levels of business and technical personnel will find this easy-to-read guide invaluable and immediately useful every day.
PHP and MySQL in easy steps, 2nd edition teaches the user to write PHP server-side scripts and how to make MySQL database queries. It has an easy-to-follow style that will appeal to: anyone who wants...
How to install mysql on ubuntu