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 Ciprian Herman <ci...@gmail.com> on 2006/06/06 11:46:46 UTC
OJB problem: reference-descriptor name="super"
Hi all!
I'm trying to work with OJB for a project and I think I discovered a bug. Or
I misunderstand something. I have 3 MySQL tables A, B, C with the following
SQL
descriptor:
CREATE TABLE `a` (
`idA` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`idA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `b` (
`idB` int(10) unsigned NOT NULL,
PRIMARY KEY (`idB`),
CONSTRAINT `FK_b_1` FOREIGN KEY (`idB`) REFERENCES `a` (`idA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `c` (
`idC` int(10) unsigned NOT NULL auto_increment,
`idA` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`idC`),
KEY `FK_C_1` (`idA`),
CONSTRAINT `FK_C_1` FOREIGN KEY (`idA`) REFERENCES `a` (`idA`) ON DELETE
CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The relations between these tables are:
A is related 1:n with C (A contains C)
A is related 1:1 with B (B is inherited from A)
So for this relations I have the next repository.xml :
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE descriptor-repository PUBLIC "-//Apache Software Foundation//DTD
OJB Repository//EN" "repository.dtd">
<descriptor-repository version="1.0" isolation-level="read-uncommitted">
<jdbc-connection-descriptor jcd-alias="default"
default-connection="true"
platform="MySQL"
jdbc-level="3.0"
driver="com.p6spy.engine.spy.P6SpyDriver"
protocol="jdbc"
subprotocol="mysql"
dbalias="//localhost:3306/_test"
username="user"
password="******"
eager-release="false"
batch-mode="false"
useAutoCommit="1"
ignoreAutoCommitExceptions="false">
<connection-pool maxActive="21" validationQuery=""/>
</jdbc-connection-descriptor>
<class-descriptor class="src.java.A"
table="A">
<field-descriptor name="idA"
primarykey="true"
column="IDA"
jdbc-type="INTEGER"/>
<collection-descriptor name="bList"
element-class-ref="src.java.B"
proxy="true">
<inverse-foreignkey field-ref="idB"/>
</collection-descriptor>
<collection-descriptor name="cList"
element-class-ref="src.java.C"
proxy="true">
<inverse-foreignkey field-ref="idA"/>
</collection-descriptor>
</class-descriptor>
<class-descriptor class="src.java.B"
table="B">
<field-descriptor name="idB"
primarykey="true"
column="IDB"
jdbc-type="INTEGER"/>
<reference-descriptor name="idBRef"
class-ref="src.java.A"
proxy="false">
<foreignkey field-ref="idB"/>
</reference-descriptor>
<reference-descriptor name="super"
class-ref="src.java.A">
<foreignkey field-ref="idB"/>
</reference-descriptor>
</class-descriptor>
<class-descriptor class="src.java.C" table="C">
<field-descriptor name="idC"
primarykey="true"
column="IDC"
jdbc-type="INTEGER"/>
<field-descriptor name="idA"
nullable="false"
column="IDA"
jdbc-type="INTEGER"/>
<reference-descriptor name="idARef"
class-ref="src.java.A"
proxy="true">
<foreignkey field-ref="idA"/>
</reference-descriptor>
</class-descriptor>
</descriptor-repository>
Now, what I want to do is to get the list of C objects that are contained by
a B object.
PersistenceBroker broker = null;
try
{
broker = PersistenceBrokerFactory.defaultPersistenceBroker();
Criteria criteria = new Criteria();
// class A
criteria.addEqualTo("IDA", "1");
// class B
criteria.addEqualTo("IDB", "1");
Query query = QueryFactory.newQuery(B.class, criteria);
Collection result = broker.getCollectionByQuery(query);
for(Iterator it = result.iterator(); it.hasNext();)
{
B b = (B) it.next();
System.out.println("IDB: " + b.getIdB());
Collection cList = b.getCList();
System.out.println("cList: " + cList);
for(Iterator i = cList.iterator(); i.hasNext();)
{
C c = (C) i.next();
System.out.println("IDC: " + c.getIdC() + " IDA: " +
c.getIdA() );
}
}
}
catch (PersistenceBrokerException e)
{
if (broker != null)
{
broker.abortTransaction();
System.out.println("Transaction aborted");
}
System.out.println(e.toString());
}
Everything is OK until I actually try to access the list Iterator it =
result.iterator();. App crash because of an SQL Error:
IDB: 1
cList: org.apache.ojb.broker.core.proxy.ListProxyDefaultImpl@12452e8
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR:
* SQLException during execution of sql-statement:
* sql statement was 'SELECT A0.IDA,A1.IDB,CASE WHEN A1.IDB IS NOT NULL THEN
'src.java.B' ELSE 'src.java.A' END AS OJB_CLAZZ FROM A A0 LEFT OUTER JOIN B
A1 ON
A0.IDB=A1.IDB WHERE A0.IDA = ?'
* Exception message is [Unknown column 'A0.IDB' in 'on clause']
* Vendor error code [1054]
* SQL state code [42S22]
* Target class is 'src.java.A'
* PK of the target object is [idA]
* The root stack trace is -->
* java.sql.SQLException: Unknown column 'A0.IDB' in 'on clause'
I don't know why is ojb trying to do that query. Probably to find if he
delas with an A or a B object.
I've used p6spy and I noticed that OJB is doing all the right queries that
are necessary but the last one doesn't return a result set:
1149584392093|0|0|statement|SELECT A0.IDB FROM B A0 INNER JOIN A A1 ON
A0.IDB=A1.IDA WHERE (IDA = ?) AND IDB = ?|SELECT A0.IDB FROM B A0 INNER JOIN
A A1 ON
A0.IDB=A1.IDA WHERE (IDA = '1') AND IDB = '1'
1149584392125|-1||resultset|SELECT A0.IDB FROM B A0 INNER JOIN A A1 ON
A0.IDB=A1.IDA WHERE (IDA = '1') AND IDB = '1'|IDB = 1
this is ok
1149584392125|0|0|statement|SELECT A0.IDC,A0.IDA FROM C A0 WHERE A0.IDA =
?|SELECT A0.IDC,A0.IDA FROM C A0 WHERE A0.IDA = '1'
this is what I need but I don't get the result
1149584392140|0|0|statement|SELECT A0.IDA,A1.IDB,CASE WHEN A1.IDB IS NOT
NULL THEN 'src.java.B' ELSE 'src.java.A' END AS OJB_CLAZZ FROM A A0 LEFT
OUTER JOIN
B A1 ON A0.IDB=A1.IDB WHERE A0.IDA = ?|SELECT A0.IDA,A1.IDB,CASE WHEN
A1.IDBIS NOT NULL THEN '
src.java.B' ELSE 'src.java.A' END AS OJB_CLAZZ FROM A A0 LEFT
OUTER JOIN B A1 ON A0.IDB=A1.IDB WHERE A0.IDA = '1'
I don't know for what is doing this
If some one know what I'm doing wrong please let me help me.
Thank you,
Ciprian HERMAN
Re: OJB problem: reference-descriptor name="super"
Posted by Ciprian Herman <ci...@gmail.com>.
Hi again!
I just want to use OJB on this database structure and just seem to crash. It
is canned of urgent for me. Maybe I just not approach wheel the problem, but
if somebody have some suggestion or a solution to apply OJB over described
tables please notify me.
My approach works except that I can't use the collection from a class if in
the repository file this class is referred by a super attribute from another
class descriptor.
On 6/6/06, Ciprian Herman <ci...@gmail.com> wrote:
>
> Hi all!
>
> I'm trying to work with OJB for a project and I think I discovered a bug.
> Or I misunderstand something. I have 3 MySQL tables A, B, C with the
> following SQL
>
> descriptor:
>
> CREATE TABLE `a` (
> `idA` int(10) unsigned NOT NULL auto_increment,
> PRIMARY KEY (`idA`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> CREATE TABLE `b` (
> `idB` int(10) unsigned NOT NULL,
> PRIMARY KEY (`idB`),
> CONSTRAINT `FK_b_1` FOREIGN KEY (`idB`) REFERENCES `a` (`idA`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> CREATE TABLE `c` (
> `idC` int(10) unsigned NOT NULL auto_increment,
> `idA` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`idC`),
> KEY `FK_C_1` (`idA`),
> CONSTRAINT `FK_C_1` FOREIGN KEY (`idA`) REFERENCES `a` (`idA`) ON DELETE
> CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
>
> The relations between these tables are:
> A is related 1:n with C (A contains C)
> A is related 1:1 with B (B is inherited from A)
>
> So for this relations I have the next repository.xml :
>
> <?xml version="1.0" encoding="UTF-8"?>
>
> <!DOCTYPE descriptor-repository PUBLIC "-//Apache Software Foundation//DTD
> OJB Repository//EN" " repository.dtd">
>
> <descriptor-repository version="1.0" isolation-level="read-uncommitted">
>
> <jdbc-connection-descriptor jcd-alias="default"
> default-connection="true"
> platform="MySQL"
> jdbc-level="3.0"
> driver="com.p6spy.engine.spy.P6SpyDriver"
> protocol="jdbc"
> subprotocol="mysql"
> dbalias="//localhost:3306/_test"
> username="user"
> password="******"
> eager-release="false"
> batch-mode="false"
> useAutoCommit="1"
> ignoreAutoCommitExceptions="false">
> <connection-pool maxActive="21" validationQuery=""/>
> </jdbc-connection-descriptor>
>
> <class-descriptor class="src.java.A"
> table="A">
> <field-descriptor name="idA"
> primarykey="true"
> column="IDA"
> jdbc-type="INTEGER"/>
> <collection-descriptor name="bList"
> element-class-ref="src.java.B"
> proxy="true">
> <inverse-foreignkey field-ref="idB"/>
> </collection-descriptor>
> <collection-descriptor name="cList"
> element-class-ref="src.java.C"
> proxy="true">
> <inverse-foreignkey field-ref="idA"/>
> </collection-descriptor>
> </class-descriptor>
>
> <class-descriptor class="src.java.B"
> table="B">
> <field-descriptor name="idB"
> primarykey="true"
> column="IDB"
> jdbc-type="INTEGER"/>
> <reference-descriptor name="idBRef"
> class-ref="src.java.A"
> proxy="false">
> <foreignkey field-ref="idB"/>
> </reference-descriptor>
> <reference-descriptor name="super"
> class-ref="src.java.A">
> <foreignkey field-ref="idB"/>
> </reference-descriptor>
> </class-descriptor>
>
> <class-descriptor class="src.java.C" table="C">
> <field-descriptor name="idC"
> primarykey="true"
> column="IDC"
> jdbc-type="INTEGER"/>
> <field-descriptor name="idA"
> nullable="false"
> column="IDA"
> jdbc-type="INTEGER"/>
> <reference-descriptor name="idARef"
> class-ref=" src.java.A"
> proxy="true">
> <foreignkey field-ref="idA"/>
> </reference-descriptor>
> </class-descriptor>
>
> </descriptor-repository>
>
> Now, what I want to do is to get the list of C objects that are contained
> by a B object.
>
>
> PersistenceBroker broker = null;
> try
> {
> broker = PersistenceBrokerFactory.defaultPersistenceBroker ();
> Criteria criteria = new Criteria();
> // class A
> criteria.addEqualTo("IDA", "1");
> // class B
> criteria.addEqualTo("IDB", "1");
> Query query = QueryFactory.newQuery(B.class, criteria);
> Collection result = broker.getCollectionByQuery(query);
> for(Iterator it = result.iterator(); it.hasNext();)
> {
> B b = (B) it.next();
> System.out.println("IDB: " + b.getIdB());
> Collection cList = b.getCList();
> System.out.println("cList: " + cList);
>
> for(Iterator i = cList.iterator(); i.hasNext();)
> {
> C c = (C) i.next();
> System.out.println("IDC: " + c.getIdC () + " IDA: " +
> c.getIdA() );
> }
>
> }
> }
> catch (PersistenceBrokerException e)
> {
> if (broker != null)
> {
> broker.abortTransaction();
> System.out.println("Transaction aborted");
> }
> System.out.println(e.toString());
> }
>
> Everything is OK until I actually try to access the list Iterator it =
> result.iterator();. App crash because of an SQL Error:
>
> IDB: 1
> cList: org.apache.ojb.broker.core.proxy.ListProxyDefaultImpl@12452e8
> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR:
> * SQLException during execution of sql-statement:
> * sql statement was 'SELECT A0.IDA,A1.IDB,CASE WHEN A1.IDB IS NOT NULL
> THEN 'src.java.B' ELSE 'src.java.A' END AS OJB_CLAZZ FROM A A0 LEFT OUTER
> JOIN B A1 ON
>
> A0.IDB=A1.IDB WHERE A0.IDA = ?'
> * Exception message is [Unknown column ' A0.IDB' in 'on clause']
> * Vendor error code [1054]
> * SQL state code [42S22]
> * Target class is 'src.java.A'
> * PK of the target object is [idA]
> * The root stack trace is -->
> * java.sql.SQLException: Unknown column ' A0.IDB' in 'on clause'
>
> I don't know why is ojb trying to do that query. Probably to find if he
> delas with an A or a B object.
> I've used p6spy and I noticed that OJB is doing all the right queries that
> are necessary but the last one doesn't return a result set:
>
> 1149584392093|0|0|statement|SELECT A0.IDB FROM B A0 INNER JOIN A A1 ON
> A0.IDB=A1.IDA WHERE (IDA = ?) AND IDB = ?|SELECT A0.IDB FROM B A0 INNER
> JOIN A A1 ON
>
> A0.IDB=A1.IDA WHERE (IDA = '1') AND IDB = '1'
> 1149584392125|-1||resultset|SELECT A0.IDB FROM B A0 INNER JOIN A A1 ON
> A0.IDB=A1.IDA WHERE (IDA = '1') AND IDB = '1'|IDB = 1
> this is ok
> 1149584392125|0|0|statement|SELECT A0.IDC,A0.IDA FROM C A0 WHERE A0.IDA =
> ?|SELECT A0.IDC,A0.IDA FROM C A0 WHERE A0.IDA = '1'
> this is what I need but I don't get the result
> 1149584392140|0|0|statement|SELECT A0.IDA,A1.IDB,CASE WHEN A1.IDB IS NOT
> NULL THEN 'src.java.B' ELSE 'src.java.A' END AS OJB_CLAZZ FROM A A0 LEFT
> OUTER JOIN
>
> B A1 ON A0.IDB=A1.IDB WHERE A0.IDA = ?|SELECT A0.IDA,A1.IDB,CASE WHEN
> A1.IDB IS NOT NULL THEN 'src.java.B' ELSE 'src.java.A' END AS OJB_CLAZZ
> FROM A A0 LEFT
>
> OUTER JOIN B A1 ON A0.IDB=A1.IDB WHERE A0.IDA = '1'
> I don't know for what is doing this
>
> If some one know what I'm doing wrong please let me help me.
>
> Thank you,
> Ciprian HERMAN
>
>