db-ojb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Armin Waibel <arm...@apache.org>
Subject Re: Unexpected result
Date Mon, 07 Apr 2008 16:01:00 GMT
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


Mime
View raw message