How to fetch data from multiple table query by hibernate?

Question

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 :

Table 1 (UserInfo):

@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):

@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):

@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):

@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):

@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).

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

I am getting this back from my 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]

why are you selecting individual fields instead of "select User u from ..." ? — radai, Feb 04 '14 at 20:16
you're getting a list of objects, what is wrong with that? do you join u and a in your query? seems you select values only from u, is that intended? — peter.petrov, Feb 04 '14 at 20:22

score 1 · Answer 1 · edited May 23 '17 at 11:47

I'd suggest using native SQL and JDBC for reporting (see How should I use Hibernate Mapping while dealing with huge data table)

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.

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:

@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.

score 0 · Answer 2 · answered Feb 04 '14 at 21:42

By adding LEFT JOIN FETCH or FETCH ALL PROPERTIES. This will force JOINS insteed of lazy initialization

 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

score 0 · Answer 3 · edited May 23 '17 at 10:31

0

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.

Secondly, the syntax of HQL joins is normally like this:

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)

edited May 23 '17 at 10:31

Community

1
1

answered Feb 04 '14 at 21:58

gerrytan

40,313
9
84
99

the problem is there are four parameter and based on that i have to generate report . here are the parameters list of 1:all active users, 2: list of all inactive users 3:list of users in application A , 4:list of all users associated to team X . user can select one or more option and report has to be generated based on the selection . Now if user just select 2: list of all inactive then the other three parameters would be null or user can select list of all active users in team B and so on .. . Can you give me a SQL that can handle this scenario – user3174407 Feb 05 '14 at 16:44

How to fetch data from multiple table query by hibernate?

3 Answers3