歡迎來到Hibernate本地SQL查詢示例教程。我們在早前的文章中研究了Hibernate查詢語言和Hibernate標準，今天我們將通過示例來了解Hibernate本地SQL查詢。
Hibernate SQL查詢
Hibernate 提供了通過使用 SQLQuery 對象執行本地 SQL 查詢的選項。當我們需要執行不受 Hibernate API 支持的特定於數據庫供應商的查詢時，Hibernate SQL 查詢非常方便。例如，在 Oracle 數據庫中使用查詢提示或 CONNECT 關鍵字。對於正常情況，Hibernate SQL 查詢不是推薦的方法，因為我們會失去與 Hibernate 聯繫和 hibernate 一級緩存 相關的好處。我將使用 MySQL 數據庫和與 HQL 示例 中使用的相同表和數據設置，所以您應該首先檢查該示例以了解表和相應的模型類映射。
Hibernate 本地 SQL 示例
對於 Hibernate 本地 SQL 查詢，我們使用 Session.createSQLQuery(String query) 來創建 SQLQuery 對象並執行它。例如，如果您想要讀取 Employee 表中的所有記錄，我們可以通過以下代碼實現。
// 預處理
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.getCurrentSession();
// 獲取所有員工
Transaction tx = session.beginTransaction();
SQLQuery query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee");
List rows = query.list();
for(Object[] row : rows){
Employee emp = new Employee();
emp.setId(Long.parseLong(row[0].toString()));
emp.setName(row[1].toString());
emp.setSalary(Double.parseDouble(row[2].toString()));
System.out.println(emp);
}
當我們執行上述數據設置的代碼時，它會生成以下輸出。
Hibernate: select emp_id, emp_name, emp_salary from Employee
Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
Id= 2, Name= David, Salary= 200.0, {Address= null}
Id= 3, Name= Lisa, Salary= 300.0, {Address= null}
Id= 4, Name= Jack, Salary= 400.0, {Address= null}
請注意，list()方法返回Object數組的列表，我們需要明確將它們解析為double、long等。我們的Employee和Address類別具有以下toString()方法實現。
@Override
public String toString() {
return "Id= " + id + ", Name= " + name + ", Salary= " + salary
+ ", {Address= " + address + "}";
}
@Override
public String toString() {
return "AddressLine1= " + addressLine1 + ", City=" + city
+ ", Zipcode=" + zipcode;
}
請注意，我們的查詢未返回Address數據，而如果我們使用HQL查詢"from Employee"，它會返回相關的表數據。
Hibernate SQL查詢addScalar
Hibernate使用ResultSetMetadata來推斷查詢返回的列的類型，從性能的角度來看，我們可以使用addScalar()方法來定義列的數據類型。然而，我們仍然會以Object數組的形式獲取數據。
//獲取所有員工 - addScalar示例
query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee")
.addScalar("emp_id", new LongType())
.addScalar("emp_name", new StringType())
.addScalar("emp_salary", new DoubleType());
rows = query.list();
for(Object[] row : rows){
Employee emp = new Employee();
emp.setId(Long.parseLong(row[0].toString()));
emp.setName(row[1].toString());
emp.setSalary(Double.parseDouble(row[2].toString()));
System.out.println(emp);
}
生成的輸出將是相同的，但是在數據量巨大時，我們將看到輕微的性能提升。
Hibernate原生SQL多表
如果我們想從Employee和Address表中獲取數據，我們可以簡單地編寫SQL查詢並解析結果集。
query = session.createSQLQuery("select e.emp_id, emp_name, emp_salary,address_line1, city,
zipcode from Employee e, Address a where a.emp_id=e.emp_id");
rows = query.list();
for(Object[] row : rows){
Employee emp = new Employee();
emp.setId(Long.parseLong(row[0].toString()));
emp.setName(row[1].toString());
emp.setSalary(Double.parseDouble(row[2].toString()));
Address address = new Address();
address.setAddressLine1(row[3].toString());
address.setCity(row[4].toString());
address.setZipcode(row[5].toString());
emp.setAddress(address);
System.out.println(emp);
}
對於以上代碼，輸出的結果將如下所示。
Hibernate: select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id
Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}
使用Hibernate本地SQL實體和連接
我們還可以使用addEntity()和addJoin()方法通過表聯接從關聯表中提取數據。例如，上面的數據也可以如下檢索。
// 使用addEntity和addJoin的聯接示例
query = session.createSQLQuery("select {e.*}, {a.*} from Employee e join Address a ON e.emp_id=a.emp_id")
.addEntity("e",Employee.class)
.addJoin("a","e.address");
rows = query.list();
for (Object[] row : rows) {
for(Object obj : row) {
System.out.print(obj + "::");
}
System.out.println("\n");
}
// 上面的聯接在數組中返回員工和地址對象
for (Object[] row : rows) {
Employee e = (Employee) row[0];
System.out.println("Employee Info::"+e);
Address a = (Address) row[1];
System.out.println("Address Info::"+a);
}
{[aliasname].*} 用於返回實體的所有屬性。 當我們像上面那樣使用addEntity()和addJoin()與聯接查詢時，它將返回上述兩個對象，如上所示。 由上述代碼產生的輸出如下。
Hibernate: select e.emp_id as emp_id1_1_0_, e.emp_name as emp_name2_1_0_, e.emp_salary as emp_sala3_1_0_, a.emp_id as emp_id1_0_1_, a.address_line1 as address_2_0_1_, a.city as city3_0_1_, a.zipcode as zipcode4_0_1_ from Employee e join Address a ON e.emp_id=a.emp_id
Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129::
Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051::
Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100::
Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}::AddressLine1= City Centre, City=New Delhi, Zipcode=100100::
Employee Info::Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
Address Info::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129
Employee Info::Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
Address Info::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051
Employee Info::Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
Address Info::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100
Employee Info::Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}
Address Info::AddressLine1= City Centre, City=New Delhi, Zipcode=100100
您可以在mysql客戶端中運行這兩個查詢並注意到產生的輸出相同。
mysql> select e.emp_id as emp_id1_1_0_, e.emp_name as emp_name2_1_0_, e.emp_salary as emp_sala3_1_0_, a.emp_id as emp_id1_0_1_, a.address_line1 as address_2_0_1_, a.city as city3_0_1_, a.zipcode as zipcode4_0_1_ from Employee e join Address a ON e.emp_id=a.emp_id;
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
| emp_id1_1_0_ | emp_name2_1_0_ | emp_sala3_1_0_ | emp_id1_0_1_ | address_2_0_1_ | city3_0_1_ | zipcode4_0_1_ |
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
| 1 | Pankaj | 100 | 1 | Albany Dr | San Jose | 95129 |
| 2 | David | 200 | 2 | Arques Ave | Santa Clara | 95051 |
| 3 | Lisa | 300 | 3 | BTM 1st Stage | Bangalore | 560100 |
| 4 | Jack | 400 | 4 | City Centre | New Delhi | 100100 |
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
4 rows in set (0.00 sec)
mysql> select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id;
+--------+----------+------------+---------------+-------------+---------+
| emp_id | emp_name | emp_salary | address_line1 | city | zipcode |
+--------+----------+------------+---------------+-------------+---------+
| 1 | Pankaj | 100 | Albany Dr | San Jose | 95129 |
| 2 | David | 200 | Arques Ave | Santa Clara | 95051 |
| 3 | Lisa | 300 | BTM 1st Stage | Bangalore | 560100 |
| 4 | Jack | 400 | City Centre | New Delhi | 100100 |
+--------+----------+------------+---------------+-------------+---------+
4 rows in set (0.00 sec)
mysql>
帶參數的Hibernate本地SQL查詢
我們也可以像JDBC PreparedStatement一樣，為Hibernate SQL查詢傳遞參數。參數可以使用名稱或索引設置，如下例所示。
query = session
.createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = ?");
List<Object[]> empData = query.setLong(0, 1L).list();
for (Object[] row : empData) {
Employee emp = new Employee();
emp.setId(Long.parseLong(row[0].toString()));
emp.setName(row[1].toString());
emp.setSalary(Double.parseDouble(row[2].toString()));
System.out.println(emp);
}
query = session
.createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = :id");
empData = query.setLong("id", 2L).list();
for (Object[] row : empData) {
Employee emp = new Employee();
emp.setId(Long.parseLong(row[0].toString()));
emp.setName(row[1].toString());
emp.setSalary(Double.parseDouble(row[2].toString()));
System.out.println(emp);
}
上述代碼產生的輸出為：
Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
Id= 2, Name= David, Salary= 200.0, {Address= null}
這就是Hibernate SQL查詢的簡要介紹，除非您想執行特定於數據庫的查詢，否則應該避免使用它。
Source:
https://www.digitalocean.com/community/tutorials/hibernate-native-sql-query-example