RE: [OJB-developers] Field descriptor order requirement and primary keys
Brought to you by:
thma
Menu
▾
▴
RE: [OJB-developers] Field descriptor order requirement and primary keys
|
From: John F. <jfr...@so...> - 2002-04-03 16:45:02
|
Thank you for your detailed reply Thomas!
However, there is I believe definitely a bug here, but I'm not
communicating it effectively so I created a simple test case based on
your example showing the problem. All the necessary files are attached
in the zip.
When you select all the products that have a value of 1 you should get
these 3:
Cars BMW 34000 1 BMW 330ci 1
Cars Volvo 23000 2 Volvo 990 1
Boats Swan 2500000 4 55 foot 1
But when you use OJB you don't. You get:
Product[id=3D1,name=3DBMW,price=3D34000,stock=3D1category=3DCars]
Product[id=3D1,name=3DBMW,price=3D34000,stock=3D1category=3DCars]
Product[id=3D4,name=3DSwan,price=3D2500000,stock=3D1category=3DBoats]
When I dug into this using my debugger, I found that the snippet of code
in RsIterator.java was the problem (or appeared to be - perhaps the root
problem lies elsewhere).
Hope this helps illustrate the problem with the code that I'm seeing.
Just trying to help make OJB even better!
I'm using 0.8.361, but I uncommented some of the logging stuff in the
JdbcAccess just to get more info on what was going on.
SQL Server 2000 w/ SP3 and NetDirect JSQLConnect JDBC drivers.
Regards,
- John
-----Original Message-----
From: Thomas Mahler [mailto:tho...@ho...]
Sent: Wednesday, April 03, 2002 5:13 AM
To: John Freeborg
Cc: Obj...@li...
Subject: Re: [OJB-developers] Field descriptor order requirement and
primary keys
Hi John,
John Freeborg wrote:
> I guess I'm confused.
>=20
> In my existing database schema, the primary key column(s) are not
always
> the first ones in the table.
No problem.
>=20
> Also, I sometimes have "extra" columns in a table that I don't want
OJB
> to do anything with so they aren't even in the repository.xml as it
> would just slow things down more.=20
No problem.
> Is this allowed as long as those
> columns have default values to handle any sql inserts OJB would
perform?
>=20
OJB won't touch these columns, as it does not know anything about them!
> With that background, what should I be setting my id=3D"" attributes =
of
> the FieldDescriptor's to? They can't possibly be the order of the
> columns in the table because then the one with
> <PrimaryKey>true</PrimaryKey> will not have id=3D"1" as it is the 4th
> column in the table.
>=20
Say we have this table:
CREATE TABLE PRODUCT (
NAME CHAR(100),
PRICE DOUBLE,
ID INT PRIMARY KEY,
HIDDEN VARCHAR(1000),
STOCK INT
)
This is the persistent class:
public class Product
{
private int id;
protected String name;
protected double price;
// column HIDDEN is not mapped to any attribute
protected int stock;
}
This is the ClassDescriptor:
<ClassDescriptor id=3D"product">
<class.name>tutorial.Product</class.name>
<table.name>PRODUCT</table.name>
<FieldDescriptor id=3D"1">
<field.name>name</field.name>
<column.name>NAME</column.name>
<jdbc_type>CHAR</jdbc_type>
</FieldDescriptor>
<FieldDescriptor id=3D"2">
<field.name>price</field.name>
<column.name>PRICE</column.name>
<jdbc_type>DOUBLE</jdbc_type>
</FieldDescriptor>
<FieldDescriptor id=3D"3">
<field.name>id</field.name>
<column.name>ID</column.name>
<jdbc_type>INT</jdbc_type>
<PrimaryKey>true</PrimaryKey>
<autoincrement>true</autoincrement>
</FieldDescriptor>
<FieldDescriptor id=3D"4">
<field.name>stock</field.name>
<column.name>STOCK</column.name>
<jdbc_type>INT</jdbc_type>
</FieldDescriptor>
</ClassDescriptor>
The ids of the FieldDescriptors correspond directly to the *order* of=20
the mapped table columns. E.g. the stock column is *not* the fourth=20
column of the table. but it is the fourth *mapped* column.
>>From the RsIterator code snippet below, there is an assumption that
the
> primary keys are always first in the m_row member.=20
this would be an uneccessary restriction!
> And the ordering of
> the m_row member seems to correspond to the id=3D"" numbers from the
field
> descriptor.
That's right
>=20
> So, my assertion is that if you have a field descriptor with
> <PrimaryKey>true</PrimaryKey> and it is not id=3D"1" then OJB doesn't
> work. =20
As long as you maintain the order of the mapped columns in the FK ids=20
OJB does work!
> And by extension if you had a compound primary key with multiple
> field descriptors having <PrimaryKey> elements, they would need to be
1,
> 2, 3, etc.
they might as well be 4, 17, 23 !
> Is that just the way it is designed? I don't see this documented
> anywhere, but I may have missed it of course.
>=20
I thought it was documented somewhere, but did not find it :-(
> If this is true, I'll volunteer to add some checking of this during
the
> parsing of repository.xml as I think this is a pretty error prone part
> of mapping.
>=20
Maybe it would be the best thing to eliminate the id based lookup of=20
columns as there are several things like freeform SQL queries that don't
work well with this approach.
cu,
Thomas
> Thanks,
> - John
>=20
>=20
> -----Original Message-----
> From: Thomas Mahler [mailto:tho...@ho...]
> Sent: Tuesday, April 02, 2002 2:58 PM
> To: John Freeborg
> Cc: Obj...@li...
> Subject: Re: [OJB-developers] Field descriptor order requirement and
> primary keys
>=20
> Hi John,
>=20
> FieldDescriptor need a numeric ID. The Ids must ordered ascending=20
> according to the corresponding columns in the database table.
>=20
> The physical position in the xml repository is not important.
>=20
> HTH,
>=20
> Thomas
>=20
> There is some code that
>=20
> John Freeborg wrote:
>=20
>=20
>>Does OJB have a requirement that in the repository.xml file, all
>>
> primary
>=20
>>key field descriptors must be first inside a class descriptor?
>>
>>I don't have it that way (my primary key field descriptor happened to
>>
> be
>=20
>>number 4, not 1). After scratching my head a lot on why my query for
>>
> a
>=20
>>collection looked so strange, I hauled out BugSeeker and found the
>>following code marked with ---> below that seems to assume a certain
>>primary key field descriptor order. Interestingly the line above that
>>is commented out doesn't appear to have this problem.
>>
>>Is this an unintended side effect of the optimization? Is this
>>
> broken?
>=20
>>Thanks,
>> - John
>>
>>
>>>From RsIterator.java in 0.8.361:
>>
>> protected Identity getIdentityFromResultSet() throws
>>PersistenceBrokerException
>> {
>> try
>> {
>> // fill primary key values from Resultset
>> FieldDescriptor fld;
>> FieldDescriptor[] pkFields =3D m_mif.getPkFields();
>> Object[] pkValues =3D new Object[pkFields.length];
>>
>> for (int i =3D 0; i < pkFields.length; i++)
>> {
>> fld =3D pkFields[i];
>> //pkValues[i] =3D JdbcAccess.getObjectFromColumn(m_rs,
>>fld);
>>---> pkValues[i] =3D m_row[i];
>> }
>> // return identity object build up from primary keys
>> return new Identity(getExtentClass(), pkValues);
>> }
>> catch (Throwable t)
>> {
>> logger.error(t);
>> throw new PersistenceBrokerException(t);
>> }
>> }
|
