WEB开发网
开发学院软件开发Java JDBC调用MySQL5存储过程 阅读

JDBC调用MySQL5存储过程

 2009-12-19 00:00:00 来源:WEB开发网   
核心提示:一、环境MySQL5.1mysql-connector-java-5.1.10jdk1.5表createtableuser(idint(11)notnullauto_increment,namevarchar(50)notnull,pswdvarchar(50)defaultnull,piclongblob,remar

一、环境

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

Tags:JDBC 调用 MySQL

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接