java编写存储过程
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
Java编写存储过程
package test;
import java.sql.*;
public class Jdbc_create
{
Statement stm;
ResultSet rs;
Connection con;
CallableStatement cs;
String user = "sa";
String pwd= "123";
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=example";
String Dbdriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
public Connection connectionsqlserver()
{
try
{
Class.forName(Dbdriver);
con=DriverManager.getConnection(url,user,pwd);
}
catch(Exception e)
{
e.printStackTrace();
}
return con;
}
public void CreateProcedure()
{
try
{
stm = con.createStatement();
String createProcedure ="create procedure snoquery "+
"@xuehao char(10) "+
"as "+
"select sno 学号,sname 学生姓名,sage 年龄,ssex 性别,sdept 所属院系"+ "from student "+
"where sno=@xuehao";
stm.executeUpdate("USE example");
stm.executeUpdate(createProcedure);
}
catch(Exception e)
{
e.printStackTrace();
}
public void UseProcedure(String st)
{
try
{
cs = con.prepareCall("execute snoquery '"+st+"'");
rs = cs.executeQuery();
while(rs.next())
{
String sno = rs.getString(1);
String sname = rs.getString(2);
String sage=rs.getString(3);
String ssex=rs.getString(4);
String sdept=rs.getString(5);
System.out.println("学号:"+sno+" "+"姓名:"+sname+" "+"年龄:" +sage+" "+"性别:"+ssex+" "+"所属院系:"+sdept);
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
public void AlterProcedure()
{
try
{
stm = con.createStatement();
String AlterProcedure="alter procedure snoquery "+
"@name nchar(10) "+
"as "+
"select sno 学号,sname 学生姓名,sage 年龄,ssex 性别,sdept 所属院系"+
"from student "+
"where sname=@name";
stm.executeUpdate("USE example");
stm.executeUpdate(AlterProcedure);
}
catch(Exception e)
{
e.printStackTrace();
}
}
public void DropProcedure()
try
{
stm = con.createStatement();
String DropProcedure="drop procedure snoquery";
stm.executeUpdate("USE example");
stm.executeUpdate(DropProcedure);
}
catch(Exception e)
{
e.printStackTrace();
}
}
public static void main(String[] args)
{
Jdbc_create J=new Jdbc_create();
J.connectionsqlserver(); / /获得sql2005的连接
J.CreateProcedure(); //创建数据库存储过程
eProcedure("20070102"); //调用存储过程查找sno为20070102的学生信息 J.AlterProcedure(); //修改存储过程
eProcedure("王小华");//调用存储过程查找sname为王小华的学生信息
J.DropProcedure(); //删除存储过程
}
}