Hibernate/JPA Named Stored Procedure XML and Annotation Example

This tutorial show how to use Named Stored Procedures using JPA with either XML and Annotations. We show a concrete example how to use Named Stored Procedures.

Maven Dependencies

We use Apache Maven to manage the projects dependencies.

<?xml version="1.0" encoding="UTF-8"?>
<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.memorynotfound.db.hibernate</groupId>
    <artifactId>jpa-named-stored-procedure</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>HIBERNATE - ${project.artifactId}</name>
    <url>https://memorynotfound.com</url>

    <properties>
        <mysql.driver.version>6.0.4</mysql.driver.version>
        <hibernate.version>5.2.3.Final</hibernate.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.driver.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Create Stored Procedure

Stored procedures reside on the database. To use stored procedures, we first must create them. Here are two examples of stored procedures that we’re using in this example.

DROP PROCEDURE IF EXISTS SP_ADD_BOOK;
CREATE PROCEDURE SP_ADD_BOOK(IN id INTEGER(11), IN title VARCHAR(255))
BEGIN
    INSERT INTO Book (id, title) VALUES (id, title);
END;


DROP PROCEDURE IF EXISTS SP_GET_BOOK_BY_ID;
CREATE PROCEDURE SP_GET_BOOK_BY_ID(IN id INTEGER(11))
BEGIN
    SELECT * FROM Book book WHERE book.id = id;
END;

Create Model + Mappings

You can annotate the class with the @NamedStoredProcedureQueries annotation to register multiple @NamedStoredProcedureQuery annotations. The Named Stored Procedure Query can be applied to an entity or mapped superclass. The @NamedStoredProcedureQuery annotation is used to map the stored procedure to JPA. The @StoredProcedureParameter annotation is used to map the input/output parameters to the Stored Procedure.

package com.memorynotfound.hibernate;

import javax.persistence.*;

@Entity
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(
                name = "SP_GetBookById",
                procedureName = "SP_GET_BOOK_BY_ID",
                resultClasses = Book.class,
                parameters = {
                        @StoredProcedureParameter(
                                name = "id",
                                mode = ParameterMode.IN,
                                type = Integer.class)
                }
        ),
        @NamedStoredProcedureQuery(
                name = "SP_AddBook",
                procedureName = "SP_ADD_BOOK",
                parameters = {
                        @StoredProcedureParameter(
                                name = "id",
                                mode = ParameterMode.IN,
                                type = Integer.class),
                        @StoredProcedureParameter(
                                name = "title",
                                mode = ParameterMode.IN,
                                type = String.class)
                }
        )
})
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String title;

    public Book() {
    }

    public Book(String title) {
        this.title = title;
    }

    public Integer getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", title='" + title + '\'' +
                '}';
    }
}

If you prefer XML over Annotations, you can use the equivalent JPA XML mapping. This file is located in the src/main/resources/META-INF folder and is named orm.xml.

<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
                                     http://xmlns.jcp.org/xml/ns/persistence/orm_2_0.xsd" version="2.1">

    <!-- JPA Named Stored Procedures -->
    <named-stored-procedure-query name="SP_GetBookById" procedure-name="SP_GET_BOOK_BY_ID">
        <parameter class="java.lang.Integer" mode="IN" name="id"/>
        <result-class>com.memorynotfound.hibernate.Book</result-class>
    </named-stored-procedure-query>
    <named-stored-procedure-query name="SP_AddBook" procedure-name="SP_ADD_BOOK">
        <parameter class="java.lang.Integer" mode="IN" name="id"/>
        <parameter class="java.lang.String" mode="IN" name="title"/>
    </named-stored-procedure-query>

    <!-- entity mapping -->
    <entity class="com.memorynotfound.hibernate.Book">
        <attributes>
            <basic name="title"/>
        </attributes>
    </entity>

</entity-mappings>

Hibernate/JPA Configuration

We configure the JPA Persistence Unit using the persistence.xml file, which is located in the src/main/resources/META-INF directory.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
                                 http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="mnf-pu" transaction-type="RESOURCE_LOCAL">
        <!-- enable if you want xml mappings
        <mapping-file>META-INF/orm.xml</mapping-file>
        -->
        <properties>
            <!-- Configuring JDBC properties -->
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/memorynotfound?serverTimezone=Europe/Brussels"/>
            <property name="javax.persistence.jdbc.user" value="root"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>

            <!-- Hibernate properties -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.show_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

Create, Run and Test

package com.memorynotfound.hibernate;

import javax.persistence.*;
import java.util.List;

public class App {

    public static void main (String...args) throws InterruptedException {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("mnf-pu");
        EntityManager em = emf.createEntityManager();

        StoredProcedureQuery sp = em.createNamedStoredProcedureQuery("SP_AddBook")
                .setParameter("id", 1)
                .setParameter("title", "Hibernat/JPA Named Stored Procedure Example");
        sp.execute();

        Book result = (Book)em.createNamedStoredProcedureQuery("SP_GetBookById")
                .setParameter("id", 1)
                .getSingleResult();
        System.out.println(result);

        em.close();
        emf.close();
    }
}

...
Hibernate: {call SP_ADD_BOOK(?,?)}
Hibernate: {call SP_GET_BOOK_BY_ID(?)}
Book{id=1, title='Hibernat/JPA Named Stored Procedure Example'}
...

Download