Using JPA to call a stored procedure and map the result to an intern domain model.

CallEuropeanHOS.java

package de.luiswolff.test;

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

/**
 * This program uses the MySQL sample database "world" and a stored procedure from the MySQL-Tutorial
 * <a href="https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html">
 *     Connector/Net 4.1.5 Working with Stored Procedures</a>.
 */
public class CallEuropeanHOS {

    public static void main(String[] args){
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("test-jpa");
        EntityManager em = emf.createEntityManager();
        EntityTransaction et = em.getTransaction();
        try {
            et.begin();
            List results = em.createStoredProcedureQuery("country_hos", CountryHos.class)
                    .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
                    .setParameter(1, "Europe")
                    .getResultList();
            for (Object o : results){
                if (o instanceof CountryHos){
                    CountryHos countryHos = (CountryHos) o;
                    System.out.println("Country name: " + countryHos.getName() + " --> Head of state: " + countryHos.getHeadOfState());
                } else {
                    System.err.println("received unknown type " + o.getClass().getName());
                }
            }
            et.commit();
        } catch (Exception e){
            e.printStackTrace();
            et.rollback();
        }
        em.close();
        emf.close();
    }

    @Entity
    public static class CountryHos {


        private String name;

        private String headOfState;

        @Id
        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        @Basic
        public String getHeadOfState() {
            return headOfState;
        }

        public void setHeadOfState(String headOfState) {
            this.headOfState = headOfState;
        }
    }

}

persistence.xml

<?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="test-jpa" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/tutorial?noAccessToProcedureBodies=true"/>
            <property name="javax.persistence.jdbc.user" value="root"/>
            <property name="javax.persistence.jdbc.password" value="root"/>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
        </properties>
    </persistence-unit>
</persistence>