Используя Хранимые процедуры (Учебные руководства Java™> JDBC (ТМ) Доступ к базе данных> Основы JDBC)


След: JDBC (ТМ) Доступ к базе данных
Урок: Основы JDBC
Используя Хранимые процедуры
Домашняя страница > JDBC (ТМ) Доступ к базе данных > Основы JDBC

Используя Хранимые процедуры

Хранимая процедура является группой SQL-операторов, которые формируют логический модуль и выполняют определенную задачу, и они используются, чтобы инкапсулировать ряд операций или запросов, чтобы выполниться на сервере базы данных. Например, операции на базе данных сотрудника (наем, огонь, продвигают, поиск) могли быть кодированы как хранимые процедуры, выполняемые кодом программы. Хранимые процедуры могут быть скомпилированы и выполнены с различными параметрами и результатами, и они могут иметь любую комбинацию ввода, вывести, и ввести/вывести параметры.

Отметьте, что хранимые процедуры поддерживаются большинством DBMSs, но есть изрядное количество изменения в их синтаксисе и возможностях. Следовательно, учебное руководство содержит два класса, StoredProcedureJavaDBSample и StoredProcedureMySQLSample демонстрировать, как создать хранимые процедуры в DB Java и MySQL, соответственно.

Эта страница затрагивает следующие темы:

Краткий обзор Примеров Хранимых процедур

Примеры StoredProcedureJavaDBSample.java и StoredProcedureMySQLSample.java создайте и вызовите следующие хранимые процедуры:

Режимы параметра

Атрибуты параметра IN (значение по умолчанию), OUT, и INOUT режимы параметра. Они определяют действие формальных параметров. Следующая таблица суммирует информацию о режимах параметра.

Характеристика Режима Параметра В INOUT

Это должно быть определено в определении хранимой процедуры?

Нет; если опущено, то режим параметра формального параметра IN.

Должен быть определен.

Должен быть определен.

Параметр передает значение к хранимой процедуре или возвращает значение?

Передачи оценивают хранимой процедуре.

Возвраты оценивают вызывающей стороне.

Оба; начальные значения передач к хранимой процедуре; возвраты обновленные значения к вызывающей стороне.

Совершает поступок формального параметра как константу или переменную в хранимой процедуре?

Формальный параметр действует как константа.

Формальный параметр действует как неинициализированная переменная.

Формальный параметр действует как инициализированная переменная.

Формальный параметр может быть присвоен значение в хранимой процедуре?

Формальный параметр не может быть присвоен значение.

Формальный параметр не может использоваться в выражении; должен быть присвоен значение.

Формальный параметр должен быть присвоен значение.

Какие виды фактических параметров (параметры) можно передать к хранимой процедуре?

Фактический параметр может быть константой, инициализировал переменную, литерал, или выражение.

Фактический параметр должен быть переменной.

Фактический параметр должен быть переменной.

Создание Хранимых процедур в DB Java

Отметьте: См. раздел "оператор CREATE PROCEDURE" в Справочнике DB Java для получения дополнительной информации о создании хранимых процедур в DB Java.

Создание и использование хранимой процедуры в DB Java включают следующие шаги:

  1. Создайте общедоступный статический метод Java в Java class: Этот метод выполняет необходимую задачу хранимой процедуры.
  2. Создайте хранимую процедуру: Эта хранимая процедура вызывает метод Java, который Вы создали.
  3. Упакуйте Java class (который содержит общедоступный статический метод Java, который Вы создали ранее) в файле JAR.
  4. Вызовите хранимую процедуру с CALL SQL-оператор. См., что раздел Вызывает Хранимые процедуры в DB Java и MySQL.

Создание Общедоступного Статического Метода Java

Следующий метод, StoredProcedureJavaDBSample.showSuppliers, содержит SQL-операторы что хранимая процедура SHOW_SUPPLIERS вызовы:

public static void showSuppliers(ResultSet[] rs)
    throws SQLException {

    Connection con = DriverManager.getConnection("jdbc:default:connection");
    Statement stmt = null;

    String query =
        "select SUPPLIERS.SUP_NAME, " +
        "COFFEES.COF_NAME " +
        "from SUPPLIERS, COFFEES " +
        "where SUPPLIERS.SUP_ID = " +
        "COFFEES.SUP_ID " +
        "order by SUP_NAME";

    stmt = con.createStatement();
    rs[0] = stmt.executeQuery(query);
}

SHOW_SUPPLIERS хранимая процедура не берет параметров. Можно определить параметры в хранимой процедуре, определяя их в сигнатуре метода Вашего общедоступного статического метода Java. Отметьте что метод showSuppliers содержит параметр типа ResultSet[]. Если Ваша хранимая процедура возвращает какое-либо число ResultSet объекты, определите один параметр типа ResultSet[] в Вашем методе Java. Кроме того, гарантируйте, что этот метод Java общедоступен и статичен.

Получите Connection объект от URL jdbc:default:connection. Это - соглашение в DB Java, чтобы указать, что хранимая процедура будет использовать в настоящий момент существующее Connection объект.

Отметьте что Statement объект не закрывается в этом методе. Не закрывайте никого Statement объекты в методе Java Вашей хранимой процедуры; если Вы делаете так, ResultSet объект не будет существовать, когда Вы выйдете CALL оператор, когда Вы вызываете свою хранимую процедуру.

Для хранимой процедуры, чтобы возвратить сгенерированный набор результатов, следует присвоить набор результатов компоненту массива ResultSet[] параметр. В этом примере сгенерированный набор результатов присваивается компоненту массива rs[0].

Следующий метод StoredProcedureJavaDBSample.showSuppliers:

public static void getSupplierOfCoffee(String coffeeName, String[] supplierName)
    throws SQLException {

    Connection con = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    String query =
        "select SUPPLIERS.SUP_NAME " +
        "from SUPPLIERS, COFFEES " +
        "where " +
        "SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
        "and ? = COFFEES.COF_NAME";

    pstmt = con.prepareStatement(query);
    pstmt.setString(1, coffeeName);
    rs = pstmt.executeQuery();

    if (rs.next()) {
        supplierName[0] = rs.getString(1);
    } else {
        supplierName[0] = null;
    }
}

Формальный параметр coffeeName имеет режим параметра IN. Этот формальный параметр используется как любой другой параметр в методе Java. Поскольку формальный параметр supplierName имеет режим параметра OUT, это должно использовать одномерный тип данных массива. Поскольку этот метод не производит набор результатов, определение метода не содержит параметр типа ResultSet[]. Чтобы получить значение от OUT формальный параметр, следует присвоить значение, которое будет получено к компоненту массива OUT формальный параметр. В этом примере полученное имя поставщика кофе присваивается компоненту массива supplierName[0].

Следующее является сигнатурой метода StoredProcedureJavaDBSample.raisePrice method:

public static void raisePrice(
   String coffeeName, double maximumPercentage,
   BigDecimal[] newPrice) throws SQLException

Поскольку формальный параметр newPrice имеет режим параметра INOUT, это должно использовать одномерный тип данных массива. DB Java отображается FLOAT и NUMERIC Типы данных SQL к double и java.math.BigDecimal Типы данных Java, соответственно.

Создание Хранимых процедур в DB Java со Сценариями SQL или API JDBC

DB Java использует язык программирования Java для своих хранимых процедур. Следовательно, когда Вы определяете хранимую процедуру, Вы определяете, какой Java class, чтобы вызвать и где DB Java может найти это.

Следующая выборка от StoredProcedureJavaDBSample.createProcedures создает названную хранимую процедуру SHOW_SUPPLIERS:

public void createProcedures(Connection con)
    throws SQLException {

    Statement stmtCreateShowSuppliers = null;

    // ...

    String queryShowSuppliers =
        "CREATE PROCEDURE SHOW_SUPPLIERS() " +
        "PARAMETER STYLE JAVA " +
        "LANGUAGE JAVA " +
        "DYNAMIC RESULT SETS 1 " +
        "EXTERNAL NAME " +
        "'com.oracle.tutorial.jdbc." +
        "StoredProcedureJavaDBSample." +
        "showSuppliers'";

    // ...

    try {
        System.out.println("Calling CREATE PROCEDURE");
        stmtCreateShowSuppliers = con.createStatement();

        // ...

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmtCreateShowSuppliers != null) {
            stmtCreateShowSuppliers.close();
        }
        // ...
    }
}

Следующий список описывает элементы процедуры, которые можно определить в CREATE PROCEDURE оператор:

Следующий оператор (который находится в StoredProcedureJavaDBSample.createProcedures) создает названную хранимую процедуру GET_SUPPLIERS_OF_COFFEE (разрывы строки были добавлены для ясности):

CREATE PROCEDURE GET_SUPPLIER_OF_COFFEE(
    IN coffeeName varchar(32),
    OUT supplierName
    varchar(40))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    DYNAMIC RESULT SETS 0
    EXTERNAL NAME 'com.oracle.tutorial.jdbc.
        StoredProcedureJavaDBSample.
        getSupplierOfCoffee'

У этой хранимой процедуры есть два формальных параметра, coffeeName и supplierName. Спецификаторы параметра IN и OUT вызываются режимами параметра. Они определяют действие формальных параметров. См. Режимы Параметра для получения дополнительной информации. Эта хранимая процедура не получает набор результатов, таким образом, элемент процедуры DYNAMIC RESULT SETS 0.

Следующий оператор создает названную хранимую процедуру RAISE_PRICE (разрывы строки были добавлены для ясности):

CREATE PROCEDURE RAISE_PRICE(
    IN coffeeName varchar(32),
    IN maximumPercentage float,
    INOUT newPrice float)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    DYNAMIC RESULT SETS 0
    EXTERNAL NAME 'com.oracle.tutorial.jdbc.
        StoredProcedureJavaDBSample.raisePrice'

Можно использовать сценарии SQL, чтобы создать хранимые процедуры в DB Java. См. сценарий javadb/create-procedures.sql и цель Муравья javadb-create-procedure в build.xml Муравей создает сценарий.

Класс Java пакета в Файле JAR

Муравей создает сценарий build.xml содержит цели, чтобы скомпилировать и упаковать учебное руководство в файле JAR. В командной строке измените текущий каталог на <JDBC tutorial directory>. Из этого каталога, выполненного следующая команда, чтобы скомпилировать и упаковать учебное руководство в файле JAR:

ant jar

Имя файла JAR <JDBC tutorial directory>/lib/JDBCTutorial.jar.

Муравей создает сценарий, добавляет файл JDBCTutorial.jar к пути class. Можно также определить расположение файла JAR в Вашем CLASSPATH переменная окружения. Это позволяет DB Java найти метод Java, который вызывает хранимая процедура.

Добавление Файла JAR Непосредственно к Базе данных

DB Java выглядит первым в Вашем пути class для любых необходимых классов, и затем в базе данных. Этот раздел показывает Вам, как добавить файлы JAR непосредственно к базе данных.

Используйте следующие системные процедуры, чтобы добавить JDBCTutorial.jar Файл JAR к базе данных (разрывы строки были добавлены для ясности):

CALL sqlj.install_jar(
  '<JDBC tutorial directory>/
  lib/JDBCTutorial.jar',
  'APP.JDBCTutorial', 0)
CALL sqlj.replace_jar(
  '<JDBC tutorial directory>/
  lib/JDBCTutorial.jar',
  'APP.JDBCTutorial')";
CALL syscs_util.syscs_set_database_property(
  'derby.database.classpath',
  'APP.JDBCTutorial')";

Отметьте: метод StoredProcedureJavaDBSample.registerJarFile демонстрирует, как вызвать эти системные процедуры. Если Вы вызываете этот метод, гарантируете, что изменили javadb-sample-properties.xml так, чтобы значение свойства jar_file устанавливается в полный путь JDBCTutorial.jar.

install_jar процедура в SQL схема добавляет файл JAR к базе данных. Первым параметром этой процедуры является имя полного пути файла JAR на компьютере, от которого выполняется эта процедура. Вторым параметром является идентификатор что использование DB Java, чтобы обратиться к файлу JAR. (Идентификатор APP схема значения по умолчанию DB Java.) replace_jar процедура заменяет файл JAR уже в базе данных.

Системная процедура SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY наборы или удаляют значение свойства базы данных на текущем соединении. Этот метод устанавливает свойство derby.database.classpath к идентификатору, определенному в install_jar файл. DB Java сначала смотрит в Вашем Java путь class для class, тогда это заглядывает derby.database.classpath.

Создание Хранимой процедуры в MySQL

Создание и использование хранимой процедуры в DB Java включают следующие шаги:

  1. Создайте хранимую процедуру со сценарием SQL или API JDBC
  2. Вызовите хранимую процедуру с CALL SQL-оператор. См., что раздел Вызывает Хранимые процедуры в DB Java и MySQL

Создание Хранимой процедуры в MySQL со Сценариями SQL или API JDBC

MySQL использует основанный на SQL синтаксис для своих хранимых процедур. Следующая выборка из сценария SQL mysql/create-procedures.sql создает названную хранимую процедуру SHOW_SUPPLIERS:

SELECT 'Dropping procedure SHOW_SUPPLIERS' AS ' '|
drop procedure if exists SHOW_SUPPLIERS|

# ...

SELECT 'Creating procedure SHOW_SUPPLIERS' AS ' '|
create procedure SHOW_SUPPLIERS()
    begin
        select SUPPLIERS.SUP_NAME,
        COFFEES.COF_NAME
        from SUPPLIERS, COFFEES
        where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
        order by SUP_NAME;
    end|

DROP PROCEDURE оператор удаляет ту процедуру SHOW_SUPPLIERS если это существует. В MySQL операторы в хранимой процедуре разделяются точками с запятой. Однако, различный разделитель обязан заканчиваться create procedure оператор. Этот пример использует канал (|) символ; можно использовать другой символ (или больше чем один символ). Этот символ, который разделяет операторы, определяется в delimiter атрибут в цели Муравья, которая вызывает этот сценарий. Эта выборка от файла типа "build" Муравья build.xml (разрывы строки были вставлены для ясности):

<target name="mysql-create-procedure">

  <sql driver="${DB.DRIVER}"
       url="${DB.URL}" userid="${DB.USER}"
       password="${DB.PASSWORD}"
       classpathref="CLASSPATH"
       print="true"
       delimiter="|"
       autocommit="false"
       onerror="abort">
       <transaction
         src="./sql/${DB.VENDOR}/
           create-procedures.sql">
       </transaction>
  </sql>

</target>

Альтернативно, можно использовать DELIMITER SQL-оператор, чтобы определить различный символ-разделитель.

CREATE PROCEDURE оператор состоит из имени процедуры, списка разделенных запятой значений параметров в круглых скобках, и SQL-операторов в пределах BEGIN и END ключевые слова.

Можно использовать API JDBC, чтобы создать хранимую процедуру. Следующий метод, StoredProcedureMySQLSample.createProcedureShowSuppliers, выполняет те же самые задачи как предыдущий сценарий:

public void
    createProcedureShowSuppliers()
    throws SQLException {
    String createProcedure = null;

    String queryDrop =
        "DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS";

    createProcedure =
        "create procedure SHOW_SUPPLIERS() " +
        "begin " +
            "select SUPPLIERS.SUP_NAME, " +
            "COFFEES.COF_NAME " +
            "from SUPPLIERS, COFFEES " +
            "where SUPPLIERS.SUP_ID = " +
            "COFFEES.SUP_ID " +
            "order by SUP_NAME; " +
        "end";
    Statement stmt = null;
    Statement stmtDrop = null;

    try {
        System.out.println("Calling DROP PROCEDURE");
        stmtDrop = con.createStatement();
        stmtDrop.execute(queryDrop);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmtDrop != null)
        {
            stmtDrop.close();
        }
    }

    try {
        stmt = con.createStatement();
        stmt.executeUpdate(createProcedure);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

Отметьте, что разделитель не был изменен в этом методе.

Хранимая процедура SHOW_SUPPLIERS генерирует набор результатов, даже при том, что тип возврата метода createProcedureShowSuppliers void и метод не содержит параметров. Набор результатов возвращается когда хранимая процедура SHOW_SUPPLIERS вызывается с методом CallableStatement.executeQuery:

CallableStatement cs = null;
cs = this.con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

Следующая выборка от метода StoredProcedureMySQLSample.createProcedureGetSupplierOfCoffee содержит SQL-запрос, который создает названную хранимую процедуру GET_SUPPLIER_OF_COFFEE:

public void createProcedureGetSupplierOfCoffee()
    throws SQLException {

    String createProcedure = null;

    // ...

    createProcedure =
        "create procedure GET_SUPPLIER_OF_COFFEE(" +
        "IN coffeeName varchar(32), " +
        "OUT supplierName varchar(40)) " +
        "begin " +
            "select SUPPLIERS.SUP_NAME into " +
            "supplierName " +
            "from SUPPLIERS, COFFEES " +
            "where SUPPLIERS.SUP_ID = " +
            "COFFEES.SUP_ID " +
            "and coffeeName = COFFEES.COF_NAME; " +
            "select supplierName; " +
        "end";
    // ...
}

У этой хранимой процедуры есть два формальных параметра, coffeeName и supplierName. Спецификаторы параметра IN и OUT вызываются режимами параметра. Они определяют действие формальных параметров. См. Режимы Параметра для получения дополнительной информации. Формальные параметры определяются в SQL-запросе, не в методе createProcedureGetSupplierOfCoffee. Присваивать значение OUT параметр supplierName, эта хранимая процедура использует a SELECT оператор.

Следующая выборка от метода StoredProcedureMySQLSample.createProcedureRaisePrice содержит SQL-запрос, который создает названную хранимую процедуру RAISE_PRICE:

public void createProcedureRaisePrice()
    throws SQLException {

    String createProcedure = null;

    // ...

    createProcedure =
        "create procedure RAISE_PRICE(" +
        "IN coffeeName varchar(32), " +
        "IN maximumPercentage float, " +
        "INOUT newPrice numeric(10,2)) " +
        "begin " +
        "main: BEGIN " +
            "declare maximumNewPrice " +
                "numeric(10,2); " +
            "declare oldPrice numeric(10,2); " +
            "select COFFEES.PRICE into oldPrice " +
                "from COFFEES " +
                "where COFFEES.COF_NAME " +
                "= coffeeName; " +
            "set maximumNewPrice = " +
                "oldPrice * (1 + " +
                "maximumPercentage); " +
            "if (newPrice > maximumNewPrice) " +
                "then set newPrice = " +
                "maximumNewPrice; " +
            "end if; " +
            "if (newPrice <= oldPrice) " +
                "then set newPrice = oldPrice; " +
                "leave main; " +
            "end if; " +
            "update COFFEES " +
                "set COFFEES.PRICE = newPrice " +
                "where COFFEES.COF_NAME " +
                "= coffeeName; " +
            "select newPrice; " +
        "END main; " +
        "end";

    // ...
}

Хранимая процедура присваивает значение INOUT параметр newPrice с SET и SELECT операторы. Чтобы выйти из хранимой процедуры, хранимая процедура сначала включает операторы в a BEGIN ... END блок маркируется main. Чтобы выйти из процедуры, метод использует оператор leave main.

Вызов Хранимых процедур в DB Java и MySQL

Следующая выборка от метода runStoredProcedures, вызывает хранимую процедуру SHOW_SUPPLIERS и печатает сгенерированный набор результатов:

cs = this.con.prepareCall("{call SHOW_SUPPLIERS()}");
ResultSet rs = cs.executeQuery();

while (rs.next()) {
    String supplier = rs.getString("SUP_NAME");
    String coffee = rs.getString("COF_NAME");
    System.out.println(supplier + ": " + coffee);
}

Отметьте: Как с Statement объекты, чтобы вызвать хранимую процедуру, можно вызвать execute, executeQuery, или executeUpdate в зависимости от сколько ResultSet возражает возвратам процедуры. Однако, если Вы не уверены сколько ResultSet возражает возвратам процедуры, вызвать execute.

Вызов хранимой процедуры SHOW_SUPPLIERS демонстрируется в разделе, Создающем Хранимую процедуру с API JDBC в MySQL.

Следующая выборка от метода runStoredProcedures, вызывает хранимую процедуру GET_SUPPLIER_OF_COFFEE:

cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
cs.setString(1, coffeeNameArg);
cs.registerOutParameter(2, Types.VARCHAR);
cs.executeQuery();

String supplierName = cs.getString(2);

Интерфейс CallableStatement расширяется PreparedStatement. Это используется, чтобы вызвать хранимые процедуры. Определите значения для IN параметры (такой как coffeeName в этом примере) точно так же, как Вы были бы с a PreparedStatement объект, вызывая соответствующий метод метода set. Однако, если хранимая процедура содержит OUT параметр, следует зарегистрировать это в registerOutParameter метод.

Следующая выборка от метода runStoredProcedures, вызывает хранимую процедуру RAISE_PRICE:

cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
cs.setString(1, coffeeNameArg);
cs.setFloat(2, maximumPercentageArg);
cs.registerOutParameter(3, Types.NUMERIC);
cs.setFloat(3, newPriceArg);

cs.execute();

Поскольку параметр newPrice (третий параметр в процедуре RAISE_PRICE) имеет режим параметра INOUT, следует и определить его значение, вызывая соответствующий метод метода set и зарегистрировать его в registerOutParameter метод.


Проблемы с примерами? Попытайтесь Компилировать и Выполнить Примеры: FAQ.
Жалобы? Поздравление? Предложения? Дайте нам свою обратную связь.

Предыдущая страница: Используя Объекты RowId
Следующая страница: Используя JDBC с API GUI



Spec-Zone.ru - all specs in one place