[DBUTILS-54] Generated key handling for updates

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.2, Nightly Builds
• Assignee: Unassigned
• Reporter: Michael V
• Created: 09/Apr/09 22:57
• Updated: 05/Nov/09 12:30

Description

It would be great (and fairly easy to do) to provide a way to get autogenerated keys from QueryRunner.update. There was an email thread about this in 2004 but it seems it never was actually implemented.
http://mail-archives.apache.org/mod_mbox/commons-dev/200406.mbox/[e-mail]%3E

The thought is to provide an ability to recover generated keys, for instance by providing a result set handler, in which case prepared statement would be generated with RETURN_GENERATED_KEYS and getGeneratedKeys() would be passed to the result handler.

It seems that in 1.2 there is a way to get PreparedStatement and work with QueryRunner more as a support to JDBC but IMO it would be cool to add this feature.

example solution:

 
    public int update(Connection conn, String sql, Object... params)
        throws SQLException {
          update(sql, null, params);
    }
    protected PreparedStatement prepareStatement(Connection conn, String sql, int autoGeneratedKeys)
        throws SQLException {
        return conn.prepareStatement(sql, autoGeneratedKeys);
    }
    public int update(Connection conn, String sql, ResultSetHandler<?> rsh, Object... params)
        throws SQLException {

        PreparedStatement stmt = null;
        int rows = 0;

        try {
            stmt = this.prepareStatement(conn, sql, rsh==null?Statement.NO_GENERATED_KEY:Statement.RETURN_GENERATED_KEYS);
            this.fillStatement(stmt, params);
            rows = stmt.executeUpdate();
            if(rsh!=null)
                 rsh.handle(stmt.getGeneratedKeys());
        } catch (SQLException e) {
            this.rethrow(e, sql, params);
        } finally {
            close(stmt);
        }

        return rows;
    }

Thanks!