Re: [Play2.0.4-Ebean] ManyToMany insertion in initial-data error

[James Roper]

Thanks in advance for the times spent on this reading :)

No worries.

I believe the issue is that you have CascadeType.ALL on zones configured, and so ebean is attempting to insert new objects, since the objects you are supplying it hasn't seen before.  So, you have some choices, one is to not use CascadeType.ALL, but rather use everything except CascadeType.PERSIST.  Another, and probably nicer choice is to use Yaml object references, this means the zone objects will be objects it has seen before and so it can know not to insert them again.  This will look something like this:

zones:

    - &zone1 !!models.Zone
        id:                 1
        gtbName:            "ZZ01"
        alias:              "Bureau"
        lightActivated:     Yes
        blindActivated:     Yes
        airCondActivated:   Yes

    - &zone2 !!models.Zone

        ...

users:

    - &user4 !!models.User
        id:                 4
        login:              "toto"
        password:           "toto"
        firstName:          "Toto"
        lastName:           "L'Haricot"
        email:              "toto@****.com"
        profile:            *profile4
        defaultZone:        *zone3
        zones:
            - *zone1
            - *zone2
            - *zone3

As you can see, this makes for cleaner syntax in the Yaml file too.

Also, for your interest, when you upgrade to 2.1, Ebean will have a bugfix that will mean you won't need to call saveManyToManyAssociations manually anymore, you can just list all your beans as one big list, and just call something like Ebean.save((List) Yaml.load("somefile")); to load all your data.  For a very detailed tutorial (it's a work in progress) see here:

https://github.com/playframework/Play20/blob/master/documentation/manual/javaGuide/tutorials/zentasks/JavaGuide2.md

Cheers,

James

On Wednesday, 31 October 2012 20:48:39 UTC+11, Bertrand wrote:

Hi,

I'm using Play2 for few weeks now, but I didn't manage to successfully insert ManyToMany relation via initial-data.yml. Here is the code used :

User.java

@Entity
@Table(name = "users")
public class User extends Model {
    private static final long serialVersionUID = 1L;

 

    @Id
    @Required
    private Long id;

    @Column(length = 15)
    @Required
    @NonEmpty
    private String login;

    @Column(length = 15)
    @Required
    @NonEmpty
    private String password;

    @Column(length = 50)
    private String firstName;

    @Column(length = 50)
    private String lastName;

    @Column(length = 100)
    private String email;

    @ManyToOne
    private Profile profile;

    @ManyToOne
    private Community community;

    @ManyToOne
    private Zone defaultZone;

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private Set<Zone> zones = new HashSet<Zone>();

    // --- Queries
   
    public static Finder<Long, User> find = new Finder<Long, User>(Long.class, User.class);

    public static Page<User> listUser(int page, int pageSize, String sortBy, String order, String loginFilter, String fNameFilter,
            String lNameFilter, String zonesFilter) {
        List<Profile> profiles = Profile.find.all();
        Profile userProfile = new Profile();

        // Get the id of the user profile in the database.
        for (Profile profile : profiles) {
            if (profile.getType() == Profile.Type.USER) {
                userProfile = profile;
                break;
            }
        }

        return find.where().ilike("profile_id", Long.toString(userProfile.getId())).ilike("login", "%" + loginFilter + "%")
                .ilike("first_name", "%" + fNameFilter + "%").ilike("last_name", "%" + lNameFilter + "%").orderBy(sortBy + " " + order)
                .findPagingList(pageSize).getPage(page);
    }
   
    // --- Getters and Setters.

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getLogin() {
        return login;
    }

    public void setLogin(String login) {
        this.login = login;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Profile getProfile() {
        return profile;
    }

    public void setProfile(Profile profile) {
        this.profile = profile;
    }

    public Community getCommunity() {
        return community;
    }

    public void setCommunity(Community community) {
        this.community = community;
    }

    public Zone getDefaultZone() {
        return defaultZone;
    }

    public void setDefaultZone(Zone defaultZone) {
        this.defaultZone = defaultZone;
    }

    public Set<Zone> getZones() {
        return zones;
    }

    public void setZones(Set<Zone> zones) {
        this.zones = zones;
    }
}

Zone.java

@Entity
@Table(name = "zones")
public class Zone extends Model {
    private static final long serialVersionUID = 1L;

    @Id
    private Long id;

    @Column(length = 51)
    private String gtbName;

    @Column(unique = true, length = 50)
    private String alias;

    private boolean lightActivated;

    private boolean blindActivated;

    private boolean airCondActivated;
   
    @ManyToMany(mappedBy = "zones")
    private Set<User> users = new HashSet<User>();
   
    // --- Queries

    public static Model.Finder<Long, Zone> find = new Model.Finder<Long, Zone>(Long.class, Zone.class);
   
    // --- Getters and Setters.

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getGtbName() {
        return gtbName;
    }

    public void setGtbName(String gtbName) {
        this.gtbName = gtbName;
    }

    public String getAlias() {
        return alias;
    }

    public void setAlias(String alias) {
        this.alias = alias;
    }

    public boolean isLightActivated() {
        return lightActivated;
    }

    public void setLightActivated(boolean lightActivated) {
        this.lightActivated = lightActivated;
    }

    public boolean isBlindActivated() {
        return blindActivated;
    }

    public void setBlindActivated(boolean blindActivated) {
        this.blindActivated = blindActivated;
    }

    public boolean isAirCondActivated() {
        return airCondActivated;
    }

    public void setAirCondActivated(boolean airCondActivated) {
        this.airCondActivated = airCondActivated;
    }

    public Set<User> getUsers() {
        return users;
    }

    public void setUsers(Set<User> users) {
        this.users = users;
    }
}

initial-data.yml

# Profiles

profiles:

    - !!models.Profile
        id:                 1
        type:               SUPER_ADMIN

    - !!models.Profile
        id:                 2
        type:               EXPLOITATION_ADMIN

    - !!models.Profile
        id:                 3
        type:               COMMUNITY_MANAGER

    - !!models.Profile
        id:                 4
        type:               USER

# Zones

zones:

    - !!models.Zone
        id:                 1
        gtbName:            "ZZ01"
        alias:              "Bureau"
        lightActivated:     Yes
        blindActivated:     Yes
        airCondActivated:   Yes

    - !!models.Zone
        id:                 2
        gtbName:            "ZZ02"
        alias:              "Salle de réunion"
        lightActivated:     Yes
        blindActivated:     Yes
        airCondActivated:   No

    - !!models.Zone
        id:                 3
        gtbName:            "ZZ03"
        alias:              "Salle de pause"
        lightActivated:     No
        blindActivated:     No
        airCondActivated:   Yes

    - !!models.Zone
        id:                 4
        gtbName:            "ZZ04"
        alias:              "Entrepot"
        lightActivated:     Yes
        blindActivated:     No
        airCondActivated:   No

    - !!models.Zone
        id:                 5
        gtbName:            "ZZ05"
        alias:              "Logistique"
        lightActivated:     Yes
        blindActivated:     No
        airCondActivated:   Yes

# Users

users:

    - !!models.User
        id:                 1
        login:              "suadmin"
        password:           "suadmin"
        firstName:          "super"
        lastName:           "admin"
        email:              "suadmin@*****.com"
        profile:            !!models.Profile
                                id: 1

    - !!models.User
        id:                 2
        login:              "site_admin"
        password:           "siteadmin"
        firstName:          "site"
        lastName:           "admin"
        email:              "site_admin@****.com"
        profile:            !!models.Profile
                                id: 2

    - !!models.User
        id:                 3
        login:              "community_admin"
        password:           "communityadmin"
        firstName:          "community"
        lastName:           "admin"
        email:              "community_admin@****.com"
        profile:            !!models.Profile
                                id: 3

    - !!models.User
        id:                 4
        login:              "toto"
        password:           "toto"
        firstName:          "Toto"
        lastName:           "L'Haricot"
        email:              "toto@****.com"
        profile:            !!models.Profile
                                id: 4
        defaultZone:        !!models.Zone
                                id: 3
        zones:
            - !!models.Zone
                id: 1
            - !!models.Zone
                id: 2
            - !!models.Zone
                id: 3

    - !!models.User
        id:                 5
        login:              "titi"
        password:           "titi"
        firstName:          "Titi"
        lastName:           "Le Ouistiti"
        email:              "titi@****.com"
        profile:            !!models.Profile
                                id: 4
        defaultZone:        !!models.Zone
                                id: 1
        zones:
            - !!models.Zone
                id: 1
            - !!models.Zone
                id: 3

    - !!models.User
        id:                 6
        login:              "mich"
        password:           "mich"
        firstName:          "Mich"
        lastName:           "De La Cabinas"
        email:              "mich@*****.com"
        profile:            !!models.Profile
                                id: 4
        defaultZone:        !!models.Zone
                                id: 5
        zones:
            - !!models.Zone
                id: 4
            - !!models.Zone
                id: 5

And Global.java

public class Global extends GlobalSettings {
    @Override
    public void onStart(Application app) {
        InitialData.insert(app);
    }

 

    static class InitialData {
        @SuppressWarnings("unchecked")
        public static void insert(Application app) {
            Logger.debug("Read the initial-data.yml file.");
            Map<String, List<Object>> all = (Map<String, List<Object>>) Yaml.load("initial-data.yml");

            if (Ebean.find(Profile.class).findRowCount() == 0) {
                Logger.debug("Loading profiles from initial-data.yml.");
                Ebean.save(all.get("profiles"));
            }

            if (Ebean.find(Zone.class).findRowCount() == 0) {
                Logger.debug("Loading zones from initial-data.yml.");
                Ebean.save(all.get("zones"));
            }

            if (Ebean.find(User.class).findRowCount() == 0) {
                Logger.debug("Loading users from initial-data.yml.");
                Ebean.save(all.get("users"));

                for (Object user : all.get("users")) {
                    Logger.debug("Insert the user/zone relation.");
                    Ebean.saveManyToManyAssociations(user, "zones");
                }
            }

            if (Ebean.find(Settings.class).findRowCount() == 0) {
                Logger.debug("Loading settings from initial-data.yml.");
                Ebean.save(all.get("settings"));
            }
        }
    }
}

Now the error which is thrown by the frameworks :

PersistenceException: ERROR executing DML bindLog[] error[Unique index or primary key violation: "PRIMARY_KEY_5 ON PUBLIC.ZONES(ID)"; SQL statement:\n insert into zones (id, gtb_name, alias, light_activated, blind_activated, air_cond_activated) values (?,?,?,?,?,?) [23505-158]] 

javax.persistence.PersistenceException: ERROR executing DML bindLog[] error[Unique index or primary key violation: "PRIMARY_KEY_5 ON PUBLIC.ZONES(ID)"; SQL statement:\n insert into zones (id, gtb_name, alias, light_activated, blind_activated, air_cond_activated) values (?,?,?,?,?,?) [23505-158]]

     com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:116)

     com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.insert(DmlBeanPersister.java:76)

     com.avaje.ebeaninternal.server.persist.DefaultPersistExecute.executeInsertBean(DefaultPersistExecute.java:91)

     com.avaje.ebeaninternal.server.core.PersistRequestBean.executeNow(PersistRequestBean.java:527)

     com.avaje.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:557)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:404)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.saveEnhanced(DefaultPersister.java:345)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.saveRecurse(DefaultPersister.java:315)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.saveAssocManyDetails(DefaultPersister.java:916)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.saveMany(DefaultPersister.java:791)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.saveAssocMany(DefaultPersister.java:704)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:409)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.saveEnhanced(DefaultPersister.java:345)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.saveRecurse(DefaultPersister.java:315)

     com.avaje.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:282)

     com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1765)

     com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1743)

     com.avaje.ebean.Ebean.save(Ebean.java:608)

     com.avaje.ebean.Ebean.save(Ebean.java:615)

     Global$InitialData.insert(Global.java:45)

     Global.onStart(Global.java:21)

     play.core.j.JavaGlobalSettingsAdapter.onStart(JavaGlobalSettingsAdapter.scala:16)

     play.api.GlobalPlugin.onStart(Global.scala:134)

     play.api.Play$$anonfun$start$1.apply(Play.scala:60)

     play.api.Play$$anonfun$start$1.apply(Play.scala:60)

     scala.collection.LinearSeqOptimized$class.foreach(LinearSeqOptimized.scala:59)

     scala.collection.immutable.List.foreach(List.scala:45)

     play.api.Play$.start(Play.scala:60)

     play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$3$$anonfun$1.apply(ApplicationProvider.scala:125)

     play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$3$$anonfun$1.apply(ApplicationProvider.scala:112)

     scala.Option.map(Option.scala:133)

     play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$3.apply(ApplicationProvider.scala:112)

     play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$3.apply(ApplicationProvider.scala:110)

     scala.Either$RightProjection.flatMap(Either.scala:277)

     play.core.ReloadableApplication$$anonfun$get$1.apply(ApplicationProvider.scala:110)

     play.core.ReloadableApplication$$anonfun$get$1.apply(ApplicationProvider.scala:110)

     akka.dispatch.Future$$anon$3.liftedTree1$1(Future.scala:195)

     akka.dispatch.Future$$anon$3.run(Future.scala:194)

     akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:94)

     akka.jsr166y.ForkJoinTask$AdaptedRunnableAction.exec(ForkJoinTask.java:1381)

     akka.jsr166y.ForkJoinTask.doExec(ForkJoinTask.java:259)

     akka.jsr166y.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:975)

     akka.jsr166y.ForkJoinPool.runWorker(ForkJoinPool.java:1479)

     akka.jsr166y.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:104)

I put the whole stack trace if it helps. 

So I understood from this message that Ebean try to insert what is suppose to be my ManyToMany association into the Zones tables but there is already entries because I insert zone's data before users' one. In order to work I insert by myself in h2-browser the relation but it's not the solution. So what am I doing wrong? I used the zentasks' sample code to create my initial-data by the way and the zentask project works fine... I don't understand.

Thanks in advance for the times spent on this reading :)

[Bertrand]

Hi,

Thanks a lot for your help, it works great ! You saved me a lot of time wasted to fill the database row by row.

[Benjamin Carlson]

Thanks James, this helped me out too!

-Ben

[Diogo Nunes]

Hi James, I have a similar problem (https://groups.google.com/forum/#!topic/play-framework/Jut2h5aVjqw), if you could have a look I would be very thankful

Cheers ;)