Hello,
I have three tables one called CUSTOMER, one called ACCOUNT and one called
OPERATION.
The "Client" class is mapped to the CUSTOMER table.
The "Compte" class is mapped to the ACCOUNT table.
The "Operation" class is mapped to the OPERATION table.
I want to retrieve all customers with this criteria :
- The attribute "solde" of an account class equal 1000
- The attibute "montant" of an operation class equal to -123.45
I am using the xml at the end of this mail.
I am using OJB 0.9.6, the code is
broker.beginTransaction();
Criteria criteres = new Criteria();
criteres.addEqualTo("comptes.solde",new
Double(1000));
criteres.addEqualTo("comptes.operations.montant",new Double(-123.45));
Query requete =
QueryFactory.newQuery(Client.class,criteres);
Collection listeClients = (Collection)
broker.getCollectionByQuery(requete);
Iterator iterate = listeClients.iterator();
while(iterate.hasNext()){
Client client = (Client)iterate.next();
System.out.println("Client | ID : " +
client.getId() + " - NOM : "
+ client.getNom() + " - PRENOM : " +
client.getPrenom());
}
broker.commitTransaction();
To the execution following exception is raised.
Have you an idea ?, please.
02/10/2002|10|0|statement|SELECT
A0.CUS_LOGIN,A0.CUS_PASSWORD,A0.CUS_NAME,A0.CUS_ID,A0.CUS_FIRST_NAME FROM
CUSTOMER A0 INNER JOIN (ACCOUNT A1 INNER JOIN null A2 ON
A1.ACC_ID=A2.ACC_ID) ON A0.CUS_ID=A1.CUS_ID WHERE (A1.ACC_BALANCE = ? ) AND
comptes.operations.montant = ? |SELECT
A0.CUS_LOGIN,A0.CUS_PASSWORD,A0.CUS_NAME,A0.CUS_ID,A0.CUS_FIRST_NAME FROM
CUSTOMER A0 INNER JOIN (ACCOUNT A1 INNER JOIN null A2 ON
A1.ACC_ID=A2.ACC_ID) ON A0.CUS_ID=A1.CUS_ID WHERE (A1.ACC_BALANCE =
'1000.0' ) AND comptes.operations.montant = '-123.45'
[org.apache.ojb.broker.accesslayer.JdbcAccess] ERROR: SQLException during
the execution of the query (for a aston.banque.gestionClient.metier.Client):
java.sql.SQLException: Incorrect syntax near the keyword 'null'.
java.sql.SQLException: Incorrect syntax near the keyword 'null'.
java.sql.SQLException: java.sql.SQLException: Incorrect syntax near the
keyword 'null'.
at com.ashna.jturbo.driver.w.a(w.java)
at com.ashna.jturbo.driver.w.executeQuery(w.java)
at com.ashna.jturbo.driver.x.d(x.java)
at com.ashna.jturbo.driver.x.execute(x.java)
at com.ashna.jturbo.driver.x.executeQuery(x.java)
at
com.p6spy.engine.spy.P6PreparedStatement.executeQuery(P6PreparedStatement.ja
va:192)
at org.apache.ojb.broker.accesslayer.JdbcAccess.executeQuery(Unknown
Source)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown
Source)
at
org.apache.ojb.broker.singlevm.RsIteratorFactoryImpl.createRsIterator(Unknow
n Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getRsIteratorFromQuery(
Unknown Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getIteratorFromQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at TestOJB.listerClientCriteres(TestOJB.java:167)
at TestOJB.main(TestOJB.java:191)
<!-- Definitions for Client -->
<class-descriptor
class="aston.banque.gestionClient.metier.Client"
table="CUSTOMER">
<field-descriptor id="1"
name="id"
column="CUS_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="nom"
column="CUS_NAME"
jdbc-type="VARCHAR"
/>
<field-descriptor id="3"
name="prenom"
column="CUS_FIRST_NAME"
jdbc-type="VARCHAR"
/>
<field-descriptor id="4"
name="password"
column="CUS_PASSWORD"
jdbc-type="VARCHAR"
/>
<field-descriptor id="5"
name="login"
column="CUS_LOGIN"
jdbc-type="VARCHAR"
/>
<collection-descriptor
name="comptes"
element-class-ref="aston.banque.gestionCompte.metier.Compte"
auto-retrieve="false"
auto-update="false"
orderby="ACC_BALANCE"
sort="DESC"
>
<inverse-foreignkey field-id-ref="4"/>
</collection-descriptor>
</class-descriptor>
<!-- Definitions for Compte -->
<class-descriptor
class="aston.banque.gestionCompte.metier.Compte"
table="ACCOUNT">
<field-descriptor id="1"
name="id"
column="ACC_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="solde"
column="ACC_BALANCE"
jdbc-type="DOUBLE"
/>
<field-descriptor id="3"
name="decouvert"
column="ACC_FALL"
jdbc-type="DOUBLE"
/>
<field-descriptor id="4"
name="clientID"
column="CUS_ID"
jdbc-type="INTEGER"
/>
<field-descriptor id="5"
name="typeID"
column="ACT_ID"
jdbc-type="INTEGER"
/>
<reference-descriptor
name="type"
class-ref="aston.banque.gestionCompte.metier.TypeCompte"
>
<foreignkey field-id-ref="5"/>
</reference-descriptor>
<collection-descriptor
name="operations"
element-class-ref="aston.banque.gestionCompte.metier.Operation"
auto-retrieve="false"
auto-update="true"
orderby="OPE_DATE"
sort="DESC"
>
<inverse-foreignkey field-id-ref="2"/>
</collection-descriptor>
</class-descriptor>
<!-- Definitions for TypeCompte -->
<class-descriptor
class="aston.banque.gestionCompte.metier.TypeCompte"
table="ACCOUNT_TYPE">
<field-descriptor id="1"
name="id"
column="ACT_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="libelle"
column="ACT_LABEL"
jdbc-type="VARCHAR"
/>
<field-descriptor id="3"
name="code"
column="ACT_CODE"
jdbc-type="VARCHAR"
/>
</class-descriptor>
<!-- Definitions for extent aston.banque.gestionCompte.metier.Operation -->
<class-descriptor class="aston.banque.gestionCompte.metier.Operation">
<extent-class
class-ref="aston.banque.gestionCompte.metier.OperationCredit" />
<extent-class
class-ref="aston.banque.gestionCompte.metier.OperationDebit" />
</class-descriptor>
<!-- Definitions for aston.banque.gestionCompte.metier.OperationCredit -->
<class-descriptor
class="aston.banque.gestionCompte.metier.OperationCredit"
table="OPERATION"
>
<field-descriptor id="1"
name="id"
column="OPE_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="compteId"
column="ACC_ID"
jdbc-type="INTEGER"
/>
<field-descriptor id="3"
name="libelle"
column="OPE_LABEL"
jdbc-type="VARCHAR"
/>
<field-descriptor id="4"
name="montant"
column="OPE_AMOUNT"
jdbc-type="DOUBLE"
/>
<field-descriptor id="5"
name="date"
column="OPE_DATE"
jdbc-type="DATE"
conversion="aston.solution.jdo.converters.DateToDateSQL"
/>
<field-descriptor id="6"
name="ojbConcreteClass"
column="CLASS_NAME"
jdbc-type="VARCHAR"
/>
</class-descriptor>
<!-- Definitions for aston.banque.gestionCompte.metier.OperationDebit -->
<class-descriptor
class="aston.banque.gestionCompte.metier.OperationDebit"
table="OPERATION"
>
<field-descriptor id="1"
name="id"
column="OPE_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="compteId"
column="ACC_ID"
jdbc-type="INTEGER"
/>
<field-descriptor id="3"
name="libelle"
column="OPE_LABEL"
jdbc-type="VARCHAR"
/>
<field-descriptor id="4"
name="montant"
column="OPE_AMOUNT"
jdbc-type="DOUBLE"
/>
<field-descriptor id="5"
name="date"
column="OPE_DATE"
jdbc-type="DATE"
conversion="aston.solution.jdo.converters.DateToDateSQL"
/>
<field-descriptor id="6"
name="ojbConcreteClass"
column="CLASS_NAME"
jdbc-type="VARCHAR"
/>
</class-descriptor>
