欢迎使用Hibernate Native SQL Query示例教程。
我们在之前的文章中研究了Hibernate Query Language和Hibernate Criteria，今天我们将通过示例研究Hibernate Native SQL查询。

Hibernate SQL查询

Hibernate提供了通过使用SQLQuery对象执行本机SQL查询的选项。
当我们必须执行Hibernate API不支持的数据库供应商特定查询时，Hibernate SQL Query非常方便。
例如，查询提示或者Oracle数据库中的CONNECT关键字。

在正常情况下，不建议使用Hibernate SQL查询，因为我们失去了与Hibernate关联和Hibernate一级缓存有关的好处。

我将使用与HQL示例中使用的MySQL数据库以及相同的表和数据设置，因此您应该先检查一下以了解表和对应的模型类映射。

Hibernate本机SQL示例

对于Hibernate Native SQL Query，我们使用Session.createSQLQuery(String query)创建SQLQuery对象并执行它。
例如，如果您想从Employee表中读取所有记录，我们可以通过以下代码来完成。

//Prep work
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.getCurrentSession();

//Get All Employees
Transaction tx = session.beginTransaction();
SQLQuery query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee");
List<Object[]> 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= hyman, 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= Hyman, Salary= 400.0, {Address= null}

注意，list()方法返回Object数组的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查询"来自雇员"，它也会返回关联的表数据。

Hibernate SQL查询addScalar

Hibernate使用" ResultSetMetadata"来推断查询返回的列的类型，从性能的角度来看，我们可以使用" addScalar()"方法来定义列的数据类型。
但是，我们仍将以对象数组的形式获取数据。

//Get All Employees - addScalar example
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= hyman, 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= Hyman, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}

Hibernate本机SQL实体和联接

我们还可以使用addEntity()和addJoin()方法使用表连接从关联表中获取数据。
例如，上面的数据也可以如下检索。

//Join example with addEntity and 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");
}
//Above join returns both Employee and Address Objects in the array
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= hyman, 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= Hyman, 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= hyman, 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= Hyman, 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 | hyman         |            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 | Hyman           |            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 | hyman   |        100 | Albany Dr     | San Jose    | 95129   |
|      2 | David    |        200 | Arques Ave    | Santa Clara | 95051   |
|      3 | Lisa     |        300 | BTM 1st Stage | Bangalore   | 560100  |
|      4 | Hyman     |        400 | City Centre   | New Delhi   | 100100  |
+--------+----------+------------+---------------+-------------+---------+
4 rows in set (0.00 sec)

mysql>

具有参数的Hibernate本机SQL查询

我们还可以将参数传递给Hibernate SQL查询，就像JDBC PreparedStatement一样。
可以使用名称和索引来设置参数，如下例所示。

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= hyman, 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查询的简要介绍，除非您要执行任何数据库特定的查询，否则应避免使用它。