编写用Java JDBC 调用Oracle存储过程的接口，支持通过URL进行动态传参

使用说明

参数说明

参数	说明	示例
procName	必要参数，传入存储过程名	testProcedure
paramInputTag	可选参数，需要传参数到存储过程时，编写传值类型列表	int_string
paramInputStr	可选参数，需要传参数到存储过程时，编写传值参数值列表	1_haha
paramOutputTag	可选参数，存储过程有输出参数时，编写输出参数类型列表	string（若有多个输出参数，也是用_隔开)

注意事项

● paramInputTag和paramInputStr的值一一对应，参数类型对应参数值，顺序不可调换
● 传入参数、传出参数目前只支持4种类型
字符型：string
整形：int
浮点型：double
时间类型：date
● 时间类型 统一格式 yyyy-MM-dd
● 存储过程返回值未做处理，暂时存入result中，返回给浏览器

示例

http://IP:端口号/接口？procName=存储过程名&paramInputTag=类型1_类型二&paramInputStr=参数值1_参数值2&paramOutputTag=类型1_类型2

无传入、传出参数：
http://192.168.1.XX:XXXX/execStmt?procName=testProcedureNone

两个传入参数，无传出参数
http://192.168.1.XX:XXXXX/execStmt?procName=testProcedureInput&paramInputTag=int_string&paramInputStr=1_haha

无传入参数，一个传出参数
http://192.168.1.XX:XXXXX/execStmt?procName=testProcedureOutput&paramOutputTag=string

三个传入参数，三个传出参数
http://192.168.1.XX:XXXXX/execStmt?procName=testProcedureInputOutput&paramOutputTag=string_string_date&paramInputTag=int_string_date&paramInputStr=1_ha_2018-11-21

完整代码

创建一个Result entity存放调用结果

public class Result<T> {
	private String code;  // 返回代码 0-成功；1-失败；2-token失效
	private String message; // 返回消息
	private T data; // 返回内容
	
	
	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	public T getData() {
		return data;
	}

	public void setData(T data) {
		this.data = data;
	}
	
	@Override
	public String toString() {
		return "Result [code=" + code + ", data=" + data + ", message=" + message + "]";
	}

}

创建一个HttpRequest请求，来向指定 URL 发送POST方法的请求

public class HttpRequest {
    
    /**
     * 向指定 URL 发送POST方法的请求
     * 
     * @param url
     *            发送请求的 URL
     * @param param
     *            请求参数，请求参数应该是 name1=value1&name2=value2 的形式。
     * @return 所代表远程资源的响应结果
     */
    public static String sendPost(String url, String param) {
        PrintWriter out = null;
        BufferedReader in = null;
        String result = "";
        try {
            URL realUrl = new URL(url);
            
            // 打开和URL之间的连接
            URLConnection conn = realUrl.openConnection();
            // 设置通用的请求属性
            conn.setRequestProperty("accept", "*/*");
            conn.setRequestProperty("connection", "Keep-Alive");
            conn.setRequestProperty("user-agent",
                    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)");
            conn.setRequestProperty("Accept-Charset", "UTF-8");
            conn.setRequestProperty("contentType", "UTF-8");
            conn.setRequestProperty("Content-type", "application/x-www-form-urlencoded;charset=UTF-8");
            
            // 发送POST请求必须设置如下两行
            conn.setDoOutput(true);
            conn.setDoInput(true);
            // 获取URLConnection对象对应的输出流
            out = new PrintWriter(conn.getOutputStream());
            // 发送请求参数
            out.print(param);
            // flush输出流的缓冲
            out.flush();
            /*// 定义BufferedReader输入流来读取URL的响应
            in = new BufferedReader(
                    new InputStreamReader(conn.getInputStream()));
            String line;
            while ((line = in.readLine()) != null) {
                result += line;
            }*/
            result = readStrByCode(conn.getInputStream(), "UTF-8");
            return result;
        } catch (Exception e) {
            System.out.println("发送 POST 请求出现异常！"+e);
            e.printStackTrace();
        }
        //使用finally块来关闭输出流、输入流
        finally{
            try{
                if(out!=null){
                    out.close();
                }
                if(in!=null){
                    in.close();
                }
            }
            catch(IOException ex){
                ex.printStackTrace();
            }
        }
        return result;
    }   
    
    public static String readStrByCode(InputStream is, String code) {
        StringBuilder builder = new StringBuilder();
        BufferedReader reader = null;
        String line="";
        try
        {
          reader = new BufferedReader(new InputStreamReader(is, code));
          while ((line = reader.readLine()) != null)
          {

            builder.append(line);
          }
        } catch (Exception e) {
          e.printStackTrace();
          try
          {
            reader.close();
          }
          catch (IOException e1) {
            e1.printStackTrace();
          }
        }
        finally
        {
          try
          {
            reader.close();
          }
          catch (IOException e) {
            e.printStackTrace();
          }
        }
        return builder.toString();
      }
}

编写Controller

@RestController
public class handerController {
	@Autowired
	JdbcTemplate jdbcTemplate;
	@SuppressWarnings("finally")
	@RequestMapping(value = "/execStmt")
	@ResponseBody
	public Result execStmt(
			@RequestParam(value = "procName", required = true) String procName,
			@RequestParam(value = "paramInputTag", defaultValue = "") String paramInputTag,
			@RequestParam(value = "paramOutputTag", defaultValue = "") String paramOutputTag,
			@RequestParam(value = "paramInputStr", defaultValue = "") String paramInputStr)
			throws SQLException, UnsupportedEncodingException {
		System.out.println("<<<<<<------THE PROCEDURE BEGINS EXECUTION----->>>>>>");
		System.out.println("token ID:" + taskId);
		System.out.println("paramInputTag:" + paramInputTag);
		System.out.println("paramInputStr:" + paramInputStr);
		System.out.println("paramOutputTag:" + paramOutputTag);
		// 解析输入输出参数类型列表
		List<String> paramInputList = null;
		List<String> paramInputStrList = null;
		List<String> paramOutputList = null;
		int paramInputNum = 0;
		int paramOutputNum = 0;
		if (!paramInputTag.equals("") && !paramInputStr.equals("")) {
			paramInputList = Arrays.asList(paramInputTag.split("_"));
			paramInputStrList = Arrays.asList(paramInputStr.split("_"));
			paramInputNum = paramInputList.size();
		}
		if (!paramOutputTag.equals("")) {
			paramOutputList = Arrays.asList(paramOutputTag.split("_"));
			paramOutputNum = paramOutputList.size();
		}
		// 获取参数个数

		Result result = new Result();
		result.setCode("0");
		CallableStatement cst = null;
		Connection cn = null;
		String outResultStr="";
		String remark="success";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@//192.168.XXX.XX:1521/XXXX";
			cn = DriverManager.getConnection(url, "XXXX", "XXXX");
			String sql = "";
			if (paramInputTag.equals("") && paramOutputTag.equals("")) {
				sql = "{call " + procName + "()}";
				cst = cn.prepareCall(sql);
				System.out.println("------sql runs without param-----");
				cst.execute();

			} else {
				// 编写sql语句
				sql = "{call " + procName + "(";// 调用存储过程的语句，call后面的就是存储过程名和需要传入的参数

				for (int i = 0; i < paramInputNum + paramOutputNum; i++) {
					if (i == paramInputNum + paramOutputNum - 1) {
						sql += "?";
					} else {
						sql += "?,";
					}
				}
				sql += ")}";

				System.out.println(sql);

				cst = cn.prepareCall(sql);
				
				// 将输入参数传入sql中
				int index = 1;
				if (!paramInputTag.equals("")) {
					Map<String, String> paramMap = new LinkedHashMap<String, String>();
					
					for (int i = 0; i < paramInputList.size(); i++) {
						paramMap.put(paramInputList.get(i), paramInputStrList.get(i));
					}
					for (Map.Entry<String, String> entry : paramMap.entrySet()) {
						String paramType = entry.getKey();
						if (paramType.equals("string")) {
							cst.setString(index, entry.getValue().toString());
						}
						if (paramType.equals("int")) {
							cst.setInt(index, Integer.valueOf(entry.getValue()));
						}
						if (paramType.equals("double")) {
							cst.setDouble(index, Double.valueOf(entry.getValue()));
						}
						if (paramType.equals("date")) {
							cst.setDate(index, new java.sql.Date(
									(new SimpleDateFormat("yyyy-MM-dd")).parse(entry.getValue()).getTime()));
						}
						index++;
					}
				}
				// 将输出参数传入sql中
				int outIndex = index;
				if (!paramOutputTag.equals("")) {
					for (String paramOutput : paramOutputList) {
						if (paramOutput.equals("string")) {
							cst.registerOutParameter(index, Types.VARCHAR);
						}
						if (paramOutput.equals("double")) {
							cst.registerOutParameter(index, Types.DOUBLE);
						}
						if (paramOutput.equals("int")) {
							cst.registerOutParameter(index, Types.INTEGER);
						}
						if (paramOutput.equals("date")) {
							cst.registerOutParameter(index, Types.DATE);
						}
						index++;

					}
				}
			
				System.out.println("------sql execute with param-----");
				cst.execute();

				if (!paramOutputTag.equals("")) {
					List<String> outResult = new ArrayList<String>();
				
					StringBuilder sb = new StringBuilder();
					for (String paramOutput : paramOutputList) {
						if (paramOutput.equals("string")) {
							outResult.add(cst.getString(outIndex));
						}
						if (paramOutput.equals("double")) {
							outResult.add(String.valueOf(cst.getDouble(outIndex)));
						}
						if (paramOutput.equals("int")) {
							outResult.add(String.valueOf(cst.getInt(outIndex)));
						}
						if (paramOutput.equals("date")) {
							outResult.add(String.valueOf(cst.getDate(outIndex)));
						}
						outIndex++;

					}
					for(int i=0;i<outResult.size();i++){

							if (i < outResult.size() - 1) {
								sb.append(outResult.get(i) + ",");
							} else {
								sb.append(outResult.get(i));
							}

					}
					
					
					
					outResultStr = sb.toString();
					
				}
				
				
				

			}
			
			if(outResultStr.equals("")){
				outResultStr="success";
				System.out.println(outResultStr);
			}
			result.setCode("1");
			result.setMessage(outResultStr);
			remark = URLEncoder.encode(outResultStr, "utf-8");
			System.out.println(procName+" 任务执行成功");
		} catch (IllegalArgumentException e) {
			result.setCode("0");
			result.setMessage("参数类型错误");
			remark = URLEncoder.encode("任务执行失败,错误为：" + e.toString(), "utf-8");
			System.out.println(procName+"任务执行失败,错误为：" + e.toString());
		} catch (ClassCastException e) {
			result.setCode("0");
			result.setMessage("类型转换错误");

			remark = URLEncoder.encode("任务执行失败,错误为：" + e.toString(), "utf-8");
			System.out.println(procName+"任务执行失败,错误为：" + e.toString());
		} catch (Exception e) {
			result.setCode("0");
			result.setMessage("执行存储过程时出现错误:" + e.getMessage());
			remark = URLEncoder.encode("任务执行失败,错误为：" + e.toString(), "utf-8");
			System.out.println(procName+"任务执行失败,错误为：" + e.toString());
		} finally {
			cst.close();
			cn.close();
			//测试
			//HttpRequest.sendPost("http://192.168.X.XXX:XXX/XXXXX","resultcode=0&remark=" + remark + "&updatename=system");
			
			System.out.println("<<<<<<------THE PROCEDURE FINISHED----->>>>>>");
			return result;
		}
	}}