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
(
@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
