Java中调用sqlServer的存储过程（CallableStatement）的几种简单情况

一、调用不带参数的存储过程

--创建存储过程
create procedure testselect as
begin
	select bno from book;
end

package com.nc.dao;

import java.sql.*;
public class testCall {
    public static  void main(String[] args){

        Connection connection = JDBCUtil.getConn();
        String sql = "{call testselect}";
        try {
            // 建立数据库的连接
            CallableStatement cstmt = connection.prepareCall(sql);
            // 查询
            ResultSet rs = cstmt.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

对比数据库查询结果

二、调用带参数的存储过程

// 带参数
create procedure testselect @bno char(12)
as
begin
	select bno from book where bno = @bno
end

package com.nc.dao;

import java.sql.*;
public class testCall {
    public static  void main(String[] args){

        Connection connection = JDBCUtil.getConn();
        String sql = "{call testselect(?)}";
        try {
            // 建立数据库的连接
            CallableStatement cstmt = connection.prepareCall(sql);
            cstmt.setString(1, "0000001");
            // 查询
            ResultSet rs = cstmt.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

对比数据库查询结果

三、带输出参数的存储过程

go
create procedure testselect @bno char(12), @result char(12) output
as
begin
	select @result = bno from book where bno = @bno
end
go

package com.nc.dao;

import java.sql.*;
public class testCall {
    public static  void main(String[] args){

        Connection connection = JDBCUtil.getConn();
        String sql = "{call testselect(?, ?)}";
        try {
            // 建立数据库的连接
            CallableStatement cstmt = connection.prepareCall(sql);
            // 按照索引设置参数
//            cstmt.setString(1, "0000001");
            // 将名为 parameterName 的参数注册为 JDBC 类型 sqlType。
//            cstmt.registerOutParameter(2, Types.CHAR);
            // 按参数名折设置参数
           cstmt.setString("bno", "0000001");
           // 将名为 parameterName 的参数注册为 JDBC 类型 sqlType。
           cstmt.registerOutParameter("result", Types.CHAR);
            cstmt.execute();
            // 参数索引必须与call的参数位置一致
            System.out.println(cstmt.getString(2));
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

对比数据库查询结果