用于执行 SQL 存储过程的可调用语句接口。JDBC API 提供了一种存储过程 SQL 转义语法,该语法允许以标准方式为所有 RDBMS 调用存储过程。
连接接口提供准备调用(字符串 SQL) 方法来创建用于调用数据库存储过程的可调用状态对象。对象提供用于设置其 IN 和 OUT 参数的方法,以及用于执行对存储过程的调用的方法。
使用的技术
- JDK - 1.8 or later
- MySQL - 5.7.12
- IDE - Eclipse Neon
- JDBC API - 4.2
使用 JDBC 处理存储过程 SQL 语句的步骤
- 建立连接。
- 从连接对象创建可调用语句。
- 执行存储过程查询。
- 使用“使用资源试用”语句自动关闭 JDBC 资源
从 JDBC 4.0 开始,我们不需要在代码中包含“类.forName()”来加载 JDBC 驱动程序。将自动加载在类路径中找到的 JDBC 4.0 驱动程序。
具有单个结果集示例的可调用状态
请考虑以下 MySQL 存储过程。
DELIMITER $$
USE `mysql_database`$$
CREATE PROCEDURE `retreive_users` ()
BEGIN
select * from users;
END$$
DELIMITER ;
下面的程序演示如何调用 retreive_users() 存储过程并生成单个结果集。
package com.javaguides.jdbc.storedprocedure;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Simple CallableStatement Example
* @author Ramesh Fadatare
*
*/
public class SimpleCallableStatementExample {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false";
String username = "root";
String password = "root";
String sql = "call retreive_users()";
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); CallableStatement stmt = conn.prepareCall(sql); ResultSet rs = stmt.executeQuery();) {
while (rs.next()) {
System.out.println("ID = " + rs.getInt(1) + ", NAME = " + rs.getString(2) + ", Email = " +
rs.getString(3) + ", Country = " + rs.getString(4) + ", Password = " + rs.getString(5));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
输出:
ID = 1, NAME = A, Email = tony@gmail.com, Country = US, Password = secret
ID = 2, NAME = Pramod, Email = pramod@gmail.com, Country = India, Password = 123
ID = 3, NAME = A, Email = a@gmail.com, Country = India, Password = 123
ID = 4, NAME = B, Email = b@gmail.com, Country = India, Password = 123
ID = 5, NAME = C, Email = c@gmail.com, Country = India, Password = 123
ID = 6, NAME = D, Email = d@gmail.com, Country = India, Password = 123
具有多个结果集示例的可调用状态
请考虑以下具有多个选择语句的 MySQL 存储过程。
DELIMITER $$
USE `mysql_database`$$
BEGIN
select distinct name from users where id = 1;
select distinct email from users;
select count(id) as users_count from users;
END
DELIMITER ;
下面的程序演示如何调用 retreive_different_results() 存储过程并获取多个结果集。
package com.javaguides.jdbc.storedprocedure;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* CallableStatement Example
* @author Ramesh Fadatare
*
*/
public class CallableMultipleResultSetExample {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false";
String username = "root";
String password = "root";
String sql = "call retreive_different_results()";
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); CallableStatement stmt = conn.prepareCall(sql);) {
boolean hasRs = stmt.execute();
System.out.println();
// Get Product Names
if (hasRs) {
try (ResultSet rs = stmt.getResultSet()) {
while (rs.next()) {
System.out.println("NAME = " + rs.getString(1));
}
}
}
// Get Total Price
if (stmt.getMoreResults()) {
try (ResultSet rs = stmt.getResultSet()) {
if (rs.next()) {
System.out.println("Email = " + rs.getString(1));
}
}
}
// Get Max/Min Price
if (stmt.getMoreResults()) {
try (ResultSet rs = stmt.getResultSet()) {
if (rs.next()) {
System.out.println("Users count = " + rs.getInt(1));
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
输出:
NAME = A
Email = tony@gmail.com
Users count = 6