How to properly call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA?

Question

I'm using Spring MVC 4, Hibernate and PostgreSQL 9.3 and have defined function (stored procedure) inside Postgres like this:

CREATE OR REPLACE FUNCTION spa.create_tenant(t_name character varying)
  RETURNS void AS
  $BODY$
    BEGIN
      EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I AUTHORIZATION postgres', t_name);
    END
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION spa.create_tenant(character varying)
OWNER TO postgres;

If I run this function inside pgAdmin like this it's working fine:

select spa.create_tenant('somename');

Now I'm trying to run this function from my service like this:

@Override
@Transactional
public void createSchema(String name) {
    StoredProcedureQuery sp = em.createStoredProcedureQuery("spa.create_tenant");
    sp.registerStoredProcedureParameter("t_name", String.class, ParameterMode.IN);
    sp.setParameter("t_name", name);
    sp.execute();
}

If I run my method I'm getting following error:

javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

I'm guessing this is because of return type void that is defined in function so I changed return type to look like this:

RETURNS character varying AS

If I run my method again I'm getting this exception instead:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults

Does anyone know what is going on here and how to properly call stored procedures in PostgreSQL even with void as return type?

Answer 1

In case you are using also spring data, you could just define a procedure inside your @Repository interface like this,

@Procedure(value = "spa.create_tenant")
public void createTenantOrSomething(@Param("t_name") String tNameOrSomething);

More in the docs.

Answer 2

In your entity class, define a NamedNativeQuery like you would call postgresql function with select.

import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Entity;
@NamedNativeQueries(
    value={
            // cast is used for Hibernate, to prevent No Dialect mapping for JDBC type: 1111
            @NamedNativeQuery(
                  name = "Tenant.createTenant",
                 query = "select cast(create_tenant(?) as text)"
            )
     }
)
@Entity
public class Tenant

hibernate is not able to map void, so a workaround is to cast result as text

public void createSchema(String name) {
    Query query = em.createNamedQuery("Tenant.createTenant")
            .setParameter(1, name);
    query.getSingleResult();
}

Answer 3

Since you're using PostgreSQL, you can, as you've already written, call any stored procedure of type function in SELECT (Oracle, otherwise, would let you only execute functions declared to be read only in selects).

You can use EntityManager.createNativeQuery(SQL).

Since you're using Spring, you can use SimpleJdbcTemplate.query(SQL) to execute any SQL statement, as well.