Calling a PostgreSQL Proc using EntityManager

Question

I'm trying to call a stored procedure on a PostgreSQL db. The procedure handles inserting, updating, and deleting table entries, so it accepts params. Some of the params are sometimes null, depending on the kind of operation you want to perform. I was able to call the proc using Hibernate's SessionFactory, but now I want to use the JPA EntityManager. The procedure using SessionFactory is as follows:

public Result callFunction(String type_proc, String node, String fullcode, Date bdate){
    Session session = this.sessionFactory.getCurrentSession();

    org.hibernate.Query query = session.createSQLQuery(
            "SELECT*FROM public.someFunction(:type_proc, :node, :fullcode, :bdate, CAST(:cat_id AS BIGINT)")
            .addEntity(Result.class)
            .setParameter("type_proc", type_proc)
            .setParameter("node", node)
            .setParameter("fullcode", fullcode)
            .setParameter("bdate", bdate)
            .setParameter("cat_id", null, LongType.INSTANCE)

    return (Result) query.uniqueResult();
}

As you can see, I specify the type of the field twice: one for Java, one for Postgres. Now if I do the same using the EntityManager, there is no setParameter method that accepts LongType.INSTANCE as an argument. Is there any workaround to this?

EDIT: Also looking for a way to do this using jdbcTemplate. Tried doing this way: https://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-simple-jdbc-call-1, but got a NullPointerException on the .execute(in) line.

HAYMbl4 · Accepted Answer · 2016-04-25 10:48:17Z

1

May be you should use EntityManager.createStoredProcedureQuery. In this way you can set type for parameters, for example:

    private final static String STORED_PROCEDURE = "public.someFunction";

    StoredProcedureQuery query = em.createStoredProcedureQuery(STORED_PROCEDURE);
    query.
            registerStoredProcedureParameter(1, String.class, ParameterMode.IN).setParameter(1, type_proc).
            registerStoredProcedureParameter(2, String.class, ParameterMode.IN).setParameter(2, node).
            registerStoredProcedureParameter(3, String.class, ParameterMode.IN).setParameter(3, fullcode).
            registerStoredProcedureParameter(4, Date.class, ParameterMode.IN).setParameter(4, bdate).
            registerStoredProcedureParameter(5, LongType.INSTANCE, ParameterMode.IN).setParameter(5, null).
            execute();

I used EntityManager.createStoredProcedureQuery for Oracle may be it's help you.

answered Apr 25, 2016 at 10:48

HAYMbl4

1,4802 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Burs Over a year ago

Thanks for the answer. I'll try it and get back to you.

Burs Over a year ago

Now the db is saying that I didn't pass a parameter to the function. I did pass it and it was null.

HAYMbl4 Over a year ago

Are you speak about 5 parameter? In my answer it is null.

Burs Over a year ago

Yes, I pass it, and the database doesn't recognize it as a passed parameter. Do you know of a way to cast the parameter before it's passed? Like I did with session factory: CAST(:cat_id AS BIGINT)

HAYMbl4 Over a year ago

Why are you cant cast param into java code? For example change LongType.INSTANCE to Integer.class or Number.class. If you know that you put integer param.

Collectives™ on Stack Overflow

Calling a PostgreSQL Proc using EntityManager

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related