[大数据]oracle数据库调用外部https接口

1.在数据库执行下面脚本创建java类

create or replace and compile java source named cux_http_utl as
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import java.io.Writer;
import java.sql.SQLException;
import java.net.URL;

import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSession;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;

import oracle.sql.CLOB;


public class CUX_HTTP_UTL {
    static class TrustAllTrustManager implements TrustManager, X509TrustManager {

        @Override
        public void checkClientTrusted(X509Certificate[] x509Certificate, String string) throws CertificateException {
            return;
        }

        @Override
        public void checkServerTrusted(X509Certificate[] x509Certificate, String string) throws CertificateException {
            return;
        }

        @Override
        public X509Certificate[] getAcceptedIssuers() {
            return null;
        }
    }
    static TrustManager[] tm = { new TrustAllTrustManager() };
   private static OracleConnection getConnection() throws SQLException {
        return (OracleConnection) new OracleDriver().defaultConnection();
    }
     private static CLOB stringToClob(String input) throws Exception {
        OracleConnection conn = null;
        Writer writer = null;
        CLOB clob = null;
        try {
            conn = getConnection();
            clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
            writer = clob.setCharacterStream(0L);
            writer.write(input);
            return clob;
        } catch (Exception e) {
            throw e;
        } finally {
            if (writer != null) {
                try {
                    writer.close();
                } catch (Exception e) {

                }
            }

            if (clob != null) {
                try {
                    clob.close();
                } catch (Exception e) {

                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {

                }
            }
        }
    }
    
     private static String clobToString(CLOB input) throws Exception {
        BufferedReader bufferedReader = null;
        StringBuffer buf = new StringBuffer("");
        String line = "";
        try {
            bufferedReader = new BufferedReader(input.getCharacterStream());
            while ((line = bufferedReader.readLine()) != null) {
                buf.append(line);
            }
            return buf.toString();
        } catch (Exception e) {
            throw e;
        } finally {
            if (bufferedReader != null) {
                try {
                    bufferedReader.close();
                } catch (Exception e) {
                }
            }
        }
    }
    public static CLOB httpsRequest(String requestUrl, String requestMethod, String contentType,
                                    CLOB inputClob) throws Exception {
        String str = "";
        //  try {
        String inputStr = "";
        if (inputClob != null) 
          inputStr = clobToString(inputClob);
        StringBuffer buffer = null;
        InputStream is = null;
        InputStreamReader isr = null;
        BufferedReader br = null;

        HostnameVerifier hv = new HostnameVerifier() {
            @Override
            public boolean verify(String urlHostName, SSLSession session) {
                return true;
            }
        };

        SSLContext sslContext = SSLContext.getInstance("SSL");
        sslContext.init(null, tm, null);
        HttpsURLConnection.setDefaultSSLSocketFactory(sslContext.getSocketFactory());

        HttpsURLConnection.setDefaultHostnameVerifier(hv);
        URL url = new URL(requestUrl);
        HttpsURLConnection conn = (HttpsURLConnection)url.openConnection();
        conn.setDoOutput(true);
        conn.setDoInput(true);
        conn.setUseCaches(false);
        conn.setRequestMethod(requestMethod);
        if (contentType != null) {
            conn.setRequestProperty("Content-Type", contentType);
        }
        conn.connect();
        if (null != inputStr) {
            OutputStream os = conn.getOutputStream();
            os.write(inputStr.getBytes("utf-8"));
            os.close();
        }
        if (conn.getResponseCode() != 200) {
            throw new RuntimeException("HTTP GET Request Failed with Error code : " + conn.getResponseCode());
        }
        is = conn.getInputStream();
        isr = new InputStreamReader(is, "utf-8");
        br = new BufferedReader(isr);
        buffer = new StringBuffer();
        String line = null;
        while ((line = br.readLine()) != null) {
            buffer.append(line);
        }
        br.close();
        conn.disconnect();
        if (buffer != null & !"".equals(buffer.toString()))
            str = buffer.toString();
        else
            str = "1";
        CLOB clob; 
        clob =stringToClob( str);
        return clob;
        /*  } catch (Exception e) {
      e.printStackTrace();
      return "ERROR:" + e.getMessage();
    } */
    }
 
}

2.在数据库创建函数

create or replace function cux_https_request(requestUrl    VARCHAR2,
                                             requestMethod VARCHAR2,
                                             contentType   VARCHAR2,
                                             inputClob     Clob)
  return Clob as
  language java name 'CUX_HTTP_UTL.httpsRequest(java.lang.String,java.lang.String,java.lang.String,oracle.sql.CLOB ) return oracle.sql.CLOB';

3.调用函数即可

```sql
 select Cux_https_request(requesturl    => 'https://opencs.517la.com/api/token/getToken',
                            requestmethod => 'POST',
                            contenttype   => 'application/json',
                            inputclob     => '{
    "data": {
      "appid": "string",
      "appsecret": "string"
    },
    "requestid": "20180616112955089",
    "version": "0.0.1"
  }')
    from dual;