Wednesday, 6 June 2012

Select only few columns in Hibernate create query API

 

Step : 1

Set up Java + Hibernate Environment as by this post.

image

Step : 2

Alter our patient table as shown

image

Step : 3

Now let go back to our patient.java and add the columns as follows

package mypack;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Column;
import javax.persistence.Transient;

@Entity
@Table(name = "patient")
public class patient {

private Integer id;
private String firstName;
private String lastName;
private String password;
private String email;
private String address1;
private String address2;
private String city;
private String state;
private String zipCode;


@Transient
public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

@Id
@GeneratedValue
@Column(name = "ID")
public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

@Column(name = "firstname")
public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

@Column(name = "lastname")
public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getAddress1() {
return address1;
}

public void setAddress1(String address1) {
this.address1 = address1;
}

public String getAddress2() {
return address2;
}

public void setAddress2(String address2) {
this.address2 = address2;
}

public String getCity() {
return city;
}

public void setCity(String city) {
this.city = city;
}

public String getState() {
return state;
}

public void setState(String state) {
this.state = state;
}

public String getZipCode() {
return zipCode;
}

public void setZipCode(String zipCode) {
this.zipCode = zipCode;
}


}


Step : 4



Modify our Test.java file as follows


package mypack;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import Utility.HibernateUtil;

public class Test {


public static void main(String[] args) {
getAllRecords();
}

public static void getAllRecords() {
List<patient> allpatients;
Session session = HibernateUtil.beginTransaction();
Query q1 = session.createQuery("from patient");
allpatients = q1.list();
for (int i = 0; i < allpatients.size(); i++) {
patient pt = (patient) allpatients.get(i);
System.out.println(pt.getLastName());
}
HibernateUtil.CommitTransaction();
}


}


Step : 5



Please remember from the above step, it will retrieve all the columns from the DB, you can check this by printing each value. But suppose if we want to select Firstname, lastname, then how we can do that ?  Let us try now


package mypack;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import Utility.HibernateUtil;

public class Test {


public static void main(String[] args) {
getAllRecords();
}

public static void getAllRecords() {
List<patient> allpatients = null;
Session session = HibernateUtil.beginTransaction();
Query q1 = session.createQuery("Select firstName, lastName from patient");
for (int i = 0; i < allpatients.size(); i++) {
patient pt = (patient) allpatients.get(i);
System.out.println(pt.getLastName());
}
allpatients = q1.list();
HibernateUtil.CommitTransaction();
}


}


If we now run this, then it will throw error, just let us remove the for loop and see


package mypack;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import Utility.HibernateUtil;

public class Test {


public static void main(String[] args) {
getAllRecords();
}

public static void getAllRecords() {
List<patient> allpatients = null;
Session session = HibernateUtil.beginTransaction();
Query q1 = session.createQuery("Select firstName, lastName from patient");
allpatients = q1.list();
HibernateUtil.CommitTransaction();
}


}


Now it will compile and run even though there is no output.


So what is happening ?  Here is the concept


if you use select, then it becomes Native SQL Query. So the output will not be your collection of patient objects, instead of that it will return list of object arrays. So we need to change our for loop as follows


package mypack;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import Utility.HibernateUtil;

public class Test {


public static void main(String[] args) {
getAllRecords();
}

public static void getAllRecords() {
List allpatients;
Session session = HibernateUtil.beginTransaction();
Query q1 = session.createQuery("Select firstName, lastName from patient");
allpatients= q1.list();
for (Iterator it = allpatients.iterator(); it.hasNext(); ) {
Object[] myResult = (Object[]) it.next();
String firstName = (String) myResult[0];
String lastName = (String) myResult[1];
System.out.println( "Found " + firstName + " " + lastName );
}
HibernateUtil.CommitTransaction();
}


}


Now if you run , you can see the output as follows


Hibernate: select patient0_.firstname as col_0_0_, patient0_.lastname as col_1_0_ from patient patient0_
Found Simth John
Found ROBERT DAVID
Found RICHARD JOSEPH
Found Mark Anderson


 



Step : 6



What is the alternate way to retrieve as patient objects ?


Here you go


Query q1 = session.createQuery("Select new patient(firstName,lastName) from patient");


Before this step, we should have proper constructor to create object with only two values. Now let us go back to our patient.java and add the constructor as follows


package mypack;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Column;
import javax.persistence.Transient;

@Entity
@Table(name = "patient")
public class patient {

private Integer id;
private String firstName;
private String lastName;
private String password;
private String email;
private String address1;
private String address2;
private String city;
private String state;
private String zipCode;

//Our default Constructor
public patient()
{

}

//constructor with only two columns
public patient(String fname, String lname)
{
this.firstName= fname;
this.lastName = lname;
}

@Transient
public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

@Id
@GeneratedValue
@Column(name = "ID")
public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

@Column(name = "firstname")
public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

@Column(name = "lastname")
public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getAddress1() {
return address1;
}

public void setAddress1(String address1) {
this.address1 = address1;
}

public String getAddress2() {
return address2;
}

public void setAddress2(String address2) {
this.address2 = address2;
}

public String getCity() {
return city;
}

public void setCity(String city) {
this.city = city;
}

public String getState() {
return state;
}

public void setState(String state) {
this.state = state;
}

public String getZipCode() {
return zipCode;
}

public void setZipCode(String zipCode) {
this.zipCode = zipCode;
}


}


 


Step : 7



Let us modify our test.java file as follows


package mypack;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import Utility.HibernateUtil;

public class Test {


public static void main(String[] args) {
getAllRecords();
}

public static void getAllRecords() {
List allpatients;
Session session = HibernateUtil.beginTransaction();
Query q1 = session.createQuery("Select new patient(firstName,lastName) from patient");
allpatients = q1.list();
for (int i = 0; i < allpatients.size(); i++) {
patient pt = (patient) allpatients.get(i);
System.out.println(pt.getLastName());
System.out.println(pt.getFirstName());
}
HibernateUtil.CommitTransaction();
}

}


Now you can run and see the output. Now it retrieves as patient objects

14 comments:

  1. Very useful document to understand.Concept is Explained clearly.

    ReplyDelete
  2. When you use "select", it does NOT become a native query. It selects columns instead of entities, but the language is still HQL.
    To get a native query (SQL), you must use createNativeQuery, where you also can select column, but then depend on the DBMS used.

    ReplyDelete
  3. You made my day!!... found very much useful.
    Thanks a lot :)

    ReplyDelete
  4. Good example ..

    ReplyDelete
  5. @Thomas I guess you meant createSQLQuery (and thanks for the tip).

    ReplyDelete
  6. This was great thanks. Can you do an update after this? That is can I fill in other fields of patient and then do an update. In my case I am filling in EVERY non-queried column. I have a getter app and a setter app. the getter is obvious. The setter gets 2 columns and for each column calcs new remaining columns and updates them. Basically the setter does a get of the 2 cols to know what to update.

    ReplyDelete
  7. Great...This was very useful :)

    ReplyDelete
  8. Hi sir, this is well detailed. I've a problem in the Java Spring framework using Hibernate Query. My problem in my table doesn't have the id of getting value, but this is having in another table how to get id into present trouble (This is one too many) how please tell me and give me solution

    ReplyDelete
  9. Great Workaround for the named queries!!!

    ReplyDelete
  10. very clean/simple explanation...

    ReplyDelete
  11. It is really a great work and the way in which u r sharing the knowledge is excellent.
    Thanks for helping me to understand HQL concepts. As a beginner in java programming your post help me a lot.Thanks for your informative article. Best java training in chennai

    ReplyDelete
  12. It is really a great work and the way in which u r sharing the knowledge is excellent.
    Thanks for helping me to understand HQL concepts. As a beginner in java programming your post help me a lot.Thanks for your informative article. Best java training in chennai

    ReplyDelete