mysql ejb-ql query with collection comparison
chris_pollentier Apr 29, 2004 4:53 AMHi,
We are moving our project from a postgres database to a mysql database.
I encountered ad problem with one of our finders:
There are two tables: 'Kp' and 'scanner'. The scanner table has a foreign key to the Kp table. I defined a one-to-many relation between scanner to kp
I defined the following ejb-ql to get all Kp records with a scanner record pointing to it:
SELECT DISTINCT OBJECT(o) FROM Kp o WHERE o.countryId = ?1 AND o.scanners IS NOT EMPTY
When I trace the SQL statements of the cmp engine, I see the following in the log file:
2004-04-29 11:36:58,695 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Kp#findByCountryIdAndHasScanner] Executing SQL: SELECT DISTINCT t0_o.kp_pk FROM kp t0_o WHERE (t0_o.country_fk = ? AND t1_o_scanners.scanner_pk IS NOT NULL)
2004-04-29 11:36:58,696 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Kp#findByCountryIdAndHasScanner] Find failed
java.sql.SQLException: General error, message from server: "Unknown table 't1_o_scanners' in where clause"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
In the SQL query that is executed, the table 'scanner' is missing in the FROM part.
Does anyone know if this a bug related to mysql (it worked fine with postgres), or is there an error in my EJQ-QL query ?
jbosscmp-jdbc.xml snippet:
<ejb-relation>
<ejb-relation-name>kp-cmp20-scanner-cmp20</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>scanner-cmp20-has-kp-cmp20</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>kp-cmp20-has-scanner-cmp20</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>kpId</field-name>
<column-name>kp_fk</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>parcel-cmp20-barcode-cmp20</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>barcode-cmp20-has-parcel-cmp20</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>parcel-cmp20-has-barcode-cmp20</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>parcelId</field-name>
<column-name>parcel_fk</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>
ejb-jar snippet:
<ejb-relation >
<ejb-relation-name>parcel-cmp20-barcode-cmp20</ejb-relation-name>
<ejb-relationship-role >
<ejb-relationship-role-name>barcode-cmp20-has-parcel-cmp20</ejb-relationship-role-name>
Many
<relationship-role-source >
<ejb-name>Barcode</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>parcel</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role >
<ejb-relationship-role-name>parcel-cmp20-has-barcode-cmp20</ejb-relationship-role-name>
One
<relationship-role-source >
<ejb-name>Parcel</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>barcodes</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation >
<ejb-relation-name>kp-cmp20-scanner-cmp20</ejb-relation-name>
<ejb-relationship-role >
<ejb-relationship-role-name>scanner-cmp20-has-kp-cmp20</ejb-relationship-role-name>
Many
<relationship-role-source >
<ejb-name>Scanner</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>kp</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role >
<ejb-relationship-role-name>kp-cmp20-has-scanner-cmp20</ejb-relationship-role-name>
One
<relationship-role-source >
<ejb-name>Kp</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>scanners</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>