Insert into, calling the function

Hibernate ORM
1

Hello, dear community.

Example entities:


@Entity
class DemoEntity {
    @Id
    private UUID id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "a_id")
    private A a;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "b_id")
    private B b;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "c_id")
    private C c;
    @Column("my_hash")
    private UUID my_hash;
}

@Entity
class A {
    @Id
    private Integer id;
}
@Entity
class B {
    @Id
    private Integer id;
}
@Entity
class C {
    @Id
    private Integer id;
}

I’d like to write the following query using hibernate (avoiding native queries):

insert into demo(id, a_id, b_id, c_id, my_hash)
values('6a7078ef-d761-4e05-b743-0d4b9eb242cf', 
1, 2, 3, md5(CONCAT(1 , '/', 2, '/', 3))::uuid)

I’ve tried to use createCriteriaInsertValues like this:

var cb = (HibernateCriteriaBuilder) entityManager.getCriteriaBuilder();
        var insertCriteria = cb.createCriteriaInsertValues(DemoEntity.class);
        insertCriteria.setInsertionTargetPaths(
                insertCriteria.getTarget().get(DemoEntity_.ID),
                insertCriteria.getTarget().get(DemoEntity_.A).get(A_.ID),
                insertCriteria.getTarget().get(DemoEntity_.B).get(B_.ID),
                insertCriteria.getTarget().get(DemoEntity_.C).get(C_.ID),
                insertCriteria.getTarget().get(DemoEntity_.MY_HASH)
        );
        insertCriteria.values(cb.values(
                cb.value(UUID.fromString("6a7078ef-d761-4e05-b743-0d4b9eb242cf")),
                cb.value(1),
                cb.value(2),
                cb.value(3),
                cb.function(...) // here we execute md5 function
        ));
entityManager.unwrap(Session.class).createMutationQuery(insertCriteria)
                .executeUpdate();

But I receive the following exception:

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.query.SemanticException: Not expecting multiple table references for an SQM INSERT-SELECT
	at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371) ~[spring-orm-6.1.8.jar:6.1.8]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246) ~[spring-orm-6.1.8.jar:6.1.8]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550) ~[spring-orm-6.1.8.jar:6.1.8]
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-6.1.8.jar:6.1.8]
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335) ~[spring-tx-6.1.8.jar:6.1.8]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-6.1.8.jar:6.1.8]

Could you please propose any ideas how to write this query avoiding natives?

Sincerely,
Borys

3 Likes
2

Looks correct to me. Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

1 Like
3

We created a bug: [HHH-18647] - Hibernate JIRA

Thank you so much!

1 Like