java调用mysql的存储过程

14 篇文章 0 订阅

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();
    }
}

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值