Обсуждение: preparedStatement, Batch, DEFAULT, getGeneratedKeys()

Поиск
Список
Период
Сортировка

preparedStatement, Batch, DEFAULT, getGeneratedKeys()

От
Martin Eriksson
Дата:
Hi,

I've been scourging the mailing list for a while and general google/stack overflow looking for a solution dealing with JDBC, and batching..

  1. appears that there is no way to specific which keys you want to be returned with preparedStatment.getGeneratedKeys() ? I had assumed I could define it as part of the query with "RETURNING column1, column2 but that appear not to work.. its always using RETURNING * which makes it a bit expensive when some columns are like huge blobs.. or will it use the insert values provided and not actually get them sent from the database to client? e.g. if I would do around 100,000 inserts in a batch. The client is far from DB, and one of the fields is a large blob field, would the query return the result to the client with the returning * or are only actually generated keys returned back as data? e.g. 3 fields one timestamp with a now() default, and a big text field, to get at the date field I need to have RETURN_GENERATED_KEYS set, would the large text field be transfered from db to client or would only the timestamp field data be transfered since that is really the only field with "generated keys"?
  2. I've been searching and searching for a way to set "DEFAULT" on a prepared statement, is it even possible? e.g. "INSERT INTO a (id) VALUES (?) and then doing something like prepStatment.setInt(1,"DEFAULT");  I have found no way of doing this... when doing big batches that would be very nice indeed.... to not having to split everything depending on the existence of data in default fields
Cheers
Martin

Re: preparedStatement, Batch, DEFAULT, getGeneratedKeys()

От
dmp
Дата:
Martin Eriksson wrote:
 > Hi,
 >
 > I've been scourging the mailing list for a while and general
 > google/stack overflow looking for a solution dealing with JDBC, and
 > batching..
 >
 >    2. I've been searching and searching for a way to set "DEFAULT" on a
 >       prepared statement, is it even possible? e.g. "INSERT INTO a (id)
 >       VALUES (?) and then doing something like
 >       prepStatment.setInt(1,"DEFAULT"); I have found no way of doing
 >       this... when doing big batches that would be very nice indeed....
 >       to not having to split everything depending on the existence of
 >       data in default fields
 >
 > Cheers
 > Martin
 >

Hello Martin,

With regard to 2.

Some databases do allow you to insert default in the prepared statement,
but I have not found that to be the case with PostgreSQL to my knowledge.

Default can be inserted though via the following.

Given:

CREATE TABLE "keY_tAble2" (
   "Host" char(60) NOT NULL default '',
   "Db" char(64) NOT NULL default '',
   "Username" char(16) NOT NULL default '',
   Select_priv boolean NOT NULL default TRUE,
   PRIMARY KEY ("Host","Db","Username")
);

To give the default value to "Host" Use the following prepared statement.

INSERT INTO "public"."keY_tAble2" ("Db", "Username", "select_priv") VALUES (?, ?, ?)

I guess as you said though this does not help with batching.

danap.