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: [EMAIL PROTECTED]
[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
