Is it possible to map JPA entity to stored procedure (function)?

Question

I need to map JPA entity to Postgres function which returns table. So each time when I query for this entity, it will automatically invoke this function. Is it possible? Maybe @NamedNativeQuery can help?

Something like this maybe?

Named query in your object is only a shortcut who makes you write the query only once and use everytime you want without write it again — Frank, Jun 20 '17 at 10:16
An Entity is mapped to a table or view. That is all. The results of a FUNCTION can be mapped to a DTO just like any query can be — , Jun 20 '17 at 10:37
i think it would be enough to set the table name to the function, but i am not sure if it will then be called every time — XtremeBaumer, Jun 20 '17 at 11:57
@XtremeBaumer No, I believe we can on;y put there table/view/materialized view name. — Don_Quijote, Jun 20 '17 at 15:44

Michel Milezzi · Answer 1 · 2017-06-20T21:13:31.120

If your function don't have any arguments you can encapsulate it on a view:

CREATE VIEW my_entity_procedure AS
    SELECT 'Id:' || i AS some_field, i FROM generate_series(0, 100) i;

And then map it to a read-only entity (@Immutable).

I agree, that's weird and I don't recommend this approach. Remember that you can use a ResultTransformer to transform a raw ResultSet in a list of objects (DTO in this case). Something like below must works:

public List<MyFunctionDTO> getAllFromMyFunction() {
    StringBuilder sql = new StringBuilder("SELECT i FROM generate_series(0, 100) i");
    Query query = getHibernate().getSession().createSQLQuery(sql.toString()); 
    query.setResultTransformer(Transformers.aliasToBean(MyFunctionDTO.class));
    return query.list();
}

I already thought about view approach. But my function needs to accept arguments. — Don_Quijote, Jun 21 '17 at 07:21

score 1 · Answer 2 · answered Feb 16 '18 at 17:23

I don't know the limitations on Postgres but the general rule for mapping an Entity to an stored procedure using JPA is here:

@Entity
@NamedStoredProcedureQueries({
  @NamedStoredProcedureQuery(
    name = "myNamedProcedure", 
    procedureName = "SP_NAME", 
    resultClasses = { MyEntity.class }, 
    parameters = { 
        @StoredProcedureParameter(
          name = "parameter1", 
          type = Integer.class, 
          mode = ParameterMode.IN) ... }) 
})
public class MyEntity {...}

That's adding named stored procedure query, not mapping – Javasick May 15 '19 at 14:26 — Javasick, May 15 '19 at 14:26

score -1 · Answer 3 · answered Jun 20 '17 at 10:25

-1

You can use CallableStatement

con is your Connection object

 CallableStatement cs = con.prepareCall("{call your_procedure()}");

if you have parameters :

CallableStatement cs = con.prepareCall("{call your_procedure(?,?)}");

EXAMPLE

cs.setString(1,your_param);

if you wanna get result :

EXAMPLE

cs.registerOutParameter(2, Types.INTEGER);

answered Jun 20 '17 at 10:25

Frank

873
1
7
17

this has not much to do with the question asked, because its plain sql and neither hibernate nor jpa – XtremeBaumer Jun 20 '17 at 11:59

Is it possible to map JPA entity to stored procedure (function)?

3 Answers3