Hibernate JPA 2.1 – StoredProcedureQuery

by Corinne Vionnet / from beautifuldecay.com

Thanks to… someone… that we have JPA 2.1 and the feature StoredProcedureQuery. Throught this method we can call easily a procedure in our database, set parameters and convert the possible result into Java object automatically.

Let’s imagine a simple procedure called ‘SALES_ACCOUNTING’ that receives two dates (‘INITIALDATE’ and ‘FINALDATE’) that determine the time period and returns something like that:

employee	conversion_rate	sales_amount	item_sales_amount	attendance_amount
Tássio Auad	90	90	94	100

Firstly, we can create a class that represents this result, in the same way any hibernate entity represents a database table.

	@Entity
	public class SalesAccounting implements Serializable {

	@Id
	@Column(name = "employee")
	private String employeeName;

	@Column(name = "conversion_rate")
	private Float conversionRate;

	@Column(name = "sales_amount")
	private Integer salesAmount;

	@Column(name = "item_sales_amount")
	private Integer itemSalesAmount;

	@Column(name = "attendance_amount")
	private Integer attendanceAmount;

	//…
	}

view raw

SalesAccounting.java

hosted with ❤ by GitHub

Ok, now we can call our procedure and transform each result automatically to a SalesAccounting instance. The main class, the class that will represent our procudure, is called StoredProcedureQuery and we can instanciate it throught EntityManager.createStoredProcedureQuery(). In the first parameters we will pass the procuure’s name and in the seconde paramter, the class that will be instanciated to receive the values of results.

	public class SalesAccountingModel {

	private EntityManager entityManager;

	public SalesAccountingModel(EntityManager entityManager) {
	this.entityManager = entityManager;
	}

	public List listAll(Date initialDate, Date finalDate) {
	StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("SALES_ACCOUNTING", SalesAccounting.class); }
	}
	}

view raw

SalesAccountingModel.java

hosted with ❤ by GitHub

Ok, but what about our procudure parameter ‘INITIALDATE’ and ‘FINALDATE’? How can I inform that there is these two parameters? Easy. Just use the method registerStoredProcedureParameter(parametername, classtype, inORout?) like this:

	public class SalesAccountingModel {

	private EntityManager entityManager;

	public SalesAccountingModel(EntityManager entityManager) {
	this.entityManager = entityManager;
	}

	public List listAll(Date initialDate, Date finalDate) {
	StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("SALES_ACCOUNTING", SalesAccounting.class)
	.registerStoredProcedureParameter("INITIALDATE", Date.class, ParameterMode.IN)
	.registerStoredProcedureParameter("FINALDATE", Date.class, ParameterMode.IN);
	}

	}

view raw

SalesAccountingModel.java

hosted with ❤ by GitHub

And, to finish our step-by-step, let’s execute our StoredProcedureQuery instance:

	public class SalesAccountingModel {

	private EntityManager entityManager;


	public SalesAccountingModel(EntityManager entityManager) {
	this.entityManager = entityManager;
	}

	public List listAll(Date initialDate, Date finalDate) {
	StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("SALES_ACCOUNTING", SalesAccounting.class)
	.registerStoredProcedureParameter("INITIALDATE", Date.class, ParameterMode.IN)
	.registerStoredProcedureParameter("FINALDATE", Date.class, ParameterMode.IN);
	return (List) storedProcedureQuery.setParameter("INITIALDATE", dataInicio).setParameter("FINALDATE", dataFim).getResultList();
	}

	}