CODEWITHSUNDEEP
javamysqlperformancehibernatejpa
ManojP
ManojP

Reputation: 6248

Can i insert multiple row in one shot?

I have scenario where I need to insert n rows in a table where only one column value will varies rest will be constant.

I am under impression that there is no way to insert n records in one shot. Please correct me if I am in wrong.

thanks in advance.

I was trying with below query

INSERT INTO table1 (project_id,assumption_id,VALUE,created_at,updated_at)
VALUES (SELECT id FROM projects WHERE custom=0),7622,NULL,'2013-09-10 17:18:49','2013-09-10 17:18:49');

Upvotes: 1

Views: 2024

Answers (3)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153710

The reference documentation says that:

Only the INSERT INTO ... SELECT ... form is supported; not the INSERT INTO ... VALUES ... form.

The properties_list is analogous to the column specification in the SQL INSERT statement. For entities involved in mapped inheritance, only properties directly defined on that given class-level can be used in the properties_list. Superclass properties are not allowed and subclass properties do not make sense. In other words, INSERT statements are inherently non-polymorphic.

So if you already have a select criteria to use for your insert you can then run:

int createdEntities = s.createQuery( "insert into DelinquentAccount (id, name) select c.id, c.name from Customer c where ..." )
    .executeUpdate();

Another option is to use batch JDBC inserts and for this you need to set the following Hibernate properties:

<property name="hibernate.order_updates" value="true"/>
<property name="hibernate.order_inserts" value="true"/>
<property name="hibernate.jdbc.batch_versioned_data" value="true"/>
<property name="hibernate.jdbc.fetch_size" value="50"/>
<property name="hibernate.jdbc.batch_size" value="30"/>
<property name="hibernate.default_batch_fetch_size" value="50"/>

For update, you can use DML-style bulk UPDATE:

int updatedEntities = s.createQuery( "update YourEntity set yourProperty = :newValue where yourProperty = :oldValue" )
    .setString( "newValue", newValue )
    .setString( "oldValue", oldValue )
    .executeUpdate();

Upvotes: 2

Predrag Maric
Predrag Maric

Reputation: 24403

Yes, you can. You just need to create a new object every time, and not reuse the old one and just change some of its fields.

MyObject obj;
for (...) {
    obj = new MyObject(...);
    session.save(obj);
}

Upvotes: 1

Eclipse
Eclipse

Reputation: 404

There are many ways to do it I guess.

Why not just do a loop?

While ($x <= $limit){
 // insert query goes here
}

Upvotes: 1

Related Questions