Uploaded image for project: 'Commons DbUtils'
  1. Commons DbUtils
  2. DBUTILS-54

Generated key handling for updates

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • 1.2, Nightly Builds
    • None
    • None

    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/%3C20040602024627.21604.qmail@web50606.mail.yahoo.com%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!

      Attachments

        1. QueryRunner.patch
          22 kB
          Julien Aymé
        2. GeneratedKeysHandler.java
          1 kB
          eugen p.
        3. GenKeyQueryRunner.java
          3 kB
          eugen p.
        4. GenKeyQueryRunner.java
          14 kB
          Julien Aymé

        Activity

          People

            Unassigned Unassigned
            gverig Michael V
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: