欢迎来到Hibernate本地SQL查询示例教程。在之前的文章中，我们已经研究了Hibernate查询语言和Hibernate Criteria，今天我们将通过示例来了解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()方法返回对象数组的列表，我们需要明确将它们解析为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()方法来定义列的数据类型。然而，我们仍然会以对象数组的形式获得数据。
//获取所有员工 - 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");
}
//上面的连接返回数组中的Employee和Address对象
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