Обработка объектов CallableStatement с использованием именованных параметров

Новая функциональная возможность Informix Dynamic Server

Из этой статьи вы узнаете об именованных параметрах в объектах CallableStatement – новой функциональной возможности сервера IBM® Informix® Dynamic Server (IDS), позволяющей вам обращаться к параметру по его имени, а не по его порядковому местоположению. Таким образом, эта функция расширяет возможности использования объектов класса CallableStatements (вызываемые операторы), позволяя обращаться к параметрам как по их порядковому местоположению, так и по имени. В статье рассказывается о различиях между этими двумя методами, а также о преимуществах использования именованных параметров в сравнении с использованием порядкового местоположения.

К. Ахилеш Тивари, разработчик программного обеспечения, руководитель группы Informix CSDK, IBM

Akhilesh Tiwary photoАхилеш Кумар Тивари (Akhilesh Kumar Tiwary) руководит командой CSDK и работает над JDBC в лаборатории IBM Software Labs, Индия.



Тхамишчелван A. Анбалаган, инженер, IBM  

Thamizhchelvan Anbalagan, инженер в команде разработки приложений JDBC в IBM Software Labs, Индия. Последние 3 года он работал над различными Informix-CSDK для IDS.



26.03.2010

Введение. Обработка операторов в JDBC

В JDBC-приложении объект JDBC Statement (объект для оператора JDBC) используется для отправки SQL-оператора на сервер баз данных. Объект для оператора связан с объектом Connection и является объектом, обрабатывающим взаимодействие между приложением и сервером баз данных.

В JDBC существуют три типа объектов для операторов:

  1. General statement
  2. Prepared statement
  3. Callable statement

Поскольку объект для операторов связан с объектом Connection, для его создания необходимо установить подключение к базе данных.

Создание подключения

Пример кода для создания подключения приведен в листинге 1.

Листинг 1. Пример кода для загрузки драйвера Informix и создания подключения
Connection con = null;
try {
Class.forName("com.informix.jdbc.IfxDriver");				
	String url = "jdbc:informix-sqli://hostname:port_number/dbname:
informixserver=servername; userid=userid;password=pwd;";
con = DriverManager.getConnection(url);		
}

Давайте теперь попробуем по очереди поработать с каждым из типов объектов для операторов.


Объекты типа General statement

Для создания оператора этого типа применяется метод createStatement объекта Connection (листинг 2). Этот метод часто используется для SQL-операторов, когда нет необходимости передавать какое-либо значение в качестве параметра.

Листинг 2. Пример кода для создания оператора create
Statement stmt = con.createStatement();
      cmd = "create database testDB;";
      rc = stmt.executeUpdate(cmd);
stmt.close();

Объекты типа Prepared statement

Объекты типа Prepared statement (подготовленные операторы) являются подклассом класса Statement (листинг 3). Основное различие заключается в том, что в отличие от объекта класса Statement объект класса Prepared statement компилируется и оптимизируется один раз, после чего его можно использовать многократно, задавая различные значения параметров. Поэтому если вы хотите выполнять оператор несколько раз, объект типа Prepared statement является наилучшим решением. Поскольку объект находится в скомпилированном виде, время, затрачиваемое на его выполнение, сокращается. Таким образом, преимущество заключается не только в том, что объект типа Prepared statement содержит в себе SQL-оператор, но и в том, что этот SQL-оператор предварительно скомпилирован. Другим отличием является то, что SQL-оператор передается в объект Prepared statement сразу же после его создания.

Листинг 3. Пример объекта Prepared statement
PreparedStatement pstmt = con.prepareStatement("UPDATE tab1 "+
                    "set col1 = ? where key = 1");
pstmt.setShort(1, (short)2);
int rowcount = pstmt.executeUpdate();

В данном примере этот же подготовленный оператор может использоваться с другим значением параметра col1. После того как значение параметра задано, оно не меняется до момента его сброса или вызова функции clearParameters. Эта возможность позволяет использовать подготовленные операторы в пакетных заданиях INSERT/UPDATE.

Пакетное обновление

Функция пакетного обновления (batch update) улучшает производительность отдельного оператора, выполняющегося несколько раз с различными значениями параметра. Это позволяет единожды определить источник данных для последующей обработки несколькими операциями обновления. Объекты класса Statement также могут работать с пакетным обновлением. Разница в том, что объект Statement получает для групповой обработки различные SQL-операторы, а объект Prepared statement – набор параметров.

В листинге 4 показано, как использовать групповую операцию insert в подготовленном операторе.

Листинг 4. Пример кода пакетного обновления
PreparedStatement pst = conn.prepareStatement("insert into tab1 values (?)");
for loop....
{
	pst.setInt (1, i);
	pst.addBatch();
}
pst.executeBatch();

Метод addBatch добавляет оператор в кэш и передает его в базу данных при помощи метода executeBatch(). Таким образом, сохраняются компиляция и оптимизация оператора, поскольку он компилируется только один раз (с помощью объекта Prepared statement), а также сокращается время доставки данных до сервера и обратно, поскольку групповая операция insert передается за один раз.


Объекты типа Callable statement

Использование объекта типа Callable statement (вызываемый оператор) является третьим способом вызова SQL-оператора и предоставляет возможность вызова хранимой процедуры, расположенной на сервере, из Java™-приложения. Вызываемые операторы также должны быть подготовлены заранее, после чего их параметры задаются с помощью соответствующих методов. Значения параметров можно задать одним из следующих способов:

  1. Порядковое местоположение
  2. Именованный параметр

Использование порядкового местоположения является традиционным способом задания параметра на основе его местоположения в объекте CallableStatement. Однако использование именованного параметра предоставляет определенную гибкость, заключающуюся в задании параметра на основе его имени, а не местоположения. Внутри отдельного вызова процедуры обращения к параметрам объекта CallableStatement должны выполняться либо только по их именам, либо только по порядковым номерам. Если, например, вы присваиваете имя параметру для одного аргумента, вы должны использовать имена параметров для всех аргументов.

Именованные параметры оказываются особенно полезными при вызове хранимых процедур, имеющих много аргументов, некоторые из которых имеют значения по умолчанию. Если процедура уникальна, вы можете опускать параметры, имеющие значения по умолчанию, а также указывать параметры в любом порядке. Именованные параметры делают работу приложения более надежной, поскольку вам не приходится менять код приложения в случаях, если меняется последовательность параметров в хранимой процедуре.

Драйвер JDBC содержит метод DatabaseMetaData.supportsNamedParameters() для определения того, поддерживаются ли именованные параметры объекта CallableStatement компонентами RDMS. Если именованные параметры поддерживаются, система возвращает значение true. Пример приведен в листинге 5.

Листинг 5. Использование метода supportsNamedParameters()
Connection myConn = . . .   // connection to the RDBMS for Database
      DatabaseMetaData dbmd = myConn.getMetaData();
      if (dbmd.supportsNamedParameters() == true)
      {
          System.out.println("NAMED PARAMETERS FOR CALLABLE"
                            + "STATEMENTS IS SUPPORTED");
      }

Получение имен параметров хранимых процедур

Имена параметров хранимых процедур можно получить с помощью метода getprocedureColumns объекта DatabaseMetaData, определенного в листинге 6.

Листинг 6. Использование метода getProcedureColumn()
Connection myConn = . . .   // connection to the RDBMS for Database
 . .
      DatabaseMetaData dbmd = myConn.getMetaData();
      ResultSet rs = dbmd.getProcedureColumns(
       "myDB", schemaPattern, procedureNamePattern, columnNamePattern);
      rs.next() {
          String parameterName = rs.getString(4);
 - - - or - - -
 String parameterName = rs.getString("COLUMN_NAME"); 
 - - -
          System.out.println("Column Name: " + parameterName);

В результате будут выведены имена всех столбцов, совпадающих с параметрами метода getProcedureColumns().

В листинге 7 приведен пример использования именованного параметра объекта CallableStatement.

Листинг 7. Использование вызываемого параметра OUT
create procedure createProductDef(productname   varchar(64),
                 productdesc  varchar(64),
                 listprice    float,
                 minprice     float,
             out prod_id      float);
. . .
  let prod_id="value for prod_id";
end procedure;

Создание хранимой процедуры

Код на языке Java, приведенный в листинге 8, создает объект CallableStatement с пятью параметрами, соответствующими параметрам хранимой процедуры. Символы вопроса (?) внутри JDBC-вызова указывают на параметры (задание или регистрация всех параметров). Имена параметров задаются в формате cstmt.setString("arg", имя);, где arg – имя аргумента в соответствующей хранимой процедуре. Не обязательно называть параметры порядке расположения аргументов в хранимой процедуре.

Листинг 8. Использование вызываемого именованного параметра
String sqlCall = "{call CreateProductDef(?,?,?,?,?)}";
      CallableStatement cstmt = conn.prepareCall(sqlCall);

      cstmt.setString("productname", name);     // Set Product Name.
      cstmt.setString("productdesc", desc);     // Set Product Description.
      cstmt.setFloat("listprice", listprice);   // Set Product ListPrice.
      cstmt.setFloat("minprice", minprice);     // Set Product MinPrice.

      // Register out parameter which should return the product is created.

      cstmt.registerOutParameter("prod_id", Types.FLOAT);

      // Execute the call.
      cstmt.execute();

      // Get the value of the id from the OUT parameter: prod_id
      float id = cstmt.getFloat("prod_id");

Если количество параметров в объекте CallableStatement меньше, чем число аргументов в хранимой процедуре, оставшиеся аргументы должны иметь значения по умолчанию. Нет необходимости задавать значения для аргументов, имеющих значения по умолчанию, поскольку они будут использованы сервером автоматически. Например, если хранимая процедура содержит десять аргументов, четыре из которых не имеют значений по умолчанию, а шесть – имеют, объект CallableStatement должен содержать, по меньшей мере, четыре знака вопроса. Также вы можете использовать большее количество знаков вопроса – вплоть до десяти. В следующей хранимой процедуре аргументы listprice и minprice имеют значения по умолчанию (листинг 9).

Листинг 9. Создание процедуры с аргументами, имеющими значения по умолчанию
create procedure createProductDef(productname   varchar(64),
                 productdesc  varchar(64),
                 listprice    float default 100.00,
                 minprice     float default  90.00,
             out prod_id      float);
. . .
  let prod_id = value for prod_id;
end procedure;

Приведенный в листинге 10 Java-код вызывает хранимую процедуру с параметрами, количество которых меньше, чем количество аргументов в хранимой процедуре (четыре параметра и пять аргументов). Поскольку аргумент listprice имеет значение по умолчанию, его можно опустить в объекте CallableStatement.

Листинг 10. Использование параметров по умолчанию
String sqlCall = "{call CreateProductDef(?,?,?,?)}";
                                              // 4 params for 5 args
      CallableStatement cstmt = conn.prepareCall(sqlCall);

      cstmt.setString("productname", name);   // Set Product Name.
      cstmt.setString("productdesc", desc);   // Set Product Description.
    
      cstmt.setFloat("minprice", minprice);   // Set Product MinPrice.

      // Register out parameter which should return the product id created.

      cstmt.registerOutParameter("prod_id", Types.FLOAT);

      // Execute the call.
      cstmt.execute();

      // Get the value of the id from the OUT parameter: prod_id
      float id = cstmt.getFloat("prod_id");

Если вызываемый оператор содержит параметр типа OUT или INOUT, вам необходимо зарегистрировать эти параметры с помощью метода registerOutParameter объекта CallableStatement. В листинге 11 параметр prod_id типа out используется для создания хранимой процедуры с параметром OUT. Аналогично, параметр INOUT можно создать с помощью ключевого слова INOUT.

Листинг 11. Использование параметров INOUT и OUT
create procedure createProductDef(productname   varchar(64),
                 productdesc  varchar(64),
             inout    listprice    float default 100.00,
                 minprice     float default  90.00,
             out prod_id      float);

В листинге 12 используется метод registerOutparameter для регистрации параметра типа out объекта CallableStatement.

Листинг 12. Регистрация параметра объекта типа OUT с помощью объекта CallableStatement
cstmt.registerOutParameter("prod_id", Types.FLOAT);

В листинге 13 объединены все операторы, использующие функциональные возможности именованных параметров.

Листинг 13. Программа, демонстрирующая возможности именованных параметров
package Callable;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class out1 {
    static Connection conn;	
    public static void main(String[] args) {
        getConnect();
        System.out.println("Connection Established");
        createProc();
        runthis();
        System.out.println("\n=============Finished=============");
        System.exit(0);
    }
    private static void getConnect() {
        try
        {
            Class.forName("com.informix.jdbc.IfxDriver");
            String url = "jdbc:informix-sqli://host name or ip :porn number/database
                 name:informixserver=dbservername;";
            System.out.println("URL: "+url);
            conn = DriverManager.getConnection(url);
            }
        catch( Exception e )
        {
            e.printStackTrace();
            System.exit(1);
        }
    }
    private static void createProc() {
        String str=null;
        Statement stmt = null;
        try 
        {
            stmt = conn.createStatement();
        } 
        catch (SQLException e2) 
        {
            e2.printStackTrace();
        }
        str="drop function c_out_proc";
        try 
        {
            stmt.executeUpdate (str);
        } 
        catch (SQLException e1) 
        {	}
        str = "create function  c_out_proc ( i int, OUT d varchar(20) ) \n" +
        "returning float; \n" +
        "define f float; \n" +
        "let d= \"Hello OUT\"; \n" +
        "let f=i*2; \n" +
        "return f; \n" + 
        "end function; \n";
        try
        {
            stmt.executeUpdate (str);
            System.out.println("Function created \n");
        }
        catch (SQLException e)
        {
            System.out.println("Error on creating function: " + e.toString());
            System.exit(1);
        }		
    }
    private static void runthis() 
    {
        CallableStatement cstmt = null;
        String command = "{? = call c_out_proc(?, ?)}  ";
        try 
        {
            cstmt = conn.prepareCall (command);
            cstmt.setInt(1, 2);
            cstmt.registerOutParameter(2, Types.VARCHAR);
            ResultSet rs = cstmt.executeQuery();
            if (rs == null)
            {
                System.out.println("rs is null *** this is BAD.");
                System.exit(0);
            }
            else
            {
                rs.next();
                System.out.println(rs.getFloat(1));
                System.out.println(cstmt.getString(2));
            }
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
    }
}

Заключение

Сначала в этой статье были представлены различные типы операторов, доступных через драйвер IDS JDBC. Затем была рассмотрена функция именованных параметров и способы ее использования в объектах CallableStatement.

В завершение статьи был представлен листинг демонстрационной программы, использующей функцию именованных параметров сервера IDS версии 11. Теперь у вас есть инструменты для того чтобы самостоятельно попробовать и оценить преимущества функции именованных параметров.

Ресурсы

Научиться

Получить продукты и технологии

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management, Технология Java
ArticleID=477895
ArticleTitle=Обработка объектов CallableStatement с использованием именованных параметров
publish-date=03262010