Calling PL/SQL Stored Procedure from Hibernate

Stored Procedure

create or replace procedure test (out_cursor out sys_refcursor)
is
begin
  open out_cursor for
  select 'one' a, 'two' b from dual;
end test;

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">

  <persistence-unit name="com.sandahaung_testsp_jar_1.0-SNAPSHOTPU" transaction-type="RESOURCE_LOCAL">

    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <properties>

      <property name="hibernate.connection.username" value="hr"/>

      <property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver"/>

      <property name="hibernate.connection.password" value="hradmin"/>

      <property name="hibernate.connection.url" value="jdbc:oracle:thin:@localhost:1521:XE"/>

      <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>

      <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9iDialect"/>

      <property name="hibernate.hbm2ddl.auto" value="update"/>

    </properties>

  </persistence-unit>

</persistence>

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sandahaung</groupId>

    <artifactId>testsp</artifactId>

    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

    <name>testsp</name>

    <url>http://maven.apache.org</url>

    <repositories>

        <repository>

            <url>http://download.java.net/maven/2/</url>

            <id>hibernate-persistence</id>

            <layout>default</layout>

            <name>Repository for library Library[hibernate-persistence]</name>

        </repository>

    </repositories>

    <properties>

        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    </properties>

    <dependencies>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>3.8.1</version>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.hibernate</groupId>

            <artifactId>hibernate</artifactId>

            <version>3.2.5.ga</version>

        </dependency>

        <dependency>

            <groupId>org.hibernate</groupId>

            <artifactId>ejb3-persistence</artifactId>

            <version>1.0.1.GA</version>

        </dependency>

        <dependency>

            <groupId>org.hibernate</groupId>

            <artifactId>hibernate-entitymanager</artifactId>

            <version>3.3.2.GA</version>

        </dependency>

        <dependency>

            <groupId>javax.sql</groupId>

            <artifactId>jdbc-stdext</artifactId>

            <version>2.0</version>

        </dependency>

        <dependency>

            <groupId>javax.transaction</groupId>

            <artifactId>jta</artifactId>

            <version>1.0.1B</version>

        </dependency>

        <dependency>

            <groupId>com.oracle</groupId>

            <artifactId>ojdbc14</artifactId>

            <version>11.1.0.7.0</version>

        </dependency>

            

    </dependencies>

</project>

Entity

package com.sandahaung.testsp.entities;

import java.io.Serializable;

import javax.persistence.*;

/**

 *

 * @author Sandah

 */

@NamedNativeQueries({

    @NamedNativeQuery(name = "testCall",

    query = "call test(?)",

    hints = {@QueryHint(name = "org.hibernate.callable", value = "true")},

    resultSetMapping = "oneTwo")

})

@SqlResultSetMapping(name = "oneTwo", entities = {

    @EntityResult(entityClass = com.sandahaung.testsp.entities.OneTwo.class, fields = {

        @FieldResult(name = "a", column = "a"),

        @FieldResult(name = "b", column = "b")

    })

})

@Entity

public class OneTwo implements Serializable {

    @Id

    private String a;

    private String b;

    public String getA() {

        return a;

    }

    public void setA(String a) {

        this.a = a;

    }

    public String getB() {

        return b;

    }

    public void setB(String b) {

        this.b = b;

    }

}

main

package com.sandahaung.testsp;

import com.sandahaung.testsp.entities.OneTwo;

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import javax.persistence.Persistence;

import javax.persistence.Query;

public class App {

    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("com.sandahaung_testsp_jar_1.0-SNAPSHOTPU");

        EntityManager em = emf.createEntityManager();

        

        Query query = em.createNamedQuery("testCall");

        

        OneTwo ot = (OneTwo) query.getSingleResult();

        

        System.out.println("a: " + ot.getA());

        System.out.println("b: " + ot.getB());

        

        em.close();

    }

}