Hibernate 原生 SQL
原生 SQL
如果你想使用数据库特定的功能如查询提示或 Oracle 中的 CONNECT 关键字的话，你可以使用原生 SQL 数据库来表达查询。Hibernate 3.x 允许您为所有的创建，更新，删除，和加载操作指定手写 SQL ，包括存储过程。
您的应用程序会在会话界面用 createSQLQuery() 方法创建一个原生 SQL 查询：
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(
实体查询
以上的查询都是关于返回标量值的查询，只是基础性地返回结果集中的“原始”值。以下是从原生 SQL 查询中通过 addEntity() 方法获取实体对象整体的语法：
String sql = "SELECT * FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql
query.addEntity(Employee.class
List results = query.list(
指定 SQL 查询
以下是从原生 SQL 查询中通过 addEntity() 方法和使用指定 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(
原生 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 表创建。
$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>
