eclipselink+postgresql+stored procedure

[zamek42]

Hi All,

I would like to use Postgresql stored procedures instead of a complicated sql subqueries.
I have a simple userpermissions(userId integer) stored procedure,
which is resultset of a userpermission table (and jpa object named Permissions).

How can I call it from jpa (eclipselink)?

I tried:
1. Query qry = em.createNativeQuery("select * from userpermissions(?)");
        qry.setParameter(1, usr.getId());
        return qry.getResultList();
it works well, but its results not a List of Permission, but a List of Object :(

2. @NamedStoredProcedureQueries (
    @NamedStoredProcedureQuery(name="userpermissions", procedureName="userpermissions",
            parameters=@StoredProcedureParameter(name="userId",queryParameter="userId",type=Integer.class,direction=Direction.IN),
            resultClass=Permissions.class, returnsResultSet=true)
)
...
       Query qry =em.createNamedQuery(EJBUtilConst.USER_PERMISSION_STORED_PROC);
        qry.setParameter("userId", usr.getId());
        return qry.getResultList();

it didn't works: "prepared statement "userpermissions" does not exist"

3. PLSQLStoredProcedureCall plsql = new PLSQLStoredProcedureCall();
        plsql.setProcedureName("userpermissions");
        plsql.addNamedArgument("userId", "userId", Integer.TYPE);
        ReadAllQuery q = new ReadAllQuery();
        q.setCall(plsql);
        q.addArgument("userId");
        List args = new ArrayList();
        args.add(usr.getId());
        q.addArgumentValues(args);
        Query query = JpaHelper.createQuery(q, em);
        return query.getResultList();
it didn't works:
"Query argument userId not found in the list of parameters provided during query execution."

And I don't have any more idea:(

thx a lot
Zamek

[James Sutherland]

Assuming your SQL is correct, and returns the correct data for building a Permission as it is mapped, then you can use,

em.createNativeQuery("select * from userpermissions(?)", Permission.class);

If you don't include the class, then the raw data is returned as an Object[], which you could still use to build your object.  You may also consider using a ResultSetMapping.

PostgreSQL technially does not support stored procedures, only stored functions, which are somewhat different, so you cannot use StoredProcedureCall.

zamek42 wrote:

Hi All,

I would like to use Postgresql stored procedures instead of a complicated sql subqueries.
I have a simple userpermissions(userId integer) stored procedure,
which is resultset of a userpermission table (and jpa object named Permissions).

How can I call it from jpa (eclipselink)?

I tried:
1. Query qry = em.createNativeQuery("select * from userpermissions(?)");
        qry.setParameter(1, usr.getId());
        return qry.getResultList();
it works well, but its results not a List of Permission, but a List of Object :(

2. @NamedStoredProcedureQueries (
    @NamedStoredProcedureQuery(name="userpermissions", procedureName="userpermissions",
            parameters=@StoredProcedureParameter(name="userId",queryParameter="userId",type=Integer.class,direction=Direction.IN),
            resultClass=Permissions.class, returnsResultSet=true)
)
...
       Query qry =em.createNamedQuery(EJBUtilConst.USER_PERMISSION_STORED_PROC);
        qry.setParameter("userId", usr.getId());
        return qry.getResultList();

it didn't works: "prepared statement "userpermissions" does not exist"

3. PLSQLStoredProcedureCall plsql = new PLSQLStoredProcedureCall();
        plsql.setProcedureName("userpermissions");
        plsql.addNamedArgument("userId", "userId", Integer.TYPE);
        ReadAllQuery q = new ReadAllQuery();
        q.setCall(plsql);
        q.addArgument("userId");
        List args = new ArrayList();
        args.add(usr.getId());
        q.addArgumentValues(args);
        Query query = JpaHelper.createQuery(q, em);
        return query.getResultList();
it didn't works:
"Query argument userId not found in the list of parameters provided during query execution."

And I don't have any more idea:(

thx a lot
Zamek

[zamek42]

thanks for your reply.

I tried it, but it doesn't works:

Query qry = em.createNativeQuery("select p_id,p_name,p_mask from userpermissions(?)",Permissions.class);
        qry.setParameter(1, usr.getId());
        List result = qry.getResultList();
        return result;

and I get an error:
Exception Description: The primary key read from the row [DatabaseRecord(
         => 100
         => teachers
         => 15)] during the execution of the query was detected to be null.  Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=Permissions sql="select p_id,p_name,p_mask from userpermissions(?)")

Its very interest, because id is 100! Why it says primary key must not contain null ?

Permission(table and JPA entity) has 3 fields: id:Integer,name:String,mask:Integer.

the results of userpermissions stored function is resultset%permissions which is same as permissions table.

James Sutherland wrote:

Assuming your SQL is correct, and returns the correct data for building a Permission as it is mapped, then you can use,

em.createNativeQuery("select * from userpermissions(?)", Permission.class);

If you don't include the class, then the raw data is returned as an Object[], which you could still use to build your object.  You may also consider using a ResultSetMapping.

PostgreSQL technially does not support stored procedures, only stored functions, which are somewhat different, so you cannot use StoredProcedureCall.

[zamek42]

Hi All,

Only one version is working:

@NamedNativeQuery(name = "userpermissions",
                  query = "SELECT p_id, p_name, p_mask FROM userpermissions(?1)",
                  resultSetMapping = "uperm")
@SqlResultSetMapping(name = "uperm",
        entities = {@EntityResult(entityClass = Permissions.class,
              fields = {@FieldResult(name = "id", column = "p_id"),
                        @FieldResult(name = "name", column = "p_name"),
                        @FieldResult(name = "mask", column = "p_mask")
                       })
        })
and this caling method:

        Query qry =em.createNamedQuery("userpermissions");
        qry.setParameter(1, usr.getId());
        List res = qry.getResultList();
        return res;