You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by johne <je...@yahoo.com> on 2008/04/07 07:40:50 UTC
Unexpected result
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 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>
--
View this message in context: http://www.nabble.com/Unexpected-result-tp16533913p16533913.html
Sent from the Apache DB - ObjectRelationalBridge Users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Unexpected result
Posted by johne <je...@yahoo.com>.
That was it! I apologize for such a silly problem. I don't know how I
managed to get away without having to use that method for so long. I owe
you a beer Carlos. I think I owe Armin a couple cases by now!
Regards,
John
--
View this message in context: http://www.nabble.com/Unexpected-result-tp16533913p16558743.html
Sent from the Apache DB - ObjectRelationalBridge Users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Unexpected result
Posted by Armin Waibel <ar...@apache.org>.
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Unexpected result
Posted by Carlos Chávez <cc...@agssa.net>.
Carlos Chávez wrote:
> Hi johne.
>
Ops, forget my email,
I misunderstood your mail.
I thought the "region" can be null.
Cheers,
Carlos Chávez.
> I think you need to set the outer join against the object "region",
> query.setPathOuterJoin("region");
>
> Cheers,
> Carlos Chávez.
>
>
>> 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 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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Unexpected result
Posted by Carlos Chávez <cc...@agssa.net>.
Hi johne.
I think you need to set the outer join against the object "region",
query.setPathOuterJoin("region");
Cheers,
Carlos Chávez.
> 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 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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Unexpected result
Posted by johne <je...@yahoo.com>.
Thank you so much for the responses.
I had never used query.setPathOuterJoin("region"). This is strange
considering how long I have been using OJB and how many queries I have built
with it. This may be the solution. I will give it a shot later.
It is very possible that region can be null inside of the servicelocation
table. There are countries without regions (states/provinces) that still
need to be included in this end result.
We are coming out with a new release of http://jobbbank.com in a couple of
weeks, and this is one of the few remaining bugs that came out in testing.
I will let you know how it turns out.
John
--
View this message in context: http://www.nabble.com/Unexpected-result-tp16533913p16539109.html
Sent from the Apache DB - ObjectRelationalBridge Users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Unexpected result
Posted by johne <je...@yahoo.com>.
Thank you so much for the responses.
I had never used query.setPathOuterJoin("region"). This is strange
considering how long I have been using OJB and how many queries I have built
with it. This may be the solution. I will give it a shot later.
It is very possible that region can be null inside of the servicelocation
table. There are countries without regions (states/provinces) that still
need to be included in this end result.
We are coming out with a new release of http://jobbbank.com in a couple of
weeks, and this is one of the few remaining bugs that came out in testing.
I will let you know how it turns out.
John
--
View this message in context: http://www.nabble.com/Unexpected-result-tp16533913p16539109.html
Sent from the Apache DB - ObjectRelationalBridge Users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org