If it helps, I use these two methods with JPA to call Stored Procedures / Functions: <div><br></div><div><div> protected Object executeFunction(EntityManager entityManager, String functionName, Object... params) {</div>
<div> StringBuilder functionStr = new StringBuilder(functionName);</div><div> functionStr.append("(");</div><div> for (int i = 1; i <= params.length; i++) {</div><div> functionStr.append("?").append(i);</div>
<div> functionStr.append(",");</div><div> }</div><div> functionStr.deleteCharAt(functionStr.length() - 1);</div><div> functionStr.append(")");</div><div> Query nativeQuery = entityManager.createNativeQuery("SELECT " + functionStr.toString() + " FROM DUAL");</div>
<div> for (int i = 1; i <= params.length; i++) {</div><div> nativeQuery.setParameter(i, params[i - 1]);</div><div> }</div><div> return nativeQuery.getSingleResult();</div><div> }</div>
<div><br></div><div> protected Object executeFunctionUpdate(DataSource dataSource, String functionName, Object... params) {</div><div> StringBuilder functionStr = new StringBuilder(functionName);</div><div> functionStr.append("(");</div>
<div> for (int i = 1; i <= params.length; i++) {</div><div> functionStr.append("?,");</div><div> }</div><div> functionStr.deleteCharAt(functionStr.length() - 1);</div><div> functionStr.append(")");</div>
<div> String sqlString = "{? = call " + functionStr.toString() + "}";</div><div> Connection con = null;</div><div> CallableStatement call = null;</div><div> try {</div><div>
con = dataSource.getConnection();</div><div> call = con.prepareCall(sqlString);</div><div> call.registerOutParameter(1, java.sql.Types.NUMERIC);</div><div> for (int i = 1; i <= params.length; i++) {</div>
<div> call.setObject(i + 1, params[i - 1]);</div><div> }</div><div> call.execute();</div><div> return call.getObject(1);</div><div> } catch (SQLException se) {</div>
<div>
throw new GvtIntegraException(se);</div><div> } finally {</div><div> DBUtil.closeQuietly(con, call, null);</div><div> }</div><div> }</div><div><br></div><br><div class="gmail_quote">
2011/11/26 <a href="mailto:michal.kaplon@poczta.fm">michal.kaplon@poczta.fm</a> <span dir="ltr"><<a href="mailto:michal.kaplon@poczta.fm">michal.kaplon@poczta.fm</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Hi,<br>
<br>
It smells like a challenge for cdi-query extension guys ;)<br>
<br>
--<br>
BR,<br>
Michal<br>
<div class="im"><br>
On 25.11.2011 21:32, John D. Ament wrote:<br>
> Here here.<br>
><br>
> I know the biggest issue I always run into is stored procedure<br>
> invocation. Something really easy to implement would be binding stored<br>
> procedures (not ones that return results, just do work) to interface methods<br>
><br>
> public interface SomePackageNameOrDboOrSchemaName{<br>
><br>
> @StoredProcedure("someProcedureName(?,?,?)")<br>
> public void someProcedureName(String s, int i, Long l);<br>
> }<br>
><br>
> And do automatic binding of the method args to the procedure invocation.<br>
><br>
> For result sets, I always find it easier to just bind it as a JPA query.<br>
><br>
> On Fri, Nov 25, 2011 at 3:07 PM, José Rodolfo Freitas<br>
</div>> <<a href="mailto:joserodolfo.freitas@gmail.com">joserodolfo.freitas@gmail.com</a> <mailto:<a href="mailto:joserodolfo.freitas@gmail.com">joserodolfo.freitas@gmail.com</a>>><br>
<div class="im">> wrote:<br>
><br>
> Yeah, a JBDC module would be very useful. Eventually I cross with<br>
> requirements that asks for JDBC connections. Essentially for<br>
> perfomance with gigantic queries and reports.<br>
><br>
><br>
> On Fri, Nov 25, 2011 at 3:39 PM, John D. Ament<br>
</div><div class="im">> <<a href="mailto:john.d.ament@gmail.com">john.d.ament@gmail.com</a> <mailto:<a href="mailto:john.d.ament@gmail.com">john.d.ament@gmail.com</a>>> wrote:<br>
><br>
> Maybe we need a Seam JDBC module.<br>
><br>
><br>
> On Fri, Nov 25, 2011 at 12:32 PM, Jason Porter<br>
</div><div class="im">> <<a href="mailto:lightguard.jp@gmail.com">lightguard.jp@gmail.com</a> <mailto:<a href="mailto:lightguard.jp@gmail.com">lightguard.jp@gmail.com</a>>> wrote:<br>
><br>
> No it does not, JPA only.<br>
><br>
> Sent from my iPhone<br>
><br>
> On Nov 25, 2011, at 8:21, José Rodolfo Freitas<br>
> <<a href="mailto:joserodolfo.freitas@gmail.com">joserodolfo.freitas@gmail.com</a><br>
</div><div class="im">> <mailto:<a href="mailto:joserodolfo.freitas@gmail.com">joserodolfo.freitas@gmail.com</a>>> wrote:<br>
><br>
>> afaik, it doesn't, but maybe a feature or two escaped from<br>
>> my eyes.<br>
>><br>
>> On Fri, Nov 25, 2011 at 1:20 PM, José Rodolfo Freitas<br>
>> <<a href="mailto:joserodolfo.freitas@gmail.com">joserodolfo.freitas@gmail.com</a><br>
</div><div class="im">>> <mailto:<a href="mailto:joserodolfo.freitas@gmail.com">joserodolfo.freitas@gmail.com</a>>> wrote:<br>
>><br>
>> Hey guys, quick question:<br>
>> Does seam-persistence provide any mechanism that<br>
>> handles jdbc connections and sql native queries?<br>
>><br>
>><br>
>><br>
>><br>
>> _______________________________________________<br>
>> seam-dev mailing list<br>
</div>>> <a href="mailto:seam-dev@lists.jboss.org">seam-dev@lists.jboss.org</a> <mailto:<a href="mailto:seam-dev@lists.jboss.org">seam-dev@lists.jboss.org</a>><br>
<div class="im">>> <a href="https://lists.jboss.org/mailman/listinfo/seam-dev" target="_blank">https://lists.jboss.org/mailman/listinfo/seam-dev</a><br>
><br>
> _______________________________________________<br>
> seam-dev mailing list<br>
</div>> <a href="mailto:seam-dev@lists.jboss.org">seam-dev@lists.jboss.org</a> <mailto:<a href="mailto:seam-dev@lists.jboss.org">seam-dev@lists.jboss.org</a>><br>
<div><div></div><div class="h5">> <a href="https://lists.jboss.org/mailman/listinfo/seam-dev" target="_blank">https://lists.jboss.org/mailman/listinfo/seam-dev</a><br>
><br>
><br>
><br>
><br>
><br>
><br>
> _______________________________________________<br>
> seam-dev mailing list<br>
> <a href="mailto:seam-dev@lists.jboss.org">seam-dev@lists.jboss.org</a><br>
> <a href="https://lists.jboss.org/mailman/listinfo/seam-dev" target="_blank">https://lists.jboss.org/mailman/listinfo/seam-dev</a><br>
<br>
_______________________________________________<br>
seam-dev mailing list<br>
<a href="mailto:seam-dev@lists.jboss.org">seam-dev@lists.jboss.org</a><br>
<a href="https://lists.jboss.org/mailman/listinfo/seam-dev" target="_blank">https://lists.jboss.org/mailman/listinfo/seam-dev</a><br>
</div></div></blockquote></div><br></div>