hibernate4笔记

HQL本身支持修改和删除，不支持插入，用本地sql来解决

@Test
public void testNativeSQL(){
    String sql = "INSERT INTO gg_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();
}

------------------------------------------------------------------------------------

HQL查询的helloworld

private SessionFactory sessionFactory;
private Session session;
private Transaction transaction;

@Before
public void init(){
    Configuration configuration = new Configuration().configure();
    ServiceRegistry serviceRegistry = 
            new ServiceRegistryBuilder().applySettings(configuration.getProperties())
                                        .buildServiceRegistry();
    sessionFactory = configuration.buildSessionFactory(serviceRegistry);
    
    session = sessionFactory.openSession();
    transaction = session.beginTransaction();
}

@After
public void destroy(){
    transaction.commit();
    session.close();
    sessionFactory.close();
}

@Test
public void testHQL(){
    
    //1. 创建 Query 对象
    //基于位置的参数. 
    String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ? AND e.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%")
         .setEntity(2, dept);
    
    //3. 执行查询
    List<Employee> emps = query.list();
    System.out.println(emps.size());  
}

@Test
public void testHQLNamedParameter(){
    
    //1. 创建 Query 对象
    //基于命名参数. 
    String hql = "FROM Employee e WHERE e.salary > :sal AND e.email LIKE :email";
    Query query = session.createQuery(hql);
    
    //2. 绑定参数
    query.setFloat("sal", 7000)
         .setString("email", "%A%");
    
    //3. 执行查询
    List<Employee> emps = query.list();
    System.out.println(emps.size());  
}

@Test
public void testPageQuery(){
    String hql = "FROM Employee";
    Query query = session.createQuery(hql);
    
    int pageNo = 22;
    int pageSize = 5;
    
    List<Employee> emps = 
                            query.setFirstResult((pageNo - 1) * pageSize)
                                 .setMaxResults(pageSize)
                                 .list();
    System.out.println(emps);
}

@Test
public void testFieldQuery(){
    String hql = "SELECT e.email, e.salary, e.dept 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));
    }
}

@Test
public void testFieldQuery2(){
    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(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", 8000);
    
    List<Object []> result = query.list();
    for(Object [] objs: result){
        System.out.println(Arrays.asList(objs));
    }
}

@Test
public void testLeftJoinFetch(){
//        String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN FETCH d.emps";
    String hql = "FROM Department d INNER JOIN FETCH d.emps";
    Query query = session.createQuery(hql);
    
    List<Department> depts = query.list();
    depts = new ArrayList<>(new LinkedHashSet(depts));//用来去重
    System.out.println(depts.size()); 
    
    for(Department dept: depts){
        System.out.println(dept.getName() + "-" + dept.getEmps().size());
    }
}

查询结果会有重复的，用distinct或者linkedHashSet来去重

@Test
public void testLeftJoin(){
    String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.emps";
    Query query = session.createQuery(hql);
    
    List<Department> depts = query.list();
    System.out.println(depts.size());
    
    for(Department dept: depts){
        System.out.println(dept.getName() + ", " + dept.getEmps().size()); 
    }
}

去重只能用distinct来去重

两种左外连接，要用就用迫切左外连接

-------------------------------------------------------------------

QBC的helloworld，完全是基于对象的，支持动态传入参数

@Test
public void testQBC(){
    //1. 创建一个 Criteria 对象
    Criteria criteria = session.createCriteria(Employee.class);
    
    //2. 添加查询条件: 在 QBC 中查询条件使用 Criterion 来表示
    //Criterion 可以通过 Restrictions 的静态方法得到
    criteria.add(Restrictions.eq("email", "SKUMAR"));
    criteria.add(Restrictions.gt("salary", 5000F));
    
    //3. 执行查询
    Employee employee = (Employee) criteria.uniqueResult();//也可以list
    System.out.println(employee); 
}

@Test
public void testQBC2(){
    Criteria criteria = session.createCriteria(Employee.class);
    
    //1. AND: 使用 Conjunction 表示
    //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); 
    
    //2. OR
    Disjunction disjunction = Restrictions.disjunction();
    disjunction.add(Restrictions.ge("salary", 6000F));
    disjunction.add(Restrictions.isNull("email"));
    
    criteria.add(disjunction);
    criteria.add(conjunction);
    
    criteria.list();
}

@Test
public void testQBC3(){
    Criteria criteria = session.createCriteria(Employee.class);
    
    //统计查询: 使用 Projection 来表示: 可以由 Projections 的静态方法得到
    criteria.setProjection(Projections.max("salary"));
    
    System.out.println(criteria.uniqueResult()); 
}

@Test
public void testQBC4(){
    Criteria criteria = session.createCriteria(Employee.class);
    
    //1. 添加排序
    criteria.addOrder(Order.asc("salary"));
    criteria.addOrder(Order.desc("email"));
    
    //2. 添加翻页方法
    int pageSize = 5;
    int pageNo = 3;
    criteria.setFirstResult((pageNo - 1) * pageSize)
            .setMaxResults(pageSize)
            .list();
}

-----------------------------------------------------------

对于批量操作，使用原生 JDBC API是最快的

@Test
public void testBatch(){
    session.doWork(new Work() {            
        @Override
        public void execute(Connection connection) throws SQLException {
            //通过 JDBC 原生的 API 进行操作, 效率最高, 速度最快!
        }
    });
}