扫码一下
查看教程更方便
教程 > Hibernate 教程 > Hibernate 基础
阅读：21
Hibernate 本机 SQL
如果想利用数据库特定的功能，例如查询提示或 Oracle 中的 CONNECT 关键字，我们可以使用本机 SQL 来表示数据库查询。 Hibernate 允许我们为所有创建、更新、删除和加载操作指定手写 SQL，包括存储过程。
我们的应用程序将使用会话接口上的 createSQLQuery() 方法从会话创建本机 SQL 查询
public SQLQuery createSQLQuery(String sqlString) throws HibernateException
将包含 SQL 查询的字符串传递给 createSQLQuery() 方法后，我们可以使用 addEntity()、addJoin() 和 addScalar() 方法将 SQL 结果与现有 Hibernate 实体、连接或标量结果相关联。
标量查询
最基本的 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();
实体查询
上面的查询都是关于返回标量值，基本上是从结果集中返回“原始”值。 以下是通过 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();
本机 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();
/* 在数据库中添加一些员工记录 */
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);
/* 使用标量查询列出员工及其工资 */
ME.listEmployeesScalar();
/* 使用实体查询列出完整的员工信息 */
ME.listEmployeesEntity();
}
/* 在数据库中创建员工的方法 */
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;
}
/* 使用标量查询读取所有员工的方法 */
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();
}
}
/* 使用实体查询读取所有员工的方法 */
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)
