JR,
I've done something similar to generate 15 character order numbers. If
you can deal with the account number being alpha numeric, it will work
well.
It takes the generated primary key, and base36 encodes it. After I
encode the value, I append a date suffix to it. This put some context
into the value so you can easily see when the account was created. The
other advantage to this approach is that you can decode the value at a
later point and still fetch the account by primary key (which I would
guess be indexed).
So if you create a new account object, let's say the primary key is
12234.
The encoding routine would translate that into:
000009FU-011505
Later, if you need to fetch this account by account number you can
decode it back to the value 12234, then fetch the account by ObjectId.
This insures that your account number values will be unique and will
still work when your pk sequences are huge. I think this is bit more
solid than the +3 thing.
Here's the gotcha, I'm successfully using this with Hibernate right
now. I'm not entirely 100% sure that Cayenne assigns its primary keys
prior to calling validateForInsert(), which is where I would put this
code if I was doing it in a Cayenne project. Maybe Andrus could
confirm if the ObjectId is created before validateForInsert() is
called. If it isn't, maybe he can offer a more appropriate place for
it.
If you're interested I can send you a little test class that does all
the stuff described above.
Cheers,
e.
On Jan 15, 2005, at 3:24 PM, JR Ruggentaler wrote:
> I would like to generate a unique account number using Cayenne and
> MySQL. This would be similar to the Cayenne primary key generation and
> as SAFE. The account number would be a BIGINT MySQL type. How would I
> Model this and how would I get the next unique account number using
> Cayenne APIs? The account number would be similar to a credit card
> number (16 digits) and I DON'T want to use the Entity's primary key as
> the account number.
>
> My idea of how this would work in SQL is:
>
> Begin transaction:
> UPDATE tablename SET NEXT_ACCOUNT_NUM=NEXT_ACCOUNT_NUM + 3;
> SELECT NEXT_ACCOUNT_NUM from tablename;
> End transaction:
>
> J.R.
>
This archive was generated by hypermail 2.0.0 : Sat Jan 15 2005 - 16:05:10 EST