• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unique Constraint Check

 
Mark Varley
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi - I have a table (Product) which has a property (QuickBook) which allows a Vendor to specify up to 5 products to appear on a special "Quick Book" screen. I want to enforce this rule so when the Vendor is setting up his list of products he receives an error message if he tries to insert a new record or update an existing record which will violate this constraint. I have a Seam app using Hibernate calling a MySQL db with the following code which is called on persist or update of the product. The problem is that I want to exclude the current product from the result list but if it is a new product and the id is null hibernate writes id!=null and the result list returns no matches even if there are already quick book products in the table. I can workaround this with messy null checking logic in my code but I am sure there must be a simpler way of achieving this logic (i.e. show me products in a table except for my current product where this may not yet have been created).

Any help would be very much appreciated - I have trawled the forums and spent the whole morning on this so far!

Thanks,

mark




 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a few doubts here..
1. What is the purpose behind
exclude the current product from the result list

2. Since excluding the current product, it is checked for 5 records, the application will allow 5+1 products for a vendor which is different from the requirement.
3. I am not sure why the hibernate will return no results when the results are present in the table as mentioned below.
and the id is null hibernate writes id!=null and the result list returns no matches even if there are already quick book products in the table


I think a normal query like "Select count(*) from Product where vendor = <getCurrentVendor>" should be enough both in case when you are updating or inserting.
 
Mark Varley
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rahul - thanks for the quick reply. Let me elaborate with two cases, one for an insert, one for an update, where there are already 5 quickbook products in the table...

1) Inserting a new product with quickbook=true: select count(*) from product where vendor=<vendor> returns 5, error message CORRECT BEHAVIOUR

2) Updating an existing product to change update a property e.g. price where quickbook is already true: select count(*) from product where vendor=<vendor> returns 5, error message INCORRECT BEHAVIOUR

See, the insert is fine but when updating a record where quickbook is already true, the list returned by the database will include the product to be updated - I will need to exclude this from the count or will get incorrect error messages.

If you or anyone knows of a simple way around this I would be most grateful.


 
Mark Varley
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This code works however it would be nice if I could do this with SQL though so I could just work with count(*) rather than having to return the whole ResultSet - I would be very interested in any other solutions. Thanks.





 
Mark Varley
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just got the solution from the Hibernate forum:

"from Product where vendor=:vendor and quickBook=true and (id!=:id or :id is null)"



Works perfectly!


 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for sharing..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic