今天说一说用在JDBC中调用存储过程，本次讲解为初级篇采用的数据库为SQLSERVER，根据需要还会发布相应的高级篇。
一、建立实例数据库
描述：创建两张表，一个学生信息表（stuinfo）,另一个是学生成绩表（stuMarks）,在这两张表中分别插入学生信息
并设置stuinfo为主键表,stuMarks为外键表。创建一个存储过程查询本班没有通过考试的学员人数（可适当将未及格学员的信息打印出来）。
建表：
use stuDB go if exists(select * from sysobjects where name='stuinfo') truncate table stuinfo create table stuinfo( stuName varchar(20) not null, stuNo char(6) not null, stuAge int not null, stuID numeric(18, 0), stuSeat smallint identity(1, 1), stuAddress text ) go if exists(select * from sysobjects where name='stuMarks') truncate table stuMarks create table stuMarks( ExamNo char(7) not null, stuNo char(6) not null, writtenExam int not null, LabExam int not null )
插入数据：
alter table stuMarks add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo) on delete cascade insert into stuinfo values('张秋丽', 's25301', 18,123876493217894538, '北京海淀区'); insert into stuinfo values('李文才', 's25302', 28,348907621874635832, '地址不详'); insert into stuinfo values('李斯文', 's25303', 22,371409823723841987, '河南洛阳'); insert into stuinfo values('欧阳峻峰', 's25304',34, 238651097452691873, '地址不详'); insert into stuinfo values('梅超风', 's25318', 23,371893123876453221, '地址不详'); insert into stuMarks values('s271811', 's25303', 80, 58) insert into stuMarks values('s271813', 's25302', 50, 90) insert into stuMarks values('s271816', 's25301', 77, 82) insert into stuMarks values('s271818', 's25318', 45, 65) insert into stuMarks values('s271819', 's25304', 60, 60)
创建存储过程：
if exists(select * from sysobjects where name='pro_stufout') drop procedure pro_stufout go create procedure pro_stufout @notpassSum int output, @writtenPass int=60, @labPass int=60 as print '------------------------------------------------------' print ' 参加本次考试没有通过的学员是:' select stuName, stuinfo.stuNo, writtenExam, labExam from stuinfo inner join stuMarks on stuinfo.stuNo = stuMarks.stuNo where writtenExam < @writtenPass or labExam < @labPass return select @notpassSum=count(stuNo) from stuMarks where writtenExam < @writtenPass or labExam < @labPass go二、JDBC调用存储过程
无参调用带参数调用（包括输出参数和输入参数）方法调用以带参数的方法调用为例：
public int executeProcedure(){ int returnValue = 0; CallableStatement call = null; Connection conn = SQLConnection.getSQLConnection("qdaoStuDB"); try { call = conn.prepareCall("{?=call pro_stufout(?, ?)}"); call.setInt(3, 60); call.registerOutParameter(1, java.sql.Types.INTEGER); call.registerOutParameter(2, java.sql.Types.INTEGER); ResultSet rs = call.executeQuery(); while(rs.next()){ System.out.println(rs.getString(1)); } // call.execute(); // System.out.println(b); returnValue = call.getInt(1); System.out.println("----" + returnValue); System.out.println("@@@@@" + call.getInt(2)); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return returnValue; }输出结果：
张秋丽 李文才 欧阳俊雄 ----3 @@@@@3
后续章节待续。
相关资源：jdbc调用存储过程实现增删改查