Hibernate 简明教程
Hibernate - Native SQL
如果你希望利用数据库的特定功能(例如 Oracle 中的查询提示或 CONNECT 关键字),你可以使用原生 SQL 来表达数据库查询。Hibernate 3.x 让你可以指定手工编写的 SQL(包括存储过程)用于所有创建、更新、删除和加载操作。
You can use native SQL to express database queries if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. Hibernate 3.x allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.
你的应用程序会利用 Session 接口的 createSQLQuery() 方法从会话中创建一个原生 SQL 查询——
Your application will create a native SQL query from the session with the createSQLQuery() method on the Session interface −
public SQLQuery createSQLQuery(String sqlString) throws HibernateException
在你向 createSQLQuery()
方法传递包含 SQL 查询的字符串之后,你可以使用 addEntity()
、addJoin()
和 addScalar()
方法分别将 SQL 结果与现有的 Hibernate 实体、联接或标量结果关联起来。
After you pass a string containing the SQL query to the createSQLQuery() method, you can associate the SQL result with either an existing Hibernate entity, a join, or a scalar result using addEntity(), addJoin(), and addScalar() methods respectively.
Scalar Queries
最基本的 SQL 查询是从一张或多张表中获取标量(值)的列表。以下是使用原生 SQL 查询标量值的语法——
The most basic SQL query is to get a list of scalars (values) from one or more tables. Following is the syntax for using native SQL for scalar values −
String sql = "SELECT first_name, salary FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List results = query.list();
Entity Queries
以上所有查询都涉及返回标量值,基本上从结果集中返回“原始”值。以下是使用 addEntity()
从原生 SQL 查询中整体获取实体对象的语法。
The above queries were all about returning scalar values, basically returning the "raw" values from the result set. Following is the syntax to get entity objects as a whole from a native sql query via addEntity().
String sql = "SELECT * FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
List results = query.list();
Named SQL Queries
以下是使用 addEntity()
和命名 SQL 查询从原生 SQL 查询中获取实体对象的语法。
Following is the syntax to get entity objects from a native sql query via addEntity() and using named SQL query.
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 Example
考虑以下 POJO 类——
Consider the following POJO class −
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 对象——
Let us create the following EMPLOYEE table to store Employee objects −
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)
);
以下是映射文件——
Following will be mapping file −
<?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()
方法创建我们的应用程序类以运行应用程序,在其中我们将使用 Native SQL 查询——
Finally, we will create our application class with the main() method to run the application where we will use Native SQL queries −
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();
}
}
}
Compilation and Execution
以下是编译和运行上述应用程序的步骤。请确保在继续编译和执行之前,已适当地设置 PATH 和 CLASSPATH。
Here are the steps to compile and run the above mentioned application. Make sure, you have set PATH and CLASSPATH appropriately before proceeding for the compilation and execution.
-
Create hibernate.cfg.xml configuration file as explained in configuration chapter.
-
Create Employee.hbm.xml mapping file as shown above.
-
Create Employee.java source file as shown above and compile it.
-
Create ManageEmployee.java source file as shown above and compile it.
-
Execute ManageEmployee binary to run the program.
您将获得以下结果,并且将在 EMPLOYEE 表中创建记录。
You would get the following result, and records would be created in the EMPLOYEE table.
$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 表,它应包含以下记录 −
If you check your EMPLOYEE table, it should have the following records −
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>