Commons DbUtils
  1. Commons DbUtils
  2. DBUTILS-50

Support CallableStatement "out" parameters

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • 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 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Dan Fabulich
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Development