How do I register out parameter in CallableStatement?

3089 ワード

Only for reference.
 
import java.sql.*;

public class TestCallPrecedure {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            // Creates a CallableStatement for executing the
            // stored procedure
            String query = "call GET_DETAIL_BY_PRODUCT(?, ?, ?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setString(1, "bag");

            // Registers the out parameters
            cb.registerOutParameter(2, Types.VARCHAR);
            cb.registerOutParameter(3, Types.DECIMAL);
            cb.registerOutParameter(4, Types.INTEGER);

            // Executes the query
            cb.executeQuery();

            // Gets the query result output
            System.out.println("Code    : " + cb.getString(2));
            System.out.println("Price   : " + cb.getBigDecimal(3));
            System.out.println("Quantity: " + cb.getInt(4));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

 
Mysql Stored Procedure:
DROP PROCEDURE IF EXISTS kodejavadb.GET_DETAIL_BY_PRODUCT;
CREATE PROCEDURE kodejavadb.`GET_DETAIL_BY_PRODUCT`(IN vproduct varchar(30),
OUT vcode varchar(5),
OUT vprice decimal,
OUT vqty int)
BEGIN
  SELECT
  code INTO vcode
  FROM orcl.products
  WHERE name = vproduct;

  SELECT
  price INTO vprice
  FROM orcl.products
  WHERE name = vproduct;

  SELECT
  qty INTO vqty
  FROM orcl.products
  WHERE name = vproduct;
END;