一、Hibernate的HQL查询
hql是面向对象查询,格式:from + 类名 + 类对象 + where + 对象的属性,如果是对数据库进行查询操作先比sql比较优。
二、代码
import java.util.HashSet;
import java.util.Set;
public class Department {
private Integer id;
private String name;
private Set<Employee> employees = new HashSet<>();
//get/set...
}
public class Employee {
private Integer id;
private String name;
private float salary;
private String email;
private Department dept;
public Employee() {
}
public Employee( String email, float salary,Department dept) {
super();
this.salary = salary;
this.email = email;
this.dept = dept;
}
//get/set...
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + name + "]";
}
}
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-4-4 15:42:11 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
<class name="com.hibernate.entities.Department" table="DEPARTMENT">
<id name="id" type="java.lang.Integer">
<column name="ID" />
<generator class="native" />
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-4-4 15:42:11 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
<class name="com.hibernate.entities.Employee" table="EMPLOYEES">
<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="com.hibernate.entities.Department" fetch="join">
<column name="DEPT_ID" />
</many-to-one>
</class>
<query name="salaryEmps"><![CDATA[FROM Employee e WHERE e.salary > :minSalart AND e.salary < :maxSalary]]></query>
</hibernate-mapping>
hibernate.cfg.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- 配置链接数据库的基本信息 -->
<property name="connection.username">root</property>
<property name="connection.password">root</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernate01</property>
<!-- hibernate的基本信息 -->
<!-- hibernate使用的数据库方言 -->
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<!-- 执行操作时是否在控制台打印sql语句 -->
<property name="show_sql">true</property>
<!-- 是否对sql进行格式化 -->
<property name="format_sql">true</property>
<!-- 指定自动生成数据表的策略 -->
<property name="hbm2ddl.auto">update</property>
<!-- 设置hibernate的隔离级别 -->
<property name="connection.isolation">2</property>
<!-- 删除对象后,设置其OID为null -->
<property name="use_identifier_rollback">true</property>
<!-- 配置C3P0数据源 -->
<property name="c3p0.max_size">100</property>
<property name="c3p0.min_size">5</property>
<property name="c3p0.acquire_increment">2</property>
<property name="c3p0.idle_test_period">2000</property>
<property name="c3p0.timeout">2000</property>
<property name="c3p0.max_statements">10</property>
<!-- 设置JDBC的statement读取数据库的时候每次从数据库中读取记录条数,这个值100合适,太大会损耗内存,小了速度慢 -->
<property name="jdbc.fetch_size">100</property>
<!-- 设置数据库进行批量删除,更新,插入的时候批次的大小,30比较合适 -->
<property name="jdbc.batch_size">30</property>
<!-- 指定关联的hbm.xml文件 -->
<mapping resource="com/hibernate/entities/Department.hbm.xml"/>
<mapping resource="com/hibernate/entities/Employee.hbm.xml"/>
</session-factory>
</hibernate-configuration>
三、测试代码
import static org.junit.Assert.*;
import java.nio.channels.SeekableByteChannel;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Set;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.hibernate.entities.Department;
import com.hibernate.entities.Employee;
import javassist.expr.NewArray;
public class HibernateTest {
private SessionFactory sessionFactory;
private Session session;
private Transaction transaction;
@Test
public void testSave() {
List<Department> departments = new ArrayList<>();
for (int i = 0; i < 26; i++) {
Department department = new Department();
department.setName("DEPT_" + ("A" + i));
departments.add(department);
Employee employee1 = new Employee();
employee1.setName("EMP1_" + ("a" + i));
employee1.setEmail("EMAIL1_" + ("a" + i));
employee1.setSalary(1000 + i);
employee1.setDept(departments.get(i));
Employee employee2 = new Employee();
employee2.setName("EMP2_" + ("b" + i));
employee2.setEmail("EMAIL2_" + ("b" + i));
employee2.setSalary(2000 + i);
employee2.setDept(departments.get(i));
department.getEmployees().add(employee1);
department.getEmployees().add(employee2);
employee1.setDept(department);
employee2.setDept(department);
session.save(department);
session.save(employee1);
session.save(employee2);
}
}
/***
* 基于位置的参数
*/
@Test
public void testHQL() {
// 1、创建Query对象
// Employee-对象而不是表名
String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ? AND e.dept = ? ORDER BY e.salary DESC";
Query query = session.createQuery(hql);
// 2、绑定参数
Department dept = new Department();
dept.setId(27);
query.setFloat(0, 100f).setString(1, "%a0%").setEntity(2, dept);
// 3、查询
List<Employee> employees = query.list();
System.out.println(employees.size());
}
/***
* 基于命名参数
*/
@Test
public void testHQLNameParam() {
// 1、创建Query对象
// Employee-对象而不是表名
String hql = "FROM Employee e WHERE e.salary > :sal AND e.email LIKE :email";
Query query = session.createQuery(hql);
// 2、绑定参数
query.setFloat("sal", 1500f).setString("email", "%b5%");
// 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 numPage = 3;// 第3页
int pageSize = 5;// 每一页5条
List<Employee> employees = query.setFirstResult((numPage - 1) * pageSize).setMaxResults(pageSize).list();
System.out.println(employees);
}
/***
* 命名查询
*/
@Test
public void testNameQuery() {
//salaryEmps-<query name="salaryEmps">
Query query = session.getNamedQuery("salaryEmps");
//查询工资大于1000小于1020 的员工
List<Employee> employees = query.setFloat("minSalart", 1000f).setFloat("maxSalary", 1020f).list();
System.out.println(employees.size());
}
/***
* 投影查询
*/
@Test
public void testFieldQuery() {
//Employee类中要有对应的构造器
String hql = "SELECT new Employee(e.email, e.salary, e.dept) "
+ "FROM Employee e "
+ "WHERE e.dept =:dept";
Query query = session.createQuery(hql);
Department dept = new Department();
dept.setId(28);
// List<Object[]> employees = query.setEntity("dept", dept).list();
// for(Object[] objs : employees){
// System.out.println(Arrays.asList(objs));
// }
List<Employee> employees = query.setEntity("dept", dept).list();
for(Employee employee : employees){
System.out.println(employee.getSalary()+ " "+employee.getDept().getName());
}
}
@Test
public void testGroupBy(){
//查询每个部门 最低工资 > minsal,和最高工资
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);
List<Object[]> objects = query.setFloat("minsal", 1000).list();
for(Object[] objs : objects){
System.out.println(Arrays.asList(objs));
}
}
/***
* 迫切左外链接 推荐使用
* employees被初始化
*/
@Test
public void testLeftJoinFetch(){
//SELECT DISTINCT -去除重复的元素
String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN FETCH d.employees";
// String hql2 = "FROM Department d LEFT JOIN FETCH d.employees";
Query query = session.createQuery(hql);
List<Department> departments = query.list();
System.out.println(departments.size());
}
/***
* 左外链接
* employees没有被初始化,使用的时候会再去查
*/
@Test
public void testLeftJoin(){
//SELECT DISTINCT -去除重复的元素
// String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.employees";
// Query query = session.createQuery(hql);
String hql2 = "FROM Department d LEFT JOIN d.employees";
Query query = session.createQuery(hql2);
List<Object[]> departments = query.list();
for(Object[] objects : departments){
System.out.println(Arrays.asList(objects));
}
}
/***
* 内链接
* employees没有被初始化,使用的时候会再去查
*/
@Test
public void testInnerJoin(){
//SELECT DISTINCT -去除重复的元素
// String hql = "SELECT DISTINCT d FROM Department d INNER JOIN d.employees";
// Query query = session.createQuery(hql);
String hql2 = "FROM Department d INNER JOIN d.employees";
Query query = session.createQuery(hql2);
List<Object[]> departments = query.list();
for(Object[] objects : departments){
System.out.println(Arrays.asList(objects));
}
}
@Before
public void init() {
System.out.println("HibernateTest.init()");
// 1.创建一个sessionFactory对象
Configuration configuration = new Configuration().configure();
ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties())
.buildServiceRegistry();
sessionFactory = configuration.buildSessionFactory(serviceRegistry);
// 2.创建一个session对象
session = sessionFactory.openSession();
// 3.开启事务
transaction = session.beginTransaction();
}
@After
public void destroy() {
System.out.println("HibernateTest.destroy()");
// 5.提交事务
transaction.commit();
// 6.关闭session
session.close();
// 7.关闭SessionFactory
sessionFactory.close();
}
}