[seam-dev] jdbc connection with seam-persistence

[George Gastaldi]

If it helps, I use these two methods with JPA to call Stored Procedures /
Functions:

    protected Object executeFunction(EntityManager entityManager, String
functionName, Object... params) {
        StringBuilder functionStr = new StringBuilder(functionName);
        functionStr.append("(");
        for (int i = 1; i <= params.length; i++) {
            functionStr.append("?").append(i);
            functionStr.append(",");
        }
        functionStr.deleteCharAt(functionStr.length() - 1);
        functionStr.append(")");
        Query nativeQuery = entityManager.createNativeQuery("SELECT " +
functionStr.toString() + " FROM DUAL");
        for (int i = 1; i <= params.length; i++) {
            nativeQuery.setParameter(i, params[i - 1]);
        }
        return nativeQuery.getSingleResult();
    }

    protected Object executeFunctionUpdate(DataSource dataSource, String
functionName, Object... params) {
        StringBuilder functionStr = new StringBuilder(functionName);
        functionStr.append("(");
        for (int i = 1; i <= params.length; i++) {
            functionStr.append("?,");
        }
        functionStr.deleteCharAt(functionStr.length() - 1);
        functionStr.append(")");
        String sqlString = "{? = call " + functionStr.toString() + "}";
        Connection con = null;
        CallableStatement call = null;
        try {
            con = dataSource.getConnection();
            call = con.prepareCall(sqlString);
            call.registerOutParameter(1, java.sql.Types.NUMERIC);
            for (int i = 1; i <= params.length; i++) {
                call.setObject(i + 1, params[i - 1]);
            }
            call.execute();
            return call.getObject(1);
        } catch (SQLException se) {
            throw new GvtIntegraException(se);
        } finally {
            DBUtil.closeQuietly(con, call, null);
        }
    }


2011/11/26 michal.kaplon at poczta.fm <michal.kaplon at poczta.fm>

> Hi,
>
> It smells like a challenge for cdi-query extension guys ;)
>
> --
> BR,
> Michal
>
> On 25.11.2011 21:32, John D. Ament wrote:
> > Here here.
> >
> > I know the biggest issue I always run into is stored procedure
> > invocation.  Something really easy to implement would be binding stored
> > procedures (not ones that return results, just do work) to interface
> methods
> >
> > public interface SomePackageNameOrDboOrSchemaName{
> >
> > @StoredProcedure("someProcedureName(?,?,?)")
> > public void someProcedureName(String s, int i, Long l);
> > }
> >
> > And do automatic binding of the method args to the procedure invocation.
> >
> > For result sets, I always find it easier to just bind it as a JPA query.
> >
> > On Fri, Nov 25, 2011 at 3:07 PM, José Rodolfo Freitas
> > <joserodolfo.freitas at gmail.com <mailto:joserodolfo.freitas at gmail.com>>
> > wrote:
> >
> >     Yeah, a JBDC module would be very useful. Eventually I cross with
> >     requirements that asks for JDBC connections. Essentially for
> >     perfomance with gigantic queries and reports.
> >
> >
> >     On Fri, Nov 25, 2011 at 3:39 PM, John D. Ament
> >     <john.d.ament at gmail.com <mailto:john.d.ament at gmail.com>> wrote:
> >
> >         Maybe we need a Seam JDBC module.
> >
> >
> >         On Fri, Nov 25, 2011 at 12:32 PM, Jason Porter
> >         <lightguard.jp at gmail.com <mailto:lightguard.jp at gmail.com>>
> wrote:
> >
> >             No it does not, JPA only.
> >
> >             Sent from my iPhone
> >
> >             On Nov 25, 2011, at 8:21, José Rodolfo Freitas
> >             <joserodolfo.freitas at gmail.com
> >             <mailto:joserodolfo.freitas at gmail.com>> wrote:
> >
> >>             afaik, it doesn't, but maybe a feature or two escaped from
> >>             my eyes.
> >>
> >>             On Fri, Nov 25, 2011 at 1:20 PM, José Rodolfo Freitas
> >>             <joserodolfo.freitas at gmail.com
> >>             <mailto:joserodolfo.freitas at gmail.com>> wrote:
> >>
> >>                 Hey guys, quick question:
> >>                 Does seam-persistence provide any mechanism that
> >>                 handles jdbc connections and sql native queries?
> >>
> >>
> >>
> >>
> >>             _______________________________________________
> >>             seam-dev mailing list
> >>             seam-dev at lists.jboss.org <mailto:seam-dev at lists.jboss.org>
> >>             https://lists.jboss.org/mailman/listinfo/seam-dev
> >
> >             _______________________________________________
> >             seam-dev mailing list
> >             seam-dev at lists.jboss.org <mailto:seam-dev at lists.jboss.org>
> >             https://lists.jboss.org/mailman/listinfo/seam-dev
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> > seam-dev mailing list
> > seam-dev at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/seam-dev
>
> _______________________________________________
> seam-dev mailing list
> seam-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/seam-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/seam-dev/attachments/20111128/ec16266d/attachment.html