jdbc中使用简单存储过程

今天说一说用在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调用存储过程

1. 无参调用
2. 带参数调用（包括输出参数和输入参数）
3. 方法调用

     以带参数的方法调用为例：

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

 后续章节待续。