使用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

存储过程的优点：

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

缺点：

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