作者:whisper
链接:http://proprogrammar.com:443/article/295
声明:请尊重原作者的劳动,如需转载请注明出处
Hibernate中共提供了三种检索方式:HQL(Hibernate Query Language)、QBC、QBE(Query By Example)。
HQL 是Hibernate Query Language的简写,即hibernate查询语言:HQL采用面向对象的查询方式。
QBC(Query By Criteria) API提供了检索对象的另一种方式,它主要由Criteria接口、Criterion接口和Expresson类组成,它支持在运行时动态生成查询语句。
HQL 查询包括以下步骤:
QBC检索步骤:
1.调用Session的createCriteria()方法创建一个Criteria对象。
2.设定查询条件。Restrictions类提供了一系列用于设定查询条件的静态方法,
这些静态方法都返回Criterion实例,每个Criterion实例代表一个查询条件。
Criteria的add()方法用于加入查询条件。
3.调用Criteria的list()方法执行查询语句。该方法返回List类型的查询结果,在
List集合中存放了符合查询条件的持久化对象。
下面列出我学习的代码。
Department类
package oracle.hqlqbc;
import java.util.HashSet;
import java.util.Set;
public class Department {
private Integer id;
private String name;
private Set<Employee> emps = new HashSet<>();
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Department [id=");
builder.append(id);
builder.append(", name=");
builder.append(name);
builder.append(", empsSize=");
builder.append(emps.size());
builder.append("]");
return builder.toString();
}
public void setName(String name) {
this.name = name;
}
public Set<Employee> getEmps() {
return emps;
}
public void setEmps(Set<Employee> emps) {
this.emps = emps;
}
}
Employee类
package oracle.hqlqbc;
public class Employee {
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Employee [id=");
builder.append(id);
builder.append(", name=");
builder.append(name);
builder.append(", salary=");
builder.append(salary);
builder.append(", email=");
builder.append(email);
builder.append(", deptId=");
builder.append(dept.getId());
builder.append("]");
return builder.toString();
}
private Integer id;
private String name;
private float salary;
private String email;
private Department dept;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
public Employee(float salary, String email, Department dept) {
super();
this.salary = salary;
this.email = email;
this.dept = dept;
}
public Employee() {
}
}
Department.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2017-7-29 2:43:46 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping package="oracle.test">
<class name="Department" table="DEPARTMENT">
<id name="id" type="java.lang.Integer">
<column name="ID" />
<generator class="native" />
</id>
<property name="name" type="java.lang.String">
<column name="NAME" />
</property>
<set name="emps" table="EMPLOYEE" inverse="true" lazy="true">
<key><column name="DEPT_ID" /></key>
<one-to-many class="Employee" />
</set>
</class>
</hibernate-mapping>
Employee.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2017-7-29 2:43:46 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping package="oracle.test">
<class name="Employee" table="EMPLOYEE">
<id name="id" type="java.lang.Integer">
<column name="ID" />
<generator class="native" />
</id>
<property name="name" type="java.lang.String">
<column name="NAME" />
</property>
<property name="salary" type="float">
<column name="SALARY" />
</property>
<property name="email" type="java.lang.String">
<column name="EMAIL" />
</property>
<many-to-one name="dept" class="Department" fetch="join"><!-- join select subselect-->
<column name="DEPT_ID" />
</many-to-one>
</class>
<query name="salaryEmps"><![CDATA[FROM Employee e WHERE e.salary > :minSal AND e.salary < :maxSal]]></query>
</hibernate-mapping>
具体用法的例子:
package oracle.hqlqbc;
import java.util.Arrays;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import oracle.hqlqbc.Department;
import oracle.hqlqbc.Employee;
public class HibernateTest {
private SessionFactory sessionFactory;
private Session session;
private Transaction transaction;
@Before
public void init()
{
System.out.println("init");
// 1. 创建一个SessionFactory对象
sessionFactory = null;
Configuration configuration = new Configuration().configure();
// before 4.0
// sessionFactory = configuration.buildSessionFactory();
ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties())
.buildServiceRegistry();
sessionFactory = configuration.buildSessionFactory(serviceRegistry);
// 2. 创建一个Session 对象
session = sessionFactory.openSession();
// 3. 开启事务
transaction = session.beginTransaction();
}
@After
public void destory()
{
System.out.println("destory");
// 5. 提交事务
transaction.commit();
// 6. 关闭Session
session.close();
// 7. 关闭SesssionFactory
sessionFactory.close();
}
@Test
public void testNamedParameter()
{
String hql = "FROM Employee e WHERE e.salary > :sal AND e.email LIKE :email";
Query query = session.createQuery(hql);
// 可以用命名参数
query.setFloat("sal", 7000)
.setString("email", "%A%");
List<Employee> emps = query.list();
System.out.println(emps.size());
}
@Test
public void createTable()
{
}
@Test
public void testHQL()
{
// 1. 创建Query对象: 可以加 ORDER BY
String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ? AND dept = ? ORDER BY e.salary";
Query query = session.createQuery(hql);
// 2. 绑定参数
// Query对象调用setXxx方法支持方法链的编程风格
Department dept = new Department();
dept.setId(80);
// 可以用方法链
query.setFloat(0, 6000).setString(1, "%A%");
// 可用用实体
query.setEntity(2, dept);
// 3. 执行查询
List<Employee> employees = query.list();
System.out.println(employees.size());
}
/*
* 分页查询
*/
@Test
public void testPageQuery()
{
String hql = "FROM Employee";
Query query = session.createQuery(hql);
int pageNo = 3;
int pageSize = 5;
List<Employee> emps = query.setFirstResult((pageNo - 1)* pageSize)
.setMaxResults(pageSize).list();
System.out.println(emps);
}
/*
* 命名查询
*/
@Test
public void namedQuery()
{
Query query = session.getNamedQuery("salaryEmps");
List<Employee> emps = query.setFloat("minSal", 5000)
.setFloat("maxSal", 10000)
.list();
System.out.println(emps.size());
}
/*
* 投影查询1: 操作数组
*/
@Test
public void testPropertyQuery()
{
String hql = "SELECT e.email, e.salary FROM Employee e where e.dept = :dept";
Query query = session.createQuery(hql);
Department dept = new Department();
dept.setId(80);
List<Object[]> result = query.setEntity("dept", dept)
.list();
for(Object[] objs: result)
{
System.out.println(Arrays.asList(objs));
}
}
/*
* 投影查询2: 操作entity:使用构造器
*/
@Test
public void testPropertyQuery2()
{
String hql = "SELECT new Employee(e.salary, e.email, e.dept) FROM Employee e where e.dept = :dept";
Query query = session.createQuery(hql);
Department dept = new Department();
dept.setId(80);
List<Employee> result = query.setEntity("dept", dept).list();
for(Employee emp: result)
{
System.out.println(emp.getId() + ", " + emp.getEmail() + ", " + emp.getSalary() + ", " + emp.getDept());
}
}
/*
* 报表查询: 可以使用聚合函数
*/
@Test
public void testGroupBy()
{
String hql = "SELECT min(e.salary), max(e.salary) FROM Employee e "
+ "group by e.dept having min(salary) > :minSal";
Query query = session.createQuery(hql).setFloat("minSal", 5000);
List<Object[]> result = query.list();
for(Object[] objs: result)
{
System.out.println(Arrays.asList(objs));
}
}
/*
* HQL 会忽略配置文件中的fetch=join的设置,如果想用,就在代码中直接写
* 迫切左外连接:返回department,注意去重
*/
@Test
public void testLeftJoinFetch()
{
String hql = "SELECT DISTINCT d FROM Department d INNER JOIN FETCH d.emps";
Query query = session.createQuery(hql);
// 集合去重
// new ArrayList<>(new HashSet<>(Collection c));
List<Department> depts = query.list();
System.out.println(depts.size());
for(Department dept: depts)
{
System.out.println(dept.getName() + ", " + dept.getEmps().size());
}
}
/*
* 默认返回对象数组,注意去重,
*/
@Test
public void testLeftJoin()
{
// 这样返回的Employee未被初始化
String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.emps";
Query query = session.createQuery(hql);
/*List<Object[]> result = query.list();
System.out.println(result);
for(Object[] objs: result)
{
System.out.println(Arrays.asList(objs));
}*/
List<Department> depts = query.list();
System.out.println(depts.size());
for(Department dept: depts)
{
System.out.println(dept.getName());
}
}
@Test
public void testLeftJoinFetch2()
{
String hql = "SELECT e FROM Employee e INNER JOIN FETCH e.dept";
Query query = session.createQuery(hql);
List<Employee> emps = query.list();
System.out.println(emps.size());
for(Employee emp: emps)
{
System.out.println(emp.getName() + ", " + emp.getDept().getName());
}
}
@Test
public void testQBCQuery()
{
Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.eq("email", "SKUMAR"));
criteria.add(Restrictions.gt("salary", 5000f));
Employee employee = (Employee) criteria.uniqueResult();
System.out.println(employee);
}
@Test
public void testQBCQuery2()
{
Criteria criteria = session.createCriteria(Employee.class);
// 1. AND: 使用Conjunction表示,本身就是一个Criterion对象
// 且其中还可以添加Criterion对象
Conjunction conjunction = Restrictions.conjunction();
conjunction.add(Restrictions.like("name", "a", MatchMode.ANYWHERE));
Department dept = new Department();
dept.setId(80);
conjunction.add(Restrictions.eq("dept", dept));
System.out.println(conjunction);
// or
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.ge("salary", 6000));
disjunction.add(Restrictions.isNotNull("email"));
criteria.add(conjunction);
criteria.add(disjunction);
criteria.list();
}
@Test
public void testQBCQuery3()
{
Criteria criteria = session.createCriteria(Employee.class);
// 统计查询
criteria.setProjection(Projections.max("salary"));
System.out.println(criteria.uniqueResult());
}
@Test
public void testQBCQuery4()
{
Criteria criteria = session.createCriteria(Employee.class);
criteria.addOrder(Order.asc("salary"));
criteria.addOrder(Order.desc("email"));
int pageSize = 5;
int pageNo = 3;
criteria.setFirstResult((pageNo - 1) * pageSize)
.setMaxResults(pageSize).list();
}
@Test
public void testNativeSQL()
{
String sql = "INSERT INTO department VALUES(?, ?)";
Query query = session.createSQLQuery(sql);
query.setInteger(0, 280)
.setString(1, "ATGUIGU")
.executeUpdate();
}
@Test
public void testHQLUpdate()
{
String hql = "DELETE FROM Department d where d.id = :id";
session.createQuery(hql).setInteger("id", 280).executeUpdate();
}
}
好了,例子都在上面,自己也复习了一遍,有了一个初步的印象,本文仅供入门了解,更深入的内容查看相关文章学习。
亲爱的读者:有时间可以点赞评论一下
全部评论