使用Java代码/SQL语句调用存储过程并接收存储过程返回值
创建存储过程
CREATE PROCEDURE add_pro (IN
a1
varchar(100), INa2
varchar(100), OUTsum
varchar(10))
BEGIN
set sum = a + b;
END;
SQL运行存储过程
CALL add_pro(1,2,@sum);
SELECT @sum;
Java代码调用存储过程,接收返回值
数据连接
package net.e_lian.bpm.damage.serviceImpl.customprocedure;
import java.io.File;
import java.io.FileInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtil {
private static String driverClass = null;
private static String url = null;
private static String user = null;
private static String password = null;
static {
try {
//读取文件中的数据库连接信息
Properties props = new Properties();
String path = JDBCUtil.class.getClassLoader().getResource("***.properties").toURI().getPath();
// 把文件读入文件输入流,存入内存中
FileInputStream fis = new FileInputStream(new File(path));
props.load(fis);
driverClass = props.getProperty("******").trim();
url = props.getProperty("******").trim();
user = props.getProperty("******").trim();
password = props.getProperty("******").trim();
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* 开启连接
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭连接
* @param conn
* @param cstmt
* @throws SQLException
*/
public static void storageDataClose(Connection conn, CallableStatement cstmt) throws SQLException {
if (conn != null) {
conn.close();
}
if (cstmt != null) {
cstmt.close();
}
}
}
JAVA调用数据存储过程
/**
* 调用存调过程,并接收返回值
* @param dataSql 调用语句
* @param a1 传入的参数1
* @param a2 传入的参数2
* @return 存储过程的返回值
*/
public static String procedureReturn(String dataSql,int a1,int a2) {
String message = null;
Connection con = null;
CallableStatement cstmt = null;
try {
con = JDBCUtil.getConnection();
cstmt = con.prepareCall(dataSql);
cstmt.setInt(1,a1);
cstmt.setInt(2,a2);
cstmt.registerOutParameter(3,Types.VARCHAR);
cstmt.execute();
//接收存储过程返回的值
message = cstmt.getString(3);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtil.storageDataClose(con, cstmt);
} catch (SQLException e) {
e.printStackTrace();
}
}
return message;
}
测试
public static void main(String[] args) {
String sql = "{call add_pro(?,?,?)}";
String s = procedureReturn(sql,1,2);
System.out.println("最终的结果:"+s);
}