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 Jakob Braeuchi <jb...@gmx.ch> on 2003/01/03 15:04:07 UTC
extent aware pathExpressions broken !
hi all,
while playing aoround with extent aware path expressions, i found that the
current implementation does not work due to caching of sql statements.
i queried for the query i did is :
crit.addEqualTo("allArticlesInGroup.productGroupId", new Integer(5));
Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);
because 'allArticlesInGroup' points to a class with two extents the 3
following queries should have been executed (one for Artikel, Books and CDs)
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
Kategorien A0 INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr WHERE
A1.Kategorie_Nr = '5'
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
Kategorien A0 INNER JOIN Books A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr WHERE
A1.Kategorie_Nr = '5'
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
Kategorien A0 INNER JOIN CDS A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr WHERE
A1.Kategorie_Nr = '5'
due to caching of the statement (and manipulation of the original, cached
query) the 'Artikel' query is executed 3 times.
imo we should look for a better way to support exent aware pathExpressions.
even if the above mentioned way would work as expected we would have read
the same productGroup 3 times although there's only one with id = 5 !.
i'm thinking of a solution where we need only ONE sql including all tables
of the extents.
any ideas ?
jakob
Scrollable Resultsets in JdbcAccess
Posted by lc...@gmx.net.
hi!
is anybody able to answer my question? i posted it some times ago, but
obviously nobody
could react. i think matthew has made the change. could you help me out?
regards,
liang
> hi!
>
> As of OJB 0.9.8 the scrollable flag in method JDBCAccess.executeSQL() has
> been set from
> Query.SCROLLABLE to Query.NOT_SCROLLABLE, which causes RsIterator.size()
> always to
> use count(*) method to determine the size although the JDBC driver
> supports
> it.
>
> public int size() throws PersistenceBrokerException
> {
> int retval = 0; // default size is 0;
> boolean forwardOnly = true;
> try
> {
> # forwardOnly flag yields true forwardOnly =
> m_rsAndStmt.m_stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY;
> }
> catch (SQLException e)
> {
> }
> if (!supportsAdvancedJDBCCursorControl()
> ||
>
PlatformFactory.getPlatformFor(m_cld.getConnectionDescriptor()).useCountForResultsetSize()
> || forwardOnly)
> {
> /**
> * MBAIRD: doesn't support the .last .getRow method, use the .getCount
> on
> the
> * persistenceBroker which executes a count(*) query.
> */
> if (logger.isDebugEnabled()) logger.debug("Executing count(*) to get
> size()");
> retval = countedSize();
> }
> else
> ...
>
> Why has to been changed and how can i force size() not to take the
> count(*)
> method?
>
> thanks,
>
> Liang Cheng
--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!
Scrollable Resultsets in JdbcAccess
Posted by lc...@gmx.net.
hi!
As of OJB 0.9.8 the scrollable flag in method JDBCAccess.executeSQL() has
been set from
Query.SCROLLABLE to Query.NOT_SCROLLABLE, which causes RsIterator.size()
always to
use count(*) method to determine the size although the JDBC driver supports
it.
public int size() throws PersistenceBrokerException
{
int retval = 0; // default size is 0;
boolean forwardOnly = true;
try
{
# forwardOnly flag yields true forwardOnly =
m_rsAndStmt.m_stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY;
}
catch (SQLException e)
{
}
if (!supportsAdvancedJDBCCursorControl()
||
PlatformFactory.getPlatformFor(m_cld.getConnectionDescriptor()).useCountForResultsetSize()
|| forwardOnly)
{
/**
* MBAIRD: doesn't support the .last .getRow method, use the .getCount on
the
* persistenceBroker which executes a count(*) query.
*/
if (logger.isDebugEnabled()) logger.debug("Executing count(*) to get
size()");
retval = countedSize();
}
else
...
Why has to been changed and how can i force size() not to take the count(*)
method?
thanks,
Liang Cheng
--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!
Re: extent aware pathExpressions fixed
Posted by Leandro Rodrigo Saad Cruz <le...@ibnetwork.com.br>.
On Sun, 2003-01-19 at 09:59, Jakob Braeuchi wrote:
> hi all,
>
> ojb now provides better support for extent aware path expression. only
> one sql is executed.
> the sql containins one 'outer join' for each extent and one 'or' for
> each criteria:
>
> SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> Kategorien A0
> LEFT OUTER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
> LEFT OUTER JOIN BOOKS A1E0 ON A0.Kategorie_Nr=A1E0.Kategorie_Nr
> LEFT OUTER JOIN CDS A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr
> WHERE
> A1.Artikelname LIKE 'F%' OR
> A1E0.Artikelname LIKE 'F%' OR
> A1E1.Artikelname LIKE 'F%'
>
> this sql returns all productgroups having an article named 'F%' (article
> has extents Books and CDs). above statement is produced by the following
> simple query:
>
> Criteria crit = new Criteria();
> crit.addLike("allArticlesInGroup.articleName", "F%");
>
> Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);
> Iterator iter = broker.getIteratorByQuery(q);
>
> there are some issues with FieldCriteria (criteria referencing another
> field of a class) because the referenced field may also be extent aware.
Nice work :-)
>
> jakob
>
> >
> >
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
--
Leandro Rodrigo Saad Cruz
IT - Inter Business Tecnologia e Servicos (IB)
http://www.ibnetwork.com.br
http://jakarta.apache.org/ojb
extent aware pathExpressions fixed
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi all,
ojb now provides better support for extent aware path expression. only
one sql is executed.
the sql containins one 'outer join' for each extent and one 'or' for
each criteria:
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
Kategorien A0
LEFT OUTER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
LEFT OUTER JOIN BOOKS A1E0 ON A0.Kategorie_Nr=A1E0.Kategorie_Nr
LEFT OUTER JOIN CDS A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr
WHERE
A1.Artikelname LIKE 'F%' OR
A1E0.Artikelname LIKE 'F%' OR
A1E1.Artikelname LIKE 'F%'
this sql returns all productgroups having an article named 'F%' (article
has extents Books and CDs). above statement is produced by the following
simple query:
Criteria crit = new Criteria();
crit.addLike("allArticlesInGroup.articleName", "F%");
Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);
Iterator iter = broker.getIteratorByQuery(q);
there are some issues with FieldCriteria (criteria referencing another
field of a class) because the referenced field may also be extent aware.
jakob
>
>
extent aware pathExpressions fixed
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi all,
ojb now provides better support for extent aware path expression. only
one sql is executed.
the sql containins one 'outer join' for each extent and one 'or' for
each criteria:
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
Kategorien A0
LEFT OUTER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
LEFT OUTER JOIN BOOKS A1E0 ON A0.Kategorie_Nr=A1E0.Kategorie_Nr
LEFT OUTER JOIN CDS A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr
WHERE
A1.Artikelname LIKE 'F%' OR
A1E0.Artikelname LIKE 'F%' OR
A1E1.Artikelname LIKE 'F%'
this sql returns all productgroups having an article named 'F%' (article
has extents Books and CDs). above statement is produced by the following
simple query:
Criteria crit = new Criteria();
crit.addLike("allArticlesInGroup.articleName", "F%");
Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);
Iterator iter = broker.getIteratorByQuery(q);
there are some issues with FieldCriteria (criteria referencing another
field of a class) because the referenced field may also be extent aware.
jakob
>
>
Re: extent aware pathExpressions broken !
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi leandro,
i'm already working on it. the following sql is generated by ojb. binding of
the ? also works (somehow)
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung
FROM Kategorien A0
INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
INNER JOIN BOOKS A1E0 ON A0.Kategorie_Nr=A1E0.Kategorie_Nr
INNER JOIN CDS A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr
WHERE A1.Kategorie_Nr = ? OR A1E0.Kategorie_Nr = ? OR A1E1.Kategorie_Nr = ?
but unfortunately the solution is not yet clean and simple :(
the ORs and the binding are the problem because we still have only ONE
criteria for Kategorie_Nr.
what i do is adding the Kategorie_Nr = ? for each extent and i also have to
bind the criteria once for each extent.
you see there's still a lot to do, not to mention that i currently only
handle simple SelectionCriteria (no Betweens etc.)
jakob
----- Original Message -----
From: "Leandro Rodrigo Saad Cruz" <le...@ibnetwork.com.br>
To: "OJB Developers List" <oj...@jakarta.apache.org>
Sent: Saturday, January 04, 2003 5:32 PM
Subject: Re: extent aware pathExpressions broken !
> On Fri, 2003-01-03 at 12:15, Jakob Braeuchi wrote:
> > hi,
>
> hi jacob !
>
> >
> > the query i'm thinking of is the following:
> >
> > SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung
> > FROM
> > Kategorien A0
> > INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
> > INNER JOIN BOOKS A2 ON A0.Kategorie_Nr=A2.Kategorie_Nr
> > INNER JOIN CDS A3 ON A0.Kategorie_Nr=A3.Kategorie_Nr
> > WHERE
> > A1.Kategorie_Nr = 5
> > or A2.Kategorie_Nr = 5
> > or A3.Kategorie_Nr = 5
> >
> > and it seems to work (at least in hsqldb, mysql and sapdb)
> > so the only problem i have is how to build this sql ....
> > any ideas or help will be appreciated.
>
> I can help you if you want. Right now I'm very busy ( trying to deliver
> a product on time ), but in 2 weeks I'll be able to help you ( if you
> can way so long :-) )
>
> >
> > jakob
> >
> > ----- Original Message -----
> > From: "Jakob Braeuchi" <jb...@gmx.ch>
> > To: "OJB Developers List" <oj...@jakarta.apache.org>
> > Cc: "OJB Users List" <oj...@jakarta.apache.org>
> > Sent: Friday, January 03, 2003 3:04 PM
> > Subject: extent aware pathExpressions broken !
> >
> >
> > > hi all,
> > >
> > > while playing aoround with extent aware path expressions, i found that
the
> > > current implementation does not work due to caching of sql statements.
> > >
> > > i queried for the query i did is :
> > >
> > > crit.addEqualTo("allArticlesInGroup.productGroupId", new Integer(5));
> > > Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);
> > >
> > > because 'allArticlesInGroup' points to a class with two extents the 3
> > > following queries should have been executed (one for Artikel, Books
and
> > CDs)
> > >
> > > SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> > > Kategorien A0 INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
> > WHERE
> > > A1.Kategorie_Nr = '5'
> > >
> > > SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> > > Kategorien A0 INNER JOIN Books A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
WHERE
> > > A1.Kategorie_Nr = '5'
> > >
> > > SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> > > Kategorien A0 INNER JOIN CDS A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
WHERE
> > > A1.Kategorie_Nr = '5'
> > >
> > > due to caching of the statement (and manipulation of the original,
cached
> > > query) the 'Artikel' query is executed 3 times.
> > >
> > > imo we should look for a better way to support exent aware
> > pathExpressions.
> > > even if the above mentioned way would work as expected we would have
read
> > > the same productGroup 3 times although there's only one with id = 5 !.
> > >
> > > i'm thinking of a solution where we need only ONE sql including all
tables
> > > of the extents.
> > >
> > > any ideas ?
> > >
> > > jakob
> > >
> > >
> > > --
> > > To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> > > For additional commands, e-mail:
<ma...@jakarta.apache.org>
> > >
> >
> >
> > --
> > To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> > For additional commands, e-mail:
<ma...@jakarta.apache.org>
> >
> >
> --
> Leandro Rodrigo Saad Cruz
> IT - Inter Business Tecnologia e Servicos (IB)
> http://www.ibnetwork.com.br
> http://jakarta.apache.org/ojb
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
Re: extent aware pathExpressions broken !
Posted by Leandro Rodrigo Saad Cruz <le...@ibnetwork.com.br>.
On Fri, 2003-01-03 at 12:15, Jakob Braeuchi wrote:
> hi,
hi jacob !
>
> the query i'm thinking of is the following:
>
> SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung
> FROM
> Kategorien A0
> INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
> INNER JOIN BOOKS A2 ON A0.Kategorie_Nr=A2.Kategorie_Nr
> INNER JOIN CDS A3 ON A0.Kategorie_Nr=A3.Kategorie_Nr
> WHERE
> A1.Kategorie_Nr = 5
> or A2.Kategorie_Nr = 5
> or A3.Kategorie_Nr = 5
>
> and it seems to work (at least in hsqldb, mysql and sapdb)
> so the only problem i have is how to build this sql ....
> any ideas or help will be appreciated.
I can help you if you want. Right now I'm very busy ( trying to deliver
a product on time ), but in 2 weeks I'll be able to help you ( if you
can way so long :-) )
>
> jakob
>
> ----- Original Message -----
> From: "Jakob Braeuchi" <jb...@gmx.ch>
> To: "OJB Developers List" <oj...@jakarta.apache.org>
> Cc: "OJB Users List" <oj...@jakarta.apache.org>
> Sent: Friday, January 03, 2003 3:04 PM
> Subject: extent aware pathExpressions broken !
>
>
> > hi all,
> >
> > while playing aoround with extent aware path expressions, i found that the
> > current implementation does not work due to caching of sql statements.
> >
> > i queried for the query i did is :
> >
> > crit.addEqualTo("allArticlesInGroup.productGroupId", new Integer(5));
> > Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);
> >
> > because 'allArticlesInGroup' points to a class with two extents the 3
> > following queries should have been executed (one for Artikel, Books and
> CDs)
> >
> > SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> > Kategorien A0 INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
> WHERE
> > A1.Kategorie_Nr = '5'
> >
> > SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> > Kategorien A0 INNER JOIN Books A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr WHERE
> > A1.Kategorie_Nr = '5'
> >
> > SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> > Kategorien A0 INNER JOIN CDS A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr WHERE
> > A1.Kategorie_Nr = '5'
> >
> > due to caching of the statement (and manipulation of the original, cached
> > query) the 'Artikel' query is executed 3 times.
> >
> > imo we should look for a better way to support exent aware
> pathExpressions.
> > even if the above mentioned way would work as expected we would have read
> > the same productGroup 3 times although there's only one with id = 5 !.
> >
> > i'm thinking of a solution where we need only ONE sql including all tables
> > of the extents.
> >
> > any ideas ?
> >
> > jakob
> >
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
> >
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
--
Leandro Rodrigo Saad Cruz
IT - Inter Business Tecnologia e Servicos (IB)
http://www.ibnetwork.com.br
http://jakarta.apache.org/ojb
Re: extent aware pathExpressions broken !
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi,
the query i'm thinking of is the following:
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung
FROM
Kategorien A0
INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
INNER JOIN BOOKS A2 ON A0.Kategorie_Nr=A2.Kategorie_Nr
INNER JOIN CDS A3 ON A0.Kategorie_Nr=A3.Kategorie_Nr
WHERE
A1.Kategorie_Nr = 5
or A2.Kategorie_Nr = 5
or A3.Kategorie_Nr = 5
and it seems to work (at least in hsqldb, mysql and sapdb)
so the only problem i have is how to build this sql ....
any ideas or help will be appreciated.
jakob
----- Original Message -----
From: "Jakob Braeuchi" <jb...@gmx.ch>
To: "OJB Developers List" <oj...@jakarta.apache.org>
Cc: "OJB Users List" <oj...@jakarta.apache.org>
Sent: Friday, January 03, 2003 3:04 PM
Subject: extent aware pathExpressions broken !
> hi all,
>
> while playing aoround with extent aware path expressions, i found that the
> current implementation does not work due to caching of sql statements.
>
> i queried for the query i did is :
>
> crit.addEqualTo("allArticlesInGroup.productGroupId", new Integer(5));
> Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);
>
> because 'allArticlesInGroup' points to a class with two extents the 3
> following queries should have been executed (one for Artikel, Books and
CDs)
>
> SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> Kategorien A0 INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
WHERE
> A1.Kategorie_Nr = '5'
>
> SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> Kategorien A0 INNER JOIN Books A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr WHERE
> A1.Kategorie_Nr = '5'
>
> SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM
> Kategorien A0 INNER JOIN CDS A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr WHERE
> A1.Kategorie_Nr = '5'
>
> due to caching of the statement (and manipulation of the original, cached
> query) the 'Artikel' query is executed 3 times.
>
> imo we should look for a better way to support exent aware
pathExpressions.
> even if the above mentioned way would work as expected we would have read
> the same productGroup 3 times although there's only one with id = 5 !.
>
> i'm thinking of a solution where we need only ONE sql including all tables
> of the extents.
>
> any ideas ?
>
> jakob
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>