DBTools Service sample for REST-SQL

DBToolsServiceRestSQLExamples.java

package io.krisice;

import com.oracle.bmc.http.signing.RequestSigningFilter;

import javax.ws.rs.client.*;
import javax.ws.rs.core.MediaType;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;

public class DBToolsServiceRestSQLExamples {
    public static void main(String[] args) throws IOException {
        System.setProperty("sun.net.http.allowRestrictedHeaders", "true");
        var configurationFilePath = "~/.oci/config";
        var profile = "SICDBAAS";

        var ocid = "ocid1.databasetoolsconnection.oc1.phx.amaaaaaacsc5xjaatnzc7o3fmtmk76ecmkhhraamkobhqvi3yr4hx5sywt4q";
        var dns = "sql.dbtools.us-phoenix-1.oci.oraclecloud.com";

        RequestSigningFilter requestSigningFilter =  RequestSigningFilter.fromConfigFile(configurationFilePath, profile);
        Client client = ClientBuilder.newBuilder().build().register(requestSigningFilter);

        // exec plain sql
        executeSQL(dns,  ocid,client);

        // exec sql using rest-sql json w/ binds
        executeSQLWithBinds(dns, ocid, client);

        // exec sql and export to CSV
        executeSQLExport(dns, ocid,client);

        // CUSTOM Format exec sql and export to CSV w/ binds
        executeSQLExportWithBindsCustomFormat(dns,ocid,client);

        // built in formats
        executeSQLExportWithBindsFormatted(dns,ocid,client,"json");
        executeSQLExportWithBindsFormatted(dns,ocid,client,"text");
        executeSQLExportWithBindsFormatted(dns,ocid,client,"csv");
        executeSQLExportWithBindsFormatted(dns,ocid,client,"xml");

    }


    private static void executeSQLWithBinds(String dns, String ocid, Client client) {
        var sqlpath = "/20201005/ords/" + ocid + "/_/sql";
        var url = "https://"+dns + sqlpath;
        // send json sql script
        WebTarget target = client.target("https://"+ dns).path(sqlpath);

        var jsonSQL = """
         {"offset":0,"limit":100,"statementText":"SET SERVEROUTPUT ON
                                             set timing ON;
                                             set define &;
                                             set escape OFF;
                                             select :NAME from dual",
                          "binds":[{"name":"NAME","data_type":"string","value":"Todd"}]}""";
        Entity<String> jsonSQLPayload = Entity.entity(jsonSQL,MediaType.APPLICATION_JSON_TYPE);
        String jsonResults= target.request().post(jsonSQLPayload,String.class);

        System.out.println(jsonResults);
        System.out.println("\n\n");

    }

    private static void executeSQL(String dns,  String ocid,Client client) {
        var sqlpath = "/20201005/ords/" + ocid + "/_/sql";
        var url = "https://"+dns + sqlpath;
        var sql ="select * from dual";
        WebTarget target = client.target("https://"+ dns).path(sqlpath);
        // send raw sql script
        Entity<String> sqlPayload = Entity.entity(sql,"application/sql");
        String results= target.request().post(sqlPayload,String.class);
        System.out.println(results);
        System.out.println("\n\n");
    }

    private static void executeSQLExport(String dns, String ocid, Client client) throws IOException {
        /// export to CSV

        var sqlpath = "/20201005/ords/" + ocid + "/_/sql/export";
        var url = "https://"+dns + sqlpath;

        var sql = """
                data={"statementText":"select * from all_objects where rownum<100","formatDetails":{"format":"csv","lineTerminator":"\\n","leftEnclosure":"\\"","rightEnclosure":"\\"","header":true}}
                """;

        WebTarget target = client.target("https://"+ dns).path(sqlpath);
        // send raw sql script
        Entity<String> sqlPayload = Entity.entity(sql,MediaType.APPLICATION_FORM_URLENCODED_TYPE);
        printResults(target, sqlPayload);

    }

    private static void executeSQLExportWithBindsCustomFormat(String dns, String ocid, Client client) throws IOException {
        /// export to CSV

        var sqlpath = "/20201005/ords/" + ocid + "/_/sql/export";
        var url = "https://"+dns + sqlpath;

        var sql = """
                data={"statementText":"select * from (select * from all_objects where rownum < :rownum)","formatDetails":{"delimiter":"|","format":"csv","header":true,"isInsertCommit":true,"leftEnclosure":"\\t","lineTerminator":",\\n","rightEnclosure":"\\t","rowInterval":100,"tableName":"EXPORT_TABLE"},"binds":[{"data_type":"NUMBER","value":"10","name":"rownum"}]}                                                                                                                                                                                                                                                                           
                """;

        WebTarget target = client.target("https://"+ dns).path(sqlpath);
        // send raw sql script
        Entity<String> sqlPayload = Entity.entity(sql,MediaType.APPLICATION_FORM_URLENCODED_TYPE);
        printResults(target, sqlPayload);

    }
    private static void executeSQLExportWithBindsFormatted(String dns, String ocid, Client client,String format) throws IOException {
        /// export to <format>> xml,json,cxv,text

        var sqlpath = "/20201005/ords/" + ocid + "/_/sql/export";
        var url = "https://"+dns + sqlpath;

        var sql = """
                data={"statementText":"select * from (select * from all_objects where rownum < :rownum)","formatDetails":{"format":" """ + format + """
                ","header":true,"rowInterval":100,"tableName":"EXPORT_TABLE"},"binds":[{"data_type":"NUMBER","value":"10","name":"rownum"}]}                                                                                                                                                                                                                                                                           
                """;

        WebTarget target = client.target("https://"+ dns).path(sqlpath);
        // send raw sql script
        Entity<String> sqlPayload = Entity.entity(sql,MediaType.APPLICATION_FORM_URLENCODED_TYPE);
        printResults(target, sqlPayload);
    }

    // print it all out
    private static void printResults(WebTarget target, Entity<String> sqlPayload) throws IOException {
        var results = target.request().post(sqlPayload, Reader.class);
        char[] c = new char[10240];
        while(results.ready()){
            int l = results.read(c);
            System.out.println(new String(c,0,l));
        }

        System.out.println("\n\n");
    }

}