Hibernate之QBC检索跟本地SQL检索

  1 package com.yl.hibernate.test;  2   3   4 import java.util.ArrayList;  5 import java.util.Arrays;  6 import java.util.LinkedHashSet;  7 import java.util.List;  8 import java.util.Set;  9  10 import oracle.net.aso.e; 11  12 import org.hibernate.Criteria; 13 import org.hibernate.Query; 14 import org.hibernate.Session; 15 import org.hibernate.SessionFactory; 16 import org.hibernate.Transaction; 17 import org.hibernate.cfg.Configuration; 18 import org.hibernate.criterion.Conjunction; 19 import org.hibernate.criterion.Disjunction; 20 import org.hibernate.criterion.MatchMode; 21 import org.hibernate.criterion.Order; 22 import org.hibernate.criterion.Projection; 23 import org.hibernate.criterion.Projections; 24 import org.hibernate.criterion.Restrictions; 25 import org.hibernate.service.ServiceRegistry; 26 import org.hibernate.service.ServiceRegistryBuilder; 27 import org.junit.After; 28 import org.junit.Before; 29 import org.junit.Test; 30  31 import com.yl.hibernate.entities.Department; 32 import com.yl.hibernate.entities.Employee; 33  34 public class HibernateTest { 35  36     private SessionFactory sessionFactory; 37     private Session session; 38     private Transaction transaction; 39      40     @Before 41     public void init() { 42         Configuration configuration = new Configuration().configure(); 43         ServiceRegistry serviceRegistry =  44                 new ServiceRegistryBuilder().applySettings(configuration.getProperties()) 45                                             .buildServiceRegistry(); 46  47         sessionFactory = configuration.buildSessionFactory(serviceRegistry); 48          49         session = sessionFactory.openSession(); 50  51         transaction = session.beginTransaction(); 52     } 53     @After 54     public void destory() { 55         transaction.commit(); 56          57         session.close(); 58          59         sessionFactory.close(); 60     } 61      62      63     @Test 64     public void testQBC() { 65         //1.创建一个Ctiteria对象 66         Criteria criteria = session.createCriteria(Employee.class); 67         //2.添加查询条件:在QBC中查询条件使用Criterion来表示 68         //Criterion可以通过Restrictions 的静态方法得到 69         criteria.add(Restrictions.eq("email", "PRESIDENT")); 70         criteria.add(Restrictions.gt("salary", 500f)); 71         //3.执行查询 72         Employee employee = (Employee) criteria.uniqueResult(); 73         System.out.println(employee); 74     } 75      76     @Test 77     public void testQBC2() { 78          79         Criteria criteria = session.createCriteria(Employee.class); 80         //1.AND: 使用Conjunction表示 81         //Conjunction 本身就是一个Criterion对象 82         //且其中还可以添加Criterion对象 83         Conjunction conjunction = Restrictions.conjunction(); 84         conjunction.add(Restrictions.like("name", "A", MatchMode.ANYWHERE)); 85         Department dept = new Department(); 86         dept.setId(20); 87         conjunction.add(Restrictions.eq("dept", dept)); 88         System.out.println(conjunction); 89          90         //2. OR 91         Disjunction disjunction = Restrictions.disjunction(); 92         disjunction.add(Restrictions.ge("salary", 1000F)); 93         disjunction.add(Restrictions.isNotNull("email")); 94          95         criteria.add(disjunction); 96         criteria.add(conjunction); 97          98         criteria.list(); 99         /**100          *  where101         (102             this_.SALARY>=? 103             or this_.EMAIL is not null104         ) 105         and (106             this_.NAME like ? 107             and this_.DEPT_ID=?108         )109          */110     }111     112     @Test113     public void testQBC3() {114         Criteria criteria = session.createCriteria(Employee.class);115         116         //统计查询： 使用Projection表示: 可以由Projections的静态方法得到117         criteria.setProjection(Projections.max("salary"));118         119         System.out.println(criteria.uniqueResult());120     }121     122     @Test123     public void testQBC4() {124         Criteria criteria = session.createCriteria(Employee.class);125         126         //1.添加排序127         criteria.addOrder(Order.asc("salary"));128         criteria.addOrder(Order.desc("email"));129         130         //2.添加分页131         int pageSize = 3;132         int pageNo = 2;133         criteria.setFirstResult((pageNo - 1) * pageSize)134                 .setMaxResults(pageSize)135                 .list();136         137     }138     139     @Test140     public void testNative() {141         String sql = "INSERT INTO YL_department VALUES(?, ?)";142         Query query = session.createSQLQuery(sql);143         144         query.setInteger(0, 50)145              .setString(1, "DEV")146              .executeUpdate();147     }148     149     @Test150     public void testHQLUpdate() {151         String hql = "DELETE FROM Department d WHERE d.id = :id";152         153         session.createQuery(hql).setInteger("id", 50)154                                 .executeUpdate();155         156     }157 }