How to optimize or alter hibernate generated SQL statement

Question

I have an undiretional OneToMany relationship parent owned with cascade.

I would like to know if there is a way to optimize the request the ORM is doing when inserting, updating or deleting. I know how to configure batching, but I see other way of improvement, many requests the ORM is doing could be done in a single query.

By example, consider the following action entityManager.persist(parent) :

0 ms|statement|insert into parent (value) values (1)
0 ms|statement|insert into child (value) values (1)
0 ms|statement|insert into child (value) values (1)
3 ms|statement|insert into child (value) values (1)
0 ms|statement|update child set parent_id=1 where id=1
0 ms|statement|update child set parent_id=1 where id=2
0 ms|statement|update child set parent_id=1 where id=3

Could be replaced by (at least for mysql dialect) :

insert into parent (value) values (1);
insert into child (value) values (1),(1),(1);
update child set parent_id=1 where id in (1, 2, 3);

The cascade delete suffer from the same problem and is optimizable.

How is it possible that hibernate doesn't detect such optimizations ? Do we have a way to hook somewhere in the ORM to filter/improve/replace the queries at runtime ?

Here are the Parent / Child class but I don't think it will help :

@Entity
public class Parent {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;

    public int value;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "parentId", referencedColumnName = "id")
    public List<Child> children;

    public Parent(Long id, int value, List<Child> children) {
        this.id = id;
        this.value = value;
        this.children = children;
    }

    private Parent() {
    }
}

@Entity
public class Child {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;

    int value;

    public Child(Long id, int value) {
        this.id = id;
        this.value = value;
    }

    private Child() {
    }
}

Answer 1

Maybe you could try the rewriteBatchStatements setting mysql? This is a setting you add to your JDBC connection URL which rewrites a large batch of insert statements and inlines them into a single statement which improves runtime. I think you also need to enable the batching you are already doing for this.

See: MySQL and JDBC with rewriteBatchedStatements=true

And also the mysql rewriteBatchedStatements configuration property in this link (you have to CTRL+F for it): https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html