Hi, I'm trying to figure what's the best approach to take in the following situation (and please excuse me if my nomenclature is off, I'm still trying to wrap my head around this EJB stuff).
Say I have an entity bean, "Foo", with fields "a", "b", "c", and "d"; and I'd like to enforce the constraint that no two entities may be created that have identical values for fields "a" AND "b". (that is, if I ignored the primary key, "a" and "b" would uniquely identify a "Foo"). I was wondering how to best enforce this constraint. Should I check in Foo's "create" method ... if I did that I'd like to signal that a "Foo" already exists with identical values for "a" and "b", so is it reasonable to throw an application exception from the "create" method (or should I subclass "CreateException"). Or should I do the check in the session bean which is doing the creating?
Anyone have any thoughts on what is the best approach? Have I made any sense?
The primary key class of your bean should take care of the uniqueness.You should override the equals() and hashCode() methods of the PK class to enforce uniqueness.I hope you would have already taken care at the database level by declaring fields corresponding to 'a' and 'b' as composite key.
If you call the create() method on the home interface from the session bean , there you can catch the "DuplicateKeyException(Subclass of CreateException)" and handle accordingly to signal that the bean already exists..
Couldn't you best enforce that unique constraint at the database level? That is, apart from declaring the primary key for that table; you could declare a unique contraint on the combination of columns "a" and "b".
When the container tries to perform an insert; you should receive a CreateException. Your calling code (session bean) could then catch and probably figure out from the message why the exception occurred (caveat: the message is dependent on the database as well as the jdbc driver).
Another solution would be to define a single object finder which accepts fields "a" & "b"; and your calling code would call that finder followed by a create() if the finder failed. Of course, the finder and create() probably need to run in the same transaction.
A third solution is to define "a" & "b" as the primary key via a primary key class. When the calling code now calls the create() method you will receive a DuplicateKeyException (subclass of CreateException) which really narrows down the error. But you probably do not want this since then you'd be using primary keys which have a business meaning.
Thanks Ashoke and Krish for your responses, it's appreciated.
I guess I should have also mentioned that I'm also new to database stuff (I know, big trouble) ... so I didn't realize that I could apply a uniqueness constraint to non-pk columns. Unfortunately, using JBoss and CMP 2.0 I'm not sure if this is going to help. After digging around JBoss's forums it appears that it's not possible to catch the SQLException that would be thrown ... sigh.
I'd really like to have the database do the work for me, but I'd prefer not to have "a" & "b" be a compound primary key (actually I was fibbing, there are 3 columns that need to be unique -- it's an international phone number: country code, area/city code and local number) ... quite a dilemma :-)
Once again, thanks!
Just to followup (i.e., a chronicle of my learning curve) ...
Initially, the database I was using was Hypersonic, which JBoss installs as the default. Unfortunately, I wasn't sure how to access it other than through JBoss (that is, via entity EJB's and the deployment descriptors), so I was unable (or didn't know how) to apply uniqueness constraints to columns, 'a' and 'b'.
Well, I eventually wanted to use PostgreSQL, so I made the switch. Since I had some (limited) knowledge of how to play with PostgreSQL outside of JBoss, this allowed me to fool with the tables. Following Krishnan's first suggestion, I applied a uniqueness constraint to columns 'a' and 'b' and then (using JBoss as the EJB container) created two instances of 'Foo' with identical values for 'a' and 'b'. JBoss threw a "CreateException" with a message identifying the initial cause as an "SQLException". It seems unfortunate to me that "CreateException" doesn't wrap the initial exception, oh well.
A little off topic ... does any know of a good forum for asking general database questions?
It is clear that you need compound primary key. Compound key can consist of two or more fields and the do not have to be of the same type. For example you ca create perfectly correct compound key of NUMBER,VARCHAR2,DATE fields. What is the reason you like to avoid compound keys? You have a soultion but you do not want to accept it?