1

I have below entity.

@Entity
public class Shop {
    @OneToMany( cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE }, orphanRemoval = true )
    @JoinTable( name = "shop_image",
            joinColumns = @JoinColumn( name = "shop_id" ),
            inverseJoinColumns = @JoinColumn( name = "image_id" ) )
    @OrderColumn( name = "order_number" )
    @BatchSize( size = 20 )
    private List<Image> images = new ArrayList<Image>( 0 );
}

shop_image table == (id PK, shop_id, image_id, order_number) and has unique constraint (shop_id, image_id).

When below code is invoked (in transactional context):

@PersistenceContext
EntityManager em;

@Transactional
// shop exist in DB
public void reorderImages(Shop shop) {
    //shop.getImages().size == 2; {img0, img1}
    List<Image> newImages = new ArrayList<>();
    newImages.add(shop.getImages().get(1));
    newImages.add(shop.getImages().get(0));
    shop.setImages(newImages); // {img1, img0}

    em.merge(shop);
}

I get below exception

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '5458-98711' for key 'uniqShopAndImage'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    ... 76 more

I know that it is possible to:

  1. Delete all images from shop //shop.getImages().clear()
  2. em.flush()
  3. shop.setImages(newImages);
  4. em.merge(shop)

But I don't like this solution. I use hibernate 4 as JPA Provider, MySQL DB. Thanks for your answers.

nazlo
  • 431
  • 3
  • 9

1 Answers1

1

The problem is caused because MySQL does not support deferrable constraints check (means check constraint while committing DB transaction, not while executing single SQL statement). The detailed answer is here .

About my problem, it is possible to solve it with 

show.setImages( Lists.newArrayList() );
entityManager.flush();

But I still don't like this solution.

Community
  • 1
  • 1
nazlo
  • 431
  • 3
  • 9