Hello all,
Our webapp relies heavily on JDBC's Statement.getGeneratedKeys()
abstraction. We are investigating switching from Mysql to Postgre so I
created a basic implementation of this feature in the 8.3-604 version of
the driver. The diff patch is attached. On Linux you can apply it by:
1) Download and extract: postgresql-jdbc-8.3-604.src.tar.gz
2) Copy the attached patch into the folder
3) Open a terminal to the folder and run: patch -p1 <
getGeneratedKeys.patch
4) ant jar
Here's an example usage (nothing special):
PreparedStatement ps = con.prepareStatement("INSERT INTO foo (name)
VALUES (?)", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "sweet!");
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
System.out.println(rs.getInt(1));
ps.close();
The patch modifies the following files:
org/postgresql/core/Query.java
org/postgresql/core/v2/V2Query.java
org/postgresql/core/v3/CompositeQuery.java
org/postgresql/core/v3/SimpleQuery.java
org/postgresql/jdbc2/AbstractJdbc2Statement.java
org/postgresql/jdbc3/AbstractJdbc3Connection.java
org/postgresql/jdbc3/AbstractJdbc3Statement.java
Limitations of this implementation:
* You must use PreparedStatement (not Statement)
* Your table can only have one auto generated column
* Column must have default value LIKE 'nextval(%seq%)' (this is
the case of SERIAL columns)
* Must use JDBC3 or higher have (ie >= java 1.5)
* Must be running against a version of Postgre that supports the
"RETURNING" clause (eg INSERT INTO foo (name) VALUES('hello') RETURNING
foo_id)
I'm hoping this patch will be included in the official driver. I
realize that it has some limitations but it will cover 95% of user needs.
Let me know what you think. Improvements are welcome.
- Adam
Videx Inc. 1105 N. E. Circle Blvd. Corvallis OR 97330 (541) 758-0521
CONFIDENTIAL COMMUNICATION: The email message and any attachments are intended only for the addressee. They may be
privileged,confidential, and protected from disclosure. If you are not the intended recipient, any dissemination,
distribution,or copying is expressly prohibited. If you received this email message in error, please notify the sender
immediatelyby replying to this e-mail message or by telephone
