Hibernate 原生 SQL

原生 SQL

如果你想使用數(shù)據(jù)庫特定的功能如查詢提示或 Oracle 中的 CONNECT 關(guān)鍵字的話，你可以使用原生 SQL 數(shù)據(jù)庫來表達(dá)查詢。Hibernate 3.x 允許您為所有的創(chuàng)建，更新，刪除，和加載操作指定手寫 SQL ，包括存儲(chǔ)過程。
您的應(yīng)用程序會(huì)在會(huì)話界面用 createSQLQuery() 方法創(chuàng)建一個(gè)原生 SQL 查詢：

public SQLQuery createSQLQuery(String sqlString) throws HibernateException

當(dāng)你通過一個(gè)包含 SQL 查詢的 createsqlquery() 方法的字符串時(shí)，你可以將 SQL 的結(jié)果與現(xiàn)有的 Hibernate 實(shí)體，一個(gè)連接，或一個(gè)標(biāo)量結(jié)果分別使用 addEntity(), addJoin(), 和 addScalar() 方法進(jìn)行關(guān)聯(lián)。

標(biāo)量查詢

最基本的 SQL 查詢是從一個(gè)或多個(gè)列表中獲取一個(gè)標(biāo)量（值）列表。以下是使用原生 SQL 進(jìn)行獲取標(biāo)量的值的語法：

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

實(shí)體查詢

以上的查詢都是關(guān)于返回標(biāo)量值的查詢，只是基礎(chǔ)性地返回結(jié)果集中的“原始”值。以下是從原生 SQL 查詢中通過 addEntity() 方法獲取實(shí)體對(duì)象整體的語法：

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

指定 SQL 查詢

以下是從原生 SQL 查詢中通過 addEntity() 方法和使用指定 SQL 查詢來獲取實(shí)體對(duì)象整體的語法：

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();  

原生 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;
   }
}

讓我們創(chuàng)建以下 EMPLOYEE 表來存儲(chǔ) Employee 對(duì)象：

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() 方法創(chuàng)建應(yīng)用程序類來運(yùn)行應(yīng)用程序，我們將使用原生 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(); 
      }
   }
}

編譯和執(zhí)行

這是編譯并運(yùn)行上述應(yīng)用程序的步驟。確保你有適當(dāng)?shù)?PATH 和 CLASSPATH，然后執(zhí)行編譯程序。

• 按照在配置一章講述的方法創(chuàng)建 hibernate.cfg.xml 配置文件。
• 如上述所示創(chuàng)建 employee.hbm.xml 映射文件。
• 如上述所示創(chuàng)建 employee.java 源文件并編譯。
• 如上述所示創(chuàng)建 manageemployee.java 源文件并編譯。
• 執(zhí)行 manageemployee 二進(jìn)制代碼運(yùn)行程序。

你會(huì)得到下面的結(jié)果，并且記錄將會(huì)在 EMPLOYEE 表創(chuàng)建。

$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 表，它應(yīng)該有以下記錄：

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>