Announcement Announcement Module
Collapse
No announcement yet.
Best practice for "get or insert" idiom Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Best practice for "get or insert" idiom

    I want to lookup a primary key from a table given some criteria, and if there is no match insert a new row and return the new key. For example, there is a USER table with 3 columns (ID, FIRST_NAME, LAST_NAME), where ID is the identity column, and a unique index exists on the other two columns.

    Service layer:

    Code:
    // this method can be potentially executed by multiple threads
    @Transactional
    int getId(String firstName, String lastName) {
        try {
            id = personDao.getId(firstName, lastName);
        }
        catch (EmptyResultDataAccessException e) {
            // no match - try to insert
            try {
                id = personDao.insert(firstName, lastName);
            }
            catch (DuplicateKeyException e2) {
                // concurrent thread already inserted the row - get existing ID
                id = personDao.getId(firstName, lastName);
            }
        }
    
        return id;
    }
    DAO layer:

    Code:
    @Cacheable("personIds")
    int getId(String firstName, String lastName) {
        // lookup and return ID
        ...
    }
    
    @CachePut("personIds")
    int insert(String firstName, String lastName) {
        // insert and return the new ID
        ...
    }
    Is the above design correct (let's assume that a stored procedure that would provide similar functionality is not available)?

    Are there any special considerations regarding the isolation level?

    Is Spring cache abstraction (@Cacheable, @CachePut above) thread-safe (regardless of the underlying caching provider, i.e. EHCache, Coherence, etc.) or relevant methods need to be synchronized?

    Thanks,
    Lukasz
Working...
X