Hibernate查询方式：
- 1.导航对象图检索方式：根据已有对象导航到其他对象
- 2.OID检索：对象OID检索对象
- 3.HQL检索：使用面向对象HQL查询语言
- 4.QBC检索：使用QBC API来检索对象
- 5.本地SQL检索：使用SQL语言
HQL检索有如下功能：
- 1设定查询条件
- 2检索部分属性
- 3支持分页查询
- 5支持链接查询
- 6支持分组查询，可以使用HAVING和GROUP BY
- 7提供内置函数
- 8支持子查询
- 9支持动态绑定参数
- 10能够使用用户SQL函数
Hibernate基本逻辑代码
public static void test() {
StandardServiceRegistry registry = new StandardServiceRegistryBuilder().configure().build();// 配置文件configure()
SessionFactory sessionFactory = new MetadataSources(registry).buildMetadata().buildSessionFactory();
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();
//这里写查询方法
test(session);
transaction.commit();
session.close();
sessionFactory.close();
}
查询的持久化对象和表
//员工
public class Employee {
private Integer id;
private String name;
private float salary;
private String email;
private Department dept;
public Employee() {
super();
}
public Employee(String name, String email) {
super();
this.name = name;
this.email = email;
}
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;
}
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + name + ", salary=" + salary + ", email=" + email + ", dept=" + dept
+ "]";
}
}
//部门
public class Department {
private Integer id;
private String name;
private Set<Employee> employees=new HashSet<>();
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 Set<Employee> getEmployees() {
return employees;
}
public void setEmployees(Set<Employee> employees) {
this.employees = employees;
}
@Override
public String toString() {
return "Department [id=" + id + ", name=" + name + ", employees=" + employees + "]";
}
}
此处省略映射文件（一对多关系）
部门表
员表
HQL检索步骤
1.通过Session 的createQuery（）方法获取Query对象 它包涵一个HQL语句和参数
2.动态绑定参数
3.调用Query执行方法
/**
* 第一次查询
*/
public static void queryA(Session session) {
// 1.创建Query对象
String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ? ";
Query query = session.createQuery(hql);
// 2.绑定参数
query.setParameter(0, 5255.4f).setParameter(1, "%%");
// 3.执行查询
List<Employee> eList = query.list();
System.out.println(eList.size());
}
HQL可以直接使用持久化类的类名 和属性名
命名参数：取代"?"为查询条件命名
/**
* 命名参数
* */
public static void queryB(Session session) {
// 1.创建Query对象
String hql = "FROM Employee e WHERE e.salary > :salary AND e.email LIKE :email ";
Query query = session.createQuery(hql);
// 2.绑定参数
query.setParameter("salary", 5255.4f).setParameter("email", "%%");
// 3.执行查询
List<Employee> eList = query.list();
System.out.println(eList.size());
}
实体参数 :HQL是面向对象的
/**
* 实体参数
* */
public static void queryC(Session session) {
// 1.创建Query对象
String hql = "FROM Employee e WHERE e.salary > ? AND e.dept = ? ";
Query query = session.createQuery(hql);
// 2.绑定参数
Department e=new Department();
e.setId(1);
query.setParameter(0, 5255.4f).setParameter(1, e);
// 3.执行查询
List<Employee> eList = query.list();
System.out.println(eList.size());
}
ORDER BY 排序
/**
* ORDER BY 排序
* */
public static void queryD(Session session) {
// 1.创建Query对象
String hql = "FROM Employee e WHERE e.dept = ? ORDER BY e.salary DESC";
Query query = session.createQuery(hql);
// 2.绑定参数
Department e=new Department();
e.setId(1);
query.setParameter(0, e);
// 3.执行查询
List<Employee> eList = query.list();
for (Employee employee : eList) {
System.out.println(employee.getName());
}
}
分页查询
/**
* 分页查询
* */
public static void queryF(Session session) {
String hql = "FROM Employee e WHERE e.id > ?";
Query query = session.createQuery(hql);
query.setParameter(0, 3);
int pageNo=0;//分页起始位置包涵
int pageSize=2;//每页长度
List<Employee> eList = query.setFirstResult(pageNo).setMaxResults(pageSize).list();
for (Employee employee : eList) {
System.out.println(employee.getName());
}
}
命名查询 hql放在映射文件中
在映射文件中hibernate-mapping元素下
<query name="salaryEmps"><![CDATA[FROM Employee e WHERE e.salary > :minSal AND e.salary < :maxSal]]></query>
/**
* 命名查询 hql放在映射文件中
* */
public static void queryG(Session session) {
String hql = "FROM Employee e WHERE e.id > ?";
Query query = session.getNamedQuery("salaryEmps");
query.setParameter("minSal", 5000f);
query.setParameter("maxSal", 20000f);
List<Employee> eList = query.list();
for (Employee employee : eList) {
System.out.println(employee.getName());
}
}
投影查询 查询部分属性 默认返回数组
返回数组
/**
* 投影查询 查询部分属性 默认返回数组
* */
public static void queryH(Session session) {
String hql = "SELECT e.email, e.name FROM Employee e WHERE e.dept = :dept";
Query query = session.createQuery(hql);
Department e=new Department();
e.setId(1);
query.setParameter("dept", e);
List<Object[]> eList = query.list();
for (Object[] os : eList) {
System.out.println(Arrays.asList(os));
}
}
返回对象
/**
* 投影查询 查询部分属性 默认返回数组
* */
public static void queryH(Session session) {
String hql = "SELECT e.email, e.name FROM Employee e WHERE e.dept = :dept";
Query query = session.createQuery(hql);
Department e=new Department();
e.setId(1);
query.setParameter("dept", e);
List<Object[]> eList = query.list();
for (Object[] os : eList) {
System.out.println(Arrays.asList(os));
}
}
报表查询 使用函数
/**
* 报表查询
* */
public static void queryJ(Session session) {
//持久化类提供这样的构造器
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);
query.setParameter("minsal", 1000f);
List<Object[]> eList = query.list();
for (Object[] os : eList) {
System.out.println(Arrays.asList(os));
}
}
本地sql：可以执行sql所有操作
/**
* 本地sql
* 可以执行sql所有操作
* */
public static void queryL(Session session) {
String sql = "INSERT INTO department (name) VALUES(:name)";
Query query = session.createNativeQuery(sql);//使用createNativeQuery方法
query.setParameter("name", "本地sql").executeUpdate();
}
