一、環境
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