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調用存儲過程很容易,但是開發存儲過程比較困難
本文出自 「熔 岩」 博客,請務必保留此出處http://lavasoft.blog.51cto.com/62575/238613
[火星人 ] JDBC調用MySQL5存儲過程已經有464次圍觀