Reading All the records from a table + Hibernate

[Jigar Naik]

Hi i wanted to read all the records from a table and store it in a List.

I wrote the bellow code for the same

public List<Customer> listCustomer() {
		List<Customer> customers = new ArrayList<Customer>();
		try {
			System.out.println("Before LIST_CUSTOMER");
			Session session = HibernateUtil.beginTransaction();
			Query queryResult = session.createQuery("from Customer");
			customers = queryResult.list();
			HibernateUtil.commitTransaction();
			System.out.println("After LIST_CUSTOMER");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return customers;
	}
[code]

but the code i have written fires 

[code]    
Before LIST_CUSTOMER
Hibernate: select customer0_.id as id1_, customer0_.contact1 as contact2_1_, customer0_.contact2 as contact3_1_, customer0_.customer_name as customer4_1_, customer0_.email as email1_ from licensemanager.customer customer0_
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
Hibernate: select modules0_.customer_id as customer5_1_, modules0_.id as id1_, modules0_.id as id0_0_, modules0_.code as code0_0_, modules0_.customer_id as customer5_0_0_, modules0_.expiry_date as expiry3_0_0_, modules0_.name as name0_0_ from licensemanager.module modules0_ where modules0_.customer_id=?
After LIST_CUSTOMER

where as i was expecting only

select customer0_.id as id1_, customer0_.contact1 as contact2_1_, customer0_.contact2 as contact3_1_, customer0_.customer_name as customer4_1_, customer0_.email as email1_ from licensemanager.customer customer0_

Why rest of the select queries are getting fired. ???

[Francois Nadeau]

Not sure, but why are you using a transaction if all you are doing is querying the database?
Also, you probably want to release your Session after using it (unless you are only using one I guess).

    
public List<Customer> listCustomer() {  
        // Session session = HibernateUtil.beginTransaction();  
       Session session = HibernateUtil.getSession();  
        try {  
            System.out.println("Before LIST_CUSTOMER");  
            return (List<Customer>)session.createQuery("from Customer").list();  

        } catch (Exception e) {  
            e.printStackTrace();  
            return new ArrayList<Customer>();

        }  finally {
            System.out.println("After LIST_CUSTOMER");  
            session.close();
        }
    }    

[Jigar Naik]

Hi Francois

i tried without transaction but i got bellow stack trace...

org.hibernate.HibernateException: createQuery is not valid without active transaction

[Francois Nadeau]

Hold on a minute, I think I miss read your log.

Does your Customer Object have a relationship with licensemanager.module?

If so what it is, and how did you define it?

[Jigar Naik]

Yes there is a relationship between Customer and Module

module.customer_id is a foreign key holding reference to customer.id

Customer POJO

    
package com.interactcrm.customer;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import com.interactcrm.customer.Customer;
import com.interactcrm.module.Module;
import com.opensymphony.xwork2.validator.annotations.RequiredFieldValidator;
import com.opensymphony.xwork2.validator.annotations.RequiredStringValidator;
import com.opensymphony.xwork2.validator.annotations.Validations;

@Entity
@Table(name = "customer", schema = "licensemanager")
@Validations
public class Customer {

	private Long id;
	private String customerName;
	private String email;
	private String contact1;
	private String contact2;
	private List<Module> modules;

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

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

	@Column(name = "customer_name")
	@RequiredStringValidator(message="Please enter customer name")
	public String getCustomerName() {
		return customerName;
	}

	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}

	@Column(name = "email")
	public String getEmail() {
		return email;
	}

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

	@Column(name = "contact1")
	@RequiredStringValidator(message="Please provide one contact")
	public String getContact1() {
		return contact1;
	}

	public void setContact1(String contact1) {
		this.contact1 = contact1;
	}

	@Column(name = "contact2")
	public String getContact2() {
		return contact2;
	}

	public void setContact2(String contact2) {
		this.contact2 = contact2;
	}

	@OneToMany(mappedBy = "customer", targetEntity = Module.class, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
	public List<Module> getModules() {
		return modules;
	}

	public void setModules(List<Module> modules) {
		this.modules = modules;
	}

}

Module POJO

    
package com.interactcrm.module;

import java.io.Serializable;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import com.interactcrm.customer.Customer;
import com.opensymphony.xwork2.validator.annotations.RequiredStringValidator;
import com.opensymphony.xwork2.validator.annotations.ValidatorType;

@Entity
@Table(name = "module", schema = "licensemanager")
public class Module implements Serializable {

	private Long id;
	private String code;
	private String name;
	private Date expiryDate;
	private Customer customer;

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

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

	@Column(name = "code")
	@RequiredStringValidator(message = "Code can not be blank", type = ValidatorType.FIELD, shortCircuit = true, trim = true)
	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	@Column(name = "name")
	@RequiredStringValidator(message = "Name can not be blank", type = ValidatorType.FIELD, shortCircuit = true, trim = true)
	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Column(name = "expiry_date")
	public Date getExpiryDate() {
		return expiryDate;
	}

	public void setExpiryDate(Date expiryDate) {
		this.expiryDate = expiryDate;
	}

	@ManyToOne
	@JoinColumn(name = "customer_id")
	public Customer getCustomer() {
		return customer;
	}

	public void setCustomer(Customer customer) {
		this.customer = customer;
	}

}

[Francois Nadeau]

Ok, now I understand.

The extra calls are loading the Module information when you are getting the customer info.

You have two options if that is not what you want:

1. Load the Modules lazily

@OneToMany(mappedBy = "customer", targetEntity = Module.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL)  

This will delay loading the Module info until you need it. This may increases performance if you don't always need the info.

2. Use a Join

	1.	@OneToMany(mappedBy = "customer", targetEntity = Module.class, fetch = FetchType.EAGER, cascade = CascadeType.ALL)  
@JoinColumn(name="MODULE_ID", nullable=false)

[Jigar Naik]

Hi Francois ,

Thans a lot.. it worked...

FetchType.LAZY i got...

but i am not able to understand

@JoinColumn(name="MODULE_ID", nullable=false)  

What it does ?

[Francois Nadeau]

Hi Jigar,

Sorry, about that. The JoinColumn will perform an SQL join with the Module table when you load the Customer data. So that will allow Hibernate to load the data for both of the classes with a single select query.

Be careful with the Lazy load though. You will get an LazyInitializationException if you need the information inside the Module, but no longer are within the scope of the session that you are using.

For example:

public List<Customer> listCustomer() {  
        List<Customer> customers = new ArrayList<Customer>();  
        try {  
            System.out.println("Before LIST_CUSTOMER");  
            Session session = HibernateUtil.beginTransaction();  
            Query queryResult = session.createQuery("from Customer");  
            customers = queryResult.list();  
            HibernateUtil.commitTransaction();

            session.close();
            for(Customer oneCustomer : customers) {
	            for(Module oneModule : oneCustomer.getModules()) {
                        oneModule.getName();
	            }
            }
  
            System.out.println("After LIST_CUSTOMER");  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        return customers;  
  }  

Should not work.