Re: A basic Statement.getGeneratedKeys() implementation (patch attached) - Mailing list pgsql-jdbc

As usual I forgot to attach it!  Here you go.

Adam B wrote:
> 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 
>
>
>



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 
diff -x .svn -rupN orig/org/postgresql/core/Query.java new/org/postgresql/core/Query.java
--- orig/org/postgresql/core/Query.java    2008-01-07 22:56:27.000000000 -0800
+++ new/org/postgresql/core/Query.java    2009-05-01 11:11:08.000000000 -0700
@@ -46,7 +46,11 @@ public interface Query {
      */
     String toString(ParameterList parameters);

-    /**
+    ///adamb getGeneratedKeys support
+    /**return true if this query is an INSERT with a RETURNING clause (eg INSERT INTO foo RETURNING foo_id)*/
+    boolean isReturningInsert();
+
+        /**
      * Close this query and free any server-side resources associated
      * with it. The resources may not be immediately deallocated, but
      * closing a Query may make the deallocation more prompt.
diff -x .svn -rupN orig/org/postgresql/core/v2/V2Query.java new/org/postgresql/core/v2/V2Query.java
--- orig/org/postgresql/core/v2/V2Query.java    2008-01-07 22:56:27.000000000 -0800
+++ new/org/postgresql/core/v2/V2Query.java    2009-05-01 11:11:58.000000000 -0700
@@ -97,6 +97,12 @@ class V2Query implements Query {
         return sbuf.toString();
     }

+    ///adamb getGeneratedKeys support
+    public boolean isReturningInsert()
+    {
+        throw new UnsupportedOperationException("Must use JDBC3 for this getGeneratedKeys hack");
+    }
+
     public void close() {
     }

diff -x .svn -rupN orig/org/postgresql/core/v3/CompositeQuery.java new/org/postgresql/core/v3/CompositeQuery.java
--- orig/org/postgresql/core/v3/CompositeQuery.java    2008-01-07 22:56:27.000000000 -0800
+++ new/org/postgresql/core/v3/CompositeQuery.java    2009-05-01 10:54:30.000000000 -0700
@@ -52,7 +52,13 @@ class CompositeQuery implements V3Query
             subqueries[i].close();
     }

-    public SimpleQuery[] getSubqueries() {
+        ///adamb getGeneratedKeys support
+        public boolean isReturningInsert()
+        {
+            return subqueries[0].isReturningInsert();
+        }
+
+        public SimpleQuery[] getSubqueries() {
         return subqueries;
     }

diff -x .svn -rupN orig/org/postgresql/core/v3/SimpleQuery.java new/org/postgresql/core/v3/SimpleQuery.java
--- orig/org/postgresql/core/v3/SimpleQuery.java    2008-01-07 22:56:27.000000000 -0800
+++ new/org/postgresql/core/v3/SimpleQuery.java    2009-05-01 11:12:55.000000000 -0700
@@ -54,7 +54,23 @@ class SimpleQuery implements V3Query {
         unprepare();
     }

-    //
+    ///adamb getGeneratedKeys support
+    public boolean isReturningInsert()
+    {
+        if (fragments[0].contains("INSERT INTO")  //this check will catch most and avoid the new String instance in
thesecond condition 
+            || fragments[0].toUpperCase().contains("INSERT INTO"))
+        {
+            for (String fragment: fragments)
+            {
+                if (fragment.contains("RETURNING "))
+                    return true;
+            }
+        }
+
+        return false;
+    }
+
+        //
     // V3Query
     //

@@ -121,7 +137,7 @@ class SimpleQuery implements V3Query {
         encodedStatementName = null;
     }

-    private final String[] fragments;
+        private final String[] fragments;
     private String statementName;
     private byte[] encodedStatementName;
     private PhantomReference cleanupRef;
diff -x .svn -rupN orig/org/postgresql/jdbc2/AbstractJdbc2Statement.java
new/org/postgresql/jdbc2/AbstractJdbc2Statement.java
--- orig/org/postgresql/jdbc2/AbstractJdbc2Statement.java    2008-04-02 10:06:04.000000000 -0700
+++ new/org/postgresql/jdbc2/AbstractJdbc2Statement.java    2009-05-01 11:23:55.000000000 -0700
@@ -301,11 +301,24 @@ public abstract class AbstractJdbc2State
             executeWithFlags(0);
             return 0;
         }
-        if (executeWithFlags(QueryExecutor.QUERY_NO_RESULTS))
-            throw new PSQLException(GT.tr("A result was returned when none was expected."),
-                                    PSQLState.TOO_MANY_RESULTS);
+        ///adamb getGeneratedKeys support:
+        //if the query has the RETURNING clause then the caller will likely want the result set
+        else if (preparedQuery.isReturningInsert())
+        {
+            if (!executeWithFlags(0))
+                throw new PSQLException(GT.tr("Results from RETURNING clause were not given"),
PSQLState.UNEXPECTED_ERROR);

-        return getUpdateCount();
+            //INSERT statements always return 1  (returning getUpdateCount() doesn't seem to work due to the RETURNING
clause) 
+            return 1;
+        }
+        else
+        {
+            if (executeWithFlags(QueryExecutor.QUERY_NO_RESULTS))
+                throw new PSQLException(GT.tr("A result was returned when none was expected."),
+                                        PSQLState.TOO_MANY_RESULTS);
+
+            return getUpdateCount();
+        }
     }

     /*
diff -x .svn -rupN orig/org/postgresql/jdbc3/AbstractJdbc3Connection.java
new/org/postgresql/jdbc3/AbstractJdbc3Connection.java
--- orig/org/postgresql/jdbc3/AbstractJdbc3Connection.java    2008-01-07 22:56:29.000000000 -0800
+++ new/org/postgresql/jdbc3/AbstractJdbc3Connection.java    2009-05-01 11:17:21.000000000 -0700
@@ -9,7 +9,7 @@
 */
 package org.postgresql.jdbc3;

-import java.util.Properties;
+import java.util.*;
 import java.sql.*;

 import org.postgresql.util.PSQLException;
@@ -346,15 +346,77 @@ public abstract class AbstractJdbc3Conne
      * @since 1.4
      */
     public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
-    throws SQLException
+        throws SQLException
     {
+        ///adamb getGeneratedKeys support
         if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
-            throw new PSQLException(GT.tr("Returning autogenerated keys is not supported."),
PSQLState.NOT_IMPLEMENTED);
-        return prepareStatement(sql);
+        {
+            //get the table being inserted into
+            String tableName = parseInsertTableName(sql);
+            if (tableName == null)
+                throw new PSQLException(GT.tr("Unable to parse table name from INSERT statement"),
PSQLState.SYNTAX_ERROR);
+
+            Statement st = createStatement();
+            try
+            {
+                String columnQuery = "SELECT column_name FROM information_schema.columns WHERE table_name = '"
+                    + tableName + "' AND column_default LIKE 'nextval(%seq%)'";
+
+                ResultSet rs = st.executeQuery(columnQuery);
+
+                if (rs.next())
+                {
+                    String serialColumn = rs.getString(1);
+
+                    if (!rs.next())
+                    {
+                        //add the RETURNING clause
+                        return prepareStatement(sql + " RETURNING " + serialColumn);
+                    }
+                    //else there are more results so it's ambiguous
+                }
+
+                throw new PSQLException("Auto-increment column for `" + tableName + "` could not be determined
unambiguously."
+                    + "  In other words the following query did not return exactly 1 row: " + columnQuery,
PSQLState.SYNTAX_ERROR);
+            }
+            finally
+            {
+                st.close();
+            }
+        }
+        else
+            return prepareStatement(sql);
     }

+    /**extract the table name from an INSERT INTO statement.
+     @return null if not found.  the table name will be all lower case.*/
+    private String parseInsertTableName(String sql)
+    {
+        final String insert_into = "insert into ";

-    /**
+        sql = sql.toLowerCase();
+
+        int start = sql.indexOf(insert_into) + insert_into.length();
+        if (start != -1)
+        {
+            int spc = sql.indexOf(' ', start);
+            int paren = sql.indexOf('(', start);
+            int end;
+            if (spc < paren && spc != -1)
+                end = spc;
+            else
+                end = paren;
+
+            if (end != -1)
+            {
+                return sql.substring(start, end).trim();
+            }
+        }
+
+        return null;
+    }
+
+        /**
      * Creates a default <code>PreparedStatement</code> object capable
      * of returning the auto-generated keys designated by the given array.
      * This array contains the indexes of the columns in the target
diff -x .svn -rupN orig/org/postgresql/jdbc3/AbstractJdbc3Statement.java
new/org/postgresql/jdbc3/AbstractJdbc3Statement.java
--- orig/org/postgresql/jdbc3/AbstractJdbc3Statement.java    2008-01-07 22:56:29.000000000 -0800
+++ new/org/postgresql/jdbc3/AbstractJdbc3Statement.java    2009-05-01 11:15:17.000000000 -0700
@@ -106,7 +106,9 @@ public abstract class AbstractJdbc3State
      */
     public ResultSet getGeneratedKeys() throws SQLException
     {
-        return createDriverResultSet(new Field[0], new Vector());
+        //return createDriverResultSet(new Field[0], new Vector());
+        ///adamb getGeneratedKeys support
+        return getResultSet();
     }

     /**