Unique Constraint Check

mgvarley · **Joined:** Wed Aug 13, 2008 10:30 am **Posts:** 4

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

Code:

public boolean isValidQuickBook() {
    boolean ok = true;
    List<Product> matches = getEntityManager()
        .createQuery(
            "from Product where vendor=:vendor and quickBook=true and id!=:id").setParameter(
            "vendor", getCurrentVendor()).setParameter(
            "id", getCurrentProduct().getId()).getResultList();

    if (matches.size() > 5) {
        ok = false;
        statusMessages.add(Severity.ERROR,
            "You already have the maximum of 5 quick book products");
    }
    return ok;
}

Hibernate version: 3.2.4

Name and version of the database you are using: MySQL 5.0

The generated SQL (show_sql=true):
select
product0_.id as id5_,
product0_.created as created5_,
product0_.updated as updated5_,
product0_.version as version5_,
product0_.commission_rate as commission5_5_,
product0_.default_availability as default6_5_,
product0_.description as descript7_5_,
product0_.vendor_id as vend17_5_,
product0_.extra_cost_details as extra9_5_,
product0_.extra_costs as extra10_5_,
product0_.long_description as long11_5_,
product0_.price_amount as price12_5_,
product0_.price_currency as price13_5_,
product0_.quick_book as quick16_5_,
from
mydb.t_product0_
where
product0_.vendor_id=?
and product0_.quick_book=1
and product0_.id<>?

littypreethkr · **Posted:** Thu Mar 05, 2009 9:28 am

To the HQL where clause you can just add or :id is null and this should solve ur issue. Like:

Quote:

List<Product> matches = getEntityManager()
.createQuery(
"from Product where vendor=:vendor and quickBook=true and (id!=:id or :id is null)").setParameter(
"vendor", getCurrentVendor()).setParameter(
"id", getCurrentProduct().getId()).getResultList();

mmerder · **Posted:** Thu Mar 05, 2009 10:29 am

A Criteria would also be nice:

Code:

Criteria c = ((Session)getEntityManager().getDelegate()).createCriteria(Product.class).add( Restrictions.eq("vendor", getCurrentVendor())).add(Restrictions.eq("quickBook", true));
if(getCurrentProduct().getId() != null)
{
   c.add(Restrictions.idEq(getCurrentProduct().getId()));
}
...

mmerder · **Posted:** Thu Mar 05, 2009 10:30 am

double post

mgvarley · **Joined:** Wed Aug 13, 2008 10:30 am **Posts:** 4

Litty - your solution worked perfectly, thanks very much!