欢迎使用Hibernate Native SQL查询示例教程。在前面的文章中，我们学习了Hibernate Query Language和[Hibernate SQLHibernate Criteria Example Tutorial]，今天我们将通过示例来研究Hibernate Native Criteria](/community/tutorials/hibernate-criteria-example-tutorial Query。
Hibernate SQL查询
Hibernate提供使用SQLQuery 对象执行原生Sql查询的选项。当我们必须执行Hibernate API不支持的特定于数据库供应商的查询时，Hibernate SQL查询非常方便。例如，Oracle数据库中的查询提示或CONNECT关键字。对于正常场景，Hibernate SQL查询不是推荐的方法，因为我们失去了与Hibernate关联相关的好处和Hibernate First Level cache。我将使用MySql数据库和HQL example中使用的相同的表和数据设置，所以您应该首先查看一下，以了解表和相应模型类的映射。
Hibernate Native SQL示例
对于Hibernate Native SQL查询，我们使用Session.createSQLQuery(字符串查询)来创建并执行SQLQuery对象。例如，如果您想要读取Employee表中的所有记录，我们可以通过以下代码来实现。
1// Prep work
2SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
3Session session = sessionFactory.getCurrentSession();
4
5// Get All Employees
6Transaction tx = session.beginTransaction();
7SQLQuery query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee");
8List<Object[]> rows = query.list();
9for(Object[] row : rows){
10 Employee emp = new Employee();
11 emp.setId(Long.parseLong(row[0].toString()));
12 emp.setName(row[1].toString());
13 emp.setSalary(Double.parseDouble(row[2].toString()));
14 System.out.println(emp);
15}
当我们为我们拥有的数据设置执行上面的代码时，它会产生以下输出。
1Hibernate: select emp_id, emp_name, emp_salary from Employee
2Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
3Id= 2, Name= David, Salary= 200.0, {Address= null}
4Id= 3, Name= Lisa, Salary= 300.0, {Address= null}
5Id= 4, Name= Jack, Salary= 400.0, {Address= null}
注意，List()方法返回Object数组列表，需要显式解析为Double、Long等。我们的Employee和Address类有以下toString()方法实现。
1@Override
2public String toString() {
3 return "Id= " + id + ", Name= " + name + ", Salary= " + salary
4 + ", {Address= " + address + "}";
5}
1@Override
2public String toString() {
3 return "AddressLine1= " + addressLine1 + ", City=" + city
4 + ", Zipcode=" + zipcode;
5}
注意，我们的查询不返回地址数据，而如果我们使用HQL查询from Employee，它也会返回关联表数据。
Hibernate SQL查询addScalar
Hibernate使用ResultSetMetadata来推断查询返回的列的类型，从性能的角度来看，可以使用addScalar()方法来定义列的数据类型。但是，我们仍然会以对象数组的形式获取数据。
1//Get All Employees - addScalar example
2query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee")
3 .addScalar("emp_id", new LongType())
4 .addScalar("emp_name", new StringType())
5 .addScalar("emp_salary", new DoubleType());
6rows = query.list();
7for(Object[] row : rows){
8 Employee emp = new Employee();
9 emp.setId(Long.parseLong(row[0].toString()));
10 emp.setName(row[1].toString());
11 emp.setSalary(Double.parseDouble(row[2].toString()));
12 System.out.println(emp);
13}
生成的输出将是相同的，但是当数据量很大时，我们会看到性能略有提高。
Hibernate Native SQL多表
如果我们想同时从Employee和Address表中获取数据，我们只需编写相应的SQL查询并解析结果集即可。
1query = session.createSQLQuery("select e.emp_id, emp_name, emp_salary,address_line1, city,
2 zipcode from Employee e, Address a where a.emp_id=e.emp_id");
3rows = query.list();
4for(Object[] row : rows){
5 Employee emp = new Employee();
6 emp.setId(Long.parseLong(row[0].toString()));
7 emp.setName(row[1].toString());
8 emp.setSalary(Double.parseDouble(row[2].toString()));
9 Address address = new Address();
10 address.setAddressLine1(row[3].toString());
11 address.setCity(row[4].toString());
12 address.setZipcode(row[5].toString());
13 emp.setAddress(address);
14 System.out.println(emp);
15}
对于上面的代码，产生的输出如下所示。
1Hibernate: select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id
2Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
3Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
4Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
5Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}
Hibernate Native SQL实体与Join
我们也可以使用addEntity()和addJoin()方法通过表连接从关联表中获取数据。例如，还可以按如下方式检索上述数据。
1//Join example with addEntity and addJoin
2query = session.createSQLQuery("select {e.*}, {a.*} from Employee e join Address a ON e.emp_id=a.emp_id")
3 .addEntity("e",Employee.class)
4 .addJoin("a","e.address");
5rows = query.list();
6for (Object[] row : rows) {
7 for(Object obj : row) {
8 System.out.print(obj + "::");
9 }
10 System.out.println("\n");
11}
12//Above join returns both Employee and Address Objects in the array
13for (Object[] row : rows) {
14 Employee e = (Employee) row[0];
15 System.out.println("Employee Info::"+e);
16 Address a = (Address) row[1];
17 System.out.println("Address Info::"+a);
18}
{[aliasname].* }用于返回实体的所有属性。当我们将addEntity()和addJoin()与上述联接查询一起使用时，它会返回这两个对象，如下所示。上述代码产生的输出如下所示。
1Hibernate: 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
2Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129::
3
4Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051::
5
6Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100::
7
8Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}::AddressLine1= City Centre, City=New Delhi, Zipcode=100100::
9
10Employee Info::Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
11Address Info::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129
12Employee Info::Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
13Address Info::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051
14Employee Info::Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
15Address Info::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100
16Employee Info::Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}
17Address Info::AddressLine1= City Centre, City=New Delhi, Zipcode=100100
您可以在MySQL客户端中运行这两个查询，并注意到生成的输出是相同的。
1mysql> 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;
2+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
3| 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_ |
4+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
5| 1 | Pankaj | 100 | 1 | Albany Dr | San Jose | 95129 |
6| 2 | David | 200 | 2 | Arques Ave | Santa Clara | 95051 |
7| 3 | Lisa | 300 | 3 | BTM 1st Stage | Bangalore | 560100 |
8| 4 | Jack | 400 | 4 | City Centre | New Delhi | 100100 |
9+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
104 rows in set (0.00 sec)
11
12mysql> select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id;
13+--------+----------+------------+---------------+-------------+---------+
14| emp_id | emp_name | emp_salary | address_line1 | city | zipcode |
15+--------+----------+------------+---------------+-------------+---------+
16| 1 | Pankaj | 100 | Albany Dr | San Jose | 95129 |
17| 2 | David | 200 | Arques Ave | Santa Clara | 95051 |
18| 3 | Lisa | 300 | BTM 1st Stage | Bangalore | 560100 |
19| 4 | Jack | 400 | City Centre | New Delhi | 100100 |
20+--------+----------+------------+---------------+-------------+---------+
214 rows in set (0.00 sec)
22
23mysql>
带参数的Hibernate Native SQL查询
我们还可以向Hibernate SQL查询传递参数，就像[JDBC PreparedStatement]（/community/tutorials/jdbc-statement-vs-preparedstatement-sql-injection-example)一样。可以使用名称和索引设置参数，如下面的示例所示。
1query = session
2 .createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = ?");
3List<Object[]> empData = query.setLong(0, 1L).list();
4for (Object[] row : empData) {
5 Employee emp = new Employee();
6 emp.setId(Long.parseLong(row[0].toString()));
7 emp.setName(row[1].toString());
8 emp.setSalary(Double.parseDouble(row[2].toString()));
9 System.out.println(emp);
10}
11
12query = session
13 .createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = :id");
14empData = query.setLong("id", 2L).list();
15for (Object[] row : empData) {
16 Employee emp = new Employee();
17 emp.setId(Long.parseLong(row[0].toString()));
18 emp.setName(row[1].toString());
19 emp.setSalary(Double.parseDouble(row[2].toString()));
20 System.out.println(emp);
21}
上述代码产生的输出为：
1Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
2Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
3Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
4Id= 2, Name= David, Salary= 200.0, {Address= null}
以上就是Hibernate SQL查询的简要介绍，除非您想执行任何特定于数据库的查询，否则应避免使用它。