使用CallableStatement调用存储过程

什么是存储过程？

就是带有逻辑的sql语句，由于增删改查没有带逻辑，使用存储过程可以判断，循环。

如何创建存储过程？

CREATE PROCEDURE pro_test()           --存储过程名称(参数列表)
BEGIN             
	SELECT * FROM employee;
END           

CALL pro_test();          -- 使用CALL执行 存储过程名称(参数);

带有输入参数的存储过程：

CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
BEGIN
	SELECT * FROM employee WHERE id=eid;
END 

CALL pro_findById(4);

带有输出参数的存储过程：

CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数
BEGIN
        -- 给参数赋值
	SET str='helljava';
END

删除存储过程：

DROP PROCEDURE pro_testOut;

带有条件判断的存储过程：

需求：输入一个整数，如果1，则返回“张三”,如果2，返回“李四”,如果3，返回“王五”。其他数字，返回“错误输入”;

CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
	IF num=1 THEN
		SET str='张三 ';
	ELSEIF num=2 THEN
		SET str='李四 ';
	ELSEIF num=3 THEN
		SET str='王五 ';
	ELSE
		SET str='输入错误';
	END IF;
END

CALL pro_testIf(4,@str);
SELECT @str;--查看输出的值

带有循环功能的存储过程：

需求：输入一个整数，求和。

CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
	-- 定义一个局部变量
	DECLARE i INT DEFAULT 1;
	DECLARE vsum INT DEFAULT 0;
	WHILE i<=num DO
	      SET vsum = vsum+i;
	      SET i=i+1;
	END WHILE;
	SET result=vsum;
END 
<p>CALLpro_testWhile(100,@result);</p><p>SELECT @result;</p>

语法形式就这样了

总结：

参数：

IN：   表示输入参数，可以携带数据带存储过程中

OUT： 表示输出参数，可以从存储过程中返回结果

INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

---------------------------------------------------------

一般使用navicat客户端如何操作这个存储过程：

1.先建立好数据库，然后建立存储过程

2.选中过程，点击完成

然后保存下名为pro_test2(自己随意起)

上图也写了代码，就是输入参数为id号，输出对应的名字，点击运行。

输入参数，然后就返回结果了。其中SELECT @sname就是查询sname结果。

-------------------------------------------------------------------------

本文的主题是CallableStatement执行存储过程

一个代码是带有输入参数的存储过程例子：

封装了JDBC的工具类：

public class JdbcUtil {
	private static String url = null;
	private static String user = null;
	private static String password = null;
	private static String driverClass = null;
	
	/**
	 * 静态代码块中（只加载一次）
	 */
	static{
		try {
			//读取db.properties文件
			Properties props = new Properties();
			/**
			 *  . 代表java命令运行的目录
			 *  在java项目下，. java命令的运行目录从项目的根目录开始
			 *  在web项目下，  . java命令的而运行目录从tomcat/bin目录开始
			 *  所以不能使用点.
			 */
			//FileInputStream in = new FileInputStream("./src/db.properties");
			
			/**
			 * 使用类路径的读取方式
			 *  / : 斜杠表示classpath的根目录
			 *     在java项目下，classpath的根目录从bin目录开始
			 *     在web项目下，classpath的根目录从WEB-INF/classes目录开始
			 */
			InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties");
			
			//加载文件
			props.load(in);
			//读取信息
			url = props.getProperty("url");
			user = props.getProperty("user");
			password = props.getProperty("password");
			driverClass = props.getProperty("driverClass");
			
			
			//注册驱动程序
			Class.forName(driverClass);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("驱程程序注册出错");
		}
	}

	/**
	 * 抽取获取连接对象的方法
	 */
	public static Connection getConnection(){
		try {
			Connection conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	
	
	/**
	 * 释放资源的方法
	 */
	public static void close(Connection conn,Statement stmt){
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
	}
	
	public static void close(Connection conn,Statement stmt,ResultSet rs){
		if(rs!=null)
			try {
				rs.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
				throw new RuntimeException(e1);
			}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
	}
}

这个是主要的实现类：

@Test
public void test1(){
	Connection conn = null;
	CallableStatement stmt = null;
	ResultSet rs = null;
	try {
		//获取连接
		conn = JdbcUtil.getConnection();
			
		//准备sql
		String sql = "CALL pro_findById(?)"; //可以执行预编译的sql
			
		//预编译
		stmt = conn.prepareCall(sql);
			
		//设置输入参数
		stmt.setInt(1, 1);
			
		//发送参数,所有调用存储过程的sql语句都是使用executeQuery方法执行！！！
		rs = stmt.executeQuery();
			
		//遍历结果
		while(rs.next()){
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String gender = rs.getString("gender");
			System.out.println(id+","+name+","+gender);
		}
			
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException(e);
	} finally {
		//rs,stmt,conn按顺序关了
                JdbcUtil.close(conn, stmt ,rs);
       }
}

一个带有输出参数的存储过程：

@Test
public void test2(){
	Connection conn = null;
	CallableStatement stmt = null;
	ResultSet rs = null;
	try {
		//获取连接
		conn = JdbcUtil.getConnection();
		//准备sql
		String sql = "CALL pro_test2(?,?)"; //第一个？是输入参数，第二个？是输出参数
			
		//预编译
		stmt = conn.prepareCall(sql);
			
		//设置输入参数
		stmt.setInt(1, 3);
		//设置输出参数(注册输出参数)
		/**
		 * 参数一： 参数位置
		* 参数二： 存储过程中的输出参数的jdbc类型    VARCHAR(20)
	        */
		stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			
		//发送参数，执行
		stmt.executeQuery(); //结果不是返回到结果集中，而是返回到输出参数中
			
		//得到输出参数的值
		/**
		 * 索引值： 预编译sql中的输出参数的位置
		 */
		String result = stmt.getString(2); //getXX方法专门用于获取存储过程中的输出参数
			
		System.out.println(result);

	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException(e);
	} finally {
		JdbcUtil.close(conn, stmt ,rs);
	}
}

结果：

ls

存储过程的优点：

执行效率很快（因为在数据库服务端执行的）。

缺点：

移植性差（不同数据库的存储过程是不可移植的）。