oracle存储过程运行简单java代码访问web接口的参数传递

上一篇文章介绍了通过触发器+存储过程运行java source实现访问web接口，但是是以get形式访问的，本文通过post方式访问web接口并传递参数。

1、普通 java post方式

开始是在get基础上按照普通接口形式设置访问属性，但是一直无法创建 java source，提示【ORA-29536: 资源格式错误: oracle.aurora.sqljdecl.TokenMgrError: Lexical error at line 23, column 1.  Encountered: "\\u00a0" (160), after : ""
ORA-06512: 在 line 1】。

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;

public class PostData 
{
	public static void PostAlarm(String guid, String name, String value)
	{
		try {
			
			//String params= "{\"guid\":\""+guid+",\"name\":"+name+",\"value\":"+value+""\"}"; 
			//String params= "{\"id\":\"12345\"}";  
			String params= "";
			URL url = new URL("http://127.0.0.1:8902/api/service/alarmnotify/");
			HttpURLConnection connection = (HttpURLConnection)url.openConnection();
			connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
			connection.setRequestMethod("POST"); // 设置请求方式
			connection.setDoOutput(true);
			int responseCode= connection.getResponseCode();
			
            OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(), "UTF-8"); // utf-8编码  
            out.append(params);  
            out.flush();  
            out.close();  
			} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}  
	}
}

 

2、新代码

由于本人不擅长java，oracle也无法调试，又没时间深究问题原因，因此直接摘录了另外一位仁兄的代码，稍作修改便完成了编译。此方式是通过raw格式的数据，其参数格式是【param1=value1&param2=value2】。

create or replace and compile java source named httpinvoker as
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.net.URL;
import java.net.HttpURLConnection;
import java.net.URLConnection;
import java.util.*;
import java.net.URLEncoder;
 
public class PostData
{
    
     public static String postalarm(String guid, String name,String value) throws Exception{
         //处理参数
        
        Map<String,String> p=new HashMap<String,String>();
        //全体发送pushtype为ALL，不写target
        p.put("guid",guid);
        p.put("name",name);
        p.put("value",value);
       
        String s="";
        for (Map.Entry<String, String> entry : p.entrySet()) {
            s+="&"+entry.getKey()+"="+entry.getValue();
        }
        s=s.substring(1);
        //请求地址
        String url="http://127.0.0.1:8902/api/service/alarmnotify/";
        //请求参数
        String param=s;       
         //发送post请求代码开始
         PrintWriter out = null;
        BufferedReader in = null;
        String result = "";
        try {
            URL realUrl = new URL(url);
            // 打开和URL之间的连接
            URLConnection conn = realUrl.openConnection();
            // 设置通用的请求属性
            conn.setRequestProperty("accept", "application/json, text/javascript, */*; q=0.01");
            conn.setRequestProperty("Accept-Encoding", "gzip, deflate");
            conn.setRequestProperty("Connection", "keep-alive");
            conn.setRequestProperty("Accept-Language", "zh-CN,zh;q=0.8");
            conn.setRequestProperty("Content-Length", "80");
            conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded; charset=UTF-8");
            conn.setRequestProperty("User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36");
                
            conn.setRequestProperty("user-agent",
                    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)");
            // 发送POST请求必须设置如下两行
            conn.setDoOutput(true);
            conn.setDoInput(true);
            // 获取URLConnection对象对应的输出流
            OutputStreamWriter outWriter = new OutputStreamWriter(conn.getOutputStream(), "utf-8");  
            out = new PrintWriter(outWriter);
            // 发送请求参数
            out.print(param);
            // flush输出流的缓冲
            out.flush();
            // 定义BufferedReader输入流来读取URL的响应
            in = new BufferedReader(
                    new InputStreamReader(conn.getInputStream(),"UTF-8"));
            String line;
            while ((line = in.readLine()) != null) {
                result += line;
            }
        } catch (Exception e) {
            System.out.println("发送 POST 请求出现异常！"+e);
            e.printStackTrace();
            result=e.getMessage();
        }
        //使用finally块来关闭输出流、输入流
        finally{
            try{
                if(out!=null){
                    out.close();
                }
                if(in!=null){
                    in.close();
                }
            }
            catch(IOException ex){
                ex.printStackTrace();
            }
        }
        return result;
    }
    
}

3、创建存储过程

经过调试，成功访问到了web接口且成功取到了参数。

create or replace procedure PostAlarm ( guid VARCHAR2,
name VARCHAR2, value1 VARCHAR2) AS LANGUAGE JAVA
NAME 'PostData.postalarm(java.lang.String,java.lang.String, java.lang.String)';

参考链接：

1、oracle触发器+存储过程发送http请求2

2、Oracle存储过程如何调用java程序并发送Http请求