0

Need to know how to do an insert with HQL with a select and that select contains a union. Here's my test case

create table simple_author (
id bigint generated by default as identity,
first_name varchar(255),
last_name varchar(255),
primary key (id))

@Entity
public class SimpleAuthor {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", updatable = false, nullable = false)
private Long id;
private String firstName;
private String lastName;}

Assume the requirement is to insert 2 authors and if one fails for whatever reason the other is not persisted in the database.

Using just SQL and if Oracle or H2 I can do Insert into simple_author(first_name, last_name) select ‘bob’,’smith’ from dual union select ‘frank’, ‘brown’ from dual

Or if using SQL Server: Insert into simple_author(first_name, last_name) select ‘bob’,’smith’ union select ‘frank’, ‘brown’

Assume Bob Smith and Frank Brown are supplied by a user entering data in a UI.

Looking at https://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#hql-insert Seems to support a simple case. Seems there are a couple of issues: Doing a union and the list of values to be added in this case bob smith and frank brown For the union have tried a couple of solutions as seen in threads

Hibernate Union alternatives

and

How to execute query with union in hibernate?

at best they only seem to get close to what is required. So the question is How to write: Insert into simple_author(first_name, last_name) select ‘bob’,’smith’ from dual union select ‘frank’, ‘brown’ from dual In hsql

Oxnard
  • 237
  • 1
  • 6
  • 18

2 Answers2

1

Unfortunately HQL/JPQL do not support this. Such things can be done with native query or techs like FluentJPA, which does native query under the covers.

Konstantin Triger
  • 1,576
  • 14
  • 11
0

at least for me I was using Spring and at the time of writing did not known within a DAO that Spring using the @Transactional annotation will manage the commit. Found out the entity manager will commit or rollback all rows based on if an exception occurs therefore I can reach the goal of an "all or nothing" being committed. So in my case the code is:

  // if an exception occurs then the transaction is rolled back there is not any
  // special code required.
  public List<SimpleAuthor> insertSimpleAuthors(List<SimpleAuthor> simpleAuthors) {   
   // try {
      //em.getTransaction().begin();
      for (SimpleAuthor entity : simpleAuthors) {
        em.persist(entity);     
      }
      //em.flush();
//    } catch (Exception ex) {
//      em.getTransaction().rollback();
//      throw ex;
//    }
    for (int i = 0; i < simpleAuthors.size(); i++) {
      //SimpleAuthor entity : simpleAuthors) {
      simpleAuthors.set(i, 
      em.createQuery("from SimpleAuthor where firstName = :firstName and lastName = :lastName",SimpleAuthor.class)
       .setParameter("firstName", simpleAuthors.get(i).getFirstName())
       .setParameter("lastName", simpleAuthors.get(i).getLastName())
       .getSingleResult()
       );
    }
    return simpleAuthors;
  }
Oxnard
  • 237
  • 1
  • 6
  • 18