Java 如何通过休眠从多个表查询中获取数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21562442/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fetch data from multiple table query by hibernate?
提问by user3174407
I have a user management application that allocate each user a team and one or many access to different application. Now for the reporting page I am trying to fetch data from two table (UserInfo & UserAppAccess) by Hibernate but I can't. Here are the tables :
我有一个用户管理应用程序，为每个用户分配一个团队和一个或多个不同应用程序的访问权限。现在对于报告页面，我试图通过 Hibernate 从两个表（UserInfo 和 UserAppAccess）中获取数据，但我不能。以下是表格：
Table 1 (UserInfo):
表 1（用户信息）：
@Entity
@Table(name = "user_info", uniqueConstraints = { @UniqueConstraint(columnNames = "username"), @UniqueConstraint(columnNames = "email") })
public class UserInfo implements java.io.Serializable {
public enum UserStatus {
active, inactive
}
public enum UserType {
user, creator
}
private static final long serialVersionUID = 2650114334774359089L;
@Id
@Column(name = "id", unique = true, nullable = false, length = 100)
private String id;
@Column(name = "username", unique = true, nullable = false, length = 50)
private String username;
@Column(name = "password", nullable = false, length = 80)
private String password;
@Column(name = "status", nullable = false, length = 10)
@Enumerated(EnumType.STRING)
private UserStatus status;
@Column(name = "type", nullable = false, length = 10)
@Enumerated(EnumType.STRING)
private UserType type;
@Column(name = "phone", nullable = true, length = 30)
private String phone;
@Column(name = "email", nullable = true, length = 50)
private String email;
@Column(name = "first_name", nullable = true, length = 50)
private String firstName;
@Column(name = "last_name", nullable = true, length = 50)
private String lastName;
@Column(name = "login", nullable = true, length = 100)
private long login;
@Column(name = "alert", nullable= true, length=500)
private String alert;
@OneToOne
@JoinColumn(name = "team_id")
private Team team;
}
Table 2 (Team):
表2（团队）：
@Entity
@Table(name = "team", uniqueConstraints = { @UniqueConstraint(columnNames = "team_name"), @UniqueConstraint(columnNames = "team_code") })
public class Team implements java.io.Serializable {
private static final long serialVersionUID = 7933770163144650730L;
@Id
@Column(name = "id", unique = true, nullable = false, length = 80)
private String id;
@Column(name = "team_name", unique = true, nullable = false, length = 100)
private String name;
@Column(name = "team_code", unique = true, nullable = false, length = 10)
private String code;
}
Table 3 (Access):
表 3（访问）：
@Entity
@Table(name = "access_def")
public class Access implements java.io.Serializable {
private static final long serialVersionUID = 7933770163144650730L;
@Id
@Column(name = "id", unique = true, nullable = false, length = 80)
private String id;
@Column(name = "access_name", unique = true, nullable = false, length = 100)
private String name;
@Column(name = "access_code", unique = true, nullable = false, length = 10)
private String code;
}
Table 4 (Application):
表 4（应用）：
@Entity
@Table(name = "application", uniqueConstraints = { @UniqueConstraint(columnNames = "name") })
public class Application implements java.io.Serializable {
private static final long serialVersionUID = 5803631085624275364L;
@Id
@Column(name = "name", nullable = false, length = 100)
private String name;
}
Table 5 (UserAppAccess):
表 5 (UserAppAccess)：
@Entity
@Table(name = "user_app_access")
@Embeddable
public class UserAppAccess implements java.io.Serializable {
private static final long serialVersionUID = 7933770163144650730L;
@Id
@Column(name = "id", unique = true, nullable = false, length = 80)
private String id;
@OneToOne
@JoinColumn(name = "user_id")
private UserInfo userInfo;
@Column(name = "app_name", nullable = false, length = 100)
private String appName;
@OneToOne
@JoinColumn(name = "access_id")
private Access access;
}
I have a report page that allow Admin to select multiple options (for example: list all active users in Team test and application APP1).
我有一个报告页面，允许管理员选择多个选项（例如：列出团队测试和应用程序 APP1 中的所有活动用户）。
here is my code to fetch the data but it is not working :
这是我获取数据的代码，但它不起作用：
public List<?> getReport(String teamId,String appName,UserStatus active,UserStatus inactive) {
Session session = sessionFactory.getCurrentSession();
String hql = "SELECT u.firstName,u.username,u.status,u.lastName,u.phone,u.team From UserInfo u,AppAccess a WHERE u.status =? OR u.status =? AND u.team.id = ? AND a.appName = :appName ";
Query query = session.createQuery(hql);
query.setParameter(0, active);
query.setParameter(1, inactive);
query.setParameter(2, teamId);
query.setParameter("appName", appName);
System.out.println(query.list());
return query.list();
}
For instance when I pass
例如当我通过
- Active Users: Active
- inactive User:null
- team:test
application :app1
teamId :28f66133-26c3-442b-a071-4d19d64ec0aeappName :app1active :activeinactive:null
- 活跃用户：活跃
- 非活动用户：空
- 团队：测试
应用程序：app1
teamId :28f66133-26c3-442b-a071-4d19d64ec0aeappName :app1active :activeinactive:null
I am getting this back from my return query.list();
我从我的return query.list();
[[Ljava.lang.Object;@2961116f, [Ljava.lang.Object;@23bfa3a2, [Ljava.lang.Object;@7a8ff303, [Ljava.lang.Object;@9b88d2, [Ljava.lang.Object;@6333934d, [Ljava.lang.Object;@4f0bd71c, [Ljava.lang.Object;@125797cf, [Ljava.lang.Object;@34afa071, [Ljava.lang.Object;@764e75bc, [Ljava.lang.Object;@1913c652, [Ljava.lang.Object;@61413e5a, [Ljava.lang.Object;@264b898, [Ljava.lang.Object;@22930462, [Ljava.lang.Object;@6204cfa9, [Ljava.lang.Object;@29dd9285, [Ljava.lang.Object;@11be6f3c, [Ljava.lang.Object;@6d78d53d, [Ljava.lang.Object;@17f7cff1, [Ljava.lang.Object;@e74e382, [Ljava.lang.Object;@1c047338, [Ljava.lang.Object;@68286fe6, [Ljava.lang.Object;@36ca9a76, [Ljava.lang.Object;@2f62d514, [Ljava.lang.Object;@1932c5a, [Ljava.lang.Object;@6544c984, [Ljava.lang.Object;@70a2d0d, [Ljava.lang.Object;@2d13b417, [Ljava.lang.Object;@6894691f, [Ljava.lang.Object;@6781a7dc, [Ljava.lang.Object;@7133919a]
回答by Antoniossss
By adding LEFT JOIN FETCHor FETCH ALL PROPERTIES. This will force JOINSinsteed of lazy initialization
通过添加LEFT JOIN FETCH或FETCH ALL PROPERTIES。这将强制JOINS执行延迟初始化
String hql = "SELECT u.firstName,u.username,u.status,u.lastName,u.phone,u.team From UserInfo u,AppAccess a FETCH ALL PROPERTIES WHERE u.status =? OR u.status =? AND u.team.id = ? AND a.appName = :appName ";
More information can be found in HQL Documentation
更多信息可以在HQL 文档中找到
回答by gerrytan
Firstly: I hope each of your entity classes have a toString()method (can be auto-generated with eclipse) so you can print them. Printing object reference isn't enough to infer whether/not you're getting what you want.
首先：我希望您的每个实体类都有一个toString()方法（可以使用 eclipse 自动生成），以便您可以打印它们。打印对象引用不足以推断您是否得到了您想要的东西。
Secondly, the syntax of HQL joins is normally like this:
其次，HQL 连接的语法通常是这样的：
String queryString = "select distinct f from Foo f inner join foo.bars as b" +
" where f.creationDate >= ? and f.creationDate < ? and b.bar = ?";
(taken from How do you create a Distinct query in HQL)
回答by Konstantin Pavlov
I'd suggest using native SQL and JDBC for reporting (see How should I use Hibernate Mapping while dealing with huge data table)
我建议使用本机 SQL 和 JDBC 进行报告（请参阅 如何在处理大量数据表时使用 Hibernate Mapping）
For performance reasons it is desirable to create view model objects from result set right in the DAO. It may looks like mixing levels of abstraction (view layer with persistence layer), but it's ok when you need to fetch a big amounts of data and don't want to make unnecessary object transformations from persistence models to view models.
出于性能原因，最好从 DAO 中的结果集创建视图模型对象。它可能看起来像是混合了抽象级别（视图层和持久层），但是当您需要获取大量数据并且不想进行从持久模型到视图模型的不必要的对象转换时，这没问题。
If you're want to stuck with hibernate, you may define a syntetic entity and map if on a view, containing only necessary columns from multiple:
如果你想坚持使用 hibernate，你可以定义一个合成实体并在视图上映射，只包含来自多个必要的列：
@Entity
@Table("V_USER_REPORT")
public class UserAppData {
// columns from table "user"
@Id
@Column(name = "id", unique = true, nullable = false, length = 100)
private String id;
@Column(name = "username", unique = true, nullable = false, length = 50)
private String username;
// columns from table "user"
@Column(name = "app_name", nullable = false, length = 100)
private String appName;
// columns from table "team"
@Column(name = "team_id", unique = true, nullable = false, length = 80)
private String team_id;
@Column(name = "team_name", unique = true, nullable = false, length = 100)
private String name;
@Column(name = "team_code", unique = true, nullable = false, length = 10)
private String code;
// and so on...
}
Then you fetch such entity by parameters as you do it with normal entity.
然后你通过参数获取这样的实体，就像你对普通实体一样。
