I'm trying to use bulk insert as per 13.4. DML-style operations of the manual.
My case wasn't described in this chapter so i started to experiment.
The goal is simple: I have three tables:
Code:
create table a (id int primary key);
create table b(id int primary key);
create table c(id int primary key, a_id int references a, b_id int references b);
I'm already know the A primary key and trying to fill the C with all values from B
like in the last SQL below (suppose i dont know the B's rows, second
insert is just to fill B with some data):
Code:
insert into A (id) values (1);
insert into B (id) values (1);
insert into C (id, a_id, b_id) select 1, 1, b.id from B b;
First (naive) attempt:
Code:
Query q = session.createQuery("insert into C (a, b) select :a, b from B");
q.setParameter("a", a1);
q.executeUpdate();
Not working, and gives me:
org.hibernate.QueryException: number of select types did not match those for insert [insert into C (a, b) select :a, b from Main$B]
Probably I can understand - the actual type of the ':a' is not known (why?).
[Main$B: my classes in test case are nested inside the Main class. See listing at the bottom.]
Second attempt (with extra join which is redundant and can dergade the performance):
Code:
Query q = session.createQuery("insert into C (a, b) select a, b from A a, B b where a = :a");
q.setParameter("a", a1);
q.executeUpdate();
Not working, gives me (a little strange) error:
org.postgresql.util.PSQLException: ERROR: column reference "id" is ambiguous
When debug is turned on i can see why:
could not execute update query [insert into C ( id, a_id, b_id ) select nextval ('hibernate_sequence'), main_a0_.id as col_0_0_, main_b1_.id as col_1_0_ from A main_a0_ cross join B main_b1_ where
id=?]
Last attemp is working.
Code:
Query q = session.createQuery("insert into C (a, b) select a, b from A a, B b where a.id = :a");
q.setParameter("a", a1.getId());
q.executeUpdate();
This may help to someone who trying to achieve the similar goal.
Hibernate 3.6.6.Final, PostgreSQL 8.4.8, JDK jdk1.6.0_26 (Win7).
Below is full listing of the test case:
Code:
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import javax.persistence.*;
public class Main {
public static void main(final String[] args) throws Exception {
Configuration config = new Configuration();
config.addAnnotatedClass(A.class);
config.addAnnotatedClass(B.class);
config.addAnnotatedClass(C.class);
SessionFactory factory = config.configure("hibernate.cfg.xml").buildSessionFactory();
Session session = factory.openSession();
Transaction t;
{
t = session.beginTransaction();
session.createQuery("delete from C").executeUpdate();
session.createQuery("delete from B").executeUpdate();
session.createQuery("delete from A").executeUpdate();
t.commit();
}
A a1;
B b1;
try {
t = session.beginTransaction();
a1 = new A();
session.save(a1);
b1 = new B();
session.save(b1);
t.commit();
} catch (Exception ex) {
t.rollback();
throw ex;
}
try {
t = session.beginTransaction();
Query q = session.createQuery("insert into C (a, b) select :a, b from B");
q.setParameter("a", a1);
q.executeUpdate();
System.out.println("1. WORKING");
t.commit();
} catch (Exception ex) {
t.rollback();
System.err.println("1. not working");
Throwable error = ex;
while (error != null) {
error.printStackTrace();
error = error.getCause();
}
}
try {
t = session.beginTransaction();
Query q = session.createQuery("insert into C (a, b) select a, b from A a, B b where a = :a");
q.setParameter("a", a1);
q.executeUpdate();
System.out.println("2. WORKING");
t.commit();
} catch (Exception ex) {
t.rollback();
System.err.println("2. not working");
Throwable error = ex;
while (error != null) {
error.printStackTrace();
error = error.getCause();
}
}
try {
t = session.beginTransaction();
Query q = session.createQuery("insert into C (a, b) select a, b from A a, B b where a.id = :a");
q.setParameter("a", a1.getId());
q.executeUpdate();
System.out.println("3. WORKING");
t.commit();
} catch (Exception ex) {
t.rollback();
System.err.println("3. not working");
Throwable error = ex;
while (error != null) {
error.printStackTrace();
error = error.getCause();
}
}
session.flush();
session.close();
}
@Entity(name = "A")
public static class A {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
long id;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
}
@Entity(name = "B")
public static class B {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
long id;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
}
@Entity(name = "C")
public static class C {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
long id;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
@ManyToOne
A a;
public A getA() {
return a;
}
public void setA(A a) {
this.a = a;
}
@ManyToOne
B b;
public B getB() {
return b;
}
public void setB(B b) {
this.b = b;
}
}
}
Login
FAQ
Search