Ejecutar procedimiento con JPA y anotaciones

En este post daremos dos formas de realizar llamadas a procedimientos haciendo uso de JPA. La primera de ellas, es algo menos ortodoxa y nos servirá para hacer llamadas nativas de cualquier tipo, no solo procedimientos. 

La primera forma es utilizando la anotacion '@NamedNativeQuery', que se encuentra disponible en JPA desde su primera especificación. Esta anotación te permite definir llamadas nativas sin mucha configuración, unicamente poniendole un nombre y cual va a ser la sentencia SQL Nativa. Aquí un ejemplo:

@NamedNativeQueries( { @NamedNativeQuery( name = "countColourCars", query = "CALL ASSES_PRODUCT(:color)" ) } )
@Entity
@Table( name = "CAR" )
public class Car{

Para poder realizar la llamada, podemos hacerlo a través de la implementación de un DAO. Un ejemplo:

@Repository
public class CarDaoImpl{
	@PersistenceContext
	private EntityManager em;
	public int countColourCars( final String colour) {
		Query query = em.createNamedQuery( "countColourCars" ).setParameter( "colour", colour);
		return ((BigInteger) query.getSingleResult()).intValue();
	}

Otra forma de hacerlo sería a través de la anotación '@NamedStoredProcedureQuery', la cual esta disponible a partir de la especificación JPA 2.1. Esta anotación tiene un funcionamiento similar a la anterior pero sirve solamente para procedimientos almacenados. Además tiene la particularidad de que a menos que se utilice Java 8 no puede ser repetida. Además en este ejemplo haremos uso de la antoación '@SqlResultSetMapping' que permita configurar cual va a ser la salida del procedimiento.

@StoredProcedureParameter( mode = ParameterMode.IN, type = String.class, name = "findNewsByName" ),
		@StoredProcedureParameter( mode = ParameterMode.IN, type = String.class, name = "orderField" ) }, resultSetMappings = "findNewsMapping" )
@SqlResultSetMapping( name = "findNewsMapping", classes = { @ConstructorResult( targetClass = New.class, columns = { @ColumnResult( name = "key", type = Integer.class ), @ColumnResult( name = "name", type = String.class ) } ) } )
@Entity
@Table( name = "NEW" )
public class New{

La forma de obtener los datos es practicamente igual.

@Repository
public class NewDaoImpl {
	@PersistenceContext
	private EntityManager em;

	@SuppressWarnings( "unchecked" )
	@Override
	public List<New> findNewsByName( final String newsName, final String orderField ) {
		StoredProcedureQuery storedProcedureQuery = em.createNamedStoredProcedureQuery( "findNewsByName" );
		storedProcedureQuery.setParameter( "newsName", newsName );
		storedProcedureQuery.setParameter( "orderField", orderField );
		storedProcedureQuery.execute();
		return storedProcedureQuery.getResultList();
	}

El funcionamiento es similar para ambas anotaciones, incluso también son posibles mapeos automaticos con '@NamedNativeQuery'.

Que son y como funcionan los Cursores SQL

Este va a ser un sencillo post sobre que son los cursores SQL y su funcionamiento. 

Para empezar comentaremos que hay dos tipos de cursores: Implícitos y explícitos.

Los implícitos tienen las siguientes características:

• No necesitan una declaración específica.
• Son aquellos cursores que permiten almacenar una única variable.
• Es necesaria la clausula INTO para almacenar el valor deseado.
• La variable debe ser del mismo tipo que el valor a retener.
• Comúnmente utilizados en los PL/SQL para almacenar valores a utilizar posteriormente.

declare
  vNombre VARCHAR2(50);
begin
  SELECT NOMBRE INTO vNombre from FICHEROS WHERE ID_FICHERO = 123456;
  dbms_output.put_line('El nombre es: ' || vNombre);
end;

Los explícitos son algo más complejos. Las principales características son:

• Necesitan una declaración específica antes de poder utilizarlos. 
• Permiten el almacenamiento de más de una variable en una estructura reutilizable. 
• Permiten parámetros de entrada para filtrar los resultados devueltos.

A continuación mostraremos un ejemplo de como declararlos y utilizarlos:

procedure createArticuloAlbaran (idAlbaran in VARCHAR2, idArticulo in VARCHAR2, resultado out RESULTADO_TYPE)
 is
 CURSOR CUR_PROPIO(idArt VARCHAR2) IS
  SELECT A.PRECIO AS PRECIO, A.CANTIDAD AS CANTIDAD
        FROM ARTICULOS A
        WHERE A.IDARTICULO = idArt; 

        BEGIN
  FOR REGISTRO_CUR IN CUR_PROPIO(idArticulo) LOOP
   INSERT INTO ALBARANES(IDALBARAN, IDARTICULO, PRECIO, CANTIDAD)
   VALUES(idAlbaran, idArticulo, REGISTRO_CUR.PRECIO, REGISTRO_CUR.CANTIDAD );
  END LOOP;
  COMMIT;
 end createArticuloAlbaran;

¿Porque la consulta del procedimiento no funciona? Resolución de nombres en un PL/SQL

En el día de hoy nos hemos encontrado con un asunto curioso, y es que una consulta que nos funcionaba sin problemas en el Toad, cuando la ejecutábamos dentro de un procedimiento no funcionaba como esperábamos, dando un resultado totalmente diferente.

El asunto gira en que hay que tener cuidado a la hora de poner nombre a las variables que usemos dentro del propio procedimiento. Si estas variables tienen el mismo nombre que algún campo de las tablas de la consulta, el procedimiento puede no ser capaz de realizar la sustitución y por lo tanto no realizar la consulta de forma correcta.

Para empezar debemos tener en cuenta los siguientes datos:

• Un procedimiento no diferencia entre mayúsculas y minúsculas.
• Las reglas de nomenclatura son mas restrictivas en un procedimiento que en un sentencia SQL.
• Los procedimientos resuelven los nombres de forma diferente a como lo hace una sentencia SQL. 

Teniendo en cuenta esto, aquí va algunos concejos para evitar este problema.

• Nombrar la variable con un nombre diferente al del campo (lo más lógico).
• Hacer uso de bloques dentro del procedimiento y nominar la variable haciendo referencia al bloque. 
• Al utilizar la variable local hacer referencia al procedimiento del que forma parte. 

Si queréis algo más de información o ejemplos concretos los podéis seguir en esta página de la documentación oficial.