JDBC调用存储过程

参考链接

https://www.iteye.com/blog/sjsky-1246657

https://blog.csdn.net/qq_27888773/article/details/78493537

jdbc调用存储过程:

经常使用的4种:

 1. 返回结果集的proc

 2. 输出参数

 3.使用带有返回状态的存储过程

 4.受影响行数

以下为mysql的存储过程 sqlServer同理

• 案例1: 返回结果集的proc

存储过程:

drop procedure if exists proc_selectEmployee;

create procedure proc_selectEmployee(in carid varchar(20))

begin

    select * from employee where cardID = carid;

end

 

call proc_selectEmployee('SZ65380');

 

import java.sql.*;  
      
    import java.sql.CallableStatement;  
      
    /** 
     *  
     * 简单的jdbc调用存储过程 只有输入参数 返回单个结果集 
     *  
     */  
    public class GeTest1 {  
        public static void main(String[] args) {  
      
            Connection connection = null;  
            //用于执行 SQL 存储过程的接口  
            CallableStatement statement = null;  
            ResultSet resultSet = null;  
      
            try {  
                Class.forName("com.mysql.jdbc.Driver");  
                String url = "jdbc:mysql://localhost:3306/test";  
      
                String user = "root";  
      
                String password = "123";  
      
                connection = DriverManager.getConnection(url, user, password);  
      
                String sql = "call proc_selectEmployee(?)";  
      
                //调用存储过程  
                statement = connection.prepareCall(sql);  
      
                statement.setString(1, "SZ65380");  
      
                resultSet = statement.executeQuery();  
      
                if (resultSet.next()) {  
                    System.out.println(resultSet.getString("address"));  
                }  
      
            } catch (ClassNotFoundException e) {  
                e.printStackTrace();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            } catch (Exception e) {  
                e.printStackTrace();  
            } finally {  
                try {  
                    if (resultSet != null) {  
                        resultSet.close();  
                    }  
                    if (statement != null) {  
                        statement.close();  
                    }  
                    if (connection != null) {  
                        connection.close();  
                    }  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
    }  

 

• 案例2: 输出参数

 存储过程:

drop procedure if exists proc_outtwo;

create procedure proc_outtwo(in idint int,out cardIdstring varchar(44),out addressstring varchar(88))

begin

    select cardID,address into cardIdstring,addressstring from employee where id =idint;

end

 

call proc_outtwo(1,@one,@two);

select @one;

select @two;

 

import java.sql.*;  
      
    import java.sql.CallableStatement;  
      
    /** 
     *  
     * 执行存储过程 得到输出参数 
     *  
     */  
    public class GeTest2 {  
      
        /** 
         * @param args 
         */  
        public static void main(String[] args) {  
      
            Connection connection = null;  
            //用于执行 SQL 存储过程的接口  
            CallableStatement statement = null;  
      
            ResultSet resultSet = null;  
      
            try {  
                Class.forName("com.mysql.jdbc.Driver");  
                String url = "jdbc:mysql://localhost:3306/test";  
      
                String user = "root";  
      
                String password = "123";  
      
                connection = DriverManager.getConnection(url, user, password);  
      
                  
                //第一个为输入参数后面2个为输出参数  
                String sql = "call proc_outtwo(?,?,?);";  
      
                //调用存储过程  
                statement = connection.prepareCall(sql);  
      
                //设置输入参数  
                statement.setInt(1, 1);  
      
                //设置输出参数 以及类型  
                statement.registerOutParameter(2, Types.VARCHAR);  
      
                statement.registerOutParameter(3, Types.VARCHAR);  
      
                statement.execute();  
      
                //得到输出参数  
                System.out.println(statement.getString(2));  
      
                System.out.println(statement.getString(3));  
      
            } catch (ClassNotFoundException e) {  
                e.printStackTrace();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            } catch (Exception e) {  
                e.printStackTrace();  
            } finally {  
                try {  
                    if (resultSet != null) {  
                        resultSet.close();  
                    }  
                    if (statement != null) {  
                        statement.close();  
                    }  
                    if (connection != null) {  
                        connection.close();  
                    }  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
    }  

 

 

• 案例3:使用带有返回状态的存储过程  return 1;  mysql的proc不支持 返回值 sqlserver支持

如果要获得返回值的话为:

存储过程:

create proc checkit

(@addressString varchar(50))

as

begin

if ((select count(*) from employee where address =@addressString))

    return 1

else

    return 0

go

 

CallableStatement cstmt = con.prepareCall("{? = call checkit(?)}");  
          cstmt.registerOutParameter(1, java.sql.Types.INTEGER);  
          cstmt.setString(2, "深圳");  
          cstmt.execute();  
          System.out.println("return的值" + cstmt.getInt(1)); 

•  案例4: 获得更新行数:

 

drop procedure if exists proc_updateEmployee;

create procedure proc_updateEmployee()

begin

    update Employee set job=1;

end

call proc_selectEmployee();

 

    CallableStatement cstmt = con.prepareCall("{call proc_updateEmployee()}");  
          cstmt.execute();  
          int count = cstmt.getUpdateCount();  
          cstmt.close();  
          System.out.println("受影响行数:" + count);   

最近的项目中store procedure调用全局参数@@rowcount, 作用就是 获得上次执行的记录数，但是在Java里面拿不到存储过程的返回值（影响的行数）

最终尝试上面的方法可以拿到

 

create   proc  pr_isExistLoginName
(
   @LoginName   varchar ( 30 )
)
as
select   [ LoginName ]   from   [ PersonLogin ]   where   [ LoginName ] = @LoginName
return   @@rowcount

 

 

 

SQLServer

osql工具可以在命令行与数据库交互但是-D这个参数应该有bug,不能用MSI的方式去连数据库，因此放弃这种方式，用普通的JDBC

osql的参考资料

https://docs.microsoft.com/en-us/sql/tools/osql-utility?view=sql-server-ver15

-D　is doesn't work 

https://github.com/microsoft/homebrew-mssql-release/issues/44