Автор этой статьи рассказывает, как вызывать хранимые процедуры SQL Server через Java, и подробно объясняет пять различных типов хранения. Подробности см. ниже.
1. Используйте хранимую процедуру без параметров
При использовании драйвера JDBC для вызова хранимой процедуры без параметров необходимо использовать escape-последовательность вызова SQL. Синтаксис escape-последовательности вызова без параметров следующий:
Скопируйте код кода следующим образом:
{имя-процедуры вызова}
В качестве примера создайте следующую хранимую процедуру в образце базы данных SQL Server 2005 AdventureWorks:
Скопируйте код кода следующим образом:
СОЗДАТЬ ПРОЦЕДУРУ GetContactFormalNames
КАК
НАЧИНАТЬ
ВЫБРАТЬ TOP 10 Должность + ' ' + Имя + ' ' + Фамилия КАК Официальное имя
ОТ Персона.Контакт
КОНЕЦ
Эта хранимая процедура возвращает один набор результатов, содержащий столбец данных, состоящий из должности, имени и фамилии первых десяти контактов в таблице Person.Contact.
В следующем примере этой функции передается открытое соединение с образцом базы данных AdventureWorks, а затем с помощью метода ExecuteQuery вызывается хранимая процедура GetContactFormalNames.
Скопируйте код кода следующим образом:
public static void ExecuteSprocNoParams(Connection con) ...{
пытаться...{
Заявление stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
в то время как (rs.next()) ...{
System.out.println(rs.getString("FormalName"));
}
рс.закрыть();
стмт.закрыть();
}
улов (Исключение е) ...{
е.printStackTrace();
}
}
2. Используйте хранимую процедуру с входными параметрами.
При использовании драйвера JDBC для вызова хранимой процедуры с параметрами необходимо использовать escape-последовательность вызова SQL в сочетании с методом подготовитьCall класса SQLServerConnection. Синтаксис escape-последовательности вызова с параметром IN следующий:
Скопируйте код кода следующим образом:
{имя-процедуры вызова[([параметр][,[параметр]]....)]}
При создании escape-последовательности вызова используйте символ ? (знак вопроса), чтобы указать параметр IN. Этот символ служит заполнителем для значения параметра, передаваемого в хранимую процедуру. Вы можете использовать один из методов установки класса SQLServerPreparedStatement для указания значений параметров. Доступные методы установки определяются типом данных параметра IN.
При передаче значения методу установки необходимо указать не только фактическое значение, которое будет использоваться в параметре, но также порядковый номер параметра в хранимой процедуре. Например, если хранимая процедура содержит один параметр IN, его порядковое значение равно 1. Если хранимая процедура содержит два параметра, первое порядковое значение равно 1, а второе порядковое значение — 2.
В качестве примера вызова хранимой процедуры, содержащей параметр IN, используйте хранимую процедуру uspGetEmployeeManagers в образце базы данных SQL Server 2005 AdventureWorks. Эта хранимая процедура принимает один входной параметр с именем «EmployeeID», который является целочисленным значением, и возвращает рекурсивный список сотрудников и их менеджеров на основе указанного «EmployeeID». Вот код Java, который вызывает эту хранимую процедуру:
Скопируйте код кода следующим образом:
public static void ExecuteSprocInParams(Connection con) ...{
пытаться...{
ReadedStatement pstmt = con.prepareStatement("{call dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet rs = pstmt.executeQuery();
в то время как (rs.next()) ...{
System.out.println("СОТРУДНИК:");
System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
System.out.println("МЕНЕДЖЕР:");
System.out.println(rs.getString("ManagerLastName") + ", " + rs.getString("ManagerFirstName"));
Система.out.println();
}
рс.закрыть();
пстмт.закрыть();
}
улов (Исключение е) ...{
е.printStackTrace();
}
}
3. Используйте хранимую процедуру с выходными параметрами.
При вызове таких хранимых процедур с помощью драйвера JDBC необходимо использовать escape-последовательность вызова SQL в сочетании с методом готовых вызовов класса SQLServerConnection. Синтаксис escape-последовательности вызова с параметром OUT следующий:
Скопируйте код кода следующим образом:
{имя-процедуры вызова[([параметр][,[параметр]]....)]}
При создании escape-последовательности вызова используйте символ ? (знак вопроса), чтобы указать параметр OUT. Этот символ служит заполнителем для значения параметра, возвращаемого этой хранимой процедурой. Чтобы указать значения для параметров OUT, необходимо использовать метод RegisterOutParameter класса SQLServerCallableStatement, чтобы указать тип данных каждого параметра перед запуском хранимой процедуры.
Значение, указанное для параметра OUT с помощью метода RegisterOutParameter, должно быть одним из типов данных JDBC, содержащихся в java.sql.Types, который, в свою очередь, сопоставляется с одним из собственных типов данных SQL Server. Дополнительные сведения о типах данных JDBC и SQL Server см. в разделе Общие сведения о типах данных драйвера JDBC.
Когда вы передаете значение методу RegisterOutParameter для параметра OUT, вы не только должны указать тип данных, который будет использоваться для этого параметра, но также необходимо указать порядковый номер параметра или имя параметра в хранимой процедуре. Например, если хранимая процедура содержит один параметр OUT, ее порядковый номер равен 1; если хранимая процедура содержит два параметра, первое порядковое значение равно 1, а второе порядковое значение — 2;
В качестве примера создайте следующую хранимую процедуру в образце базы данных SQL Server 2005 AdventureWorks: На основе указанного целочисленного параметра IN (employeeID) эта хранимая процедура также возвращает один целочисленный параметр OUT (managerID). На основе идентификатора сотрудника, содержащегося в таблице HumanResources.Employee, значением, возвращаемым в параметре OUT, является ManagerID.
В следующем примере этой функции передается открытое соединение с образцом базы данных AdventureWorks, а затем с помощью метода выполнения вызывается хранимая процедура GetImmediateManager:
Скопируйте код кода следующим образом:
public static void ExecuteStoredProcedure(Connection con) ...{
пытаться...{
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("ИД МЕНЕДЖЕРА: " + cstmt.getInt(2));
}
улов (Исключение е) ...{
е.printStackTrace();
}
}
В этом примере для идентификации параметров используется порядковый номер. Альтернативно, параметр можно идентифицировать по его имени, а не по его порядковому положению. Следующий пример кода изменяет предыдущий пример, чтобы проиллюстрировать, как использовать именованные параметры в приложении Java. Обратите внимание, что имена этих параметров соответствуют именам параметров в определении хранимой процедуры: 11x16CREATE PROCEDURE GetImmediateManager.
Скопируйте код кода следующим образом:
@employeeID INT,
@managerID INT ВЫВОД
КАК
НАЧИНАТЬ
ВЫБЕРИТЕ @managerID = ManagerID
ИЗ HumanResources.Сотрудник
ГДЕ идентификатор сотрудника = @employeeID
КОНЕЦ
Хранимые процедуры могут возвращать количество обновлений и несколько наборов результатов. Драйвер JDBC Microsoft SQL Server 2005 соответствует спецификации JDBC 3.0, которая гласит, что перед получением параметров OUT необходимо получить несколько наборов результатов и количество обновлений. То есть приложение должно сначала получить все объекты ResultSet и обновить счетчик, а затем использовать метод CallableStatement.getter для получения параметров OUT. В противном случае при получении параметра OUT объект ResultSet и счетчик обновлений, которые еще не были получены, будут потеряны.
4. Используйте хранимые процедуры с возвращаемым статусом
При вызове такой хранимой процедуры с помощью драйвера JDBC необходимо использовать escape-последовательность вызова SQL в сочетании с методом подготовитьCall класса SQLServerConnection. Синтаксис escape-последовательности вызова, возвращающей параметр состояния, следующий:
Скопируйте код кода следующим образом:
{[?=]вызов имени-процедуры[([параметр][,[параметр]]....)]}
При создании escape-последовательности вызова используйте символ ? (знак вопроса), чтобы указать возвращаемый параметр состояния. Этот символ служит заполнителем для значения параметра, возвращаемого этой хранимой процедурой. Чтобы указать значение для параметра состояния возврата, необходимо указать тип данных параметра с помощью метода RegisterOutParameter класса SQLServerCallableStatement перед выполнением хранимой процедуры.
Кроме того, при передаче значения параметра состояния возврата в метод RegisterOutParameter необходимо указать не только тип данных используемого параметра, но и порядковый номер параметра в хранимой процедуре. Порядковый номер возвращаемого параметра состояния всегда равен 1, поскольку он всегда является первым параметром при вызове хранимой процедуры. Хотя класс SQLServerCallableStatement поддерживает использование имени параметра для указания конкретного параметра, для возвращаемых параметров состояния можно использовать только порядковый номер позиции параметра.
В качестве примера создайте следующую хранимую процедуру в образце базы данных SQL Server 2005 AdventureWorks:
Скопируйте код кода следующим образом:
СОЗДАТЬ ПРОЦЕДУРУ CheckContactCity
(@cityName CHAR(50))
КАК
НАЧИНАТЬ
ЕСЛИ ((ВЫБРАТЬ СЧЕТ(*)
ОТ Персона.Адрес
ГДЕ Город = @cityName) > 1)
ВОЗВРАТ 1
ЕЩЕ
ВОЗВРАТ 0
КОНЕЦ
Хранимая процедура возвращает значение состояния 1 или 0, в зависимости от того, можно ли найти город, указанный параметром cityName, в таблице Person.Address.
В следующем примере этой функции передается открытое соединение с образцом базы данных AdventureWorks, а затем с помощью метода выполнения вызывается хранимая процедура CheckContactCity:
Скопируйте код кода следующим образом:
public static void ExecuteStoredProcedure(Connection con) ...{
пытаться...{
CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "Атланта");
cstmt.execute();
System.out.println("СТАТУС ВОЗВРАТА: " + cstmt.getInt(1));
}
cstmt.закрыть();
улов (Исключение е) ...{
е.printStackTrace();
}
}
5. Используйте хранимую процедуру со счетчиком обновлений.
После использования класса SQLServerCallableStatement для создания вызова хранимой процедуры вы можете использовать методы Execute или ExecuteUpdate для вызова хранимой процедуры. Метод выполненияUpdate возвращает значение int, содержащее количество строк, на которые влияет эта хранимая процедура, но метод выполнения не возвращает это значение. Если вы используете метод выполнения и хотите подсчитать количество затронутых строк, вы можете вызвать метод getUpdateCount после запуска хранимой процедуры.
В качестве примера создайте следующие таблицы и хранимые процедуры в образце базы данных SQL Server 2005 AdventureWorks:
Скопируйте код кода следующим образом:
СОЗДАТЬ ТАБЛИЦУ
(Col1 int ИДЕНТИЧНОСТЬ,
Col2 варчар(50),
Col3 int);
СОЗДАТЬ ПРОЦЕДУРУ UpdateTestTable
@Col2 варчар(50),
@Col3 целое
КАК
НАЧИНАТЬ
ОБНОВИТЬ тестовую таблицу
SET Col2 = @Col2, Col3 = @Col3
КОНЕЦ;
В следующем примере этой функции передается открытое соединение с образцом базы данных AdventureWorks, она использует метод выполнения для вызова хранимой процедуры UpdateTestTable, а затем использует метод getUpdateCount для возврата количества строк, на которые влияет хранимая процедура.
Скопируйте код кода следующим образом:
public static void ExecuteUpdateStoredProcedure(Connection con) ...{
пытаться...{
CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
cstmt.setString(1, "А");
cstmt.setInt(2, 100);
cstmt.execute();
int count = cstmt.getUpdateCount();
cstmt.закрыть();
System.out.println("ЗАТРУДНЕННЫЕ СТРОКИ: " + count);
}
улов (Исключение е) ...{
е.printStackTrace();