Make cascade persist in JPA persist in the same order as collection in one-to-many relationship

Question

I have some JPA (EclipseLink) entities called Experiment, ExperimentSetup and ExperimentResults. An Experiment entity has a one-to-many relationship to ExperimentSetup and ExperimentSetup has a one-to-many relationship to ExperimentResult. When a Experiment gets persisted ExperimentSetup and ExperimentResults should also be persisted. for this I use the CascadeType.PERSIST setting in the @OneToMany annotation. It does persist the objects I want but in a different order then I expected (that is in the order the had in the ArrayList representing the entities one-to-many relationship).

So what I'm wondering is if there is a way to make the entities be inserted to the database in the order they have in the collection, while still using CascadeType.PERSIST?

My classes (simplified):

@Entity
public class Experiment implements Serializable {
    @Id
    private String eid;
    @OneToMany(cascade = {CascadeType.PERSIST, CascadeType.REMOVE}, mappedBy = "expEID")
    private List<ExperimentSetup> setupList;
    @Column
    private String name;    

    Getters and setter...
}

@Entity
public class ExperimentSetup implements Serializable {
        @Id
        private String id;
        @OneToMany(mappedBy = "setupId",cascade = {CascadeType.PERSIST, CascadeType.REMOVE})
        private List<ExperimentResults> results;
        @Column
        private Integer setupValue;

        Getters and setter...
}

@Entity
public class ExperimentResults implements Serializable {
            @Id
            private String id;
            @Columns Integer data;

            Getters and setter...
}

Do you care in what order are the INSERT operation to DB performed or is there any logical order of `setupList` and `results` to be kept? — zbig, Jul 30 '14 at 10:20
Yes I care about the INSERT order. The results-list for example represent an ordered sequence of values. — numfar, Jul 30 '14 at 11:47
Ok, than. Inserting to DB in specified order does not usually make sense because selecting doesn't preserve insertion order (http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause). So what is your "order by" attribute? Maybe id? Are you generating ids from some sequence? — zbig, Jul 30 '14 at 12:20
A select without `order by` looked like it always gave me the result in the order of my primary key id (but reading your link makes me suspect that this is not guaranteed to always be the case). This reflects the order that they were inserted. My ids are generated from an auto incremented sequence. — numfar, Jul 30 '14 at 12:38
I strongly recommend you to know by heart that "Selection order without ORDER BY clause is not guaranteed". You'll find similar sentence in almost any DB provider manual. — zbig, Jul 30 '14 at 12:49

zbig · Accepted Answer · 2014-07-30T14:07:09.937

Based on the information from your comments, I state that you should not depend on the insertion order. The insertion order is not guaranteed on the retrieval without 'ORDER BY' clause. In JPA this translates to having @OrderBy or @OrderColumn annotations.

Instead of inserting in specified order, I recommend you a solution with @OrderColumn. Your tables will have to keep order information in a new column. The best part is that JPA will take care of maintaining this column for you. Add additional annotation to your @OneToMany relationships. For example:

@OneToMany(cascade = {CascadeType.PERSIST, CascadeType.REMOVE}, mappedBy = "expEID")
@OrderColumn(name="SETUP_ORDER")
private List<ExperimentSetup> setupList;

With this example you will have to have additional column (SETUP_ORDER) on ExperimentSetup.

UPDATE OK, Example with Printer and PrintJobs might be more intuitive.

@Entity
public class Printer {
    @Id
    @GeneratedValue
    int id;
    @OneToMany(mappedBy = "printer", cascade = {CascadeType.PERSIST})
    @OrderColumn(name="PRINT_ORDER")
    List<PrintJob> jobs;
}

@Entity
public class PrintJob {
    @Id
    @GeneratedValue
    int id;

    String name;

    @ManyToOne
    Printer printer;

    public PrintJob(){}
    public PrintJob(String n, Printer p) {
        this.name = n;
        this.printer = p;
    }
}

 Printer p = new Printer();
 p.jobs = new ArrayList<PrintJob>();
 p.jobs.add(new PrintJob("A", p));
 p.jobs.add(new PrintJob("B", p));
 p.jobs.add(new PrintJob("C", p));
 em.persist(p);

This produces:

> SELECT * FROM Printer
ID         
-----------
1          
> SELECT * FROM PrintJob
ID  |NAME  |PRINTER_ID |PRINT_ORDER
-----------------------------------
2   |A     |1          |0          
3   |B     |1          |1          
4   |C     |1          |2

Make cascade persist in JPA persist in the same order as collection in one-to-many relationship

1 Answers1