Custom Joins in Hibernate Query Language

This example tries to elaborate and explain how to make customized joins in hibernate queries. I will also explain how to retrieve the data from the HQL into a custom bean.

I will not get into hibernate setup in this blog. I will discuss that in some later blog. For this example, I am assuming a working knowledge in hibernate from you guys.

My bean in which i need to retrieve the data is:

public class UserApplicationBean implements Serializable {

private Long id;

private long userId;

private String userName;

private Double appPrice;

private Boolean free;

private Boolean isActive;

private int industryId;

private String appDescription;

private String name;

private String appIcon;

private boolean isDeleted;

private boolean android;

private boolean blackberry;

private boolean ipad;

private boolean iphone;

private String submitStatus;

private Date submitDate;

private String firstCategory;

private String secondCategory;

//Getter setter follow

}

The columns in my bean are mapped to the User table in my database i.e. for each column in my db table, I have a corresponding column in my bean.

Now, lets look at the query.

SELECT new com.vibe.bean.UserApplicationBean(userApp.id, userApp.name,

userApp.appIcon, userApp.submitDate, userApp.firstCategory, user.firstName, user.lastName,

userAppVital.isFree, userAppVital.appPrice,

(

SELECT userApp.id

))

FROM UserApplication userApp, User user, UserApplicationAppVital userAppVital

WHERE userApp.user.id = user.id

AND userAppVital.userApplication.id = userApp.id

AND userApp.submitStatus =:submitStatus

AND userApp.isDeleted = false

AND userApp.iphone = true

ORDER BY userApp.submitDate DESC

In this query, I am making a simple join between three tables user, userApplication and userApplicationAppVital (Refer to the where clause in the query).

Now lets us see the Select part of the query:

"Select new com.vibe.bean.UserApplicationBean( ... )"

In this, I am directly creating a new object of my bean and giving the columns to be selected as its arguments(refer to the query). So how does hibernate handle such a query?

To understand this lets first understand how a normal query works in hibernate.

Lets take a query :

Select * from User;

Where user is a model bean mapped to the database table. User code would be something like this:

@Entity

@Table(name = "tb_user")

public class User implements Serializable

{

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

private Long id;

private String firstName;

private String lastName;

.

.

.

}

Now, when hibernate executes the above query, hibernate internally maps the model bean in the query with the database table, fires the query and retrieves the resultset.

Now, hibernate code iterates the result set returned from the database and for every record returned from the database, creates a new object of the model bean and populates the object with the values returned.

The object is then added to a List. After iterating the complete result set, hibernate returns a reference to the List created as result to the calling function.

I hope I am clear till now. We have seen how hibernate interprets a simple query.

Now, returning back to our original query, if we see the "Select" clause of the query, the query become self-explanatory. Lets discuss the query:

"Select new com.vibe.bean.UserApplicationBean( ... )"

In this query, we have directed hibernate to use a particular bean class for storing the values returned from the database. Using this syntax, we can direct hibernate to store the result in any bean object we may like. Also, if we just require some columns to be returned instead of all the columns in the table, this method is better suited as it is faster.

Now, there are couple more things we need to do before we can make our query to work. We need to define a custom constructor in our bean corresponding to the columns mentioned in the "Select" part of the query:

public UserApplicationBean(){}

public UserApplicationBean(Long id, String name, String appIcon, Date submitDate, String firstCategory, String firstName, String lastName, Boolean isFree, Double appPrice, String submitStatus)

{

this.id=id;

this.name=name;

this.appIcon=appIcon;

this.submitDate=submitDate;

this.firstCategory=firstCategory;

this.userName = firstName + " " + lastName;

this.free = isFree;

this.appPrice = appPrice;

this.submitStatus = submitStatus;

}

Here, I have made a constructor corresponding to the new directive in the query. Also, please note that, I have only used complex data types in the constructor signature. This is necessary otherwise hibernate will throw an error.

Now we are all set. Run this query like all your other queries. Hibernate will return you a list of objects of the bean specified in the query which you can directly use ahead.

This is my first blog so please bear with me :). Also, do post your questions in the comments and I will try to resolve your issues.