JDBC调用MySQL5存储过程,过程有入参和出参,执行存储过程,并获取出参。
一、环境
MySQL5.1
mysql-connector-java-5.1.10
jdk1.5
表
create table user ( id int(11) not null auto_increment, name varchar(50) not null, pswd varchar(50) default null, pic longblob, remark longtext, primary key (id) ); |
二、写存储过程
DELIMITER $$ DROP PROCEDURE IF EXISTS testprocedure $$ CREATE DEFINER= `vcom`@`%` PROCEDURE testprocedure(in in_name varchar(20),in in_pswd varchar(20),out out_id bigint) BEGIN insert into user(name,pswd) values(in_name,in_pswd); select last_insert_id() into out_id; END $$ DELIMITER ; |
三、JDBC调用存储过程
import lavasoft.common.DBToolkit; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import java.sql.Types; /** * JDBC调用MySQL5存储过程 * * @author leizhimin 2009-12-4 10:33:12 */ public class ProcedureTest { public static void main(String[] args) { testExeProcedure(); } public static void testExeProcedure() { Connection conn = DBToolkit.getConnection(); //创建调用存储过程的预定义SQL语句 String sql = "{call testprocedure(?,?,?)}"; try { //创建过程执行器 CallableStatement cstmt = conn.prepareCall(sql); //设置入参和出参 cstmt.setString(1, "wangwu"); cstmt.setString(2, "111111"); cstmt.registerOutParameter(3, Types.BIGINT); //注册出参 cstmt.executeUpdate(); //获取输出参数值(两种方式都行) Long id = cstmt.getLong(3); //Long id = cstmt.getLong("out_id"); System.out.println("本次插入数据的id=" + id); } catch (SQLException e) { e.printStackTrace(); } finally { DBToolkit.closeConnection(conn); } } } |
运行后,控制台:
本次插入数据的id=1
Process finished with exit code 0
Java调用存储过程很容易,但是开发存储过程比较困难。