Android room 一对多查询条件 hibernate一对多查询语法

1. 前言

本章节我们讨论Hibernate一对多查询的处理。

在上一章节中（Hibernate（一）——入门），我们探讨了Hibernate执行最基本的增删改查操作。现在我们将情况复杂化：加入我们在查询用户信息的时候需要同时查询其登录日志，这样就涉及到一对多查询。那么一对多查询要怎么实现么？

2. jar包准备

在本节中，除了上一章节中用到的jar包，我还需要用log4j.jar来将Hibernate的查询语句输出到控制台。log4j.properties的配置如下：

1 log4j.rootLogger=info,console
  2 log4j.appender.console=org.apache.log4j.ConsoleAppender
  3 log4j.appender.console.layout=org.apache.log4j.PatternLayout
  4 log4j.appender.console.layout.ConversionPattern=%d %p [%c] - %m%n

 log4j的使用方法可查阅：Mybatis之一级缓存（七）中，log4j的学习和使用部分。

3. 数据库准备

我们需要新建立日志表tbLog，并产生部分的测试数据。代码如下：

1 CREATE TABLE tbLog (
2     logID VARCHAR(50),
3     userID VARCHAR(50),
4     loginDate DATETIME
5 )

1 TRUNCATE TABLE tbUser
  2 TRUNCATE TABLE tbLog
  3 
  4 DECLARE @userID1 VARCHAR(50)
  5 DECLARE @userID2 VARCHAR(50)
  6 SET @userID1 = NEWID();
  7 SET @userID2 = NEWID();
  8 
  9 INSERT INTO tbUser(userID, loginName, userName, passWord)
 10 SELECT @userID1,'luych','卢艳超','12333' UNION ALL
 11 SELECT @userID2,'guest','游客','12333'
 12 
 13 INSERT INTO tbLog(logID, userID, loginDate)
 14 SELECT NEWID(), @userID1, '2016-04-01' UNION ALL
 15 SELECT NEWID(), @userID1, '2016-04-02' UNION ALL
 16 SELECT NEWID(), @userID1, '2016-04-05' UNION ALL
 17 SELECT NEWID(), @userID1, '2016-04-08' UNION ALL
 18 
 19 SELECT NEWID(), @userID2, '2016-04-11' UNION ALL
 20 SELECT NEWID(), @userID2, '2016-04-22'
 21 
 22 SELECT * FROM tbUser;
 23 SELECT * FROM tbLog;

4. 准备JAVA对象

（1）建立与数据表tbLog相对应的JAVA对象，代码如下：

1 package com.luych.hibernate.study.entity;
  2 
  3 import java.util.Date;
  4 
  5 import javax.persistence.Entity;
  6 import javax.persistence.Id;
  7 import javax.persistence.Table;
  8 
  9 
 10 @Entity
 11 @Table(name="tbLog")
 12 public class LogEntity {
 13 
 14     @Id
 15     private String logID;
 16     private String userID;
 17     private Date loginDate;
 18 
 19     public String getLogID() {
 20         return logID;
 21     }
 22     public void setLogID(String logID) {
 23         this.logID = logID;
 24     }
 25     public String getUserID() {
 26         return userID;
 27     }
 28     public void setUserID(String userID) {
 29         this.userID = userID;
 30     }
 31     public Date getLoginDate() {
 32         return loginDate;
 33     }
 34     public void setLoginDate(Date loginDate) {
 35         this.loginDate = loginDate;
 36     }
 40 }

当然，我们同时也要在Hibernate的xml中增加相应的配置

1 <mapping class="com.luych.hibernate.study.entity.LogEntity"/>

（2）调整UserEntity对象，建立其与LogEntity的一对多关系。

1 package com.luych.hibernate.study.entity;
  2 
  3 import java.text.SimpleDateFormat;
  4 import java.util.Set;
  5 
  6 import javax.persistence.CascadeType;
  7 import javax.persistence.Entity;
  8 import javax.persistence.Id;
  9 import javax.persistence.JoinColumn;
 10 import javax.persistence.OneToMany;
 11 import javax.persistence.Table;
 12 
 13 @Entity
 14 @Table(name="tbUser")
 15 public class UserEntity {
 16 
 17     @Id
 18     private String userID;
 19     private String loginName;
 20     private String userName;
 21     private String passWord;
 22     @OneToMany(cascade=CascadeType.ALL)
 23     @JoinColumn(name="userID")
 24     private Set<LogEntity> logs;
 25 
 26     public String getUserID() {
 27         return userID;
 28     }
 29     public void setUserID(String userID) {
 30         this.userID = userID;
 31     }
 32     public String getLoginName() {
 33         return loginName;
 34     }
 35     public void setLoginName(String loginName) {
 36         this.loginName = loginName;
 37     }
 38     public String getUserName() {
 39         return userName;
 40     }
 41     public void setUserName(String userName) {
 42         this.userName = userName;
 43     }
 44     public String getPassWord() {
 45         return passWord;
 46     }
 47     public void setPassWord(String passWord) {
 48         this.passWord = passWord;
 49     }
 50     public Set<LogEntity> getLogs() {
 51         return logs;
 52     }
 53     public void setLogs(Set<LogEntity> logs) {
 54         this.logs = logs;
 55     }
 56     @Override
 57     public String toString() {
 58         String str = loginName+", "+userName+", "+passWord+", "+userID+" 登录日志：\n";
 59         for (LogEntity log: logs) {
 60             str = str+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(log.getLoginDate())+"\n";
 61         }
 62         return str;
 63     }
 64 }
 65

 其中，

@OneToMany的cascade可取值为：

CascadeType.PERSIST：级联新建，本例中即生成User的时候同时生成Log。

CascadeType.REMOVE : 级联删除，本例中即删除User的时候同时删除Log。

CascadeType.REFRESH：级联刷新，本例中即查询User的时候同时查询Log。

CascadeType.MERGE  ：级联更新，本例中即修改User的时候同时修改Log。

CascadeType.ALL    ：以上全部四项，即上面四个全都执行。

@JoinColumn的name取值为：LogEntity中的userID属性。

5. 调整Hibernate配置文件

1 <?xml version="1.0" encoding="UTF-8"?>
  2 <!DOCTYPE hibernate-configuration PUBLIC
  3         "-//Hibernate/Hibernate Configuration DTD 5.0//EN"
  4         "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
  5 <hibernate-configuration>
  6     <session-factory>
  7         <!-- 设置数据库驱动 -->
  8         <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
  9         <!-- 设置数据库URL -->
 10         <property name="hibernate.connection.url">jdbc:sqlserver://192.168.9.23:14433;databaseName=tempdb</property>
 11         <!-- 数据库用户名 -->
 12         <property name="hibernate.connection.username">sa</property>
 13         <!-- 数据库密码 -->
 14         <property name="hibernate.connection.password">123@abcd</property>
 15         <!-- 打印sql -->
 16         <property name="show_sql">true</property>
 17         <property name="format_sql">false</property>
 18         <!-- beans -->
 19         <mapping class="com.luych.hibernate.study.entity.UserEntity"/>
 20         <mapping class="com.luych.hibernate.study.entity.LogEntity"/>
 21     </session-factory>
 22 </hibernate-configuration>

 我们增加了针对show_sql和format_sql的配置，加上这两个配置后，Hibernate会输出执行的SQL脚本。

• show_sql：true，输出SQL脚本。false，不输出。
• format_sql：true，格式化SQL脚本。false，不格式化。

 本例中，并没有将format_sql设置为true，是因为格式化的SQL在控制台中显示很占篇幅，不利于我们后面看测试结果，所以关闭了。

6. 测试运行结果

1 package com.luych.hibernate.study.main;
  2 
  3 import java.util.Date;
  4 import java.util.HashSet;
  5 import java.util.List;
  6 import java.util.Set;
  7 import java.util.UUID;
  8 
  9 import org.hibernate.Query;
 10 import org.hibernate.Session;
 11 import org.hibernate.SessionFactory;
 12 import org.hibernate.cfg.Configuration;
 13 import org.junit.After;
 14 import org.junit.Before;
 15 import org.junit.Test;
 16 
 17 import com.luych.hibernate.study.entity.LogEntity;
 18 import com.luych.hibernate.study.entity.UserEntity;
 19 
 20 @SuppressWarnings("unchecked")
 21 public class TestMain {
 22 
 23     private Session session;
 24 
 25     @Before
 26     public void getSession(){
 27         Configuration config = new Configuration().configure("hibernate-config.xml");
 28         SessionFactory sessionFactory = config.buildSessionFactory();
 29         session = sessionFactory.openSession();
 30     }
 31 
 32     @After
 33     public void freeSession(){
 34         session.close();
 35     }
 36 
 37     public void sel() {
 38         Query query = session.createQuery("FROM UserEntity WHERE 1=1");
 39         List<UserEntity> userList = query.list();
 40         for (UserEntity userEntity : userList) {
 41             System.out.println(userEntity.toString());
 42         }
 43     }
 44 
 45     public void add() {
 46         session.beginTransaction();
 47         String userID = UUID.randomUUID().toString();
 48         UserEntity user  = new UserEntity();
 49         user.setLoginName("admin");
 50         user.setUserName("系统管理员");
 51         user.setPassWord("12333");
 52         user.setUserID(userID);
 53         LogEntity log1 = new LogEntity();
 54         log1.setLogID(UUID.randomUUID().toString());
 55         log1.setUserID(userID);
 56         log1.setLoginDate(new Date());
 57         LogEntity log2 = new LogEntity();
 58         log2.setLogID(UUID.randomUUID().toString());
 59         log2.setUserID(userID);
 60         log2.setLoginDate(new Date());
 61         Set<LogEntity> logs = new HashSet<LogEntity>();
 62         logs.add(log1);
 63         logs.add(log2);
 64         user.setLogs(logs);
 65         session.save(user);
 66         session.getTransaction().commit();
 67     }
 68 
 69     public void edt(){
 70         session.beginTransaction();
 71         Query query = session.createQuery("FROM UserEntity WHERE 1=1");
 72         List<UserEntity> userList = query.list();
 73         for (UserEntity userEntity : userList) {
 74             userEntity.setPassWord("45666");
 75             LogEntity log = new LogEntity();
 76             log.setLogID(UUID.randomUUID().toString());
 77             log.setUserID(userEntity.getUserID());
 78             log.setLoginDate(new Date());
 79             userEntity.getLogs().add(log);
 80             session.update(userEntity);
 81         }
 82         session.getTransaction().commit();
 83     }
 84 
 85     public void del(){
 86         session.beginTransaction();
 87         Query query = session.createQuery("FROM UserEntity WHERE 1=1");
 88         List<UserEntity> userList = query.list();
 89         for (UserEntity userEntity : userList) {
 90             session.delete(userEntity);
 91         }
 92         session.getTransaction().commit();
 93     }
 94 
 95     @Test
 96     public void test(){
 97         System.out.println("\n----------现有用户：");
 98         sel();
 99         System.out.println("\n----------开始增加用户：");
100         add();
101         System.out.println("\n----------增加用户后：");
102         sel();
103         System.out.println("\n----------开始修改用户：");
104         edt();
105         System.out.println("\n----------修改用户后：");
106         sel();
107         System.out.println("\n----------开始删除用户：");
108         del();
109         System.out.println("\n----------删除用户后：");
110         sel();
111     }
112 }

getSession和freeSession和上一章节中相同，不再赘述。

add方法，新建了一个用户并设定了两条登录日志，然后保存。edt方法，将所有用户的密码改为45666，并为所有的用户增加一条登录日志。del方法，删除所有的用户。sel方法，查询所有用户信息并输出到控制台。

右键，Run As JUnit Test后，控制台输出结果为：

1 
  2 ----------现有用户：
  3 Hibernate: select userentity0_.userID as userID1_1_, userentity0_.loginName as loginNam2_1_,

userentity0_.passWord as passWord3_1_, userentity0_.userName as userName4_1_

from tbUser userentity0_ where 1=1
  4 Hibernate: select logs0_.userID as userID3_0_0_, logs0_.logID as logID1_0_0_,

logs0_.logID as logID1_0_1_, logs0_.loginDate as loginDat2_0_1_, logs0_.userID as userID3_0_1_

from tbLog logs0_ where logs0_.userID=?
  5 
  6 luych, 卢艳超, 12333, CB6172E3-8750-4718-BEF6-EE0917015FA9 登录日志：
  7 2016-04-01 00:00:00
  8 2016-04-08 00:00:00
  9 2016-04-05 00:00:00
 10 2016-04-02 00:00:00
 11 
 12 Hibernate: select logs0_.userID as userID3_0_0_, logs0_.logID as logID1_0_0_,

logs0_.logID as logID1_0_1_, logs0_.loginDate as loginDat2_0_1_, logs0_.userID as userID3_0_1_

from tbLog logs0_ where logs0_.userID=?
 13 
 14 guest, 游客, 12333, 21539577-A3D1-4A1F-8D10-6ED0540A46A0 登录日志：
 15 2016-04-11 00:00:00
 16 2016-04-22 00:00:00
 17 
 18 
 19 ----------开始增加用户：
 20 Hibernate: select logentity_.logID, logentity_.loginDate as loginDat2_0_, logentity_.userID as userID3_0_

from tbLog logentity_ where logentity_.logID=?
 21 Hibernate: select logentity_.logID, logentity_.loginDate as loginDat2_0_, logentity_.userID as userID3_0_

from tbLog logentity_ where logentity_.logID=?
 22 Hibernate: insert into tbUser (loginName, passWord, userName, userID) values (?, ?, ?, ?)
 23 Hibernate: insert into tbLog (loginDate, userID, logID) values (?, ?, ?)
 24 Hibernate: insert into tbLog (loginDate, userID, logID) values (?, ?, ?)
 25 Hibernate: update tbLog set userID=? where logID=?
 26 Hibernate: update tbLog set userID=? where logID=?
 27 
 28 ----------增加用户后：
 29 Hibernate: select userentity0_.userID as userID1_1_, userentity0_.loginName as loginNam2_1_,

userentity0_.passWord as passWord3_1_, userentity0_.userName as userName4_1_

from tbUser userentity0_ where 1=1
 30 
 31 luych, 卢艳超, 12333, CB6172E3-8750-4718-BEF6-EE0917015FA9 登录日志：
 32 2016-04-01 00:00:00
 33 2016-04-08 00:00:00
 34 2016-04-05 00:00:00
 35 2016-04-02 00:00:00
 36 
 37 
 38 guest, 游客, 12333, 21539577-A3D1-4A1F-8D10-6ED0540A46A0 登录日志：
 39 2016-04-11 00:00:00
 40 2016-04-22 00:00:00
 41 
 42 
 43 admin, 系统管理员, 12333, 99d5d264-9d02-4e45-a8c5-f710cc14107e 登录日志：
 44 2016-04-26 17:06:00
 45 2016-04-26 17:06:00
 46 
 47 
 48 ----------开始修改用户：
 49 Hibernate: select userentity0_.userID as userID1_1_, userentity0_.loginName as loginNam2_1_,

userentity0_.passWord as passWord3_1_, userentity0_.userName as userName4_1_

from tbUser userentity0_ where 1=1
 50 Hibernate: select logentity_.logID, logentity_.loginDate as loginDat2_0_,

logentity_.userID as userID3_0_

from tbLog logentity_ where logentity_.logID=?
 51 Hibernate: select logentity_.logID, logentity_.loginDate as loginDat2_0_,

logentity_.userID as userID3_0_

from tbLog logentity_ where logentity_.logID=?
 52 Hibernate: select logentity_.logID, logentity_.loginDate as loginDat2_0_,

logentity_.userID as userID3_0_

from tbLog logentity_ where logentity_.logID=?
 53 Hibernate: insert into tbLog (loginDate, userID, logID) values (?, ?, ?)
 54 Hibernate: insert into tbLog (loginDate, userID, logID) values (?, ?, ?)
 55 Hibernate: insert into tbLog (loginDate, userID, logID) values (?, ?, ?)
 56 Hibernate: update tbUser set loginName=?, passWord=?, userName=? where userID=?
 57 Hibernate: update tbUser set loginName=?, passWord=?, userName=? where userID=?
 58 Hibernate: update tbUser set loginName=?, passWord=?, userName=? where userID=?
 59 Hibernate: update tbLog set userID=? where logID=?
 60 Hibernate: update tbLog set userID=? where logID=?
 61 Hibernate: update tbLog set userID=? where logID=?
 62 
 63 ----------修改用户后：
 64 Hibernate: select userentity0_.userID as userID1_1_, userentity0_.loginName as loginNam2_1_,

userentity0_.passWord as passWord3_1_, userentity0_.userName as userName4_1_

from tbUser userentity0_ where 1=1
 65 
 66 luych, 卢艳超, 45666, CB6172E3-8750-4718-BEF6-EE0917015FA9 登录日志：
 67 2016-04-01 00:00:00
 68 2016-04-08 00:00:00
 69 2016-04-05 00:00:00
 70 2016-04-02 00:00:00
 71 2016-04-26 17:06:00
 72 
 73 
 74 guest, 游客, 45666, 21539577-A3D1-4A1F-8D10-6ED0540A46A0 登录日志：
 75 2016-04-11 00:00:00
 76 2016-04-22 00:00:00
 77 2016-04-26 17:06:00
 78 
 79 
 80 admin, 系统管理员, 45666, 99d5d264-9d02-4e45-a8c5-f710cc14107e 登录日志：
 81 2016-04-26 17:06:00
 82 2016-04-26 17:06:00
 83 2016-04-26 17:06:00
 84 
 85 
 86 ----------开始删除用户：
 87 Hibernate: select userentity0_.userID as userID1_1_, userentity0_.loginName as loginNam2_1_,

userentity0_.passWord as passWord3_1_, userentity0_.userName as userName4_1_

from tbUser userentity0_ where 1=1
 88 Hibernate: update tbLog set userID=null where userID=?
 89 Hibernate: update tbLog set userID=null where userID=?
 90 Hibernate: update tbLog set userID=null where userID=?
 91 Hibernate: delete from tbLog where logID=?
 92 Hibernate: delete from tbLog where logID=?
 93 Hibernate: delete from tbLog where logID=?
 94 Hibernate: delete from tbLog where logID=?
 95 Hibernate: delete from tbLog where logID=?
 96 Hibernate: delete from tbUser where userID=?
 97 Hibernate: delete from tbLog where logID=?
 98 Hibernate: delete from tbLog where logID=?
 99 Hibernate: delete from tbLog where logID=?
100 Hibernate: delete from tbUser where userID=?
101 Hibernate: delete from tbLog where logID=?
102 Hibernate: delete from tbLog where logID=?
103 Hibernate: delete from tbLog where logID=?
104 Hibernate: delete from tbUser where userID=?
105 
106 ----------删除用户后：
107 Hibernate: select userentity0_.userID as userID1_1_, userentity0_.loginName as loginNam2_1_,

userentity0_.passWord as passWord3_1_, userentity0_.userName as userName4_1_

from tbUser userentity0_ where 1=1
108

从打印结果中，我们可以看到，新增、编辑、删除用户信息的时候，Hibernate都帮我们完成登录日志的新增、删除、操作。查询的时候也如此。

但是需要提点的是：在Hibernate第一次查询中，我们看到它先查询了tbUser表，然后针对tbUser表的每一个记录都又查询了下tbLog表，这就是经典的N+1查询问题，所以效率嘛…

以上就是Hibernate中一对多的查询关联，其他关联情况将在后续的博文中讲解。