Refering to Data binding
I'm tring to do some mass insertion, so i'm using data binding, now the
question is that i have (for the moment) a some field that will get the
same value.
is there a way to make it while on prepare? So that i only pass one
value to the execute??
Thanks.
One more thing... could i prepare a group of insertions together?
Like: "INSERT INTO theTable (field1, field2, field3....)
VALUES (?, ?, ?, ...);
INSERT INTO theSubTable(fieldA, fieldB, fieldC,...)
VALUES (?, ?, ?, ...);"
> I'm tring to do some mass insertion, so i'm using data binding, now the
> question is that i have (for the moment) a some field that will get the
> same value.
> is there a way to make it while on prepare? So that i only pass one
> value to the execute??
> Thanks.
>
Always use data binding.
To fill two (or more) columns with *one *setXXX():
select * from t;
id name
0 KARL
1 JAMES
2 SIMON
3 MIKE
2 SIMON
2 SIMON
DAVID
MICHAEL
The Statement:
insert into t(id, name)
select a * 100, a::varchar(255)
from table(multiset(select ?::integer as a
from table(set{1})
) );
In Java:
PreparedStatement pstat = conn.prepareStatement("insert into ...");
pstat.setInt(1, 100);
pstat.executeUpdate();
The Result:
select * from t;
id name
0 KARL
1 JAMES
2 SIMON
3 MIKE
2 SIMON
2 SIMON
DAVID
MICHAEL
100 1
But, please, test this for performance!!! (mass insertion!)
> One more thing... could i prepare a group of insertions together?
> Like: "INSERT INTO theTable (field1, field2, field3....)
> VALUES (?, ?, ?, ...);
> INSERT INTO theSubTable(fieldA, fieldB, fieldC,...)
> VALUES (?, ?, ?, ...);">
>
I don't think so.
To fill two (or more) columns with *one *setXXX():
select * from t;
id name
0 KARL
1 JAMES
2 SIMON
3 MIKE
2 SIMON
2 SIMON
DAVID
MICHAEL
The Statement:
insert into t(id, name)
select a * 100, a::varchar(255)
from table(multiset(select ?::integer as a
from table(set{1})
) );
In Java:
PreparedStatement pstat = conn.prepareStatement("insert into ...");
pstat.setInt(1, 1);
pstat.executeUpdate();
The Result:
select * from t;
id name
0 KARL
1 JAMES
2 SIMON
3 MIKE
2 SIMON
2 SIMON
DAVID
MICHAEL
100 1
But, please, test this for performance!!! (mass insertion!)
> One more thing... could i prepare a group of insertions together?
> Like: "INSERT INTO theTable (field1, field2, field3....)
> VALUES (?, ?, ?, ...);
> INSERT INTO theSubTable(fieldA, fieldB, fieldC,...)
> VALUES (?, ?, ?, ...);">
>
I don't think so.