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); }
}
}

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);
}
}

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();
}
}

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s