java存储过程带参数,高级进阶 调用 带返回参数 存储过程

1. 创建一个添加 users 并且返回 最新插入 id 存储过程

DELIMITER $

create procedure sp_user_add_id(in in_username varchar(20),in in_pwd varchar(20),out out_id int)

begin

insert into users(username,pwd)values(in_username,in_pwd);

select last_insert_id() into out_id;

end $

2.调用存储过程 sp_user_add_id

package com.jdbc12;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Types;

import com.db.DBUtil;

public class TestAdd_Id {

/**

* @param args

*/

public static void main(String[] args) {

DBUtil db=new DBUtil();

Connection conn=db.openConnection();

String sql="call sp_user_add_id(?,?,?)";

try {

CallableStatement callStmt=conn.prepareCall(sql);

callStmt.setString(1, "wangtao");

callStmt.setString(2, "222222");

callStmt.registerOutParameter(3, Types.INTEGER);

callStmt.executeUpdate();

int id=callStmt.getInt("out_id");

System.out.println(id);

} catch (SQLException e) {

e.printStackTrace();

}

}

}