在《【Hibernate】Hibernate的聚類查詢、分組查詢、排序與時間之差》(點擊打開鏈接)一文中已經講述過如何利用HQL語句取代SQL語句,進行聚類查詢、分組查詢、排序與時間之差的查詢,同時指出hql能代替sql語句做任何事情。我原本以為hql語句的多表查詢,要先對裡面的實體做Java與xml的修改,其實並不需要,同樣是一句HQL語句就能搞定的事情。SQL的多表查詢已經在《【Mysql】利用內連接與嵌套查詢實現多表查詢,主鍵、外鍵的基本概念》(點擊打開鏈接)講過。
比如如下的SQL語句:
select t1.Title,t1.Content from blog as t1 ,usertable as t2 where t1.userid=t2.id and t2.username='a'
String hql="select t1.title,t1.content from Blog as t1,Usertable as t2 where t1.userId=t2.id and t2.username='a'" List<Object> resultList = session.createQuery(hql).list(); for (int i = 0; i < resultList.size(); i++) { Object[] obj = (Object[])resultList.get(i); System.out.println(obj[0]+","+obj[1]); }
Hibernate查詢出來的結果是一個存放Object數組的List,也就是說List的每項都是一個Object數組,Object數組的第n項對應查詢結果的第n項。
可以再進行下一步的處理。
下面用一個例子,來說明HQL語句的多表查詢。
如圖,Blog記錄了用戶發表的博客,usertable記錄了用戶的基本信息。Blog表中的userid與usertable的主鍵id形成參照完整性。
這兩張表在Hibernate的Java工程種分別對應如下實體:
Blog.java
import javax.persistence.*; @Entity @Table(name = "blog") public class Blog { private int id; private String title; private String content; private int userId; @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } @Column(name = "Title") public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } @Column(name = "Content") public String getContent() { return content; } public void setContent(String content) { this.content = content; } @Column(name = "userid") public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } @Override public String toString() { return id + "," + title + "," + content + "," + userId; } }Usertable.java
import javax.persistence.*; @Entity @Table(name = "usertable") public class Usertable { private int id; private String username; private String password; @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } @Column(name = "username") public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } @Column(name = "password") public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return id + "," + username + "," + password; } }
同時,hibernate.cfg.xml做如下的配置:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!--所用的數據庫驅動 --> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <!--所用的數據庫登陸密碼 --> <property name="hibernate.connection.password">admin</property> <!--所用的數據庫名稱為test,根據實際更改 --> <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property> <!--所用的數據庫用戶名 --> <property name="hibernate.connection.username">pc</property> <!--所用的數據庫方言,與所用數據庫驅動一樣,可以在網上查到,這裡是mysql --> <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property> <property name="hibernate.format_sql">true</property> <!--如果是update表明Hibernate將保留原來的數據記錄,插入時把新記錄添加到已有的表, --> <!--如果是create,則總是創建新的表,如果原來數據庫已有的這個表,則這個表的記錄會被全部清洗 --> <property name="hibernate.hbm2ddl.auto">update</property> <!--羅列Testtable表與Java文件的映射 --> <!--將數據庫中的usertable表,blog表映射到Usertable.java與Blog.java兩個實體 --> <mapping class="Usertable" /> <mapping class="Blog" /> </session-factory> </hibernate-configuration>可以發現,這沒有進行任何的參照完整性的指定。 下面,要查詢usertable中username為a的用戶,發表的Blog。
我們知道先要查詢username為a的用戶的id,之後利用這個查出來的id到Blog表中查詢。
用sql語句完成這個多表查詢,則這樣寫:
select t1.Title,t1.Content from blog as t1 ,usertable as t2 where t1.userid=t2.id and t2.username='a'其查詢結果如下:
這使用Hibernate則這樣寫,在HibernateMultiTableTest.java中的代碼如下:<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+PHByZSBjbGFzcz0="brush:sql;">import java.util.List;
import org.hibernate.*;
import org.hibernate.cfg.*;
class dbDAO {
private Session session;
// 構造函數,初始化Session,相當於連接數據庫
public dbDAO() {
// new Configuration().configure()是吧hibernate.cfg.xml中的所有配置讀取進來
// .buildSessionFactory().openSession()是創建Session工廠並實例化session
this.session = new Configuration().configure().buildSessionFactory()
.openSession();
}
// 執行查詢
public Query query(String hql) {
return session.createQuery(hql);
}
// 執行插入、修改
public void save(Object object) {
Transaction transaction = session.beginTransaction();
session.save(object);
transaction.commit();
}
// 執行刪除
public void delete(Object object) {
Transaction transaction = session.beginTransaction();
session.delete(object);
transaction.commit();
}
// 析構函數,中斷Session,相當於中斷數據庫的連接
protected void finalize() throws Exception {
if (session.isConnected() || session != null) {
session.close();
}
}
}
@SuppressWarnings("unchecked")
public class HibernateMultiTableTest {
public static void main(String args[]) {
dbDAO db = new dbDAO();
List<Object> resultList = db
.query("select t1.title,t1.content from Blog as t1,Usertable as t2 where t1.userId=t2.id and t2.username='a'")
.list();//HQL的多表查詢
System.out.println("usertable中username為a的用戶,發表的內容如下:");
System.out.println();
for (int i = 0; i < resultList.size(); i++) {
Object[] obj = (Object[]) resultList.get(i);
System.out.println("標題:" + obj[0]);
System.out.println("內容:" + obj[1]);
System.out.println();
}
}
}
其運行結果如下: