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]  

Answer 1

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.