JDBC进阶之存储过程+CallableStatement

一、什么是存储过程

           首先介绍数据库存储程序，数据库存储程序有时也被称为存储模块 -—— 一种被数据库服务器所存储和执行的计算机程序（有一系列不同的称呼），存储程序的源代码（有时）可能是二进制编译版本几乎总是占据着数据库服务器系统的表空间，程序总是位于其数据库服务器的进程或线程的内存地址中被执行。 

           主要有三种类型的数据库存储程序，而本文所要讲的存储过程就是其中的一种类型，另外两种分别是存储函数和触发器。

           存储过程是最常见的存储程序，存储过程是能够接受数个输入和输出参数并且能够在请求时被执行的程序单元。  它是预先用SQL语句写好，用一个指定的名称存储起来，经过编译后存储在数据库中。当需要使用时，用户只需要传入指定存储过程的名字，并给出参数（如果是存储过程是带参数的话）来执行它。

二、存储过程的优点

1、可以提高数据库的执行速度：存储过程中可以包含多个执行指令，且只需要编译一次即可使用。如果将存储过程中的指令分成一条条的SQL语句，在执行时，需要一条条编译，一条条的执行，这占用很多时间，也会使得程序的运行速度很慢，使得程序的性能降低；

2、方便开发者使用：用于存储过程中只需编写一次，就可以重复使用，这大大减少了开发人员的工作量。

3、安全性高：存储过程在调用的时候可以传入参数，所以可以根据传入的参数来限制此人是否可以调用此过程，从而提高了数据的安全性。

三、创建存储过程

        存储过程可分为四类：无参数存储过程、带输入值过程、带输出值过程和既有输入参数又有输出值的存储过程。创建具体的存储过程理论指导可以参考《MySQL  存储过程编程 》这本书(电子书下载)。下面以之前的文章《JDBC连接MySQL数据库及示例》中的数据库为基础，以具体实例来演示在staff表格中创建存储过程。

1、创建村错过程使用的方式：你可以用 CREATE PROCEDURE , CREATE FUNCTION ,或者 CREATE TRIGGER  语句来创建存储程序。可以直接把这些语句直接输入 MySQL 命令行，但是对于一般的存储程序大小而言，这有些不太实际，所以我们建议你使用文本编辑器创建一个文本文件来容纳我们的存储程序，然后我们就可以使用命令行客户端和其他工具来递交这个文件。我们将使用MySQL Query Browser作为文本编辑器，如果你没有这个工具，你可以到我的空间里的上传的资源里找（http://download.csdn.net/detail/cxwen78/3681275）。你也可以使用任何操作系统上的编辑器例如 vi, emacs 或者 notepad，当然我们喜欢 MySQL Query Browser 的原因是它具备内建的帮助系统，语法高亮，执行 SQL 语句的能力以及其他一些功能 。

2、在 MySQL Query Browser中创建存储过程：下载 MySQL Query Browser并成功安装后运行它。在Schemata视图中找到“myuser”数据库，在“staff ”表格上右键>Create New Procedure>在弹出的对话框中输入存储过程的名称(如：addStaff) > 点击 Create PROCEDURE创建存储过程，演示如图所示：

3、编写存储过程：创建后，会显示一个存储过程的模版，我们将其修改如下：

DELIMITER $

DROP PROCEDURE IF EXISTS `myuser`.`addStaff` $
CREATE PROCEDURE `myuser`.`addStaff` (in sname varchar(50), in age int, in sex varchar(5), in address varchar(50), in depart varchar(50), in worklen int, in wage int,out sid int)
BEGIN
    insert into staff(name,age,sex,address,depart,worklen,wage) values (sname,age,sex,address,depart,worklen,wage);
    select last_insert_id() into sid;
END $

DELIMITER ;

代码解读：

1）.DELIMITER $$ ：将定界符定义为“$$”，这样，即在这个过程中“；”不再是语句的结束符，而是“$$”；

2）.DROP PROCEDURE IF EXISTS `myuser`.`addStaff` $$ ：判断，如果已经存在addStaff这个存储过程，则将其删除；

3）.CREATE PROCEDURE `myuser`.`addStaff` (in sname varchar(50), in age int, in sex varchar(5), in address varchar(50), in depart varchar(50), in worklen int, in wage int,out sid int) ：这是即带输入参数也带有输出参数的过程。

         myuse：是数据库名；

         addStaff：存储过程名；
         in sname varchar(50)：in 表示“sname”参数是输入型参数， varchar(50) 表示值的类型，类似还有int ；前面7个都是输入参数；

         out sid int ：out 表示 “sid ”参数是输出型参数，int表示值的类型；

4）.BEGIN 和END之间的是SQL语句集合

5）. insert into staff(name,age,sex,address,depart,worklen,wage) values (sname,age,sex,address,depart,worklen,wage); ：插入一条记录，记录各个字段的值由传入的参数进行输入，即第一个括号里的是字段名，第二个括号里得是输入参数的值；

编译执行过程代码：

按下 Execute 按钮来执行存储过程，如果你的代码发生了错误，Query Browser 将在底部显示错误并用高亮标识发生错误的行，否则，你将在左侧的 Schemata 选项卡中发现你的存储过程已被成功的创建。如图所示：

四、调用存储过程

1、新建一个类，如：procedureTest，类的实现如下。其中用于执行SQL的Statement为 CallableStatement，是专门用于执行 SQL 存储过程的接口。

package com.serein.jdbc;

import java.sql.*;

import com.sun.org.apache.xalan.internal.xsltc.compiler.util.Type;

public class procedureTest {
	
	// 数据库连接
	public static Connection getConnection() { 
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver"); // 加载Mysql数据驱动
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/myuser", "root", "root"); // 创建数据连接
		} catch (Exception e) {
			System.out.println("数据库连接失败");
		}
		return conn;
	}
	
	// 列出数据库中所有的存储过程名
	public static void GET_AllProName(Connection con) {
		try {
			DatabaseMetaData md = con.getMetaData(); // 获得数据库的元数据
			ResultSet resultSet = md.getProcedures(null, null, "%"); // 获得所有的存储过程的描述
			System.out.println("数据库现有的存储过程名为："); // 显示存储过程名，位于结果集的第三个字段
			
			while (resultSet.next()) {
				String procName = resultSet.getString(3);
				System.out.print(procName + "\n");
			}
			
			System.out.println();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//*/
	
	// 调用存储过程
	public static void CALL_Procedure(Connection con) throws Exception {
		CallableStatement cst = null;      // CallableStatement是Statement的子类
		System.out.println("开始执行存储过程");
		try {
			// 调用无参数的存储过程
			cst = con.prepareCall("{call addStaff(?,?,?,?,?,?,?,?)}");	 //8个？号作占位符
			
			//设置输入的参数值
			cst.setString(1, "Tina");
			cst.setInt(2, 23);
			cst.setString(3, "W");
			cst.setString(4, "Shanghai");
			cst.setString(5, "Personnel");
			cst.setInt(6, 1);
			cst.setInt(7, 3000);
			cst.registerOutParameter(8, Type.INTERNAL);	//注册输出参数类型
			cst.execute();	//执行
			
			int insertID = cst.getInt(8);	//获取输出的参数值
			System.out.println("The last staff ID is :" + insertID);	//将输入的参数打印出来

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cst.close();	
		}
		System.out.println("存储过程执行结束");
	}
	
	public static void main(String[] args) throws Exception{
		Connection conn = null;
		try {
			conn = getConnection(); // 获得数据库连接
			GET_AllProName(conn); // 列出数据库的所有存储过程名
			CALL_Procedure(conn); // 调用存储过程
		} catch (Exception e1) {
			throw e1;
		} finally {
			conn.close(); // 关闭数据库连接
		}

	}

}

2、运行及结果

在“控制台”显示的运行结果：

查看数据库中的“staff”表格，成功插入：