Hi Joe,
Depends on if you need incrementally unique identifiers, or if you
need just need a unique number.
I have situations where I need unique "transaction ids", but they
don't have to be ordered in any particular manner.
I use java's random number generation facilities to generate a random
number, then attempt to select a datarow from the database with that
number; if I successfully select one, the number isn't unique and the
process is repeated until I get a unique number. I use 9 digit
numbers, so the likelihood of ever generating the same number twice is
very small, and in practice, the loop never gets executed twice.
Robert
On Dec 27, 2007, at 12/2712:26 PM , Joe Baldwin wrote:
This is an upper level design question associated with how to best
generate a unique value using Cayenne and MySQL.
Step 1:
I have an existent Entity with a primary key (oid). Per the Cayenne-
recommended best practices I am avoiding direct access of this
primary key and have created another attribute (orderID) that must
be unique. So upon doing my Cayenne homework I read that the
standard behavior in the DBMS model and the ORM model is to support
only one auto-generated column and this is typically the PK column
not related to my data abstraction.
Step 2:
So then I thought, well I will just use the MySQL "MAX()" function,
find the max value, increment and voila I have generate my own
unique number. When I tried to implement this design I ran into
problems with SQLTemplate query attempting to bind the result of
MAX(orderID) to an existent data object.
Step 3:
I guess I could always execute a "select *" on the entity and find
the max attribute-value with a "for" loop, but that seems kind of
Neanderthal. OK, so I am now thinking someone must have solved this
sort of design issue before I bumped into it. What is the best way
to solve this issue using Cayenne design patterns?
Thanks,
Joe
