0

I have two tables:

CREATE TABLE transaction (
  id bigserial PRIMARY KEY,
  transactiondataid bigint,
  baseamount integer,
  pricelistamount integer,
  trxamount integer,
  ...
);

CREATE TABLE transactiondata (
  id bigserial PRIMARY KEY,
  productname character varying(255),
  ...
);

transaction references transactiondata as 1:1 relation.

How can I do batch inserts and set the transactiondataid of transaction correctly in the batch? Is it possible at all?

If it is not possible because of dbutils, is it possible with simple PreparedStatement?

Currently, I have to following single insert, which works:

    QueryRunner queryRunner = new QueryRunner([getDatasource()]);
    Long id = queryRunner.insert("insert into transactiondata (accountgroupname) values(?)", new ScalarHandler<Long>(), "Cola");
    queryRunner.insert("insert into transaction (baseamount, transactiondataid) values(?, ?)", new ScalarHandler<Long>(), 10, id);

But since I need to insert millions of records, I need to do some batching...

badera
  • 1,495
  • 2
  • 24
  • 49
  • Why not create a selectable function/stored procedure in postgresql and then use queryRunner.Execute to Select id from this function you created. You can pass in all your parameters into this function and the function can do the appropriate SQL inside. – Ed Mendez Aug 14 '17 at 18:01
  • @Ed Mendez: Thanks for the idea; well, there are even other alternatives: Request first as much ids as needed for the batch then assign them... so yes, there are alternatives, if it is not possible the common way. And this is the question. – badera Aug 15 '17 at 11:40

0 Answers0