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

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?

springhibernatepostgresqljpa


Solution 1:[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.

Solution 2:[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();
}

Solution 3:[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.

Solution 4:[4]

I think it's the RETURN VOID that's causing the issue. So, changed the FUNCTION definition like this:

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

After you changed your function to return some dummy value, change the stored procedure query to this:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("spa.create_tenant")
    .registerStoredProcedureParameter(1, 
        Long.class, ParameterMode.OUT)
    .registerStoredProcedureParameter(2, 
        String.class, ParameterMode.IN)
    .setParameter(2, name);
 
query.getResultList();

Solution 5:[5]

If you want to keep it simple, just do this:

    em.createSQLQuery("SELECT * FROM spa.create_tenant(:t_name) ")
                          .setParameter("t_name", name)").list();

Notice I used list() intentionally.. for some reason .update() didn't work for me.

Solution 6:[6]

  • PostgreSQL
  • Hibernate
  • Kotlin
CREATE OR REPLACE FUNCTION your_procedure() RETURNS text AS $$
BEGIN
    RETURN 'Some text';
END;
$$ LANGUAGE plpgsql;

val query = session.createNativeQuery("SELECT your_procedure()")
query.list().map {
    println("NativeQuery: $it")
}

Solution 7:[7]

For a procedure, try this:

@Procedure("spa.create_tenant")
String createTenant(String tenant);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Georgios Syngouroglou
Solution 2
Solution 3 Danubian Sailor
Solution 4
Solution 5 Roberto Rodriguez
Solution 6 Braian Coronel
Solution 7 procrastinator

Related Questions