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?

How would you call a JDBC CallableStatement? That's all the JPA API wraps around. If Hibernate doesn't mirror what JDBC gives then try a different JPA provider to see how that handles it — , Sep 30 '14 at 18:16

score 7 · Answer 1 · answered Aug 16 '18 at 12:50

7

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.

answered Aug 16 '18 at 12:50

Georgios Syngouroglou

18,813
9
90
92

srex · Answer 2 · 2015-07-30T09:22:25.857

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();
}

Sorry @AlexG for late response, I have edited Entity code suggestion, but I hope you already found the solution — srex, Jul 30 '15 at 09:25

score 1 · Answer 3 · answered Oct 01 '14 at 06:59

1

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.

answered Oct 01 '14 at 06:59

Danubian Sailor

1
38
145
223

How to call the function and get the result at controller? See http://stackoverflow.com/q/41864288/287948 – Peter Krauss Jan 26 '17 at 19:01

Vlad Mihalcea · Answer 4 · 2021-01-11T19:20:06.630

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();

How to call the function and get the result at controller? See http://stackoverflow.com/q/41864288/287948 — Peter Krauss, Jan 26 '17 at 19:01

score 0 · Answer 5 · answered Mar 30 '19 at 00:50

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.

score 0 · Answer 6 · answered Dec 27 '20 at 04:33

0

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")
}

answered Dec 27 '20 at 04:33

Braian Coronel

22,105
4
57
62

How about JPA please – TheRealChx101 Mar 05 '22 at 22:43

score 0 · Answer 7 · edited Mar 23 '22 at 08:31

0

For a procedure, try this:

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

edited Mar 23 '22 at 08:31

Procrastinator

2,526
30
27
36

answered Mar 17 '22 at 00:40

Deyvson Santos

11
2
4

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

7 Answers7

Linked