Update multiple rows using single query in JDO

Question

Iam having a table like this

SorOrder Name Date
1 Image1 5/6/15
2 Image2 6/8/16
3 Image3 6/8/16
4 Image4 9/8/16 ..........

Now if iam deleting image2 i want to udate the table so that the sortorder again is in ordered form like this Updated Table :
SorOrder Name Date
1 Image1 5/6/15
2 Image3 6/8/16
3 Image4 9/8/16
..........

SO how to make it posible??

This is the class for the table Images:

public class Images extends ApplicationEntity{

    @Column(name="PROFILE_ID", allowsNull="false")
    private Profile profile;
    private int sortOrder;
    private boolean active;
    private Date deletedDate;

    public Images (){
        super.setEntity("Images ");
    }

    public Images (Profile profile, int sortOrder, boolean active,
            Date deletedDate) {
        super();
        this.profile = profile;
        this.sortOrder = sortOrder;
        this.active = active;
        this.deletedDate = deletedDate;
    }


    public Profile getProfile() {
        return profile;
    }

    public int getSortOrder() {
        return sortOrder;
    }

    public void setSortOrder(int sortOrder) {
        this.sortOrder = sortOrder;
    }

    public boolean isActive() {
        return active;
    }

    public void setActive(boolean active) {
        this.active = active;
    }

    public Date getDeletedDate() {
        return deletedDate;
    }

    public void setDeletedDate(Date deletedDate) {
        this.deletedDate = deletedDate;
    }


    @Override
    public String toString() {
        return "Images [profile=" + profile + ",  sortOrder=" + sortOrder
                + ", active=" + active + ", deletedDate=" + deletedDate + "]";
    }

}

I tried this query: String query = "update Images set SORTORDER =((SELECT selected_value FROM (SELECT MAX(SORTORDER) AS selected_value FROM Images where ACTIVE = 0 && PROFILE_Id="+profileId+") AS sub_selected_value) + 1) where PROFILE_Id="+profileId;

But it updates all the sorOrder to same value.

I was using this code to update the sortorder:

int sortoder=1;  
for (Images file : imagesListFromDB) {  
            file.setSortOrder(sortOrder);  
            sortOrder++;  
        } 

But it takes more time,if iam having 8000 images then its really slow.SO i thought of updating in a single query. But not getting any idea

Answer 1

To do in a single statement you could make use of SQL. Here are a couple of ideas (adapt to your use-case) - you use the "?" parameter to set the position above what you delete.

UPDATE IMAGES SET SORTORDER = 
    (CASE WHEN (SORTORDER <= ?) THEN SORTORDER 
          ELSE (SORTORDER-1) END)

Or

UPDATE IMAGES SET SORTORDER = SORTORDER-1
WHERE SORTORDER > ?

Using DataNucleus JDOQL UPDATE extension you could do this (and set the parameter "param" to the sortOrder start point to update

pm.newQuery("UPDATE mydomain.Images SET this.sortOrder=this.sortOrder-1 WHERE this.sortOrder > :param");