Hibernate原生SQL

可以使用原生SQL来表达数据库查询，如果想利用数据库特有的功能，如查询提示或者Oracle中的CONNECT关键字。 Hibernate3.x允许使用手写SQL语句，包括存储过程，所有的创建，更新，删除和load操作。

应用程序将从会话创建一个原生SQL查询（Session接口上）createSQLQuery()方法：

public SQLQuery createSQLQuery(String sqlString) throws HibernateException

当传递一个包含SQL查询到createSQLQuery()方法，可以将SQL结果与任何现有的Hibernate实体，联接，或者一个标量结果使用addEntity()方法，addJoin()，和addScalar()方法关联的字符串。

标量查询：

最基本的SQL查询是从一个或多个表中得到标量（数值）的列表。以下是语法使用原生SQL标量的值：

String sql = "SELECT first_name, salary FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List results = query.list();

实体的查询：

上面的查询都是返回标量值，也就是从resultset中返回的“裸”数据。以下是语法通过addEntity（）方法来从原生SQL查询获得实体对象作为一个整体。

String sql = "SELECT * FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
List results = query.list();

命名SQL查询：

以下是语法通过addEntity（）方法来从原生SQL查询获得实体对象和使用命名SQL查询。

String sql = "SELECT * FROM EMPLOYEE WHERE id = :employee_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
query.setParameter("employee_id", 10);
List results = query.list();

Native SQL 例子：

考虑下面的POJO类：

public class Employee { private int id; private String firstName; private String lastName; private int salary; public Employee() {} public Employee(String fname, String lname, int salary) { this.firstName = fname; this.lastName = lname; this.salary = salary; } public int getId() { return id; } public void setId( int id ) { this.id = id; } public String getFirstName() { return firstName; } public void setFirstName( String first_name ) { this.firstName = first_name; } public String getLastName() { return lastName; } public void setLastName( String last_name ) { this.lastName = last_name; } public int getSalary() { return salary; } public void setSalary( int salary ) { this.salary = salary; } }

让我们创建下面的EMPLOYEE表来存储Employee对象：

create table EMPLOYEE ( id INT NOT NULL auto_increment, first_name VARCHAR(20) default NULL, last_name  VARCHAR(20) default NULL, salary     INT default NULL, PRIMARY KEY (id) );

以下将被映射文件。

<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-mapping PUBLIC 
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="Employee" table="EMPLOYEE"> <meta attribute="class-description"> This class contains the employee detail. </meta> <id name="id" type="int" column="id"> <generator class="native"/> </id> <property name="firstName" column="first_name" type="string"/> <property name="lastName" column="last_name" type="string"/> <property name="salary" column="salary" type="int"/> </class> </hibernate-mapping>

最后，我们将创建应用程序类的main（）方法来运行，我们将使用原生SQL查询的应用程序：

import java.util.*; import org.hibernate.HibernateException; import org.hibernate.Session; import org.hibernate.Transaction; import org.hibernate.SessionFactory; import org.hibernate.SQLQuery; import org.hibernate.Criteria; import org.hibernate.Hibernate; import org.hibernate.cfg.Configuration; public class ManageEmployee { private static SessionFactory factory; public static void main(String[] args) { try{ factory = new Configuration().configure().buildSessionFactory(); }catch (Throwable ex) { System.err.println("Failed to create sessionFactory object." + ex); throw new ExceptionInInitializerError(ex); } ManageEmployee ME = new ManageEmployee(); /* Add few employee records in database */ Integer empID1 = ME.addEmployee("Zara", "Ali", 2000); Integer empID2 = ME.addEmployee("Daisy", "Das", 5000); Integer empID3 = ME.addEmployee("John", "Paul", 5000); Integer empID4 = ME.addEmployee("Mohd", "Yasee", 3000); /* List down employees and their salary using Scalar Query */ ME.listEmployeesScalar(); /* List down complete employees information using Entity Query */ ME.listEmployeesEntity(); } /* Method to CREATE an employee in the database */ public Integer addEmployee(String fname, String lname, int salary){ Session session = factory.openSession(); Transaction tx = null; Integer employeeID = null; try{ tx = session.beginTransaction(); Employee employee = new Employee(fname, lname, salary); employeeID = (Integer) session.save(employee); tx.commit(); }catch (HibernateException e) { if (tx!=null) tx.rollback(); e.printStackTrace(); }finally { session.close(); } return employeeID; } /* Method to  READ all the employees using Scalar Query */ public void listEmployeesScalar( ){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); String sql = "SELECT first_name, salary FROM EMPLOYEE"; SQLQuery query = session.createSQLQuery(sql); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); List data = query.list(); for(Object object : data) { Map row = (Map)object; System.out.print("First Name: " + row.get("first_name")); System.out.println(", Salary: " + row.get("salary")); } tx.commit(); }catch (HibernateException e) { if (tx!=null) tx.rollback(); e.printStackTrace(); }finally { session.close(); } } /* Method to  READ all the employees using Entity Query */ public void listEmployeesEntity( ){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); String sql = "SELECT * FROM EMPLOYEE"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(Employee.class); List employees = query.list(); for (Iterator iterator = employees.iterator(); iterator.hasNext();){ Employee employee = (Employee) iterator.next(); System.out.print("First Name: " + employee.getFirstName()); System.out.print("  Last Name: " + employee.getLastName()); System.out.println("  Salary: " + employee.getSalary()); } tx.commit(); }catch (HibernateException e) { if (tx!=null) tx.rollback(); e.printStackTrace(); }finally { session.close(); } } }

编译和执行：

下面是步骤来编译并运行上述应用程序。请确保在进行的编译和执行之前，适当地设置PATH和CLASSPATH。

• 创建hibernate.cfg.xml配置文件中配置章节解释。

• 创建Employee.hbm.xml映射文件，如上图所示。

• 创建Employee.java源文件，如上图所示，并编译它。

• 创建ManageEmployee.java源文件，如上图所示，并编译它。

• 执行ManageEmployee二进制文件来运行程序。

会得到以下结果，并记录将在EMPLOYEE表中创建。

$java ManageEmployee .......VARIOUS LOG MESSAGES WILL DISPLAY HERE........ First Name: Zara, Salary: 2000 First Name: Daisy, Salary: 5000 First Name: John, Salary: 5000 First Name: Mohd, Salary: 3000 First Name: Zara Last Name: Ali Salary: 2000 First Name: Daisy Last Name: Das Salary: 5000 First Name: John Last Name: Paul Salary: 5000 First Name: Mohd Last Name: Yasee Salary: 3000

如果检查EMPLOYEE表，它应该记录下已：

mysql> select * from EMPLOYEE; +----+------------+-----------+--------+ | id | first_name | last_name | salary | +----+------------+-----------+--------+ | 26 | Zara | Ali | 2000 | | 27 | Daisy | Das | 5000 | | 28 | John | Paul | 5000 | | 29 | Mohd | Yasee | 3000 | +----+------------+-----------+--------+ 4 rows in set (0.00 sec) mysql>