一、环境
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调用存储过程很容易,但是开发存储过程比较困难,我也基本上没写过MySQL的存 储过程,写上面的存储过程参看了下面一篇博文:
http://www.blogjava.net/sxyx2008/archive/2009/11/24/303497.html
出处:http://lavasoft.blog.51cto.com/62575/238613
如果你希望成功,以恒心为良友,以经验为参谋,以小心为兄弟,以希望为哨兵。