HIBERNATE知识复习记录4-HQL和QBC

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; ???}}

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() { ???}}

<?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>

<?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(); ???}}