JDBC调用MySQL5存储过程
 
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调用存储过程很容易,但是开发存储过程比较困难,我也基本上没写过MySQL的存储过程,写上面的存储过程参看了下面一篇博文: