1 首先创建一个数据表(我这边用的是mysql5.7):
create table students(
id int primary key auto_increment,
username varchar(11),
age int,
salary int
)
表中数据如下:
然后创建存储过程:
-- test01方法的调用的储存过程
create procedure delete_stu(in delusername varchar(15))
begin
delete from students
where username=delusername;
end
-- test02方法调用的存储过程
create PROCEDURE copy_stu(in sage int,out count_num int)
BEGIN
select count(*) into count_num from students where age=sage;
END
接着编写示例程序调用这两个存储过程:
import java.sql.*;
public class Demo01 {
public static final String DRIVER_CLASS="com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://127.0.0.1:3306/demo";
public static final String USERNAME = "root";
public static final String PASSWORD = "123456";
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// test1();
test2();
}
public static void test1() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER_CLASS);
Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
String sql = "{CALL delete_stu(?)}";// 调用存储过程
CallableStatement cstm = connection.prepareCall(sql); // 实例化对象cstm
cstm.setString(1,"Python"); // 设置参数
cstm.execute();// 执行储存过程
System.out.println(cstm.toString());
cstm.close();
connection.close();
}
public static void test2() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER_CLASS);
Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
String sql = "{CALL copy_stu(?,?)}";
CallableStatement cstm =connection.prepareCall(sql);
cstm.setInt(1,10);
cstm.registerOutParameter(2, Types.INTEGER); // 设置返回值
cstm.execute();
System.out.println(cstm.getInt(2));
cstm.close();
connection.close();
}
}