Duplicate/Unique contraint problem.

[hhoang]

Hello,

I am new to Spring and Hibernate and I need help.

What I am trying to do is to prevent duplicate name from being insert into the database. To do this, I want to do a query to see if the existing name already exist. If it's already exists, I throw a new exception, else I will saveOrUpdate like usual.

This code works fine if I am trying to add a new record. It has problem when I am trying to update an existing record with the name already taken. Specifically, this I am keep getting the same Id for both updated record and existing record.

I am suspected that my getResourceType(String) in the DAO records multiple records because somehow the FormController update the data in the cache because I am using my model as a Command in spring-mvc framework terminology.

Also, what I am doing may not be a correct way, please point me to the right solution for this Duplicate/Unique contraint problem.

Thanks,
Hai

-------------------------------------
Servicce Layer

public ResourceType getResourceType(String name) {
    return dao.getResourceType(name);
}


public void saveResourceType(ResourceType resourceType) 
    throws ResourceTypeExistsException {
    boolean isDuplicate = false;
    ResourceType existing = getResourceType(resourceType.getName());
    if (existing != null) {
        if (resourceType.getId() == null) {
            isDuplicate = true;
        }
        else {
            log.debug("resourceType id " + resourceType.getId());
            log.debug("existing id " + existing.getId());
            if (!resourceType.getId().equals(existing.getId())) {
                isDuplicate = true;
            }
        }
    }
    
    if (isDuplicate) {
        throw new ResourceTypeExistsException("ResourceType '" + resourceType.getName() + "' already exists!");
    }
    else {
        try {
            dao.saveResourceType(resourceType);
        } catch (DataIntegrityViolationException e) {
            throw new ResourceTypeExistsException("ResourceType '" + resourceType.getName() + "' already exists!");
        }
    }
}

--------------------------------------
DAO Layer

public ResourceType getResourceType(String name) {
    List resourceTypes = getHibernateTemplate().find("from ResourceType r where r.name=?", name);
    if (resourceTypes.size() == 0) {
        return null;
    }

    //may record more than one here because I don't have a unique contraint in my table.
    return (ResourceType) resourceTypes.get(0);
}


public void saveResourceType(ResourceType resourceType) {
    getHibernateTemplate().saveOrUpdate(resourceType);
    getHibernateTemplate().flush();
    
}

public ModelAndView onSubmit(HttpServletRequest request,
                             HttpServletResponse response, Object command,
                             BindException errors)
throws Exception {
    if (log.isDebugEnabled()) {
        log.debug("entering 'onSubmit' method...");
    }

    ResourceType resourceType = (ResourceType) command;
    boolean isNew = (resourceType.getId() == null);
    Locale locale = request.getLocale();

    if (request.getParameter("delete") != null) {
        resourceTypeManager.removeResourceType(resourceType.getId());

        saveMessage(request, getText("resourceType.deleted", locale));
    } else {
       
        try {
             resourceTypeManager.saveResourceType(resourceType);
        } catch (ResourceTypeExistsException e) {
            log.warn(e.getMessage());

            errors.rejectValue("name", "errors.existing.name",
                               new Object[] {
                                   resourceType.getName(),
                               }, "duplicate name");
            return super.showForm(request, response, errors);
        }
        
        String key = (isNew) ? "resourceType.added" : "resourceType.updated";
        saveMessage(request, getText(key, locale));

        if (!isNew) {
            return new ModelAndView("redirect:editResourceType.html", "id", resourceType.getId());
        }
    }

    return new ModelAndView(getSuccessView());
}

[katentim]

It has problem when I am trying to update an existing record with the name already taken. Specifically, this I am keep getting the same Id for both updated record and existing record.

I'm not sure I understand the exact problem. Can you post a stack trace?

Also, take a look at the Hibernate Common Problems FAQ, especially the parts on saveOrUpdate.

[dejanp]

Theoretically, you can't solve that problem like that - it's entirely possible another client adds a new record between your check and save methods(). Actually, with enough clients it's just a matter of time when it's going to happen.

Why not just creating a unique index on a column you want to keep unique?

[katentim]

Theoretically, you can't solve that problem like that

It should catch most duplicates, but you may still get a constraint violation.

Why not just creating a unique index on a column you want to keep unique?

I assume this has already been done.

It is sometimes useful to have a check - if you have multiple unique columns, it easy to distinguish which one was a duplicate (without doing something like looking at constraint names).