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 LE-QUERE Gildas - REN <gi...@atosorigin.com> on 2004/10/25 10:06:08 UTC
collection loading and filtering
Hi all,
I have un object A referencing à collection with B elements.
A has an attribute 'name' and a relation 'elements', B has an attribute
'filter'.
I want to load elements where name='xxx' and filter='yyy'.
I use the PersitenceBroker API, here is my request:
criteria = new Criteria();
criteria.addEqualTo("name", 'xxx');
criteria.addEqualTo("elements.filter", 'yyy');
query = QueryFactory.newQuery(A.class, criteria);
A a = (A)broker.getObjectByQuery(query);
With the relationship auto-retrieve=true all elements are loaded!
If auto-retrieve=false the relationship is not loaded.
Is there a solution ?
Thanks
Gildas
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Paged query result
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi muliawan,
yes i know its faster (an i already had a primitive working version). the
Platform interface defines some methods to support sql-paging, but it's not yet
implemented.
i think the strange behaviour is caused by some kind of 'double paging' because
the PagingIterator is still active.
jakob
[ Muliawan Sjarif ] schrieb:
> Hi,
>
> Has anyone realized that doing paged query (limit query like in MySQL)
> directly from SQL statement is faster than using setStartAtIndex &
> setEndAtIndex? I did a little experiment on this -- on Oracle to be
> specific. Now I encounter a weird problem when I apply the SQL
> statement on PersistenceBroker.
>
> What I did was only override getPreparedSelectStatement method of
> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl and
> added the following:
>
> -- snipped existing code --
>
> if (query.getEndAtIndex() > 0 && query.getStartAtIndex() >= 0) {
> result = "select b.* from ( select a.*, rownum as myrownum from (" +
> result +
> ") a where rownum <= " +
> query.getEndAtIndex() +
> " ) b where b.myrownum > " +
> query.getStartAtIndex();
> }
>
> -- snipped existing code --
>
> The problem I'm having is that on the first page to display the first
> 10 records I get the correct result. But, on the subsequent page I get
> only 1, 0, 0 and 0 record till the last page as in fact there are 156
> records. Am I missing something here?
> Thanks in advance for any input.
>
> FYI, I got the above SQL statement from the Torque team mailing list.
> That's the latest and enhanced SQL statement for paged query for
> Oracle database.
>
> Regards,
> Muliawan
>
> ---------------------------------------------------------------------
> 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
Paged query result
Posted by "[ Muliawan Sjarif ]" <m4...@gmail.com>.
Hi,
Has anyone realized that doing paged query (limit query like in MySQL)
directly from SQL statement is faster than using setStartAtIndex &
setEndAtIndex? I did a little experiment on this -- on Oracle to be
specific. Now I encounter a weird problem when I apply the SQL
statement on PersistenceBroker.
What I did was only override getPreparedSelectStatement method of
org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl and
added the following:
-- snipped existing code --
if (query.getEndAtIndex() > 0 && query.getStartAtIndex() >= 0) {
result = "select b.* from ( select a.*, rownum as myrownum from (" +
result +
") a where rownum <= " +
query.getEndAtIndex() +
" ) b where b.myrownum > " +
query.getStartAtIndex();
}
-- snipped existing code --
The problem I'm having is that on the first page to display the first
10 records I get the correct result. But, on the subsequent page I get
only 1, 0, 0 and 0 record till the last page as in fact there are 156
records. Am I missing something here?
Thanks in advance for any input.
FYI, I got the above SQL statement from the Torque team mailing list.
That's the latest and enhanced SQL statement for paged query for
Oracle database.
Regards,
Muliawan
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: collection loading and filtering
Posted by LE-QUERE Gildas - REN <gi...@atosorigin.com>.
Hi Jakob,
I think you have not seen my last post :)
============ JAVA code =============================
package test.ojb;
import java.util.Vector;
public class A {
private int oid;
private String name = null;
private Vector elements = null;
public A() {
super();
}
public A(String name) {
super();
this.name = name;
}
public void add(B b){
if(elements == null) elements = new Vector();
elements.addElement(b);
}
public boolean isEqual(A a){
return this.oid == a.getOid();
}
public int getOid() {
return oid;
}
}
package test.ojb;
public class B {
private int oid;
private String value = null;
private String filter = null;
public B(){
super();
}
public B(String filter, String value) {
super();
this.filter = filter;
this.value = value;
}
public boolean isEqual(B b){
return this.oid == b.getOid();
}
public int getOid() {
return oid;
}
public String getFilter() {
return filter;
}
public String getValue() {
return value;
}
}
package test.ojb;
import java.util.Collection;
import org.apache.log4j.Category;
import org.apache.log4j.Logger;
import org.apache.ojb.broker.PersistenceBroker;
import org.apache.ojb.broker.PersistenceBrokerFactory;
import org.apache.ojb.broker.query.Criteria;
import org.apache.ojb.broker.query.Query;
import org.apache.ojb.broker.query.QueryByCriteria;
import org.apache.ojb.broker.query.QueryFactory;
/**
* Projection test with OJB 1.0.0
*/
public class ProjectionTest {
private static Category logger = null;
private PersistenceBroker broker = null;
public ProjectionTest(){
if(logger == null) logger = Logger.getInstance(ProjectionTest.class) ;
this.broker = PersistenceBrokerFactory.defaultPersistenceBroker();
}
public static void main(String[] args) {
ProjectionTest projectionTest = new ProjectionTest();
// uncomment the following line to initialize the database
//projectionTest.initDB();
// load the collection with a SQL statement
B[] sqlResult = projectionTest.getFilteredArray1("xxx", "yyy");
// load the collection with OJB projection
B[] ojbResult = projectionTest.getFilteredArray2("xxx", "yyy");
if(sqlResult != null && ojbResult != null){
if(sqlResult.length == ojbResult.length){
logger.info("result size SQL == result size OJB ");
// log obj results
for (int i=0;i<ojbResult.length;i++)
logger.info("B<" + ojbResult[i].getFilter() + ", " +
ojbResult[i].getValue() + ">");
}
else
logger.info("result size SQL != result size OJB - Test KO.");
}
}
public void initDB(){
// objects with a relationship 1:N
A a1 = new A("xxx");
A a2 = new A("fff");
// populate the "elements" relationship
for(int i=0; i<3;i++){
// a1 contains two categories of filter
a1.add(new B("yyy", "aby" + i));
a1.add(new B("zzz", "abz" + i));
// a2 contains two categories of filter
a2.add(new B("yyy", "bby" + i));
a2.add(new B("zzz", "bbz" + i));
}
broker.beginTransaction();
// store all into the DB (auto-update=true)
broker.store(a1);
broker.store(a2);
broker.commitTransaction();
// make sure the relationship is loaded from the DB
broker.serviceObjectCache().clear();
}
// example realized with a SQL (SQL92) statement
public B[] getFilteredArray1(String name, String filter){
B[] ret = null;
StringBuffer sqlStatement = new StringBuffer();
sqlStatement.append("select b.* from T_A a, T_B b where b.a_oid=A.oid and
a.name='");
sqlStatement.append(name);
sqlStatement.append("' and b.filter='");
sqlStatement.append(filter);
sqlStatement.append("'");
Query query = QueryFactory.newQuery(B.class, sqlStatement.toString());
Collection col = broker.getCollectionByQuery(query);
broker.serviceObjectCache().clear();
if(col != null){
ret = new B[col.size()];
col.toArray(ret);
}
return ret;
}
// example realize with a SQL (SQL92) statement
public B[] getFilteredArray2(String name, String filter){
B[] ret = null;
Criteria criteria = new Criteria();
// criteria relative to A
criteria.addEqualTo("name", name);
criteria.addEqualTo("elements.filter", filter);
QueryByCriteria query = QueryFactory.newQuery(A.class, criteria);
query.setObjectProjectionAttribute("elements");
Collection col = broker.getCollectionByQuery(query);
broker.serviceObjectCache().clear();
if(col != null){
ret = new B[col.size()];
col.toArray(ret);
}
return ret;
}
}
================== SQL for Oracle 8i ===========================
SPOOL projtest.log
drop table T_A;
create table T_A (
OID INTEGER NOT NULL,
name VARCHAR2 ( 16 ) NULL,
constraint PK_A PRIMARY KEY (OID)
);
commit;
drop table T_B;
create table T_B (
OID INTEGER NOT NULL,
filter VARCHAR2 ( 16 ) NULL,
value VARCHAR2 ( 16 ) NULL,
a_OID INTEGER NULL,
constraint PK_B PRIMARY KEY (OID)
);
commit;
SPOOL OFF
Exit 0 ;
.
/
=================== repository.xml ==================================
<?xml version='1.0' encoding='ISO-8859-1' ?>
<descriptor-repository version="1.0" isolation-level="read-uncommitted"
proxy-prefetching-limit="50">
<!-- Datasource for Proto1, this connection is used as the default -->
<!-- Driver P6Spy : com.p6spy.engine.spy.P6SpyDriver -->
<!-- Driver Oracle : oracle.jdbc.driver.OracleDriver -->
<jdbc-connection-descriptor
jcd-alias="sim"
default-connection="true"
platform="Oracle"
jdbc-level="2.0"
driver="com.p6spy.engine.spy.P6SpyDriver"
protocol="jdbc"
subprotocol="oracle:thin"
dbalias="@xxxxxx"
username="xxxxxxx"
password="xxxxxxx"
eager-release="false"
batch-mode="false"
useAutoCommit="1"
ignoreAutoCommitExceptions="false"
>
<sequence-manager
className="org.apache.ojb.broker.util.sequence.SequenceManagerNextValImpl">
<attribute attribute-name="autoNaming" attribute-value="true"/>
</sequence-manager>
</jdbc-connection-descriptor>
<!-- Mapping -->
<class-descriptor
class="test.ojb.A"
table="T_A"
isolation-level="read-uncommitted"
accept-locks="true"
refresh="true">
<field-descriptor
name="oid"
column="oid"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
nullable="false"/>
<field-descriptor
name="name"
column="name"
jdbc-type="VARCHAR"/>
<!-- elements relationship -->
<collection-descriptor
name="elements"
element-class-ref="test.ojb.B"
auto-update="true"
auto-retrieve="true"
auto-delete="true"
proxy="true"
>
<inverse-foreignkey field-ref="aOid"/>
</collection-descriptor>
</class-descriptor>
<class-descriptor
class="test.ojb.B"
table="T_B" isolation-level="read-uncommitted"
accept-locks="true" refresh="true" >
<field-descriptor
name="oid"
column="oid"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"/>
<field-descriptor
name="filter"
column="filter"
jdbc-type="VARCHAR"/>
<field-descriptor
name="value"
column="value"
jdbc-type="VARCHAR"/>
<field-descriptor
name="aOid"
column="a_OID"
jdbc-type="INTEGER" access="anonymous"/>
</class-descriptor>
</descriptor-repository>
========================== END source ==============================
Gildas
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: collection loading and filtering
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi gildas,
the attachment did not make it to the list. please post the sql as normal text.
jakob
LE-QUERE Gildas - REN schrieb:
> Hi Jakob
>
> You will find in attachment my test case on Oracle 8i
>
> best regards
>
> Gildas
>
> ----- Original Message -----
> From: "Jakob Braeuchi" <jb...@gmx.ch>
> To: "OJB Users List" <oj...@db.apache.org>
> Sent: Wednesday, October 27, 2004 6:55 PM
> Subject: Re: collection loading and filtering
>
>
> hi gildas,
>
> could you please post the sql without and with projectionAttribute.
> i'd like to write a testcase and eventually document this feature.
>
> jakob
>
>
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> 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: collection loading and filtering
Posted by LE-QUERE Gildas - REN <gi...@atosorigin.com>.
Hi Jakob
You will find in attachment my test case on Oracle 8i
best regards
Gildas
----- Original Message -----
From: "Jakob Braeuchi" <jb...@gmx.ch>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Wednesday, October 27, 2004 6:55 PM
Subject: Re: collection loading and filtering
hi gildas,
could you please post the sql without and with projectionAttribute.
i'd like to write a testcase and eventually document this feature.
jakob
Re: collection loading and filtering
Posted by LE-QUERE Gildas - REN <gi...@atosorigin.com>.
Hi Jakob,
I post that today.
Gildas
----- Original Message -----
From: "Jakob Braeuchi" <jb...@gmx.ch>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Wednesday, October 27, 2004 6:55 PM
Subject: Re: collection loading and filtering
hi gildas,
could you please post the sql without and with projectionAttribute.
i'd like to write a testcase and eventually document this feature.
jakob
LE-QUERE Gildas - REN schrieb:
> Excellent! that's right.
>
> Thanks for your help
>
> Gildas
>
> ----- Original Message -----
> From: "Maksimenko Alexander" <ma...@bird.cris.net>
> To: "OJB Users List" <oj...@db.apache.org>
> Sent: Wednesday, October 27, 2004 4:27 PM
> Subject: Re: collection loading and filtering
>
>
> query.setObjectProjectionAttribute("elements")
> will solve your problem
>
>
>>Hi Alexander,
>>
>>I tried your suggestion. With the "addPrefetchedRelationship()"method all
>>elements of the relationship are loaded. The filter criteria, here, is a
>>condition to retrieve the A object. In fact it's the right behaviour for
>>this request.
>>
>>The problem is to realize a kind of left outer join in OOP to get the
>>Collection of B which belong to A.
>>
>>In fact I use the A data to find some B elements. Now I think to load a
>>filtered relationship is not very good idea.
>>
>>As a last resort I can use the sqlStatement but I loose the transparency
of
>>the persistence.
>>
>>Thanks
>>
>>Gildas
>>
>>----- Original Message -----
>>From: "Maksimenko Alexander" <ma...@bird.cris.net>
>>To: "OJB Users List" <oj...@db.apache.org>
>>Sent: Wednesday, October 27, 2004 2:25 PM
>>Subject: Re: collection loading and filtering
>>
>>
>>did you try query.addPrefetchedRelationship("elements") ?
>>
>>
>>
>>
>>>Hi all,
>>>
>>>I have un object A referencing à collection with B elements.
>>>
>>>A has an attribute 'name' and a relation 'elements', B has an attribute
>>>'filter'.
>>>
>>>I want to load elements where name='xxx' and filter='yyy'.
>>>
>>>I use the PersitenceBroker API, here is my request:
>>>
>>> criteria = new Criteria();
>>>
>>> criteria.addEqualTo("name", 'xxx');
>>> criteria.addEqualTo("elements.filter", 'yyy');
>>>
>>> query = QueryFactory.newQuery(A.class, criteria);
>>>
>>> A a = (A)broker.getObjectByQuery(query);
>>>
>>>With the relationship auto-retrieve=true all elements are loaded!
>>>
>>>If auto-retrieve=false the relationship is not loaded.
>>>
>>>Is there a solution ?
>>>
>>>
>>>Thanks
>>>
>>>Gildas
>>>
>>>---------------------------------------------------------------------
>>>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
>
> ---------------------------------------------------------------------
> 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: collection loading and filtering
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi gildas,
i already got the source, but i thought the attachement would also contain the
generated without and with projectionAttribute.
imo projectionAttribute works when there are no extents. i forwarded the
problems with extents to oleg nitz, but he's busy with his political work.
jakob
LE-QUERE Gildas - REN schrieb:
> Sorry I had forgotten the source...
>
> ============ JAVA code =============================
> /*
> * Created on 28 Octobre 2004
> */
> package test.ojb;
>
>
> import java.util.Vector;
>
> public class A {
>
> private int oid;
>
> private String name = null;
>
> private Vector elements = null;
>
>
> public A() {
> super();
> }
> public A(String name) {
> super();
>
> this.name = name;
> }
> public void add(B b){
>
> if(elements == null) elements = new Vector();
> elements.addElement(b);
> }
> public boolean isEqual(A a){
>
> return this.oid == a.getOid();
> }
>
> public int getOid() {
> return oid;
> }
>
> }
>
> package test.ojb;
>
>
> public class B {
>
> private int oid;
>
> private String value = null;
>
> private String filter = null;
>
>
> public B(){
> super();
> }
> public B(String filter, String value) {
> super();
>
> this.filter = filter;
> this.value = value;
> }
> public boolean isEqual(B b){
>
> return this.oid == b.getOid();
> }
>
> public int getOid() {
> return oid;
> }
>
> public String getFilter() {
> return filter;
> }
>
> public String getValue() {
> return value;
> }
> }
>
> package test.ojb;
>
> import java.util.Collection;
>
> import org.apache.log4j.Category;
> import org.apache.log4j.Logger;
> import org.apache.ojb.broker.PersistenceBroker;
> import org.apache.ojb.broker.PersistenceBrokerFactory;
> import org.apache.ojb.broker.query.Criteria;
> import org.apache.ojb.broker.query.Query;
> import org.apache.ojb.broker.query.QueryByCriteria;
> import org.apache.ojb.broker.query.QueryFactory;
>
> /**
> * Projection test with OJB 1.0.0
> */
> public class ProjectionTest {
>
> private static Category logger = null;
>
> private PersistenceBroker broker = null;
>
> public ProjectionTest(){
>
> if(logger == null) logger = Logger.getInstance(ProjectionTest.class) ;
>
> this.broker = PersistenceBrokerFactory.defaultPersistenceBroker();
> }
>
> public static void main(String[] args) {
>
> ProjectionTest projectionTest = new ProjectionTest();
>
> // uncomment the following line to initialize the database
> //projectionTest.initDB();
>
>
>
> // load the collection with a SQL statement
> B[] sqlResult = projectionTest.getFilteredArray1("xxx", "yyy");
>
>
> // load the collection with OJB projection
> B[] ojbResult = projectionTest.getFilteredArray2("xxx", "yyy");
>
> if(sqlResult != null && ojbResult != null){
>
> if(sqlResult.length == ojbResult.length){
>
> logger.info("result size SQL == result size OJB ");
>
> // log obj results
> for (int i=0;i<ojbResult.length;i++)
> logger.info("B<" + ojbResult[i].getFilter() + ", " +
> ojbResult[i].getValue() + ">");
> }
> else
> logger.info("result size SQL != result size OJB - Test KO.");
> }
> }
>
> public void initDB(){
> // objects with a relationship 1:N
> A a1 = new A("xxx");
> A a2 = new A("fff");
>
> // populate the "elements" relationship
> for(int i=0; i<3;i++){
>
> // a1 contains two categories of filter
> a1.add(new B("yyy", "aby" + i));
> a1.add(new B("zzz", "abz" + i));
>
> // a2 contains two categories of filter
> a2.add(new B("yyy", "bby" + i));
> a2.add(new B("zzz", "bbz" + i));
> }
> broker.beginTransaction();
>
> // store all into the DB (auto-update=true)
> broker.store(a1);
> broker.store(a2);
>
> broker.commitTransaction();
>
> // make sure the relationship is loaded from the DB
> broker.serviceObjectCache().clear();
>
> }
> // example realized with a SQL (SQL92) statement
> public B[] getFilteredArray1(String name, String filter){
>
> B[] ret = null;
> StringBuffer sqlStatement = new StringBuffer();
>
> sqlStatement.append("select b.* from T_A a, T_B b where b.a_oid=A.oid and
> a.name='");
> sqlStatement.append(name);
> sqlStatement.append("' and b.filter='");
> sqlStatement.append(filter);
> sqlStatement.append("'");
>
> Query query = QueryFactory.newQuery(B.class, sqlStatement.toString());
>
> Collection col = broker.getCollectionByQuery(query);
>
> broker.serviceObjectCache().clear();
>
> if(col != null){
>
> ret = new B[col.size()];
>
> col.toArray(ret);
> }
> return ret;
> }
> // example realize with a SQL (SQL92) statement
> public B[] getFilteredArray2(String name, String filter){
>
> B[] ret = null;
> Criteria criteria = new Criteria();
>
> // criteria relative to A
> criteria.addEqualTo("name", name);
> criteria.addEqualTo("elements.filter", filter);
>
> QueryByCriteria query = QueryFactory.newQuery(A.class, criteria);
>
> query.setObjectProjectionAttribute("elements");
>
> Collection col = broker.getCollectionByQuery(query);
>
> broker.serviceObjectCache().clear();
>
> if(col != null){
>
> ret = new B[col.size()];
>
> col.toArray(ret);
> }
> return ret;
> }
> }
>
> ================== SQL for Oracle 8i ===========================
>
> SPOOL projtest.log
>
> drop table T_A;
>
> create table T_A (
> OID INTEGER NOT NULL,
> name VARCHAR2 ( 16 ) NULL,
> constraint PK_A PRIMARY KEY (OID)
> );
>
> commit;
>
>
> drop table T_B;
>
> create table T_B (
> OID INTEGER NOT NULL,
> filter VARCHAR2 ( 16 ) NULL,
> value VARCHAR2 ( 16 ) NULL,
> a_OID INTEGER NULL,
> constraint PK_B PRIMARY KEY (OID)
> );
>
> commit;
>
> SPOOL OFF
>
> Exit 0 ;
>
> .
> /
> =================== repository.xml ==================================
>
> <?xml version='1.0' encoding='ISO-8859-1' ?>
> <descriptor-repository version="1.0" isolation-level="read-uncommitted"
> proxy-prefetching-limit="50">
>
> <!-- Datasource for Proto1, this connection is used as the default -->
> <!-- Driver P6Spy : com.p6spy.engine.spy.P6SpyDriver -->
> <!-- Driver Oracle : oracle.jdbc.driver.OracleDriver -->
> <jdbc-connection-descriptor
>
> jcd-alias="sim"
> default-connection="true"
> platform="Oracle"
> jdbc-level="2.0"
> driver="com.p6spy.engine.spy.P6SpyDriver"
> protocol="jdbc"
> subprotocol="oracle:thin"
> dbalias="@xxxxxx"
> username="xxxxxxx"
> password="xxxxxxx"
> eager-release="false"
> batch-mode="false"
> useAutoCommit="1"
> ignoreAutoCommitExceptions="false"
>
> <sequence-manager
> className="org.apache.ojb.broker.util.sequence.SequenceManagerNextValImpl">
> <attribute attribute-name="autoNaming" attribute-value="true"/>
> </sequence-manager>
>
> </jdbc-connection-descriptor>
>
> <!-- Mapping -->
>
> <class-descriptor
> class="test.ojb.A"
> table="T_A"
> isolation-level="read-uncommitted"
> accept-locks="true"
> refresh="true">
>
> <field-descriptor
> name="oid"
> column="oid"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"
> nullable="false"/>
>
> <field-descriptor
> name="name"
> column="name"
> jdbc-type="VARCHAR"/>
>
> <!-- elements relationship -->
> <collection-descriptor
> name="elements"
> element-class-ref="test.ojb.B"
> auto-update="true"
> auto-retrieve="true"
> auto-delete="true"
> proxy="true"
>
> >
> <inverse-foreignkey field-ref="aOid"/>
> </collection-descriptor>
>
> </class-descriptor>
>
> <class-descriptor
> class="test.ojb.B"
> table="T_B" isolation-level="read-uncommitted"
> accept-locks="true" refresh="true" >
>
> <field-descriptor
> name="oid"
> column="oid"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"/>
>
> <field-descriptor
> name="filter"
> column="filter"
> jdbc-type="VARCHAR"/>
>
> <field-descriptor
> name="value"
> column="value"
> jdbc-type="VARCHAR"/>
>
> <field-descriptor
> name="aOid"
> column="a_OID"
> jdbc-type="INTEGER" access="anonymous"/>
>
> </class-descriptor>
>
>
> </descriptor-repository>
>
> ========================== END source ==============================
>
> Gildas
>
> ----- Original Message -----
> From: "Jakob Braeuchi" <jb...@gmx.ch>
> To: "OJB Users List" <oj...@db.apache.org>
> Sent: Wednesday, October 27, 2004 6:55 PM
> Subject: Re: collection loading and filtering
>
>
> hi gildas,
>
> could you please post the sql without and with projectionAttribute.
> i'd like to write a testcase and eventually document this feature.
>
> jakob
>
>
> ---------------------------------------------------------------------
> 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: collection loading and filtering
Posted by LE-QUERE Gildas - REN <gi...@atosorigin.com>.
Sorry I had forgotten the source...
============ JAVA code =============================
/*
* Created on 28 Octobre 2004
*/
package test.ojb;
import java.util.Vector;
public class A {
private int oid;
private String name = null;
private Vector elements = null;
public A() {
super();
}
public A(String name) {
super();
this.name = name;
}
public void add(B b){
if(elements == null) elements = new Vector();
elements.addElement(b);
}
public boolean isEqual(A a){
return this.oid == a.getOid();
}
public int getOid() {
return oid;
}
}
package test.ojb;
public class B {
private int oid;
private String value = null;
private String filter = null;
public B(){
super();
}
public B(String filter, String value) {
super();
this.filter = filter;
this.value = value;
}
public boolean isEqual(B b){
return this.oid == b.getOid();
}
public int getOid() {
return oid;
}
public String getFilter() {
return filter;
}
public String getValue() {
return value;
}
}
package test.ojb;
import java.util.Collection;
import org.apache.log4j.Category;
import org.apache.log4j.Logger;
import org.apache.ojb.broker.PersistenceBroker;
import org.apache.ojb.broker.PersistenceBrokerFactory;
import org.apache.ojb.broker.query.Criteria;
import org.apache.ojb.broker.query.Query;
import org.apache.ojb.broker.query.QueryByCriteria;
import org.apache.ojb.broker.query.QueryFactory;
/**
* Projection test with OJB 1.0.0
*/
public class ProjectionTest {
private static Category logger = null;
private PersistenceBroker broker = null;
public ProjectionTest(){
if(logger == null) logger = Logger.getInstance(ProjectionTest.class) ;
this.broker = PersistenceBrokerFactory.defaultPersistenceBroker();
}
public static void main(String[] args) {
ProjectionTest projectionTest = new ProjectionTest();
// uncomment the following line to initialize the database
//projectionTest.initDB();
// load the collection with a SQL statement
B[] sqlResult = projectionTest.getFilteredArray1("xxx", "yyy");
// load the collection with OJB projection
B[] ojbResult = projectionTest.getFilteredArray2("xxx", "yyy");
if(sqlResult != null && ojbResult != null){
if(sqlResult.length == ojbResult.length){
logger.info("result size SQL == result size OJB ");
// log obj results
for (int i=0;i<ojbResult.length;i++)
logger.info("B<" + ojbResult[i].getFilter() + ", " +
ojbResult[i].getValue() + ">");
}
else
logger.info("result size SQL != result size OJB - Test KO.");
}
}
public void initDB(){
// objects with a relationship 1:N
A a1 = new A("xxx");
A a2 = new A("fff");
// populate the "elements" relationship
for(int i=0; i<3;i++){
// a1 contains two categories of filter
a1.add(new B("yyy", "aby" + i));
a1.add(new B("zzz", "abz" + i));
// a2 contains two categories of filter
a2.add(new B("yyy", "bby" + i));
a2.add(new B("zzz", "bbz" + i));
}
broker.beginTransaction();
// store all into the DB (auto-update=true)
broker.store(a1);
broker.store(a2);
broker.commitTransaction();
// make sure the relationship is loaded from the DB
broker.serviceObjectCache().clear();
}
// example realized with a SQL (SQL92) statement
public B[] getFilteredArray1(String name, String filter){
B[] ret = null;
StringBuffer sqlStatement = new StringBuffer();
sqlStatement.append("select b.* from T_A a, T_B b where b.a_oid=A.oid and
a.name='");
sqlStatement.append(name);
sqlStatement.append("' and b.filter='");
sqlStatement.append(filter);
sqlStatement.append("'");
Query query = QueryFactory.newQuery(B.class, sqlStatement.toString());
Collection col = broker.getCollectionByQuery(query);
broker.serviceObjectCache().clear();
if(col != null){
ret = new B[col.size()];
col.toArray(ret);
}
return ret;
}
// example realize with a SQL (SQL92) statement
public B[] getFilteredArray2(String name, String filter){
B[] ret = null;
Criteria criteria = new Criteria();
// criteria relative to A
criteria.addEqualTo("name", name);
criteria.addEqualTo("elements.filter", filter);
QueryByCriteria query = QueryFactory.newQuery(A.class, criteria);
query.setObjectProjectionAttribute("elements");
Collection col = broker.getCollectionByQuery(query);
broker.serviceObjectCache().clear();
if(col != null){
ret = new B[col.size()];
col.toArray(ret);
}
return ret;
}
}
================== SQL for Oracle 8i ===========================
SPOOL projtest.log
drop table T_A;
create table T_A (
OID INTEGER NOT NULL,
name VARCHAR2 ( 16 ) NULL,
constraint PK_A PRIMARY KEY (OID)
);
commit;
drop table T_B;
create table T_B (
OID INTEGER NOT NULL,
filter VARCHAR2 ( 16 ) NULL,
value VARCHAR2 ( 16 ) NULL,
a_OID INTEGER NULL,
constraint PK_B PRIMARY KEY (OID)
);
commit;
SPOOL OFF
Exit 0 ;
.
/
=================== repository.xml ==================================
<?xml version='1.0' encoding='ISO-8859-1' ?>
<descriptor-repository version="1.0" isolation-level="read-uncommitted"
proxy-prefetching-limit="50">
<!-- Datasource for Proto1, this connection is used as the default -->
<!-- Driver P6Spy : com.p6spy.engine.spy.P6SpyDriver -->
<!-- Driver Oracle : oracle.jdbc.driver.OracleDriver -->
<jdbc-connection-descriptor
jcd-alias="sim"
default-connection="true"
platform="Oracle"
jdbc-level="2.0"
driver="com.p6spy.engine.spy.P6SpyDriver"
protocol="jdbc"
subprotocol="oracle:thin"
dbalias="@xxxxxx"
username="xxxxxxx"
password="xxxxxxx"
eager-release="false"
batch-mode="false"
useAutoCommit="1"
ignoreAutoCommitExceptions="false"
>
<sequence-manager
className="org.apache.ojb.broker.util.sequence.SequenceManagerNextValImpl">
<attribute attribute-name="autoNaming" attribute-value="true"/>
</sequence-manager>
</jdbc-connection-descriptor>
<!-- Mapping -->
<class-descriptor
class="test.ojb.A"
table="T_A"
isolation-level="read-uncommitted"
accept-locks="true"
refresh="true">
<field-descriptor
name="oid"
column="oid"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
nullable="false"/>
<field-descriptor
name="name"
column="name"
jdbc-type="VARCHAR"/>
<!-- elements relationship -->
<collection-descriptor
name="elements"
element-class-ref="test.ojb.B"
auto-update="true"
auto-retrieve="true"
auto-delete="true"
proxy="true"
>
<inverse-foreignkey field-ref="aOid"/>
</collection-descriptor>
</class-descriptor>
<class-descriptor
class="test.ojb.B"
table="T_B" isolation-level="read-uncommitted"
accept-locks="true" refresh="true" >
<field-descriptor
name="oid"
column="oid"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"/>
<field-descriptor
name="filter"
column="filter"
jdbc-type="VARCHAR"/>
<field-descriptor
name="value"
column="value"
jdbc-type="VARCHAR"/>
<field-descriptor
name="aOid"
column="a_OID"
jdbc-type="INTEGER" access="anonymous"/>
</class-descriptor>
</descriptor-repository>
========================== END source ==============================
Gildas
----- Original Message -----
From: "Jakob Braeuchi" <jb...@gmx.ch>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Wednesday, October 27, 2004 6:55 PM
Subject: Re: collection loading and filtering
hi gildas,
could you please post the sql without and with projectionAttribute.
i'd like to write a testcase and eventually document this feature.
jakob
---------------------------------------------------------------------
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: collection loading and filtering
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi gildas,
could you please post the sql without and with projectionAttribute.
i'd like to write a testcase and eventually document this feature.
jakob
LE-QUERE Gildas - REN schrieb:
> Excellent! that's right.
>
> Thanks for your help
>
> Gildas
>
> ----- Original Message -----
> From: "Maksimenko Alexander" <ma...@bird.cris.net>
> To: "OJB Users List" <oj...@db.apache.org>
> Sent: Wednesday, October 27, 2004 4:27 PM
> Subject: Re: collection loading and filtering
>
>
> query.setObjectProjectionAttribute("elements")
> will solve your problem
>
>
>>Hi Alexander,
>>
>>I tried your suggestion. With the "addPrefetchedRelationship()"method all
>>elements of the relationship are loaded. The filter criteria, here, is a
>>condition to retrieve the A object. In fact it's the right behaviour for
>>this request.
>>
>>The problem is to realize a kind of left outer join in OOP to get the
>>Collection of B which belong to A.
>>
>>In fact I use the A data to find some B elements. Now I think to load a
>>filtered relationship is not very good idea.
>>
>>As a last resort I can use the sqlStatement but I loose the transparency of
>>the persistence.
>>
>>Thanks
>>
>>Gildas
>>
>>----- Original Message -----
>>From: "Maksimenko Alexander" <ma...@bird.cris.net>
>>To: "OJB Users List" <oj...@db.apache.org>
>>Sent: Wednesday, October 27, 2004 2:25 PM
>>Subject: Re: collection loading and filtering
>>
>>
>>did you try query.addPrefetchedRelationship("elements") ?
>>
>>
>>
>>
>>>Hi all,
>>>
>>>I have un object A referencing à collection with B elements.
>>>
>>>A has an attribute 'name' and a relation 'elements', B has an attribute
>>>'filter'.
>>>
>>>I want to load elements where name='xxx' and filter='yyy'.
>>>
>>>I use the PersitenceBroker API, here is my request:
>>>
>>> criteria = new Criteria();
>>>
>>> criteria.addEqualTo("name", 'xxx');
>>> criteria.addEqualTo("elements.filter", 'yyy');
>>>
>>> query = QueryFactory.newQuery(A.class, criteria);
>>>
>>> A a = (A)broker.getObjectByQuery(query);
>>>
>>>With the relationship auto-retrieve=true all elements are loaded!
>>>
>>>If auto-retrieve=false the relationship is not loaded.
>>>
>>>Is there a solution ?
>>>
>>>
>>>Thanks
>>>
>>>Gildas
>>>
>>>---------------------------------------------------------------------
>>>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
>
> ---------------------------------------------------------------------
> 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: collection loading and filtering
Posted by LE-QUERE Gildas - REN <gi...@atosorigin.com>.
Excellent! that's right.
Thanks for your help
Gildas
----- Original Message -----
From: "Maksimenko Alexander" <ma...@bird.cris.net>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Wednesday, October 27, 2004 4:27 PM
Subject: Re: collection loading and filtering
query.setObjectProjectionAttribute("elements")
will solve your problem
>Hi Alexander,
>
>I tried your suggestion. With the "addPrefetchedRelationship()"method all
>elements of the relationship are loaded. The filter criteria, here, is a
>condition to retrieve the A object. In fact it's the right behaviour for
>this request.
>
>The problem is to realize a kind of left outer join in OOP to get the
>Collection of B which belong to A.
>
>In fact I use the A data to find some B elements. Now I think to load a
>filtered relationship is not very good idea.
>
>As a last resort I can use the sqlStatement but I loose the transparency of
>the persistence.
>
>Thanks
>
>Gildas
>
>----- Original Message -----
>From: "Maksimenko Alexander" <ma...@bird.cris.net>
>To: "OJB Users List" <oj...@db.apache.org>
>Sent: Wednesday, October 27, 2004 2:25 PM
>Subject: Re: collection loading and filtering
>
>
>did you try query.addPrefetchedRelationship("elements") ?
>
>
>
>>Hi all,
>>
>>I have un object A referencing à collection with B elements.
>>
>>A has an attribute 'name' and a relation 'elements', B has an attribute
>>'filter'.
>>
>>I want to load elements where name='xxx' and filter='yyy'.
>>
>>I use the PersitenceBroker API, here is my request:
>>
>> criteria = new Criteria();
>>
>> criteria.addEqualTo("name", 'xxx');
>> criteria.addEqualTo("elements.filter", 'yyy');
>>
>> query = QueryFactory.newQuery(A.class, criteria);
>>
>> A a = (A)broker.getObjectByQuery(query);
>>
>>With the relationship auto-retrieve=true all elements are loaded!
>>
>>If auto-retrieve=false the relationship is not loaded.
>>
>>Is there a solution ?
>>
>>
>>Thanks
>>
>>Gildas
>>
>>---------------------------------------------------------------------
>>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
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: collection loading and filtering
Posted by Maksimenko Alexander <ma...@bird.cris.net>.
query.setObjectProjectionAttribute("elements")
will solve your problem
>Hi Alexander,
>
>I tried your suggestion. With the "addPrefetchedRelationship()"method all
>elements of the relationship are loaded. The filter criteria, here, is a
>condition to retrieve the A object. In fact it's the right behaviour for
>this request.
>
>The problem is to realize a kind of left outer join in OOP to get the
>Collection of B which belong to A.
>
>In fact I use the A data to find some B elements. Now I think to load a
>filtered relationship is not very good idea.
>
>As a last resort I can use the sqlStatement but I loose the transparency of
>the persistence.
>
>Thanks
>
>Gildas
>
>----- Original Message -----
>From: "Maksimenko Alexander" <ma...@bird.cris.net>
>To: "OJB Users List" <oj...@db.apache.org>
>Sent: Wednesday, October 27, 2004 2:25 PM
>Subject: Re: collection loading and filtering
>
>
>did you try query.addPrefetchedRelationship("elements") ?
>
>
>
>>Hi all,
>>
>>I have un object A referencing à collection with B elements.
>>
>>A has an attribute 'name' and a relation 'elements', B has an attribute
>>'filter'.
>>
>>I want to load elements where name='xxx' and filter='yyy'.
>>
>>I use the PersitenceBroker API, here is my request:
>>
>> criteria = new Criteria();
>>
>> criteria.addEqualTo("name", 'xxx');
>> criteria.addEqualTo("elements.filter", 'yyy');
>>
>> query = QueryFactory.newQuery(A.class, criteria);
>>
>> A a = (A)broker.getObjectByQuery(query);
>>
>>With the relationship auto-retrieve=true all elements are loaded!
>>
>>If auto-retrieve=false the relationship is not loaded.
>>
>>Is there a solution ?
>>
>>
>>Thanks
>>
>>Gildas
>>
>>---------------------------------------------------------------------
>>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
Re: collection loading and filtering
Posted by LE-QUERE Gildas - REN <gi...@atosorigin.com>.
Hi Alexander,
I tried your suggestion. With the "addPrefetchedRelationship()"method all
elements of the relationship are loaded. The filter criteria, here, is a
condition to retrieve the A object. In fact it's the right behaviour for
this request.
The problem is to realize a kind of left outer join in OOP to get the
Collection of B which belong to A.
In fact I use the A data to find some B elements. Now I think to load a
filtered relationship is not very good idea.
As a last resort I can use the sqlStatement but I loose the transparency of
the persistence.
Thanks
Gildas
----- Original Message -----
From: "Maksimenko Alexander" <ma...@bird.cris.net>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Wednesday, October 27, 2004 2:25 PM
Subject: Re: collection loading and filtering
did you try query.addPrefetchedRelationship("elements") ?
>Hi all,
>
>I have un object A referencing à collection with B elements.
>
>A has an attribute 'name' and a relation 'elements', B has an attribute
>'filter'.
>
>I want to load elements where name='xxx' and filter='yyy'.
>
>I use the PersitenceBroker API, here is my request:
>
> criteria = new Criteria();
>
> criteria.addEqualTo("name", 'xxx');
> criteria.addEqualTo("elements.filter", 'yyy');
>
> query = QueryFactory.newQuery(A.class, criteria);
>
> A a = (A)broker.getObjectByQuery(query);
>
>With the relationship auto-retrieve=true all elements are loaded!
>
>If auto-retrieve=false the relationship is not loaded.
>
>Is there a solution ?
>
>
>Thanks
>
>Gildas
>
>---------------------------------------------------------------------
>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: collection loading and filtering
Posted by Maksimenko Alexander <ma...@bird.cris.net>.
did you try query.addPrefetchedRelationship("elements") ?
>Hi all,
>
>I have un object A referencing à collection with B elements.
>
>A has an attribute 'name' and a relation 'elements', B has an attribute
>'filter'.
>
>I want to load elements where name='xxx' and filter='yyy'.
>
>I use the PersitenceBroker API, here is my request:
>
> criteria = new Criteria();
>
> criteria.addEqualTo("name", 'xxx');
> criteria.addEqualTo("elements.filter", 'yyy');
>
> query = QueryFactory.newQuery(A.class, criteria);
>
> A a = (A)broker.getObjectByQuery(query);
>
>With the relationship auto-retrieve=true all elements are loaded!
>
>If auto-retrieve=false the relationship is not loaded.
>
>Is there a solution ?
>
>
>Thanks
>
>Gildas
>
>---------------------------------------------------------------------
>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