首页 > 代码库 > hibernate的基础学习--多表关联数据查询

hibernate的基础学习--多表关联数据查询

Hibernate共提供4种多表关联数据查询方式
  OID数据查询+OGN数据查询方式
  HQL数据查询方式
  QBC数据查询方式
  本地SQL查询方式(hibernate很少用)

 

1、OID数据查询+OGN数据查询方式

 1 public class TestOID_OGN {
 2 
 3     /**
 4      * 什么时候时候什么时候发送
 5      */
 6     @Test
 7     public void findUser() {
 8         SessionFactory sf = H3Util.getSessionFactory();
 9         Session session = sf.openSession();
10         Transaction ts = session.beginTransaction();
11         //小李子
12         User user = (User) session.get(User.class, "ff80808157c82de80157c82deb3c0001");
13         System.out.println("=========");
14         
15         System.out.println(user);
16         ts.commit();
17         session.close();
18     }
19 }

 

2、HQL数据查询方式

/**
 * HQL 数据查询
 */
public class TestHQL {

    @Test
    public void findUser() {
        SessionFactory sf = H3Util.getSessionFactory();
        Session session = sf.openSession();
        Transaction ts = session.beginTransaction();
        
        String hql = "from User";
        Query query = session.createQuery(hql);
        @SuppressWarnings("unchecked")
        List<User> users = query.list();
        for (User user : users) {
            System.out.println("=="+user);
        }
        
        ts.commit();
        session.close();
    }
    
    @Test
    public void findUser1() {
        SessionFactory sf = H3Util.getSessionFactory();
        Session session = sf.openSession();
        Transaction ts = session.beginTransaction();
        
        //项目部中的所有 员工详细信息
        
        String hql = "from Dept where deptname = ‘人事部‘";
        Query query = session.createQuery(hql);
        Dept dept = (Dept) query.uniqueResult();
        
        //OGN
        Set<User> list = dept.getUsers();
        for (User user : list) {
            System.out.println(user);
        }
        
        ts.commit();
        session.close();
    }
    
    
    @Test
    public void findUser2() {
        SessionFactory sf = H3Util.getSessionFactory();
        Session session = sf.openSession();
        Transaction ts = session.beginTransaction();
        
        //项目部中的所有 员工详细信息
        //PO
        //Dept dept = (Dept) session.get(Dept.class,"ff80808157c831050157c83106c40001");
        
        //DO
        Dept dept = new Dept();
        dept.setDeptId("ff80808157c831050157c83106c40001");
        
        //以对象作为条件查询:根据对象id
        //只要保证该对象具备:OID  PO  DO
        
        String hql = "from User  where dept = ? ";
        Query query = session.createQuery(hql);
        query.setEntity(0, dept);
        //什么类型都可以只使用
        //query.setParameter(0, dept);
        
        
        @SuppressWarnings("unchecked")
        List<User> list = (List<User>) query.list();
        System.out.println("=================");
        for (User user : list) {
            System.out.println("=="+user);
        }
        
        ts.commit();
        session.close();
    }
}

 

3、QBC数据查询方式

 1 public class TestQBC {
 2 
 3     @Test
 4     public void findUser() {
 5         SessionFactory sf = H3Util.getSessionFactory();
 6         Session session = sf.openSession();
 7         Transaction ts = session.beginTransaction();
 8         
 9         Criteria criteria = session.createCriteria(User.class);
10         criteria.add(Restrictions.eq("username", "小小"));
11         
12         User user = (User) criteria.uniqueResult();
13         //session.clear();
14         System.out.println("=========");
15         System.out.println(user.getDept());
16         
17         ts.commit();
18         session.close();
19     }
20     
21     @Test
22     public void findUser1() {
23         SessionFactory sf = H3Util.getSessionFactory();
24         Session session = sf.openSession();
25         Transaction ts = session.beginTransaction();
26         
27         Dept dept = new Dept();
28         dept.setDeptId("ff80808157c831050157c83106c40001");
29         
30         Criteria criteria = session.createCriteria(User.class);
31         criteria.add(Restrictions.eq("dept", dept));
32             
33         @SuppressWarnings("unchecked")
34         List<User> list = criteria.list();
35         System.out.println("=========分割线===========");
36         for (User user : list) {
37             System.out.println(user);
38         }
39         
40         ts.commit();
41         session.close();
42     }
43     
44     @Test
45     public void findUser2() {
46         SessionFactory sf = H3Util.getSessionFactory();
47         Session session = sf.openSession();
48         Transaction ts = session.beginTransaction();
49         
50         Dept dept = (Dept) session.get(Dept.class, "ff80808157c831050157c83106c40001");
51         
52         //from User dept.deptId = ‘4028408157c14c210157c14c25370000‘
53         Criteria criteria = session.createCriteria(User.class);
54         //使用OGN:OID
55         criteria.add(Restrictions.eq("dept.deptId", dept.getDeptId()));
56         
57         
58         System.out.println("==========分割线============");
59         @SuppressWarnings("unchecked")
60         List<User> list = criteria.list();
61         for (User user : list) {
62             System.out.println(user);
63         }
64         
65         ts.commit();
66         session.close();
67     }
68     
69     
70     @Test
71     public void findUser3() {
72         SessionFactory sf = H3Util.getSessionFactory();
73         Session session = sf.openSession();
74         Transaction ts = session.beginTransaction();
75         
76         Dept dept = (Dept) session.get(Dept.class, "ff80808157c831050157c83106c40001");
77         
78         //from User dept.deptName = ‘项目部‘
79         Criteria criteria = session.createCriteria(User.class);
80         //QBC:ONG方式中如果使用的的OID可以查询,但是如果使用的是除OID之外的其它属性
81         //就会报异常,解决方案:createAlias("dept")
82         //为dept起别名
83         criteria.createAlias("dept", "dept");
84         criteria.add(Restrictions.eq("dept.deptName", dept.getDeptName()));
85 
86         @SuppressWarnings("unchecked")
87         List<User> list = criteria.list();
88         System.out.println("==========分割线===========");
89         for (User user : list) {
90             System.out.println(user);
91         }
92         
93         ts.commit();
94         session.close();
95     }
96 }

 

4、HQL数据查询方式(测试内连接,外连接查询)

  1 /**
  2  * @author nunu
  3  * 测试内连接,外连接查询
  4  */
  5 public class TestHQL2 {
  6 
  7     
  8     /**
  9      * 内连接:
 10      *  有关联数据:
 11      *  数据总量:从表被关联的数据总量
 12      *    数据模型:主表数据(延迟从) + 从表数据(含主)
 13      * 
 14      */
 15     @Test
 16     public void findUser() {
 17         SessionFactory sf = H3Util.getSessionFactory();
 18         Session session = sf.openSession();
 19         Transaction ts = session.beginTransaction();
 20         
 21         //部门:主表
 22         //员工:从表
 23         //sql:select d.*,u.* from t_dept d inner join t_user u on d.dept_id = u.dept_id;
 24         String hql = "from Dept d inner join d.users";
 25         Query query = session.createQuery(hql);
 26         
 27         @SuppressWarnings("unchecked")
 28         List<Object[]> list = query.list();
 29         System.out.println(list.size());
 30         
 31         System.out.println("==========分割线===========");
 32         for (int i = 0; i < list.size(); i++) {
 33             Object[] objs = list.get(i);
 34             //主表数据
 35             Dept dept = (Dept) objs[0];
 36             User user = (User) objs[1];
 37             System.out.println(user);
 38             System.out.println(dept);
 39             /*
 40             //主表关联数据
 41             Set<User> userSet = dept.getUsers();
 42             for (User user : userSet) {
 43                 //如果延迟加载:回发送新语句查询
 44                 System.out.println(user);
 45             }
 46             */
 47             System.out.println("==");
 48             /*
 49             //从表数据
 50             User user = (User) objs[1];
 51             
 52             //从表关联数据
 53             Dept dept = user.getDept();
 54             
 55             System.out.println(dept);
 56             */
 57         }
 58         
 59         ts.commit();
 60         session.close();
 61     }
 62     
 63     
 64     /**
 65      *     迫切内连接(数据重复DISTINCT)
 66      *        数据总量:从表被关联的数据总量
 67      *        数据模型:主表数据 (含从)
 68     */
 69     @Test
 70     public void findUser1() {
 71         SessionFactory sf = H3Util.getSessionFactory();
 72         Session session = sf.openSession();
 73         Transaction ts = session.beginTransaction();
 74         
 75         //部门:主表
 76         //员工:从表
 77         //sql:select d.*,u.* from t_dept d inner join t_user u on d.dept_id = u.dept_id;
 78         String hql = "from Dept d inner join fetch d.users";
 79         Query query = session.createQuery(hql);
 80         @SuppressWarnings("unchecked")
 81         List<Dept> list = query.list();
 82         System.out.println(list.size());
 83         System.out.println("===============分隔符=================");
 84         session.clear();
 85         for (Dept dept : list) {
 86             Set<User> users = dept.getUsers();
 87             for (User user : users) {
 88                 System.out.println(user);
 89             }
 90             System.out.println("---------");
 91         }
 92         
 93         ts.commit();
 94         session.close();
 95     }
 96     
 97     
 98     /**
 99      *     隐式内连接
100      *         数据总量:主表数据总量
101      *         数据模型:主表数据(延迟从)
102     */
103     @Test
104     public void findUser2() {
105         SessionFactory sf = H3Util.getSessionFactory();
106         Session session = sf.openSession();
107         Transaction ts = session.beginTransaction();
108         
109         //部门:主表
110         //员工:从表
111         //sql:select d.*,u.* from t_dept d inner join t_user u on d.dept_id = u.dept_id;
112         String hql = "from Dept";        //默认就会用户关联了
113         Query query = session.createQuery(hql);
114         @SuppressWarnings("unchecked")
115         List<Dept> list = query.list();
116         System.out.println(list.size());
117         //session.clear();
118         System.out.println("===============分隔符=================");
119         for (Dept dept : list) {
120             Set<User> users = dept.getUsers();
121             for (User user : users) {
122                 System.out.println(user);
123             }
124             System.out.println("-----------------");
125         }
126         
127         ts.commit();
128         session.close();
129     }
130     
131     
132     /**
133         左外连接: 以左侧表为准
134             数据总量:从表被关联数据总量+主表未关联数据总量
135             数据模型:主表数据(延迟从) ,从表数据 (含主)
136             (主表不存在关联数据的从表数据为0)
137     */
138     @Test
139     public void findUser3() {
140         SessionFactory sf = H3Util.getSessionFactory();
141         Session session = sf.openSession();
142         Transaction ts = session.beginTransaction();
143         
144         //部门:主表
145         //员工:从表
146         //sql:select d.*,u.* from t_dept d inner join t_user u on d.dept_id = u.dept_id;
147         String hql = "from Dept d left outer join d.users";//默认就会用户关联了
148         Query query = session.createQuery(hql);
149         
150         @SuppressWarnings("unchecked")
151         List<Object[]> list = query.list();
152         System.out.println(list.size());
153         
154         System.out.println("============分隔符==============");
155         for (int i = 0; i < list.size(); i++) {
156             Object[] objs = list.get(i);
157             //主表数据
158             System.out.println("\t ==主表数据");
159             Dept dept = (Dept) objs[0];
160             System.out.println("\t "+dept);
161             
162             System.out.println("\t ==主表关联数据");
163             //主表关联数据
164             Set<User> userSet = dept.getUsers();
165             for (User user : userSet) {
166                 //如果延迟加载:回发送新语句查询
167                 System.out.println("\t "+user);
168             }
169             
170             System.out.println("\t ==从表数据");
171             //从表数据
172             User user = (User) objs[1];
173             System.out.println("\t "+user);
174             
175             System.out.println("\t ==从表关联数据");
176             //从表关联数据
177             Dept dept1 = user.getDept();
178             
179             System.out.println("\t "+dept1);
180             System.out.println("-------------");
181         }
182         ts.commit();
183         session.close();
184     }
185     
186     
187     /**
188      *    迫切左外连接(数据重复DISTINCT)
189             数据总量:从表被关联数据总量+主表未关联数据总量
190             数据模型:主表数据(含从)
191             (主表不存在关联数据的从表数据为0)
192     */
193     @Test
194     public void findUser4() {
195         SessionFactory sf = H3Util.getSessionFactory();
196         Session session = sf.openSession();
197         Transaction ts = session.beginTransaction();
198         
199         //部门:主表
200         //员工:从表
201         //sql:select d.*,u.* from t_dept d inner join t_user u on d.dept_id = u.dept_id;
202         String hql = "from Dept d left outer join fetch d.users";//默认就会用户关联了
203         Query query = session.createQuery(hql);
204         
205         @SuppressWarnings("unchecked")
206         List<Dept> list =  query.list();
207         System.out.println("============分隔符==============");
208         for (Dept dept : list) {
209             System.out.println(dept);
210             
211             Set<User> userSet = dept.getUsers();
212             for (User user : userSet) {
213                 System.out.println(user);
214             }
215             System.out.println("-----------");
216         }
217         ts.commit();
218         session.close();
219     }
220     
221     
222     
223     /**
224      *    右外连接:
225             数据总量:从表数据总量
226             数据模型:主表数据(延迟从) ,从表数据(含主)
227             (从表不存在关联数据的主表数据为null)
228     */
229     @Test
230     public void findUser5() {
231         SessionFactory sf = H3Util.getSessionFactory();
232         Session session = sf.openSession();
233         Transaction ts = session.beginTransaction();
234         
235         //部门:主表
236         //员工:从表
237         //sql:select d.*,u.* from t_dept d inner join t_user u on d.dept_id = u.dept_id;
238         String hql = "from Dept d right outer join  d.users";//默认就会用户关联了
239         Query query = session.createQuery(hql);
240         
241         @SuppressWarnings("unchecked")
242         List<Object[]> list = query.list();
243         System.out.println(list.size());
244         System.out.println("=============分隔符=================");
245         for (int i = 0; i < list.size(); i++) {
246             Object[] objs = list.get(i);
247             //主表数据
248             //Dept dept = (Dept) objs[0];
249             //System.out.println(dept.getDeptName());
250             
251             
252             //主表关联数据
253             /*Set<User> userSet = dept.getUsers();
254             for (User user : userSet) {
255                 //如果延迟加载:回发送新语句查询
256                 System.out.println(user.getUserId());
257             }*/
258             
259             //从表数据
260             User user = (User) objs[1];
261             System.out.println(user);
262             
263             //从表关联数据
264 //            Dept dept = user.getDept();
265 //            System.out.println(dept.getDeptName());
266 //            System.out.println(user.getUsername());
267             System.out.println("-------------");
268         }
269         ts.commit();
270         session.close();
271         ts.commit();
272         session.close();
273     }
274     
275     
276 }

 

hibernate的基础学习--多表关联数据查询