[eclipselink-users] UpdateAllQuery and Persist executed in wrong order!

[ossaert <decooman@xxxxxxxxx>]

Hi,

I encountered the following problem. When doing an UpdateAllQuery before a
persist-operation, the INSERT is executed first, then the UPDATE instead of
the other way around.

I roughly have the following code:

public void create(.....) {

        // Update index = index + 1
        if (....)
           this.moveIndex(portalGuid, userGuid, index);
        
        // Persist bookmark
        BookmarkEntity bookmark = new BookmarkEntity();
        bookmark.setGuid(bookmarkGuid);
        bookmark.setUserGuid(userGuid);
        bookmark.setPortalGuid(portalGuid);
        bookmark.setForURI(forURI);
        bookmark.setCaption(caption);
        bookmark.setIndex(index);
        this.entityManager.persist(bookmark);
        this.entityManager.flush();
        return bookmark;
    }

    // Move index one position upwards
    private void moveIndex(String portalGuid, String userGuid, int
fromIndex) {

        // Create expression
        ExpressionBuilder expressionBuilder = new ExpressionBuilder();
        Expression expression =
                expressionBuilder.get(BookmarkEntity.FIELD_USER_GUID).
                equal(userGuid).
                and(expressionBuilder.get(BookmarkEntity.FIELD_PORTAL_GUID).
                equal(portalGuid)).and(
                expressionBuilder.get(BookmarkEntity.FIELD_INDEX).
                greaterThanEqual(fromIndex));


        UpdateAllQuery modifyQuery = new
UpdateAllQuery(BookmarkEntity.class);
        modifyQuery.setSelectionCriteria(expression);        
       
modifyQuery.addUpdate(expressionBuilder.get(BookmarkEntity.FIELD_INDEX),
               
ExpressionMath.add(expressionBuilder.get(BookmarkEntity.FIELD_INDEX), 1));

        // Execute delete query
        JpaHelper.getEntityManager(entityManager).getActiveSession().
                executeQuery(modifyQuery);

        // Make sure that statement is flushed to database
        JpaHelper.getEntityManager(entityManager).flush();

    }


The following SQL is executed:

1) INSERT INTO ooi_bookmarks (bookmark_id, bookmark_guid, bookmark_for_uri,
bookmark_index, bookmark_caption, portal_guid, bookmark_date_creation,
user_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        bind => [null, xxx, z, 1, null, y, 2009-07-28 15:42:09.527, x]

2) UPDATE ooi_bookmarks SET bookmark_index = (bookmark_index + ?) WHERE
(((user_guid = ?) AND (portal_guid = ?)) AND (bookmark_index >= ?))
        bind => [1, x, y, 1]

3) UPDATE ooi_bookmarks SET bookmark_index = (bookmark_index + ?) WHERE
(((user_guid = ?) AND (portal_guid = ?)) AND (bookmark_index >= ?))
        bind => [1, x, y, 1]

So, when issuing the FLUSH, then two UPDATES are executed. When I omit the
flush, then only one UPDATE is executed.

When I do the update after the persist, then everything works ok and the SQL
is executed as one INSERT and one UPDATE, but of course, with the wrong
logical result.

I suppose I could have something to do with the JpaHelper being executed
first?

Have been moving the code around for a while now, but not coming closer to
the solution. I am using 2.0.0-SNAPSHOT.

Greetings
Jan
-- 
View this message in context: http://www.nabble.com/UpdateAllQuery-and-Persist-executed-in-wrong-order%21-tp24698099p24698099.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.