java实现存储过程

package com.hh.jdbc.procedure;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
* jdbc操作SQLServer的存储过程
* 创建存储过程
* @author hy
*
*/
public class CreateStoredProceduresOfSQLServer {
private static Connection conn = null;
private static Statement st = null;
public static ResultSet rs = null;

public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=students","sa","");
st = conn.createStatement();

//创建存储过程show_students
String createProcedure1 = "create procedure show_students "+"as "
+"select id,name,age from students order by id";
st.executeUpdate(getSql("show_students"));
st.executeUpdate(createProcedure1);

//创建存储过程onestudent
String createProcedure2 = "create procedure onestudent "
+"@stu_id int null, @name varchar(20) output,"
+"@age int output as if @stu_id = null"
+"BEGIN "
+" PRINT 'ERROR: You must specify a stu_id value.'"
+" RETURN "
+"END"
+"select @name = name,@age = age from coffees where id = @stu_id RETIRN";
st.executeUpdate(getSql("onestudent"));
st.executeUpdate(createProcedure2);

//创建函数
String createProcedure3 = "create function pubuse.ageofstu "
+"(@stu_name varchar(20)) RETURN int AS BEGIN "
+" DECLARE @age int "
+" select @age = age from student where stu_name like @stu_name"
+" RETURN @age "
+" END";
st.executeUpdate("if exists(select name from sysobjects where name ='ageofstu'" +
"drop function pubuse.ageofstu");
st.executeUpdate(createProcedure3);

st.close();
conn.close();

}

public static String getSql(String procedureName){
String sql = null;
sql = "if exists(select name from sysobjects where name ='"+procedureName+"'" +
"and type='p') drop procedure "+procedureName;
return sql;
}

}

 

package com.hh.jdbc.procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
* 调用存储过程
* @author hy
*
*/
public class InvokeStoreProceduresOfSQLServer {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
String callSQL1 ="{call show_students}";
String callSQL2 = "{call onestudent(?,?,?)}";
String callSQL3 = "{? = call ageofstu(?)}";

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=students","sa","");

//调用第一个存储过程
CallableStatement cs = conn.prepareCall(callSQL1);
ResultSet rs = cs.executeQuery();
System.out.println("第一个存储过程调用结果:");
while(rs.next()){
String id = rs.getString(1);
String name  = rs.getString(2);
String age = rs.getString(3);
System.out.println(id+"  "+name+"  "+age);
}

//调用第二个存储过程
cs = conn.prepareCall(callSQL2);
cs.setString(1,"2");
cs.registerOutParameter(2, Types.CHAR);
cs.registerOutParameter(3, Types.INTEGER);
cs.execute();
String name = cs.getString(2);
int age = cs.getInt(3);
System.out.println("第二个存储过程调用结果:");
System.out.println("Student's name is "+name +"and age is "+age);

//调用函数
cs = conn.prepareCall(callSQL3);
cs.setString(2, "小罗");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
age = cs.getInt(1);
System.out.println("函数的调用结果是:");
System.out.println("This is student age is "+age+".");
cs.close();
conn.close();
}
}