Hibernate原生SQL查询示例教程

欢迎来到Hibernate原生SQL查询示例教程。之前我们已经介绍了Hibernate查询语言和Hibernate Criteria查询，今天我们将通过实例来学习Hibernate原生SQL查询的使用方法。

Hibernate SQL查询概述

通过使用SQLQuery对象，Hibernate提供了执行本地SQL查询的选项。当我们需要执行不被Hibernate API支持的特定于数据库供应商的查询时，Hibernate SQL查询非常方便。例如，在Oracle数据库中使用查询提示或CONNECT关键字。对于正常情况，不推荐使用Hibernate SQL查询，因为我们会失去与Hibernate关联和Hibernate一级缓存相关的好处。我将使用MySQL数据库，并使用与HQL示例相同的表和数据设置，请您先查看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;

}

注意到我们的查询未返回地址数据，而如果我们使用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查询多个表

如果我们想从员工表和地址表中获取数据，我们可以简单地编写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);

}

使用`{[别名].*}`可以返回实体的所有属性。当我们在上述类似的联接查询中使用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_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 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 = ?

编号= 1, 姓名= Pankaj, 薪资= 100.0, {地址= null}

Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?

编号= 2, 姓名= David, 薪资= 200.0, {地址= null}

以上就是对Hibernate原生SQL查询的简要介绍。需要注意的是，除非需要执行特定于数据库的查询，否则应尽量避免使用原生SQL查询，而优先使用HQL或Criteria API等Hibernate提供的查询方式。