Jdbc调用存储过程

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);