Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL is suitable for persistent objects and their properties. HQL queries are converted by Hibernate into traditional SQL queries, which perform operations on the database.
Although it is possible to use SQL statements directly and use native SQL with Hibernate, it is recommended to use HQL to avoid database portability troubles as much as possible and take advantage of Hibernate's SQL generation and caching strategies.
Keywords like SELECT, FROM and WHERE are not case sensitive, but attributes like table names and column names are case sensitive in HQL.
FROM statement
Use the FROM clause if you want to load a complete persistent object into memory. Here is a simple syntax using the FROM clause:
String hql = "FROM Employee"; Query query = session.createQuery(hql); List results = query.list();
If you need to fully qualify a class name in HQL, just specify the package and class name as follows:
String hql = "FROM com.hibernatebook.criteria.Employee"; Query query = session.createQuery(hql); List results = query.list();
AS Statement
The AS clause can be used to alias HQL queries assigned to classes, especially when there are very long queries. For example, our previous simple example is the following:
String hql = "FROM Employee AS E"; Query query = session.createQuery(hql); List results = query.list();
The AS keyword is optional, and you can also specify an alias directly in the subsequent class name, as shown below:
String hql = "FROM Employee E"; Query query = session.createQuery(hql); List results = query.list();
SELECT clause
The SELECT clause provides more control over the result set than the FROM clause. If you want to get several properties of an object rather than the entire object, use the SELECT clause. Here is a simple syntax using a SELECT statement to get just the FIRST_NAME field of an Employee object:
String hql = "SELECT E.firstName FROM Employee E"; Query query = session.createQuery(hql); List results = query.list();
It is worth noting that here, Employee.firstName is a property of the Employee object, not A field of the EMPLOYEE table.
WHERE clause
If you want to narrow down the specific objects returned from storage, you can use the WHERE clause. Here is a simple syntax using the WHERE clause:
String hql = "FROM Employee E WHERE E.id = 10"; Query query = session.createQuery(hql); List results = query.list();
ORDER BY clause
To sort the results of a HQL query, you will need to use the ORDER BY clause. You can sort the results in the result set in ascending (ASC) or descending (DESC) order by any property in the object. Here is a simple syntax using the ORDER BY clause:
String hql = "FROM Employee E WHERE E.id > 10 ORDER BY E.salary DESC"; Query query = session.createQuery(hql); List results = query.list();
If you want to sort by more than one attribute, you would just add the additional attributes to the BY clause separated by commas Open the end of the command as shown below:
String hql = "FROM Employee E WHERE E.id > 10 " + "ORDER BY E.firstName DESC, E.salary DESC "; Query query = session.createQuery(hql); List results = query.list();
GROUP BY CLAUSE
This clause allows fetching information from Hibernate's database and groups based on the value of the property, and Commonly used results include total values. Here is a very simple syntax using a GROUP BY clause:
String hql = "SELECT SUM(E.salary), E.firtName FROM Employee E " + "GROUP BY E.firstName"; Query query = session.createQuery(hql); List results = query.list();
Using named parameters
Hibernate supports named parameters in its HQL query. This makes it easy to write HQL queries that accept input from the user without having to defend against SQL injection attacks. Here is a simple syntax using named parameters:
String hql = "FROM Employee E WHERE E.id = :employee_id"; Query query = session.createQuery(hql); query.setParameter("employee_id",10); List results = query.list();
UPDATE clause
Batch updates are new in HQL with Hibernate3, and deletions work differently in Hibernate 3 and Hibernate2 Same. The Query interface now contains a method named executeUpdate() for executing HQL UPDATE or DELETE statements.
The UPDATE clause can be used to update one or more properties in one or more objects. The following is a simple syntax using the UPDATE clause:
String hql = "UPDATE Employee set salary = :salary " + "WHERE id = :employee_id"; Query query = session.createQuery(hql); query.setParameter("salary", 1000); query.setParameter("employee_id", 10); int result = query.executeUpdate(); System.out.println("Rows affected: " + result);
DELETE Clause
The DELETE clause can be used to delete one or more objects. Here is a simple syntax using DELETE clause:
String hql = "DELETE FROM Employee " + "WHERE id = :employee_id"; Query query = session.createQuery(hql); query.setParameter("employee_id", 10); int result = query.executeUpdate(); System.out.println("Rows affected: " + result);
INSERT clause
HQL supports INSERT INTO clause only records where you can insert from one object to another object. The following is a simple syntax for using the INSERT INTO clause:
String hql = "INSERT INTO Employee(firstName, lastName, salary)" + "SELECT firstName, lastName, salary FROM old_employee"; Query query = session.createQuery(hql); int result = query.executeUpdate(); System.out.println("Rows affected: " + result);
Aggregation methods
HQL supports multiple aggregation methods, similar to SQL. They work in HQL the same way in SQL and the following is the list of available functions:
The DISTINCT keyword only counts the unique values set in that row. The following query will only return unique counts:
String hql = "SELECT count(distinct E.firstName) FROM Employee E"; Query query = session.createQuery(hql); List results = query.list();
Pagination using queries
There are two methods for the paginated query interface.
Query setFirstResult(int startPosition)
Query setMaxResults(int maxResult)
Using the above two methods together, you can build a paging component on a website or Swing application. Here is an example that can be extended to fetch 10 rows:
String hql = "FROM Employee"; Query query = session.createQuery(hql); query.setFirstResult(1); query.setMaxResults(10); List results = query.list();
Query Conditions
Hibernate provides an alternative way of manipulating objects and sequentially data available in RDBMS tables. One method is the standard API, which allows you to build a standard query object programmatically and apply filtering rules and logical conditions.
Hibernate's Session interface provides a Criteria object that can be used to create an instance of a persistent object that is returned when the application performs a conditional query on a Criteria object createCriteria() method.
The following is the simplest example of a conditional query that will simply return each object corresponding to the Employee class.
Criteria cr = session.createCriteria(Employee.class); List results = cr.list();
Restrictions and standards:
You can use the add() method to add restriction queries to the Criteria object. Below is an example to add a limit with the salary returned records being equal to 2000:
Criteria cr = session.createCriteria(Employee.class); cr.add(Restrictions.eq("salary", 2000)); List results = cr.list();
Following are a few examples covering different scenarios and can be used as per the requirement:
Criteria cr = session.createCriteria(Employee.class); // To get records having salary more than 2000 cr.add(Restrictions.gt("salary", 2000)); // To get records having salary less than 2000 cr.add(Restrictions.lt("salary", 2000)); // To get records having fistName starting with zara cr.add(Restrictions.like("firstName", "zara%")); // Case sensitive form of the above restriction. cr.add(Restrictions.ilike("firstName", "zara%")); // To get records having salary in between 1000 and 2000 cr.add(Restrictions.between("salary", 1000, 2000)); // To check if the given property is null cr.add(Restrictions.isNull("salary")); // To check if the given property is not null cr.add(Restrictions.isNotNull("salary")); // To check if the given property is empty cr.add(Restrictions.isEmpty("salary")); // To check if the given property is not empty cr.add(Restrictions.isNotEmpty("salary")); 可以创建AND或OR使用LogicalExpression限制如下条件： Criteria cr = session.createCriteria(Employee.class); Criterion salary = Restrictions.gt("salary", 2000); Criterion name = Restrictions.ilike("firstNname","zara%"); // To get records matching with OR condistions LogicalExpression orExp = Restrictions.or(salary, name); cr.add( orExp ); // To get records matching with AND condistions LogicalExpression andExp = Restrictions.and(salary, name); cr.add( andExp ); List results = cr.list();
While all the above conditions apply, HQL can be used directly as introduced in the previous tutorial.
Paging usage standards:
There are also standard interfaces and two methods for paging.
public Criteria setFirstResult(int firstResult)
public Criteria setMaxResults(int maxResults)
采用上述两种方法一起，我们可以在我们的网站或Swing应用程序构建一个分页组件。下面是例子，可以扩展来每次获取10行：
Criteria cr = session.createCriteria(Employee.class); cr.setFirstResult(1); cr.setMaxResults(10); List results = cr.list();
排序的结果：
标准的API提供了org.hibernate.criterion.Order类排序按升序或降序排列你的结果集，根据对象的属性。这个例子演示了如何使用Order类的结果集进行排序：
Criteria cr = session.createCriteria(Employee.class); // To get records having salary more than 2000 cr.add(Restrictions.gt("salary", 2000)); // To sort records in descening order crit.addOrder(Order.desc("salary")); // To sort records in ascending order crit.addOrder(Order.asc("salary")); List results = cr.list();
预测与聚合：
该Criteria API提供了一个org.hibernate.criterion.Projections类可用于获取平均值，最大值或最小值的属性值。Projections类是类似于类限制，因为它提供了几个静态工厂方法用于获得Projection 实例。 provides the
以下是涉及不同的方案的一些例子，可按规定使用：
Criteria cr = session.createCriteria(Employee.class); // To get total row count. cr.setProjection(Projections.rowCount()); // To get average of a property. cr.setProjection(Projections.avg("salary")); // To get distinct count of a property. cr.setProjection(Projections.countDistinct("firstName")); // To get maximum of a property. cr.setProjection(Projections.max("salary")); // To get minimum of a property. cr.setProjection(Projections.min("salary")); // To get sum of a property. cr.setProjection(Projections.sum("salary"));
Criteria Queries 例子：
考虑下面的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) );
以下将被映射文件。
This class contains the employee detail.
最后，我们将创建应用程序类的main（）方法来运行，我们将使用Criteria查询的应用程序：
import java.util.List; import java.util.Date; import java.util.Iterator; import org.hibernate.HibernateException; import org.hibernate.Session; import org.hibernate.Transaction; import org.hibernate.SessionFactory; import org.hibernate.Criteria; import org.hibernate.criterion.Restrictions; import org.hibernate.criterion.Projections; 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 all the employees */ ME.listEmployees(); /* Print Total employee's count */ ME.countEmployee(); /* Print Toatl salary */ ME.totalSalary(); } /* 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 having salary more than 2000 */ public void listEmployees( ){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); Criteria cr = session.createCriteria(Employee.class); // Add restriction. cr.add(Restrictions.gt("salary", 2000)); List employees = cr.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(); } } /* Method to print total number of records */ public void countEmployee(){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); Criteria cr = session.createCriteria(Employee.class); // To get total row count. cr.setProjection(Projections.rowCount()); List rowCount = cr.list(); System.out.println("Total Coint: " + rowCount.get(0) ); tx.commit(); }catch (HibernateException e) { if (tx!=null) tx.rollback(); e.printStackTrace(); }finally { session.close(); } } /* Method to print sum of salaries */ public void totalSalary(){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); Criteria cr = session.createCriteria(Employee.class); // To get total salary. cr.setProjection(Projections.sum("salary")); List totalSalary = cr.list(); System.out.println("Total Salary: " + totalSalary.get(0) ); 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: Daisy Last Name: Das Salary: 5000 First Name: John Last Name: Paul Salary: 5000 First Name: Mohd Last Name: Yasee Salary: 3000 Total Coint: 4 Total Salary: 15000
如果检查EMPLOYEE表，它应该记录如下：
mysql> select * from EMPLOYEE;
+----+------------+-----------+--------+ | id | first_name | last_name | salary | +----+------------+-----------+--------+ | 14 | Zara | Ali | 2000 | | 15 | Daisy | Das | 5000 | | 16 | John | Paul | 5000 | | 17 | Mohd | Yasee | 3000 | +----+------------+-----------+--------+ 4 rows in set (0.00 sec)
更多在Java的Hibernate框架中对数据库数据进行查询操作相关文章请关注PHP中文网！
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
