❮ 上一节

下一节 ❯

Hibernate - 原生 SQL

更新于 2024/9/14 9:27:00

如果您想利用数据库特定的功能，例如查询提示或 Oracle 中的 CONNECT 关键字，您可以使用本机 SQL 来表达数据库查询。 Hibernate 3.x 允许您为所有创建、更新、删除和加载操作指定手写 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)

);

以下是映射文件 −

"-//Hibernate/Hibernate Mapping DTD//EN"

"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

This class contains the employee detail.

最后，我们将使用 main() 方法创建我们的应用程序类来运行我们将使用 Native 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>

❮ 上一节

下一节 ❯