[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
Re: [eclipselink-users] eclipselink+postgresql+stored procedure
|
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
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
--
View this message in context: http://www.nabble.com/eclipselink%2Bpostgresql%2Bstored-procedure-tp24719825p24726483.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.