java调用存储过程

创建数据库

create database pubs
go
use pubs
go
CREATE TABLE [dbo].[authors] (
	[au_id] [varchar](20) NOT NULL ,
	[au_lname] [varchar] (40)  NOT NULL ,
	[au_fname] [varchar] (20)  NOT NULL ,
	[phone] [char] (12)  NOT NULL ,
	[address] [varchar] (40)  NULL ,
	[city] [varchar] (20)  NULL ,
	[state] [char] (2)  NULL ,
	[zip] [char] (5)  NULL ,
	[contract] [bit] NOT NULL 
) ON [PRIMARY]
GO

insert into authors values('172-32-1176','White','Johnson','408 496-7223','10932 Bigge Rd.','Menlo Park','CA','94025',1)
insert into authors values('213-46-8915','Green','Marjorie','415 986-7020','309 63rd St. #411','Oakland','CA','94618',1)
insert into authors values('238-95-7766','Carson','Cheryl','415 548-7723','589 Darwin Ln.','Berkeley','CA','94705',1)
insert into authors values('267-41-2394','OLeary','Michael','408 286-2428','22 Cleveland Av. #14','San Jose','CA','95128',1)
insert into authors values('274-80-9391','Straight','Dean','415 834-2919','5420 College Av.','Oakland','CA','94609',1)
insert into authors values('341-22-1782','Smith','Meander','913 843-0462','10 Mississippi Dr.','Lawrence','KS','66044',0)
insert into authors values('409-56-7008','Bennet','Abraham','415 658-9932','6223 Bateman St.','Berkeley','CA','94705',1)
insert into authors values('427-17-2319','Dull','Ann','415 836-7128','3410 Blonde St.','Palo Alto','CA','94301',1)
insert into authors values('472-27-2349','Gringlesby','Burt','707 938-6445','PO Box 792','Covelo','CA','95428',1)
insert into authors values('486-29-1786','Locksley','Charlene','415 585-4620','18 Broadway Av.','San Francisco','CA','94130',1)
insert into authors values('527-72-3246','Greene','Morningstar','615 297-2723','22 Graybar House Rd.','Nashville','TN','37215',0)
insert into authors values('648-92-1872','Blotchet-Halls','Reginald','503 745-6402','55 Hillsdale Bl.','Corvallis','OR','97330',1)


go
CREATE PROCEDURE FINDALL  AS
SELECT * FROM  authors
GO
CREATE PROCEDURE QUERRYNAME 
@LNAME  varchar(50) OUTPUT,
@FNAME  varchar(50) OUTPUT,
@AUID  varchar(20)
AS
SELECT @LNAME=au_lname,@FNAME=au_fname FROM authors WHERE au_id=@AUID
GO



 

调用：

数据库连接类：

package com.ruanyuan;

import java.sql.*;

public class BaseDAO {
	private static final String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	//数据库的URL
	private static final String URL="jdbc:sqlserver://localhost:1433;DatabaseName=pubs";
	//连接数据库的用户名
	private static final String USERNAME="sa";
	//连接数据库的密码
	private static final String PASSWORD="sa";
	//连接对象
	protected Connection conn;
	// 预编译的PreparedStatement对象
	protected PreparedStatement pstmt;
	// Statement对象
	//protected Statement stmt;
	//结果集ResultSet对象
	protected ResultSet rs;
	
	/*
	 * 获取数据库的连接
	 */
	protected void getConnection(){
		try {
			//注册驱动
			Class.forName(DRIVER);
			//获取连接
			conn= DriverManager.getConnection(URL,USERNAME,PASSWORD);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	//释放资源
	protected void closeResource(){
		
		if(rs!=null)
			try {
				//释放结果集
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if(pstmt!=null){
			try {
				//释放预编译的命令对象
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}		
		if(conn!=null){
			try {
				//释放连接对象
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	
	}
	
	/**
	 * 执行SQL语句,可以执行增、删、改的操作，不能执行查询
	 * @param sql    预编译的sql语句
	 * @param param  sql语句参数数组
	 * @return rows  返回受影响的行数
	 */
	protected int execUpdate(String sql, String[] paras) throws Exception {
		int rows = 0; // 受影响的行数

		try {
		         //连接数据库
			     this.getConnection();
		       //基于SQL语句得到PreparedStatement对象
				pstmt = conn.prepareStatement(sql);
				//创建并添加相应的参数
				if (paras != null && paras.length > 0) {							
				// 将SQL语句参数数组中的值依次赋给预执行语句
				for (int i = 0; i < paras.length; i++) {
					// 注意，setXXX()方法的索引值从1开始，所以有i+1
					pstmt.setString(i + 1, paras[i]);
				}									
			} 
		    //执行SQL语句
			rows = pstmt.executeUpdate();
		} finally {
			this.closeResource();// 释放资源
		}
		System.out.println("调用通用的增、删、改方法成功！");
		// 返回受影响的行数
		return rows;
	}
	/**
	 * 执行查询SQL的方法
	 * @param 查询SQL语句
	 * @param 查询参数
	 * @return 返回查询结果集
	 */
	protected ResultSet execQuery(String sql,String[]paras){				
	
		try {
			//连接数据库
			this.getConnection();
			//基于SQL语句得到PreparedStatement对象
			pstmt=conn.prepareStatement(sql);
			//将SQL语句参数数组中的值依次赋给预执行语句
			if(paras!=null&¶s.length>0){
				int index=1;
				for(String para:paras){
					pstmt.setString(index, para);
					index++;
				}			
			}
			//执行SQL语句
			rs=pstmt.executeQuery();					
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//返回查询结果集
	    return rs;
	}
}

无参调用：

 public Vector<Author> findAll(){
	   Vector<Author> vector=new Vector<Author>();
	   try {
		  
		   this.getConnection();
		   CallableStatement cstmt=conn.prepareCall("{call FINDALL}");
		   rs=cstmt.executeQuery();
		   while(rs.next()){
			   Author author=new Author();
			   author.setAu_id(rs.getString("au_id"));
			   author.setAu_fname(rs.getString("au_fname"));
			   author.setAu_lname(rs.getString("au_lname"));
			   author.setAddress(rs.getString("address"));
			   author.setCity(rs.getString("city"));
			   author.setContract(rs.getBoolean("contract"));
			   author.setPhone(rs.getString("phone"));
			   author.setState(rs.getString("state"));
			   author.setZip(rs.getString("zip"));
			   vector.add(author);
			   
		   }
		   cstmt.close();
		   
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		super.closeResource();
	}
	return vector;		   
	   
   }

带输入参数：

public Author QueryName(String au_id){
	  
	   Author author=new Author();
	  try {
		  this.getConnection();
		CallableStatement cstmt= conn.prepareCall("{call QUERRYNAME(?,?,?)}");
		cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
		cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
		cstmt.setString(3, au_id);
		cstmt.execute();
		author.setAu_lname(cstmt.getString(1));
		author.setAu_fname(cstmt.getString(2));
		author.setAu_id(au_id);
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		super.closeResource();
	}
	  
	return author;
   }