Hi,

How do you insert multiple rows using Hibernate's Insert Select statement?

My Project is using EJB3 and Hibernate's EntityManager.

My Entity has an Embedded AuditEntry object that is giving me problems.

I've tried various approaches but can't get it to work. Thanks for any help in advance.

Hibernate version:
3.2.0.ga

Mapping documents:

@MappedSuperclass
@AccessType("field")
abstract public class ImmutableEntity extends BaseEntity {

@Embedded
private AuditEntry auditEntry;
// AuditEntry is a (DateTime createdDate, Long createdByOperatorId) object
// get/set AuditEntry methods
}

public class VenueConfigurationSeat extends ImmutableEntity {

// other fields

}

Here's what I'm trying to do

String SQL =
"INSERT INTO VenueConfigurationSeat (a, b, c, auditEntry) " +
"SELECT a, b, c, :auditEntry " +
"FROM a, b, c" +
"WHERE ...";

Long operatorId = // get operatorId
DateTime createdDate = new DateTime();
AuditEntry auditEntry = new AuditEntry(createdDate, operatorId);

// execute Query
getEntityManager().createQuery(SQL)
.setParameter("venueConfigId", venueConfiguration.getId())
.setParameter("auditEntry", auditEntry)
.executeUpdate();

I've also tried a few other approaches from nested paths (auditEntry.createdDate, auditEntry.createdByOperatorId) to selecting new objects (select new com.mydomain.AuditEntry(:createdDate, :createdbyOperatorId).

No approach has had any success and each approach has thrown various exceptions from IllegalArgumentException to bad syntax to property unresolvable exceptions.

Here's some of the other approaches that I've also tried

Alt approach 1 - nested path:

INSERT INTO VenueConfigurationSeat(a, b, c, auditEntry.createdDated, auditEntry.createdByOperatorId)
SELECT a, b, c, :createdDate, :createdByOperatorId

Alt approach 2 - add setCreatedDate and setCreatedByOperatorId method on VenueConfigurationSeat

- Hibernate Exception - could not resolve property: createdDate of: com.tickets.domain.venue.entity.VenueConfigurationSeat

Alt approach 3 - add constructor to AuditEntry(DateTime createdDate, Long createdByOperatorId) - also added default public constructor

INSERT INTO VenueConfigurationSeat(a, b, c, auditEntry)
SELECT ... new com.mydomain.AuditEntry(:createdDate, :createdByOperatorId) ....


Thanks!
-Ben