You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by No...@bmi.gv.at on 2007/03/23 16:55:56 UTC
ReportQueryBySQL
Hello,
we use OJB as persistence layer. Due to project specific needs, we've built another layer on top of OJB and we can/may not use jdbc at all anymore.
The problem now is that we need queries that cannot be built with Query/Criteria objects because there is no proper mapping that could handle these scenarios.
Is there a way to execute any kind of select statements without specifying a mapping and a target persistence class? I'd like to execute a select statement and get the result in the way as for ReportQueries.
Eg:
Query query = new QueryBySQL(null, selectString);
persister.getReportQueryIteratorBySQLQuery(query); // not the 'SQL'
The thing is that we cannot create mapping for any of these very specific queries, nor we can use jdbc.
Any chance to get this done with OJB?
Many thanks!
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
AW: ReportQueryBySQL
Posted by No...@bmi.gv.at.
Hi Armin,
many thanks for the patch, it works fine! :)
-----Ursprüngliche Nachricht-----
Von: Armin Waibel [mailto:arminw@apache.org]
Gesendet: Samstag, 24. März 2007 00:19
An: OJB Users List
Betreff: Re: ReportQueryBySQL
Hi Norbert,
Norbert.Woegerbauer@bmi.gv.at wrote:
> Hello, we use OJB as persistence layer. Due to project specific
> needs, we've built another layer on top of OJB and we can/may not use
> jdbc at all anymore. The problem now is that we need queries that
> cannot be built with Query/Criteria objects because there is no
> proper mapping that could handle these scenarios. Is there a way to
> execute any kind of select statements without specifying a mapping
> and a target persistence class? I'd like to execute a select
> statement and get the result in the way as for ReportQueries. Eg:
> Query query = new QueryBySQL(null, selectString);
> persister.getReportQueryIteratorBySQLQuery(query); // note the 'SQL'
>
> The thing is that we cannot create mapping for any of these very
> specific queries, nor we can use jdbc.
>
> Any chance to get this done with OJB? Many thanks!
Sorry, currently this is not possible.
I setup a test to reproduce your issue. After some time and a hack (add
7 lines of code in 3 classes) I was able to execute arbitrary
sql-statements using a report query (based on latest version from SVN
OJB_1_0_RELEASE branch):
TEST
----
String sql = "select * from Artikel as A1 where A1.Artikelname =
'testReportQueryBySql_1174691153125'";
Query query = QueryFactory.newQuery(null, sql);
Iterator it = broker.getReportQueryIteratorByQuery(query);
while(it.hasNext())
{
Object o = it.next();
System.out.println("result: " + ArrayUtils.toString(o));
}
OUTPUT
------
result:
{200017,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
result:
{200018,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
OJB use the ResultSetMetadata of the ResultSet to determine the correct
java field type. So this solution requires a proper jdbc-driver
implementation.
Would this hack satisfy you?
regards,
Armin
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org For
> additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: AW: ReportQueryBySQL
Posted by Armin Waibel <ar...@apache.org>.
Hi Norbert,
Norbert.Woegerbauer@bmi.gv.at wrote:
> Hi Armin, many thanks for your investigation! Yes, that hack would
> satisfy our needs unless it has side-effects (we use
> getReportQueryIteratorByQuery also in it's usual way).
I don't expect side-effects. The OJB test-suite pass without failures
after changing these classes.
> The only thing
> is that we use OJB 1.0.1 (which we have patched for some issues), but
> I assume I'll manage to integrate your code.
>
I don't know if my changes are compatible with 1.0.1, but it should be
possible to adapt the 1.0.1 classes too.
> I'd be very happy if you can send me the modified classes.
The modified classes are:
db/ojb/branches/OJB_1_0_RELEASE/src/java/org/apache/ojb/broker/accesslayer/RsIterator.java
db/ojb/branches/OJB_1_0_RELEASE/src/java/org/apache/ojb/broker/accesslayer/RsQueryObject.java
db/ojb/branches/OJB_1_0_RELEASE/src/java/org/apache/ojb/broker/core/PersistenceBrokerImpl.java
additional: add a convenience method (not mandatory)
db/ojb/branches/OJB_1_0_RELEASE/src/java/org/apache/ojb/broker/query/QueryFactory.java
Here you can find the diff of my hack
http://www.mail-archive.com/ojb-dev%40db.apache.org/msg03625.html
You can get the classes from OJB SVN (check out
http://svn.apache.org/repos/asf/db/ojb/branches/OJB_1_0_RELEASE with a
SVN client) or online with
http://svn.apache.org/viewvc/db/ojb/branches/OJB_1_0_RELEASE/src/java/org/apache/ojb/broker/
regards,
Armin
>
> Best regards, Norbert.
>
>
> -----Ursprüngliche Nachricht----- Von: Armin Waibel
> [mailto:arminw@apache.org] Gesendet: Samstag, 24. März 2007 00:19 An:
> OJB Users List Betreff: Re: ReportQueryBySQL
>
> Hi Norbert,
>
> Norbert.Woegerbauer@bmi.gv.at wrote:
>> Hello, we use OJB as persistence layer. Due to project specific
>> needs, we've built another layer on top of OJB and we can/may not
>> use jdbc at all anymore. The problem now is that we need queries
>> that cannot be built with Query/Criteria objects because there is
>> no proper mapping that could handle these scenarios. Is there a way
>> to execute any kind of select statements without specifying a
>> mapping and a target persistence class? I'd like to execute a
>> select statement and get the result in the way as for
>> ReportQueries. Eg: Query query = new QueryBySQL(null,
>> selectString); persister.getReportQueryIteratorBySQLQuery(query);
>> // not the 'SQL'
>>
>> The thing is that we cannot create mapping for any of these very
>> specific queries, nor we can use jdbc.
>>
>> Any chance to get this done with OJB? Many thanks!
>
> Sorry, currently this is not possible.
>
> I setup a test to reproduce your issue. After some time and a hack
> (add 7 lines of code in 3 classes) I was able to execute arbitrary
> sql-statements using a report query (based on latest version from SVN
> OJB_1_0_RELEASE branch):
>
> TEST ----
>
> String sql = "select * from Artikel as A1 where A1.Artikelname =
> 'testReportQueryBySql_1174691153125'";
>
> Query query = QueryFactory.newQuery(null, sql); Iterator it =
> broker.getReportQueryIteratorByQuery(query); while(it.hasNext()) {
> Object o = it.next(); System.out.println("result: " +
> ArrayUtils.toString(o)); }
>
> OUTPUT ------
>
> result:
> {200017,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
> result:
> {200018,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
>
>
> OJB use the ResultSetMetadata of the ResultSet to determine the
> correct java field type. So this solution requires a proper
> jdbc-driver implementation.
>
> Would this hack satisfy you?
>
> regards, Armin
>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org For
>> additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org For
> additional commands, e-mail: ojb-user-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org For
> additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
AW: ReportQueryBySQL
Posted by No...@bmi.gv.at.
Hi Armin,
many thanks for your investigation!
Yes, that hack would satisfy our needs unless it has side-effects (we use getReportQueryIteratorByQuery also in it's usual way).
The only thing is that we use OJB 1.0.1 (which we have patched for some issues), but I assume I'll manage to integrate your code.
I'd be very happy if you can send me the modified classes.
Best regards,
Norbert.
-----Ursprüngliche Nachricht-----
Von: Armin Waibel [mailto:arminw@apache.org]
Gesendet: Samstag, 24. März 2007 00:19
An: OJB Users List
Betreff: Re: ReportQueryBySQL
Hi Norbert,
Norbert.Woegerbauer@bmi.gv.at wrote:
> Hello, we use OJB as persistence layer. Due to project specific
> needs, we've built another layer on top of OJB and we can/may not use
> jdbc at all anymore. The problem now is that we need queries that
> cannot be built with Query/Criteria objects because there is no
> proper mapping that could handle these scenarios. Is there a way to
> execute any kind of select statements without specifying a mapping
> and a target persistence class? I'd like to execute a select
> statement and get the result in the way as for ReportQueries. Eg:
> Query query = new QueryBySQL(null, selectString);
> persister.getReportQueryIteratorBySQLQuery(query); // not the 'SQL'
>
> The thing is that we cannot create mapping for any of these very
> specific queries, nor we can use jdbc.
>
> Any chance to get this done with OJB? Many thanks!
Sorry, currently this is not possible.
I setup a test to reproduce your issue. After some time and a hack (add
7 lines of code in 3 classes) I was able to execute arbitrary
sql-statements using a report query (based on latest version from SVN
OJB_1_0_RELEASE branch):
TEST
----
String sql = "select * from Artikel as A1 where A1.Artikelname =
'testReportQueryBySql_1174691153125'";
Query query = QueryFactory.newQuery(null, sql);
Iterator it = broker.getReportQueryIteratorByQuery(query);
while(it.hasNext())
{
Object o = it.next();
System.out.println("result: " + ArrayUtils.toString(o));
}
OUTPUT
------
result:
{200017,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
result:
{200018,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
OJB use the ResultSetMetadata of the ResultSet to determine the correct
java field type. So this solution requires a proper jdbc-driver
implementation.
Would this hack satisfy you?
regards,
Armin
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org For
> additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: ReportQueryBySQL
Posted by Armin Waibel <ar...@apache.org>.
Hi Norbert,
Norbert.Woegerbauer@bmi.gv.at wrote:
> Hello, we use OJB as persistence layer. Due to project specific
> needs, we've built another layer on top of OJB and we can/may not use
> jdbc at all anymore. The problem now is that we need queries that
> cannot be built with Query/Criteria objects because there is no
> proper mapping that could handle these scenarios. Is there a way to
> execute any kind of select statements without specifying a mapping
> and a target persistence class? I'd like to execute a select
> statement and get the result in the way as for ReportQueries. Eg:
> Query query = new QueryBySQL(null, selectString);
> persister.getReportQueryIteratorBySQLQuery(query); // not the 'SQL'
>
> The thing is that we cannot create mapping for any of these very
> specific queries, nor we can use jdbc.
>
> Any chance to get this done with OJB? Many thanks!
Sorry, currently this is not possible.
I setup a test to reproduce your issue. After some time and a hack (add
7 lines of code in 3 classes) I was able to execute arbitrary
sql-statements using a report query (based on latest version from SVN
OJB_1_0_RELEASE branch):
TEST
----
String sql = "select * from Artikel as A1 where A1.Artikelname =
'testReportQueryBySql_1174691153125'";
Query query = QueryFactory.newQuery(null, sql);
Iterator it = broker.getReportQueryIteratorByQuery(query);
while(it.hasNext())
{
Object o = it.next();
System.out.println("result: " + ArrayUtils.toString(o));
}
OUTPUT
------
result:
{200017,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
result:
{200018,testReportQueryBySql_1174691153125,0,<null>,<null>,0.0,0,0,0,0}
OJB use the ResultSetMetadata of the ResultSet to determine the correct
java field type. So this solution requires a proper jdbc-driver
implementation.
Would this hack satisfy you?
regards,
Armin
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org For
> additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org