Select only few columns in Hibernate create query API

 

Step : 1

Set up Java + Hibernate Environment as by this post.

Step : 2

Alter our patient table as shown

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

Read More

Hibernate 4.1.1 will create the column if we misspelled in the annotation

If we misspelled the column name in the annotation, then hibernate will create that column automatically. I don’t know whether we can turn this feature off.

Steps to Reproduce

1. Setup the environment as said here.
2. Now go back to patient.java and change @Column(name="firstname") to @Column(name="firtname")
3. Now run the test java file.
4. You can see, it will run successfully without any error.
5. And now go back to db. and if you check the patient table, now you can see one more column added and null inserted for all the records

Read More

Hibernate Query uniqueResult Method

 

After completing my Hibernate Post, I want to know what will happen if there are more than one row expected when we use uniqueResult method.

So in the same example, I put one more method called getPatientByLastName, where we can pass any last name and print the other details of the patient.

Here is the complete code for Test.Java as per Part 4

package mypack;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.hibernate.HibernateException;

public class Test {

    private static SessionFactory factory;
    private static ServiceRegistry serviceRegistry;

    public static void main(String[] args) {
        /*
        createRecords();
        getAllRecords();
         getPatientByID(1);
        getPatientByID(13);
        */
        getPatientByLastName("Smith");
       
    }

    public static void createRecords() {

        try {
            Configuration configuration = new Configuration();
            configuration.configure();
            serviceRegistry = new ServiceRegistryBuilder().applySettings(
                    configuration.getProperties()).buildServiceRegistry();
            factory = configuration.buildSessionFactory(serviceRegistry);
        } catch (Throwable ex) {
            System.err.println("Failed to create sessionFactory object." + ex);
            throw new ExceptionInInitializerError(ex);
        }
        System.out.println("**Example : Hibernate 4 SessionFactory**");
        System.out.println("----------------------------------------");

        Session session = factory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            patient pt = new patient();
            pt.setFirstName("John12");
            pt.setLastName("Smith23");
            session.saveOrUpdate(pt);
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }
   
    public static void getAllRecords() {

        try {
            Configuration configuration = new Configuration();
            configuration.configure();
            serviceRegistry = new ServiceRegistryBuilder().applySettings(
                    configuration.getProperties()).buildServiceRegistry();
            factory = configuration.buildSessionFactory(serviceRegistry);
        } catch (Throwable ex) {
            System.err.println("Failed to create sessionFactory object." + ex);
            throw new ExceptionInInitializerError(ex);
        }
        System.out.println("**Example : Hibernate  SessionFactory**");
        System.out.println("----------------------------------------");
       
        List allpatients;
       
        Session session = factory.openSession();
        Transaction tx = null;
        try {
            tx = session.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());
            }
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

   
    public static void getPatientByID(int anyvalue) {

        try {
            Configuration configuration = new Configuration();
            configuration.configure();
            serviceRegistry = new ServiceRegistryBuilder().applySettings(
                    configuration.getProperties()).buildServiceRegistry();
            factory = configuration.buildSessionFactory(serviceRegistry);
        } catch (Throwable ex) {
            System.err.println("Failed to create sessionFactory object." + ex);
            throw new ExceptionInInitializerError(ex);
        }
        System.out.println("**Example : Hibernate  SessionFactory**");
        System.out.println("----------------------------------------");
       
       
       
        Session session = factory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            Query q1 = session.createQuery("from patient where id =:idvalue");
            q1.setInteger("idvalue", anyvalue);
            Object queryResult = q1.uniqueResult();
            patient p1 = (patient)queryResult;
            tx.commit();
            System.out.print(p1.getLastName());
        } catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

   
    public static void getPatientByLastName(String anyvalue) {

        try {
            Configuration configuration = new Configuration();
            configuration.configure();
            serviceRegistry = new ServiceRegistryBuilder().applySettings(
                    configuration.getProperties()).buildServiceRegistry();
            factory = configuration.buildSessionFactory(serviceRegistry);
        } catch (Throwable ex) {
            System.err.println("Failed to create sessionFactory object." + ex);
            throw new ExceptionInInitializerError(ex);
        }
        System.out.println("**Example : Hibernate  SessionFactory**");
        System.out.println("----------------------------------------");
       
       
       
        Session session = factory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            Query q1 = session.createQuery("from patient where lastname =:varLastName");
            q1.setString("varLastName",anyvalue);
            Object queryResult = q1.uniqueResult();
            patient p1 = (patient)queryResult;
            tx.commit();
            System.out.print(p1.getLastName());
        } catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

}

If we run this, we will get the following error without any problem. Thank god, at least hibernate throwing exception if something is wrong.

**Example : Hibernate  SessionFactory**
----------------------------------------
Hibernate: select patient0_.ID as ID0_, patient0_.firstname as firstname0_, patient0_.lastname as lastname0_ from patient patient0_ where lastname=?
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
    at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:899)
    at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    at mypack.Test.getPatientByLastName(Test.java:162)
    at mypack.Test.main(Test.java:26)

Read More

Hibernate retrieve the first record always when we try retrieve all records

 

Today I come across a very strange behavior with hibernate while I am trying to retrieve all the records.

Here is that

1. Go to my Hibernate Introduction Part 4 Post

2. In the Patient.Java bean class, you can see the following lines

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

Assume that we misspelled the Column name id as RowID (this may happen when we cut and paste code from different project/examples)

Now it will look like

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

 

Now go to Test.Java and comment all the methods except getallRecords()

Now run the application

You can observer that first record is repeated up to total records in the table.

 

Read More

Hibernate POC

I am not sure whether title for this post is correct or wrong. But in the post , i am going to write how hibernate works if we play around in the application.

1. Tables are automatically created
 After Hibernate Introduction Part 1, i just went to the back end database, change the table name from patient

to patient1. Now i again run the application, it runs without any exception. But when i refresh the db at the back end, i saw , again patient table is created and record is inserted.

Read More