You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Brian McCallister <br...@apache.org> on 2004/12/09 22:03:28 UTC
Named Queries, Stored Procedures, etc
Vadim Gritsenko and I have exchanged a couple emails on providing
mapping when *all* queries need to go through stored procedures. Some
good thoughts (and a patch from Vadim, in followup email) have come of
it.
-Brian
Part of thread below (more to follow)
On Dec 9, 2004, at 10:09 AM, Vadim Gritsenko wrote:
> Brian McCallister wrote:
>> Is QueryBySql at the PersistenceBroker level sufficient support?
>> Could switch on sql.toUpperCase().startsWith("CALL").
>
> So, after adding modifications to that class, you could run stored
> procedures using QueryFactory.newQuery(classToSearchFrom, String
> anSqlStatement)? Cool.
>
> It's a step forward, but I think this won't be enough: how OJB will
> traverse relations? Will have to come up with a way to tell OJB which
> stored procedure to use to select related objects..
I am becoming better at pl/sql than I want to be in the last few weeks,
myself =/
I think a more general approach would be named queries, as you could
have umpteen different stored procedure queries for a given thing...
Okay, I think the general solution to this might be a strong mapping
system for named queries. Consider having named queries with named
parameters on a class, which can be either sql, stored procedure, or
OQL (when we get a general OQL -> Criteria compiler, which is being
worked on), or single-string JDOQL (a JDO2 thing that I don't think has
been released to the public yet).
You could map a relation to a parameterized named procedure, something
like:
<query name="articles-in-group"
type="org.apache.ojb.broker.Article" >
<parameter name="groupId" type="java.lang.Long" />
<procedure>CALL QUERIES.ARTICLES_IN_GROUP(:groupId)</procedure>
</query>
<!-- non-procedure named queries --
<query name="articles-in-group-sql"
type="org.apache.ojb.broker.Article" >
<parameter name="groupId" type="java.lang.Long" />
<sql>select article_id, name, group_id from articles where
group_id = (:groupId)</sql>
</query>
<query name="articles-in-group-oql"
type="org.apache.ojb.broker.Article" >
<parameter name="group" type="org.apache.ojb.broker.Group" />
<oql>select a from Articles a from where a.group = :group</oql>
</query>
<query name="articles-in-group-jdoql"
type="org.apache.ojb.broker.Article" >
<parameter name="group" type="org.apache.ojb.broker.Group" />
<jdoql>select Articles where group = :group</jdoql>
</query>
<!-- collection mapping by referencing named query -->
<collection-descriptor
name="allArticlesInGroup"
element-class-ref="org.apache.ojb.broker.Article" >
<fetch-by-query>
<named-query query="articles-in-group">
<param name="groupId">${this.groupId}</param>
</named-query>
</collection-descriptor>
<!-- or inline the query --->
<collection-descriptor
name="allArticlesInGroup"
element-class-ref="org.apache.ojb.broker.Article" >
<query name="articles-in-group"
type="org.apache.ojb.broker.Article" >
<parameter name="groupId" type="java.lang.Long" />
<procedure>CALL
QUERIES.ARTICLES_IN_GROUP(${this.groupId})</procedure>
</query>
</collection-descriptor>
Thoughts?
This would be hefty to implement, but is certainly feasible. Mind if I
CC ojb-dev on this thread?
-Brian
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Named Queries, Stored Procedures, etc
Posted by Brian McCallister <br...@chariotsolutions.com>.
Vadim had forwarded me this (edited to remove thread) as well (Vadim's
patch):
-Brian
Vadim Gritsenko wrote:
<snip />
Ok, attached is a hack to make queries work with stored procedures and
Oracle. There is one Oracle specific thing in there (sql type -10)
which I don't know how should look like in general case - any ideas?
<snip />
Query calling stored function looks like:
QueryFactory.newQuery(My.class, "{?=call my_function('param')}");
<snip />
Index: src/java/org/apache/ojb/broker/accesslayer/JdbcAccessImpl.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/accesslayer/
JdbcAccessImpl.java,v
retrieving revision 1.17.2.1
diff -u -u -r1.17.2.1 JdbcAccessImpl.java
--- src/java/org/apache/ojb/broker/accesslayer/JdbcAccessImpl.java 7
Jan 2004 13:02:04 -0000 1.17.2.1
+++ src/java/org/apache/ojb/broker/accesslayer/JdbcAccessImpl.java 9
Dec 2004 17:43:42 -0000
@@ -384,8 +346,20 @@
try
{
PreparedStatement stmt = stmtMan.getPreparedStatement(cld,
sqlStatement, scrollable);
- stmtMan.bindValues(stmt, values, 1);
- ResultSet rs = stmt.executeQuery();
+
+ ResultSet rs;
+ // FIXME: VG: If CALL, first argument is ResultSet, the
rest should be shifted
+ if (stmt instanceof CallableStatement) {
+ // oracle.jdbc.OracleTypes.CURSOR
+ ((CallableStatement)stmt).registerOutParameter(1, -10);
+ stmtMan.bindValues(stmt, values, 2);
+ stmt.execute();
+ rs = (ResultSet)
((CallableStatement)stmt).getObject(1);
+ } else {
+ stmtMan.bindValues(stmt, values, 1);
+ rs = stmt.executeQuery();
+ }
+
// as we return the resultset for further operations, we
cannot release the statement yet.
// that has to be done by the JdbcAccess-clients (i.e.
RsIterator, ProxyRsIterator and PkEnumeration.)
retval = new ResultSetAndStatement(
Index:
src/java/org/apache/ojb/broker/accesslayer/StatementsForClassImpl.java
===================================================================
RCS file:
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/accesslayer/
StatementsForClassImpl.java,v
retrieving revision 1.18
diff -u -u -r1.18 StatementsForClassImpl.java
---
src/java/org/apache/ojb/broker/accesslayer/StatementsForClassImpl.java
6 Oct 2003 07:24:02 -0000 1.18
+++
src/java/org/apache/ojb/broker/accesslayer/StatementsForClassImpl.java
9 Dec 2004 17:43:42 -0000
@@ -210,7 +164,8 @@
PreparedStatement stmt = null;
try
{
- stmt = prepareStatement(con, sql, scrollable);
+ // FIXME: VG: Hack to allow use of stored procedures in
QueryBySQL
+ stmt = prepareStatement(con, sql, scrollable,
!sql.toLowerCase().startsWith("{?=call"));
}
catch (java.sql.SQLException ex)
{
@@ -228,7 +183,7 @@
}
try
{
- return prepareStatement(con, selectByPKSql,
Query.NOT_SCROLLABLE);
+ return prepareStatement(con, selectByPKSql,
Query.NOT_SCROLLABLE, true);
}
catch (SQLException ex)
{
@@ -277,7 +232,7 @@
* @param scrollable determines if the statement will be
scrollable.
* @param createPreparedStatement if <code>true</code>, then a
* {@link PreparedStatement} will be created. if
<code>false</code>, then
- * a {@link CallableStatement} will be created.
+ * a {@link java.sql.CallableStatement} will be created.
*
* @return a statement that can be used to execute the syntax
contained in
* the <code>sql</code> argument.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org