CODEWITHSUNDEEP
javahibernatepostgresql-9.1spring-data-jpa
Mariusz
Mariusz

Reputation: 1985

How to ignore unique violation during insert list of objects which contain set of object

I use PostgreSQL nad Spring data JPA with Hibernate. I have relation OneToMany with orphanRemoval = false because I very often add many childs to relation.

Parent:

@OneToMany(mappedBy = "parent", cascade = { CascadeType.ALL }, orphanRemoval = false, fetch = FetchType.LAZY) public Set getChildren() { return children; }

Child:

@ManyToOne @JoinColumn(name = "parent_id") public Parent getParent() { return parent; }

To persist or merge object I use method

Iterable< T > save(Iterable< extends T> entities)

form CrudRepository. I save list of parents, where every parent contain set of child. Child table has unique constraint. If constraint violations occurs I want to ignore that and ommit (do not persist) child which cases viloations but I want insert every child which doesn't case constraint violation. How to do that?

Upvotes: 7

Views: 5181

Answers (3)

Kamil Kalinowski
Kamil Kalinowski

Reputation: 486

This is a Postgres-specific answer and not very kosher, but you could employ a similar approach with different DBs:

take the advantage of on conflict clause in native Postgres insert statement. That way you won't have to handle the unique constraint exceptions at all, the DB will solve the conflicts for you as it encounters them.

  1. Prepare a native SQL insert statement e.g. insert into child (id, parent_id, data) values (:id, :parent_id, :data) on conflict do nothing
  2. Use the previously written statement with javax.persistence.EntityManager#createNativeQuery(java.lang.String)
EntityManager.createNativeQuery(sql)
                .setParameter("id", it.id)
                .setParameter("parent_id", parentId)
                .setParameter("data", it.data)
                .executeUpdate()
  1. Repeat for all the children

Upvotes: 0

Grim
Grim

Reputation: 1974

Handle this dirty by Exceptions.

  1. Try to Update the Database, if fine break here. Catch the UniqueViolationException and find the JDBCException. Upcast to your qualified Database Exception and find the broken Children.

  2. Remove the Children from the Parent.

  3. Go to 1.

Upvotes: 2

Grim
Grim

Reputation: 1974

The clean way is to filter the Entitys who will produce unique-violation-exceptions. After you filtered that entitys, you can save the good ones.

Exceptions should used as they realy are: Exceptions.

Upvotes: 0

Related Questions