Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Unique Constraint Check

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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




 
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for sharing..
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic