getGeneratedKeys() problem

Поиск
Список
Период
Сортировка
От Viktor Pravdin
Тема getGeneratedKeys() problem
Дата
Msg-id 004801cb7518$cbc632e0$635298a0$@pravdin@disi.unitn.it
обсуждение исходный текст
Ответы Re: getGeneratedKeys() problem
Список pgsql-jdbc
Hello,

I've encountered a strange problem with the Statement.getGeneratedKeys()
method. In our project we use Hibernate with PostgreSQL, the issue was
detected with Hibernate 3.5.0-Final and 3.6.0.Final, PostgreSQL server
9.0.0-1 and PostgreSQL JDBC driver 9.0-801.jdbc3/4 on Windows 7 32 bit. The
issue is that for some reason the call to
PreparedStatement.getGeneratedKeys() returns all columns of the table and
not just the one that has the generated key, and since Hibernate expects the
identity column in case of the long IDs to be the first one in the list and
the schema is generated automatically and thus the column order is
undefined, we got an org.postgresql.util.PSQLException: Bad value for type
long: <whatever column value is the first one>. In other words, Hibernate
expects getGeneratedKeys() to return only the column which contains the long
ID, but instead the driver returns all of them, and if the first one isn't
the ID column then Hibernate crashes.

If I understand correctly the prepared statement is generated by
AbstractJdbc3Connection.prepareStatement (String sql, int autoGeneratedKeys)
which results in a query like "INSERT blah-blah RETURNING *". Could it be
the case that this "RETURNING *" statement is what actually causes all
columns to be returned by getGeneratedKeys()?

Here's a really simple test case:

CREATE TABLE users
(
  id bigserial NOT NULL,
  creation_date timestamp without time zone NOT NULL,
  modification_date timestamp without time zone NOT NULL,
  email character varying(255) NOT NULL,
  "login" character varying(255) NOT NULL,
  "password" character varying(255) NOT NULL
)

public static void main(String[] args) throws SQLException {
        Connection conn=DriverManager.getConnection(dbUrl);
        PreparedStatement ps=conn.prepareStatement("INSERT INTO USERS
(creation_date,modification_date,email,\"login\",\"password\") VALUES
(?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
        ps.setDate(1, new Date(System.currentTimeMillis()));
        ps.setDate(2,new Date(System.currentTimeMillis()));
        ps.setString(3, "a@b.c.d");
        ps.setString(4,"testjdbc");
        ps.setString(5,"test");
        ps.executeUpdate();
        System.out.println("Column count:
"+ps.getGeneratedKeys().getMetaData().getColumnCount());
    }
It returns 6 columns instead of 1.

So, let me sum it up: in the case of
  PreparedStatement ps = connection.prepareStatement( sql,
PreparedStatement.RETURN_GENERATED_KEYS );
  ps.executeUpdate();
  ResultSet rs = rs.getGeneratedKeys();
the result set contains all columns of the inserted record instead of
containing the ID column(s) only. Is it a bug in the JDBC driver or is there
something wrong in the way the statement is prepared? How could it be
resolved in the latter case?

Thanks,
Viktor Pravdin


В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Robert Wimmer
Дата:
Сообщение:
Следующее
От: Adam Todorski
Дата:
Сообщение: Re: getGeneratedKeys() problem