• Java对存储过程的调用方法 --转载


    一、Java如何实现对存储过程的调用:

    A:不带输出参数的

    create procedure getsum  
    @n int =0<--此处为参数-->  
    as 
    declare @sum int<--定义变量-->  
    declare @i int 
    set @sum=0  
    set @i=0  
    while @i<=@n begin 
    set @sum=@sum+@i  
    set @i=@i+1  
    end 
    print 'the sum is '+ltrim(rtrim(str(@sum))) 

    在SQL中执行:

    exec getsum 100

    在JAVA中调用:

    JAVA可以调用 但是在JAVA程序却不能去显示该存储过程的结果 因为上面的存储过程的参数类型int 传递方式是in(按值)方式

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
     
     
    //创建存储过程的对象  
    CallableStatement c=conn.prepareCall("{call getsum(?)}");  
     
    //给存储过程的参数设置值  
    c.setInt(1,100); //将第一个参数的值设置成100  
     
    //执行存储过程  
    c.execute();  
    conn.close();  
    }  
    }  
     

    B:带输出参数的

    1:返回int

    alter procedure getsum  
    @n int =0,  
    @result int output 
    as 
    declare @sum int 
    declare @i int 
    set @sum=0  
    set @i=0  
    while @i<=@n begin 
    set @sum=@sum+@i  
    set @i=@i+1  
    end 
    set @result=@sum 

    在查询分析器中执行:

    declare @myResult int 
    exec getsum 100,@myResult output 
    print @myResult 

    在JAVA中调用:

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
     
     
    //创建存储过程的对象  
    CallableStatement c=conn.prepareCall("{call getsum(?,?)}");  
     
    //给存储过程的第一个参数设置值  
    c.setInt(1,100);  
     
    //注册存储过程的第二个参数  
    c.registerOutParameter(2,java.sql.Types.INTEGER);  
     
    //执行存储过程  
    c.execute();  
     
    //得到存储过程的输出参数值  
    System.out.println (c.getInt(2));  
    conn.close();  
    }  
    }  

    2:返回varchar

    存储过程带游标:

    在存储过程中带游标 使用游标不停的遍历orderid

    create procedure CursorIntoProcedure  
    @pname varchar(8000) output 
    as 
    --定义游标  
    declare cur cursor for select orderid from orders  
    --定义一个变量来接收游标的值  
    declare @v varchar(5)  
    --打开游标  
    open cur  
    set @pname=''--给@pname初值  
    --提取游标的值  
    fetch next from cur into @v  
    while @@fetch_status=0  
    begin 
     
     
    set @pname=@pname+';'+@v  
    fetch next from cur into @v  
    end 
    print @pname  
    --关闭游标  
    close cur  
    --销毁游标  
    deallocate cur 

    执行存储过程:

    exec CursorIntoProcedure '' 

    JAVA调用:

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
    CallableStatement c=conn.prepareCall("{call CursorIntoProcedure(?)}");  
     
     
    c.registerOutParameter(1,java.sql.Types.VARCHAR);  
     
    c.execute();  
     
    System.out.println (c.getString(1));  
    conn.close();  
    }  
    }  

    C:删除数据的存储过程

    存储过程:

    drop table 学生基本信息表  
    create table 学生基本信息表  
    (  
    StuID int primary key,  
    StuName varchar(10),  
    StuAddress varchar(20)  
    )  
    insert into 学生基本信息表 values(1,'三毛','wuhan')  
    insert into 学生基本信息表 values(2,'三毛','wuhan')  
    create table 学生成绩表  
    (  
    StuID int,  
    Chinese int,  
    PyhSics int 
    foreign key(StuID) references 学生基本信息表(StuID)  
    on delete cascade  
    on update cascade  
    )   
    insert into 学生成绩表 values(1,99,100)  
    insert into 学生成绩表 values(2,99,100) 

    创建存储过程:

    create procedure delePro  
    @StuID int 
    as 
    delete from 学生基本信息表 where StuID=@StuID  
    --创建完毕  
    exec delePro 1 --执行存储过程  
    --创建存储过程  
    create procedure selePro  
    as 
    select * from 学生基本信息表  
    --创建完毕  
    exec selePro --执行存储过程 

    在JAVA中调用:

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
     
     
    //创建存储过程的对象  
    CallableStatement c=conn.prepareCall("{call delePro(?)}");  
     
    c.setInt(1,1);  
     
    c.execute();  
     
    c=conn.prepareCall("{call selePro}");  
    ResultSet rs=c.executeQuery();  
     
    while(rs.next())  
    {  
    String Stu=rs.getString("StuID");  
    String name=rs.getString("StuName");  
    String add=rs.getString("StuAddress");  
     
    System.out.println ("学号:"+" "+"姓名:"+" "+"地址");  
    System.out.println (Stu+" "+name+" "+add);  
    }  
    c.close();  
    }  
    } 

    D:修改数据的存储过程

    创建存储过程:

    create procedure ModPro  
    @StuID int,  
    @StuName varchar(10)  
    as 
    update 学生基本信息表 set StuName=@StuName where StuID=@StuID 

    执行存储过程:

    exec ModPro 2,'四毛' 

    JAVA调用存储过程:

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
     
     
    //创建存储过程的对象  
    CallableStatement c=conn.prepareCall("{call ModPro(?,?)}");  
     
    c.setInt(1,2);  
    c.setString(2,"美女");  
     
    c.execute();  
     
    c=conn.prepareCall("{call selePro}");  
    ResultSet rs=c.executeQuery();  
     
    while(rs.next())  
    {  
    String Stu=rs.getString("StuID");  
    String name=rs.getString("StuName");  
    String add=rs.getString("StuAddress");  
     
    System.out.println ("学号:"+" "+"姓名:"+" "+"地址");  
    System.out.println (Stu+" "+name+" "+add);  
    }  
    c.close();  
    }  
    } 

    E:查询数据的存储过程(模糊查询)

    存储过程:

    create procedure FindCusts  
    @cust varchar(10)  
    as 
    select customerid from orders where customerid   
    like '%'+@cust+'%' 

    执行:

    execute FindCusts 'alfki' 

    在JAVA中调用:

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
     
     
    //创建存储过程的对象  
    CallableStatement c=conn.prepareCall("{call FindCusts(?)}");  
    c.setString(1,"Tom");  
     
    ResultSet rs=c.executeQuery();  
     
    while(rs.next())  
    {  
    String cust=rs.getString("customerid");   
    System.out.println (cust);  
    }  
    c.close();  
    }  
    } 

    F:增加数据的存储过程

    存储过程:

    create procedure InsertPro  
    @StuID int,  
    @StuName varchar(10),  
    @StuAddress varchar(20)  
    as 
    insert into 学生基本信息表 values(@StuID,@StuName,@StuAddress)

    调用存储过程:

    exec InsertPro 5,'555','555' 

    在JAVA中执行:

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
     
     
    //创建存储过程的对象  
    CallableStatement c=conn.prepareCall("{call InsertPro(?,?,?)}");  
    c.setInt(1,6);  
    c.setString(2,"Liu");  
    c.setString(3,"wuhan");  
     
    c.execute();  
     
    c=conn.prepareCall("{call selePro}");  
    ResultSet rs=c.executeQuery();  
     
    while(rs.next())  
    {  
    String stuid=rs.getString("StuID");   
    String name=rs.getString("StuName");   
    String address=rs.getString("StuAddress");   
    System.out.println (stuid+" "+name+" "+address);  
    }  
    c.close();  
    }  
    } 

    G:在JAVA中创建存储过程 并且在JAVA中直接调用

    import java.sql.*;  
    public class ProcedureTest   
    {  
    public static void main(String args[]) throws Exception  
    {  
    //加载驱动  
    DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());  
    //获得连接  
    Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");  
     
     
    Statement stmt=conn.createStatement();  
    //在JAVA中创建存储过程  
    stmt.executeUpdate("create procedure OOP as select * from 学生成绩表");  
     
     
    CallableStatement c=conn.prepareCall("{call OOP}");  
     
    ResultSet rs=c.executeQuery();  
    while(rs.next())  
    {  
    String chinese=rs.getString("Chinese");  
     
    System.out.println (chinese);  
    }  
    conn.close();  
     
    }  
    } 

     

    原文链接:http://technicalsearch.iteye.com/blog/1433293

  • 相关阅读:
    Python Day7(相关补充)
    Python Day7
    Python Day6
    Python Day5
    Python Day4
    Python Day3
    Python Day2
    Python Day1
    复杂装饰器原理分析
    Unity 坐标
  • 原文地址:https://www.cnblogs.com/davidwang456/p/4094874.html
Copyright © 2020-2023  润新知