Hi,
johne wrote:
Hey all,
Been a while since I had need to post. I am looking forward to the new
release as I am still on 1.0.3. Thank you for all of the hard work Armin.
Using 1.0.3, I am getting an unexpected result with one query using the PB.
I am using this query:
QueryByCriteria query = new QueryByCriteria(objectClass);
query.addOrderByAscending("productGroupId");
query.addOrderByAscending("country.name");
query.addOrderByAscending("region.name");
For this query, it is valid to have region names that are null. Despite
this, I would want those records to be included in the result set, but they
are not.
I setup a test similar to yours using latest from SVN (OJB_1_0_RELEASE
branch). The test first insert two Location instances with Region and
Country objects (allow OJB to cascade insert referenced objects). One
Region object has set name to NULL. The test looks like this:
String tmp = ojbTestMethodIdentifier();
String id_1 = tmp + "_1";
String id_2 = tmp + "_2";
Country cou_1 = new Country();
cou_1.setCountryId(id_1);
Region reg_1 = new Region();
reg_1.setCountryId(id_1);
reg_1.setRegionId("r1");
reg_1.setName(null);
Location loc_1 = new Location();
loc_1.setCountry(cou_1);
loc_1.setRegion(reg_1);
Country cou_2 = new Country();
cou_2.setCountryId(id_2);
cou_2.setName("cou_2");
Region reg_2 = new Region();
reg_2.setCountryId(id_2);
reg_2.setRegionId("r1");
reg_2.setName("reg_2");
Location loc_2 = new Location();
loc_2.setCountry(cou_2);
loc_2.setRegion(reg_2);
broker.beginTransaction();
broker.store(loc_1);
broker.store(loc_2);
broker.commitTransaction();
Criteria c = new Criteria();
QueryByCriteria query = QueryFactory.newQuery(Location.class, c, true);
query.addOrderByAscending("productGroupId");
query.addOrderByAscending("country.name");
query.addOrderByAscending("region.name");
Collection result = broker.getCollectionByQuery(query);
assertEquals(2, result.size());
The test pass!
The generated SQL:
INSERT INTO country_ (countryid_,name_)
VALUES ('_testQueryOrderBy_1207583603109__1','')
INSERT INTO region_ (countryid_,regionid_,name_)
VALUES ('_testQueryOrderBy_1207583603109__1','r1','')
INSERT INTO servicelocation_ (id_,productgroupid_,countryid_,regionid_)
VALUES ('200001','','_testQueryOrderBy_1207583603109__1','r1')
INSERT INTO country_ (countryid_,name_)
VALUES ('_testQueryOrderBy_1207583603109__2','cou_2')
INSERT INTO region_ (countryid_,regionid_,name_)
VALUES ('_testQueryOrderBy_1207583603109__2','r1','reg_2')
INSERT INTO servicelocation_ (id_,productgroupid_,countryid_,regionid_)
VALUES ('200002','','_testQueryOrderBy_1207583603109__2','r1')
SELECT DISTINCT A0.id_,A0.productgroupid_,A0.countryid_,A0.regionid_,
A1.name_ as ojb_col_5,A2.name_ as ojb_col_6 FROM servicelocation_ A0
INNER JOIN country_ A1 ON A0.countryid_=A1.countryid_
INNER JOIN region_ A2 ON A0.countryid_=A2.countryid_ AND
A0.regionid_=A2.regionid_
ORDER BY 2,5,6
So the issue seems to be fixed in upcoming OJB_1.0.5 or you are doing
something wrong (with OJB 1.0.3). Is column 'countryid' and 'regionid'
correctly set in table servicelocation?
You can trace generated SQL with P6Spy (included in OJB)
http://db.apache.org/ojb/docu/faq.html#traceProfileSQL
or log it by database.
regards,
Armin
I am using this mapping:
<!-- Definitions for the ServiceLocationVO object -->
<class-descriptor
class="com.jmjmedia.sp.model.service.activelocation.ServiceLocationVO"
table="servicelocation">
<field-descriptor
name="slId"
column="slid"
jdbc-type="BIGINT"
primarykey="true"
autoincrement="true"
/>
<field-descriptor
name="productGroupId"
column="productgroupid"
jdbc-type="INTEGER"
/>
<field-descriptor
name="countryId"
column="countryid"
jdbc-type="CHAR"
/>
<field-descriptor
name="regionId"
column="regionid"
jdbc-type="CHAR"
/>
<reference-descriptor
name="country"
class-ref="com.jmjmedia.sp.model.places.CountryVO"
auto-retrieve="true"
auto-update="none"
auto-delete="none"
proxy="false">
<foreignkey field-ref="countryId"/>
</reference-descriptor>
<reference-descriptor
name="region"
class-ref="com.jmjmedia.sp.model.places.RegionVO"
auto-retrieve="true"
auto-update="none"
auto-delete="none"
proxy="false">
<foreignkey field-ref="countryId"/>
<foreignkey field-ref="regionId"/>
</reference-descriptor>
</class-descriptor>
<!-- Definitions for the Country object -->
<class-descriptor class="com.jmjmedia.sp.model.places.CountryVO"
table="country">
<!-- -1 within a per class cache forces the cache to stay in memory. -->
<object-cache
class="org.apache.ojb.broker.cache.ObjectCacheDefaultImpl">
<attribute attribute-name="timeout" attribute-value="-1" />
</object-cache>
<field-descriptor name="countryId" column="ccode" jdbc-type="CHAR"
primarykey="true" />
<field-descriptor name="name" column="cname" jdbc-type="VARCHAR" />
</class-descriptor>
<!-- Definitions for the Region object -->
<class-descriptor class="com.jmjmedia.sp.model.places.RegionVO"
table="region">
<!-- -1 within a per class cache forces the cache to stay in memory. -->
<object-cache
class="org.apache.ojb.broker.cache.ObjectCacheDefaultImpl">
<attribute attribute-name="timeout" attribute-value="-1" />
</object-cache>
<field-descriptor name="countryId" column="r_ccode" jdbc-type="CHAR"
primarykey="true" />
<field-descriptor name="regionId" column="rcode" jdbc-type="CHAR"
primarykey="true" />
<field-descriptor name="name" column="rname" jdbc-type="VARCHAR" />
</class-descriptor>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
