Commons DbUtils
  1. Commons DbUtils
  2. DBUTILS-50

Support CallableStatement "out" parameters

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.7
    • Labels:
      None

      Description

      Using fillStatement and the new fillStatementWithBean, you can use a CallableStatement like a PreparedStatement, retrieve its ResultSet and handle it with a ResultSetHandler. But we don't yet support registering "out" parameters on the CallableStatement in a convenient way and retrieving them back into an object.

      DBUTILS-28 requests stored procedure support and provides a patch, but I don't like the patch. Regardless, we really should support a few helpers like:

      void registerOutParameters(CallableStatement stmt, int... sqlTypes)
      
      Object[] getOutParameters(CallableStatement stmt)
      
      void registerOutParameters(CallableStatement stmt, Class<?> beanClass)
      
      <T> T getOutParameters(CallableStatement stmt, Class<T> beanClass)
      

      You should be able to write code like this:

      CallableStatement stmt = runner.prepareCall(myString);
      helper.registerOutParameters(stmt, MyBean.class);
      queryRunner.fillStatement(stmt, foo, bar, baz);
      stmt.executeUpdate();
      MyBean bean = helper.getOutParameters(stmt, MyBean.class);
      

      Or like this:

      CallableStatement stmt = runner.prepareCall(myString);
      helper.registerOutParameters(stmt, TINYINT, DECIMAL);
      queryRunner.fillStatement(stmt, foo, bar, baz);
      stmt.executeUpdate();
      Object[] result = helper.getOutParameters(stmt);
      
      1. DBUTILS-50.patch
        41 kB
        Raymond DeCampo

        Issue Links

          Activity

          Dan Fabulich created issue -
          Henri Yandell made changes -
          Field Original Value New Value
          Link This issue is duplicated by DBUTILS-28 [ DBUTILS-28 ]
          Hide
          Liam Seamus Coughlin added a comment -

          I really dislike the idea of having a public fillStatementMethod, or of having a user deal with a statement directly at all really – part of debutils is hiding a lot of the nastyness associated with jdbc – I'd rather see a CallableStatementRunner which has a seperate interface from the queryrunner then a mixnmatch of helper object calls and jdbc calls, and direct calls to row handlers, etc.

          Show
          Liam Seamus Coughlin added a comment - I really dislike the idea of having a public fillStatementMethod, or of having a user deal with a statement directly at all really – part of debutils is hiding a lot of the nastyness associated with jdbc – I'd rather see a CallableStatementRunner which has a seperate interface from the queryrunner then a mixnmatch of helper object calls and jdbc calls, and direct calls to row handlers, etc.
          Hide
          Raymond DeCampo added a comment -

          Implementation of the desired functionality which (I hope) stays true to the dbutils project as much as possible. Essentially adds a class representing OUT parameters allowing users to intermix them with the list of parameters for the SQL statement. Added execute() methods to QueryRunner, which use CallableStatements and handle the OUT parameters; one flavor for procedures returning one or more result sets and one for procedures returning update counts. Unit tests are included.

          Show
          Raymond DeCampo added a comment - Implementation of the desired functionality which (I hope) stays true to the dbutils project as much as possible. Essentially adds a class representing OUT parameters allowing users to intermix them with the list of parameters for the SQL statement. Added execute() methods to QueryRunner, which use CallableStatements and handle the OUT parameters; one flavor for procedures returning one or more result sets and one for procedures returning update counts. Unit tests are included.
          Raymond DeCampo made changes -
          Attachment DBUTILS-50.patch [ 12667910 ]
          Hide
          Carl Hall added a comment -

          Hey Raymond DeCampo, thanks for the extensive patch. I'm looking through it now and hope to get back to you soon.

          Show
          Carl Hall added a comment - Hey Raymond DeCampo , thanks for the extensive patch. I'm looking through it now and hope to get back to you soon.
          Carl Hall made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Assignee Carl Hall [ thecarlhall ]
          William R. Speirs made changes -
          Link This issue is duplicated by DBUTILS-104 [ DBUTILS-104 ]
          Hide
          Carl Hall added a comment -

          Patch looks great. This has been committed with revision r1674183. Thanks, Raymond DeCampo!

          Show
          Carl Hall added a comment - Patch looks great. This has been committed with revision r1674183. Thanks, Raymond DeCampo !
          Carl Hall made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Carl Hall made changes -
          Fix Version/s 1.7 [ 12327380 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open In Progress In Progress
          2240d 1h 57m 1 Carl Hall 15/Apr/15 19:55
          In Progress In Progress Resolved Resolved
          1d 6h 44m 1 Carl Hall 17/Apr/15 02:39

            People

            • Assignee:
              Carl Hall
              Reporter:
              Dan Fabulich
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development