className // 存储过程的名称
int totalLen // 参数个数
int[] inPlace // In参数索引
int[] outPlace // Out参数索引
List valArgs // In参数集合
List outValType // OutType参数类型
加载完连接池后
通过获取ProFuncDto对象中的传入过来的值,对对象中的数据进行处理
我们要动态获取传入过来的参数
然后对java类型和数据库IN字段进行匹配
public static void getInPlace(int[] inPlace, List<Object> valArgs, CallableStatement statement) {
// 对java类型和数据库IN字段进行匹配
for (int i = 0; i < inPlace.length; i++) {
try {
if (valArgs.get(i) instanceof String) {
statement.setString(inPlace[i], valArgs.get(i).toString());
}
if (valArgs.get(i) instanceof Long) {
statement.setLong(inPlace[i], (long) valArgs.get(i));
}
if (valArgs.get(i) instanceof Integer) {
statement.setInt(inPlace[i], (int) valArgs.get(i));
}
if (valArgs.get(i) instanceof Double) {
statement.setDouble(inPlace[i], (double) valArgs.get(i));
}
if (valArgs.get(i) instanceof Date) {
statement.setDate(inPlace[i], (Date) valArgs.get(i));
}
if (valArgs.get(i) instanceof Boolean) {
statement.setBoolean(inPlace[i],(boolean) valArgs.get(i));
}
if (valArgs.get(i) instanceof Timestamp) {
statement.setTimestamp(inPlace[i],(Timestamp) valArgs.get(i));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//对java类型和数据库Out字段进行匹配
public static void getOutPlace(int[] outPlace, List<Object> outValType, CallableStatement statement) {
// 对java类型和数据库Out字段进行匹配
for (int i = 0; i < outPlace.length; i++) {
try {
if (outValType.get(i).equals("12")) {
statement.registerOutParameter(outPlace[i], Types.VARCHAR);
}
if (outValType.get(i).equals("-5")) {
statement.registerOutParameter(outPlace[i], Types.BIGINT);
}
if (outValType.get(i).equals("8")) {
statement.registerOutParameter(outPlace[i], Types.DOUBLE);
}
if (outValType.get(i).equals("4")) {
statement.registerOutParameter(outPlace[i], Types.INTEGER);
}
if (outValType.get(i).equals("16")) {
statement.registerOutParameter(outPlace[i], Types.BOOLEAN);
}
if (outValType.get(i).equals("2")) {
statement.registerOutParameter(outPlace[i], Types.NUMERIC);
}
if (outValType.get(i).equals("1")) {
statement.registerOutParameter(outPlace[i], Types.CHAR);
}
if (outValType.get(i).equals("93")) {
statement.registerOutParameter(outPlace[i], Types.TIMESTAMP);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
再调用查询就可以了。
statement.execute();
最后贴上整个代码块
package com.more.mes.command;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.more.fw.core.common.method.SpringContextUtil;
import com.more.fw.core.dbo.model.service.ModelService;
import com.more.fw.core.sysfa.usermessage.model.ProFuncDto;
public class JDBCPro{
private static ModelService modelService = (ModelService) SpringContextUtil.getBean("modelService");
public static List<Object> procComm(ProFuncDto pdfDto) {
List<Object> list = new ArrayList<Object>();
try {
Connection conn = modelService.getConnection();
String className = pdfDto.getClassName(); // 存储过程名称
int totalLen = pdfDto.getTotalLen(); // 参数个数
int[] inPlace = pdfDto.getInPlace(); // In参数索引
int[] outPlace = pdfDto.getOutPlace(); // Out参数索引
List<Object> valArgs = pdfDto.getValArgs(); // In参数集合
List<Object> outValType = pdfDto.getOutValType(); // OutType参数类型
// 对存储过程中的参数进行动态传入
String procedure = getProcedure(totalLen, className);
// 将调用存储过程的语句加入连接池
CallableStatement statement = conn.prepareCall(procedure);
// 对java类型和数据库IN字段进行匹配
getInPlace(inPlace, valArgs, statement);
//对java类型和数据库Out字段进行匹配
getOutPlace(outPlace, outValType, statement);
// 执行存储过程
statement.execute();
// 获取执行存储过程后的返回值
for (int i = 0; i < outPlace.length; i++) {
list.add(statement.getObject(outPlace[i]));
}
//conn.close();
} catch (SQLException sqe) {
list.add("failure");
System.out.println("Unexpected exception : " + sqe.toString() + ", sqlstate = " + sqe.getSQLState());
//System.exit(1);
} catch (Exception e) {
list.add("failure");
System.out.println(e.getMessage());
}
return list;
}
/***
* 对java类型和数据库IN字段进行匹配
*
* @param inPlace
* @param valArgs
* @param statement
*/
public static void getInPlace(int[] inPlace, List<Object> valArgs, CallableStatement statement) {
// 对java类型和数据库IN字段进行匹配
for (int i = 0; i < inPlace.length; i++) {
try {
if (valArgs.get(i) instanceof String) {
statement.setString(inPlace[i], valArgs.get(i).toString());
}
if (valArgs.get(i) instanceof Long) {
statement.setLong(inPlace[i], (long) valArgs.get(i));
}
if (valArgs.get(i) instanceof Integer) {
statement.setInt(inPlace[i], (int) valArgs.get(i));
}
if (valArgs.get(i) instanceof Double) {
statement.setDouble(inPlace[i], (double) valArgs.get(i));
}
if (valArgs.get(i) instanceof Date) {
statement.setDate(inPlace[i], (Date) valArgs.get(i));
}
if (valArgs.get(i) instanceof Boolean) {
statement.setBoolean(inPlace[i],(boolean) valArgs.get(i));
}
if (valArgs.get(i) instanceof Timestamp) {
statement.setTimestamp(inPlace[i],(Timestamp) valArgs.get(i));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/***
* // 对java类型和数据库Out字段进行匹配
* @param outPlace
* @param outValType
* @param statement
*/
public static void getOutPlace(int[] outPlace, List<Object> outValType, CallableStatement statement) {
// 对java类型和数据库Out字段进行匹配
for (int i = 0; i < outPlace.length; i++) {
try {
if (outValType.get(i).equals("12")) {
statement.registerOutParameter(outPlace[i], Types.VARCHAR);
}
if (outValType.get(i).equals("-5")) {
statement.registerOutParameter(outPlace[i], Types.BIGINT);
}
if (outValType.get(i).equals("8")) {
statement.registerOutParameter(outPlace[i], Types.DOUBLE);
}
if (outValType.get(i).equals("4")) {
statement.registerOutParameter(outPlace[i], Types.INTEGER);
}
if (outValType.get(i).equals("16")) {
statement.registerOutParameter(outPlace[i], Types.BOOLEAN);
}
if (outValType.get(i).equals("2")) {
statement.registerOutParameter(outPlace[i], Types.NUMERIC);
}
if (outValType.get(i).equals("1")) {
statement.registerOutParameter(outPlace[i], Types.CHAR);
}
if (outValType.get(i).equals("93")) {
statement.registerOutParameter(outPlace[i], Types.TIMESTAMP);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
这里out类型equals的数字是 数据类型在JDBC中索引
可以查看该地址内容
https://www.cnblogs.com/cn-chy-com/p/7672931.html
/***
* 对存储过程中的参数进行动态传入
*
* @param totalLen
* @param className
* @return
*/
public static String getProcedure(int totalLen, String className) {
String procedureNum = "";
for (int i = 0; i < totalLen; i++) {
procedureNum += "?,";
}
procedureNum = procedureNum.substring(0, procedureNum.length() - 1);
String procedure = " { call " + className + " ( " + procedureNum + " ) } ";
return procedure;
}
}
贴两张图