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