jdbc调用存储过程:
经常使用的4种:
1. 返回结果集的proc
2. 输出参数
3.使用带有返回状态的存储过程
4.受影响行数
以下为mysql的存储过程 sqlServer同理
案例1: 返回结果集的proc
存储过程:
drop procedure if exists proc_selectEmployee;
create procedure proc_selectEmployee(in carid varchar(20))
begin
select * from employee where cardID = carid;
end
call proc_selectEmployee('SZ65380');
- import java.sql.*;
- import java.sql.CallableStatement;
- /**
- *
- * 简单的jdbc调用存储过程 只有输入参数 返回单个结果集
- *
- */
- public class GeTest1 {
- public static void main(String[] args) {
- Connection connection = null;
- //用于执行 SQL 存储过程的接口
- CallableStatement statement = null;
- ResultSet resultSet = null;
- try {
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/test";
- String user = "root";
- String password = "123";
- connection = DriverManager.getConnection(url, user, password);
- String sql = "call proc_selectEmployee(?)";
- //调用存储过程
- statement = connection.prepareCall(sql);
- statement.setString(1, "SZ65380");
- resultSet = statement.executeQuery();
- if (resultSet.next()) {
- System.out.println(resultSet.getString("address"));
- }
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- try {
- if (resultSet != null) {
- resultSet.close();
- }
- if (statement != null) {
- statement.close();
- }
- if (connection != null) {
- connection.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
案例2: 输出参数
存储过程:
drop procedure if exists proc_outtwo;
create procedure proc_outtwo(in idint int,out cardIdstring varchar(44),out addressstringvarchar(88))
begin
select cardID,address into cardIdstring,addressstring from employee where id =idint;
end
call proc_outtwo(1,@one,@two);
select @one;
select @two;
- import java.sql.*;
- import java.sql.CallableStatement;
- /**
- *
- * 执行存储过程 得到输出参数
- *
- */
- public class GeTest2 {
- /**
- * @param args
- */
- public static void main(String[] args) {
- Connection connection = null;
- //用于执行 SQL 存储过程的接口
- CallableStatement statement = null;
- ResultSet resultSet = null;
- try {
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/test";
- String user = "root";
- String password = "123";
- connection = DriverManager.getConnection(url, user, password);
- //第一个为输入参数后面2个为输出参数
- String sql = "call proc_outtwo(?,?,?);";
- //调用存储过程
- statement = connection.prepareCall(sql);
- //设置输入参数
- statement.setInt(1, 1);
- //设置输出参数 以及类型
- statement.registerOutParameter(2, Types.VARCHAR);
- statement.registerOutParameter(3, Types.VARCHAR);
- statement.execute();
- //得到输出参数
- System.out.println(statement.getString(2));
- System.out.println(statement.getString(3));
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- try {
- if (resultSet != null) {
- resultSet.close();
- }
- if (statement != null) {
- statement.close();
- }
- if (connection != null) {
- connection.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
案例3:
使用带有返回状态的存储过程 return 1; mysql的proc不支持 返回值 sqlserver支持
如果要获得返回值的话为:
存储过程:
create proc checkit
(@addressString varchar(50))
as
begin
if ((select count(*) from employee where address =@addressString))
return 1
else
return 0
go
- CallableStatement cstmt = con.prepareCall("{? = call checkit(?)}");
- cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
- cstmt.setString(2, "深圳");
- cstmt.execute();
- System.out.println("return的值" + cstmt.getInt(1));
案例4:
获得更新行数:
drop procedure if exists proc_updateEmployee;
create procedure proc_updateEmployee()
begin
update Employee set job=1;
end
call proc_selectEmployee();
- CallableStatement cstmt = con.prepareCall("{call proc_updateEmployee()}");
- cstmt.execute();
- int count = cstmt.getUpdateCount();
- cstmt.close();
- System.out.println("受影响行数:" + count);