Spring: Blocking vs non-blocking: R2DBC vs JDBC and WebFlux vs Web MVC

Spring Framework version 5, released in Sept 2017, introduced Spring WebFlux. A fully reactive stack. In Dec 2019 Spring Data R2DBC, a reactive relational database driver was released. In this blog post I'll show that at high concurrency, WebFlux and R2DBC perform better. They have better response times and higher throughput. As additional benefits, they use less memory and CPU per request processed and when leaving out JPA in case of R2DBC, your fat JAR becomes a lot smaller. At high concurrency using WebFlux and R2DBC is a good idea!

Calling an Oracle DB stored procedure from Spring Boot using Apache Camel

There are different ways to create data services. The choice for a specific technology to use, depends on several factors inside the organisation which wishes to realize these services. In this blog post I'll provide a minimal sample on how you can use Spring Boot with Apache Camel to call an Oracle database procedure which returns the result of an SQL query as an XML. You can browse the code here.

A transparent Spring Boot REST service to expose Oracle Database logic

Sometimes you have an Oracle database which contains a lot of logic and you want to expose specific logic as REST services. There are a variety of ways to do this. The most obvious one to consider might be Oracle REST Data Services. It is quite powerful and supports multiple authentication mechanisms like OAuth. Another option might be using the database embedded PL/SQL gateway This gateway however is deprecated for APEX and difficult to tune (believe me, I know).

Sometimes there are specific requirements which make the above solutions not viable. For example if you have complex custom authentication logic implemented elsewhere which might be difficult to translate to ORDS or the embedded PL/SQL gateway. ORDS also runs in stand-alone in a Docker container but this is not so easy for the PL/SQL gateway. Also if you are looking for a product or framework which can be used for multiple flavors of database, these solutions might be too Oracle specific.

You can consider creating your own custom service in for example Java. The problem here however is that it is often tightly coupled with the implementation. If for example parameters of a database procedure are mapped to Java objects or a translation from a view to JSON takes place in the service, there is often a tight coupling between the database code and the service.

In this blog post I'll provide a solution for a transparent Spring Boot REST service which forwards everything it receives to the database for further processing without this tight coupling, only to to a generic database procedure to handle all REST requests. The general flow of the solution is as follows:

• The service receives an HTTP request from a client
• Service translates the HTTP request to an Oracle database REST_REQUEST_TYPE object type
• Service calls the Oracle database over JDBC with this Object
• The database processes the REST_REQUEST_TYPE and creates a REST_RESPONSE_TYPE Object
• The database returns the REST_RESPONSE_TYPE Object to the service
• The service translates the REST_RESPONSE_TYPE Object to an HTTP response
• The HTTP response is returned to the client

JDBC from the Oracle Service Bus

There are different ways to do database calls from the Oracle Service Bus. In this blog post I look at several methods.

The methods looked at;
1 using an external webservice (without OSB)
2 using an external webservice proxied by the OSB

3 using the fn-bea:execute-sql function from the OSB

4 using the JCA DbAdapter from the OSB provided by Oracle as part of Oracle SOA Suite

First steps into the Oracle Database Cloud

Oracle provides a Database Cloud Service. In a previous post I've looked at the Oracle Java Cloud Service (http://javaoraclesoa.blogspot.nl/2013/12/first-steps-into-oracle-java-cloud.html). The database is of course also an important component used in most applications. In this blog post I'll describe my first experiences with the Oracle Database Cloud service. I've used two methods to connect to the Oracle Database Cloud service. The first one from SQLDeveloper. Next I created a webservice, deployed it to the Oracle Java Cloud service and fetched data from the Oracle Database Cloud service with it.

There are of course other methods to interact with the Oracle Database Cloud service. It is for example possible using SQLWorkshop from the Apex interface to expose RESTful services to access the database. See for example http://multikoop.blogspot.nl/2012/11/oracle-java-and-database-cloud-services.html on how to create these services. By default, calls to the Oracle Cloud are encrypted. See https://weblogs.java.net/blog/bleonard/archive/2013/05/02/calling-oracle-cloud-service-java on how to call services.

WLST; obtaining parameters, recovering JDBC database user passwords and testing database connections

WLST (Weblogic Scripting Tool) is very powerful. Most of the things (and more) which can be done with the Weblogic console can also be done by means of WLST scripting.

I've already written a post describing two options for datasource monitoring; http://javaoraclesoa.blogspot.nl/2012/09/monitoring-datasources-on-weblogic.html. The methods described in that post have some drawbacks;
- by using a servlet, you are exposing server datasource status and you are using a custom developed servlet to achieve functionality. the servlet does not show connection errors, just OK or NOK. Also it does not take into account different managed servers and datasource targets.
- by using WLST as in the example in the post, you're not actually testing creating a connection but are just monitoring current status

What the script should do

At a customer I noticed database availability was an issue. This often caused data sources to go to 'Suspended' state. Also when the database was available again, we often encountered connection errors like 'ORA-12514: TNS:listener does not currently know of service requested in connect', 'ORA-011033: ORACLE initialization or shutdown in progress' and 'ORA-01017: invalid username/password; logon denied'. The customer used a multitude of datasources. We wanted a quick way to resume every one of them and determine connection exceptions in order to inform the DBA to fix it. Since the script would run on several machines, it should be able to determine the IP to connect to and the required paths (to for example SerializedSystemIni.dat) on it's own.

In the below image, the 'call chain' is illustrated. This post focusses on the WLST script. In a second post (http://javaoraclesoa.blogspot.nl/2013/09/inform-dba-if-weblogic-cant-connect-to.html) I'll describe how we automated the process of calling the script over SSH by using an Ant plugin in Maven on several environments so we could schedule this to run every morning in Jenkins and automatically mail the DBA's to go and fix their DB's.

How the script is implemented

Obtaining local information
Since the script needed to be as environment neutral as possible, I obtained several pieces of information from the machine the script runs on.

Obtaining the physical interface IP address
I obtained the IP address of the local machine by using the ip command. See; http://stackoverflow.com/questions/6243276/how-to-get-the-physical-interface-ip-address-from-an-interface

intf = 'eth0'
intf_ip = commands.getoutput("/sbin/ip address show dev " + intf).split()
intf_ip = intf_ip[intf_ip.index('inet') + 1].split('/')[0]
print 'Using IP: ',intf_ip

Obtaining the path to SerializedSystemIni.dat
I needed to obtain the path to SerializedSystemIni.dat for decrypting passwords (see later in this post). I obtained the path by (after connecting);

rootdir=cmo.getRootDirectory()
secdir=rootdir+'/security'

secdir is the directory where the SerializedSystemIni.dat file is usually located.

Obtaining parameters
I wanted my script to be flexible. I wanted to have the option to use a properties file (useful for the development environment), to use command-line arguments (useful when calling the script from Maven) and I wanted the script to ask for login details otherwise (useful for a DBA executing the script). This required 3 methods of obtaining parameters. The logic used was as followed;

- do we have a file? if so, use it else continue
- do we have command line arguments. if so, use them. if not, ask for them

Command line arguments
In Weblogic 10.3.6, Python/Jython 2.2.1 is used (https://forums.oracle.com/thread/2210448). This limits the use of several of the more recent Python libraries to make working with parameters more easy (such as optparse and argparse). We can however use getopt; http://davidmichaelkarr.blogspot.nl/2008/10/make-wlst-scripts-more-flexible-with.html

The above described library also covered asking for the parameters if they were not supplied. If this failed for whatever reason, raw_input can be used.

Properties file
Using a properties file was relatively easy. See for example; http://java-brew.blogspot.nl/2011/01/reading-properties-file-in-wlst-jython.html

Because we are using Jython with WLST, we can use the Properties class from java.util. No need to reinvent the wheel here.

Obtaining login details and testing connections
When a datasource is suspended and the connection pool is tested, you can the following exception;
Connection test failed with the following exception: weblogic.common.resourcepool.ResourceDisabledException: Pool testuserNonXa is Suspended, cannot allocate resources to applications.

When however a connection cannot be made, you can resume the datasource and test it and nothing will appear to be wrong. In the log file however you might see one of the previously mentioned exceptions; you won't be able to use the datasource to get to the database. So testing just the datasource from the Weblogic console is not enough to confirm it is working.

To determine if a connection could be made, I wanted to create a new connection to the database while not using the datasource but with the same connection details. For this I needed to obtain the connection information the datasource was using. Then I encountered the following challenge; the cleartext passwords could not be read due to Weblogic server policies; "Access to sensitive attribute in clear text is not allowed due to the setting of ClearTextCredentialAccessEnabled attribute in SecurityConfigurationMBean". Also see; http://serverfault.com/questions/386724/clear-text-credential-access-enabled-field

I did not want to change this policy as it would introduce a security vulnerability. I had to decode the passwords. Based on several blog posts such as; http://connectionserver.blogspot.nl/2009/06/recovering-weblogic-passwords.html I could recover the database user passwords (which is of course very useful...). The following which I found online is also interesting; http://recover-weblogic-password.appspot.com/. As you can see in the code below, you can uncomment a line to display the passwords in plain text on the console.

First I specified the path where the SerializedSystemIni.dat file could be found. Then I used that to decrypt the encrypted passwords I obtained from the MBeans. Then I used zxJDBC to connect to the database using the obtained credentials; http://www.informit.com/articles/article.aspx?p=26143

The script

Mind the indentation! It's Jython. You should execute this with the wlst.sh script in your Weblogic server installation.

import commands
import os
import weblogic.security.internal.SerializedSystemIni
import weblogic.security.internal.encryption.ClearOrEncryptedService
import traceback
import sys
import getopt

from com.ziclix.python.sql import zxJDBC
from java.io import FileInputStream

intf = 'eth0'
intf_ip = commands.getoutput("/sbin/ip address show dev " + intf).split()
intf_ip = intf_ip[intf_ip.index('inet') + 1].split('/')[0]
print 'Using IP: ',intf_ip
var_user=''
var_pass=''

try:
        fh = open("resume.properties", "r")
        fh.close()
        print 'Using resume.properties'
        propInputStream = FileInputStream("resume.properties")
        configProps = Properties()
        configProps.load(propInputStream)
        var_user=configProps.get("userName")
        var_pass=configProps.get("passWord")
except IOError:
        try:
                opts, args = getopt.getopt(sys.argv[1:], "", ["username=", "password="])
                for o, a in opts:
                        if o == "--username":
                                var_user=a
                                print 'User: ',var_user
                        elif o == "--password":
                                var_pass=a
                                print 'Pass: ',var_pass
                        else:
                                assert False, "unhandled option"
        except getopt.GetoptError, err:
                print 'No -u and -p commandline arguments and no resume.properties...'
                var_user = raw_input("Enter user: ")
                var_pass = raw_input("Enter pass: ")

connect(var_user,var_pass,intf_ip+':7001')
rootdir=cmo.getRootDirectory()
secdir=rootdir+'/security'
allServers=domainRuntimeService.getServerRuntimes();
if (len(allServers) > 0):
  for tempServer in allServers:
    print 'Processing: ',tempServer.getName()
    jdbcServiceRT = tempServer.getJDBCServiceRuntime();
    dataSources = jdbcServiceRT.getJDBCDataSourceRuntimeMBeans();
    if (len(dataSources) > 0):
                for dataSource in dataSources:
                        #print 'Resuming: ',dataSource.getName()
                        dataSource.resume()
                        dataSource.testPool()
                        cd('/JDBCSystemResources/' + dataSource.getName() + '/JDBCResource/' + dataSource.getName() + '/JDBCDriverParams/' + dataSource.getName() + '/Properties/' + dataSource.getName())
                        dbuser=cmo.lookupProperty('user').getValue()
                        #print 'User: ',dbuser
                        cd('/JDBCSystemResources/' + dataSource.getName() + '/JDBCResource/' + dataSource.getName() + '/JDBCDriverParams/' + dataSource.getName())
                        dburl=cmo.getUrl()
                        #print 'DbUrl: ',dburl
                        dbpassword=cmo.getPasswordEncrypted()
                        es=weblogic.security.internal.SerializedSystemIni.getEncryptionService(secdir)
                        ces=weblogic.security.internal.encryption.ClearOrEncryptedService(es)
                        dbpassword_decrypted=str(ces.decrypt("".join(map(chr, dbpassword))))
                        #print 'DbPassword: ',dbpassword_decrypted
                        dbdriver=cmo.getDriverName()
                        #print 'DbDriverName: ',dbdriver
                        try:
                                con=zxJDBC.connect(dburl,dbuser,dbpassword_decrypted,dbdriver)
                                cursor=con.cursor()
                                result=cursor.execute('select sysdate from dual')
                        except:
                                print 'ERROR: Url: ',dburl,' User: ',dbuser
                                traceback.print_exc()

Monitoring DataSources on Weblogic

As an Oracle SOA developer, I've often heard the phrase; 'BPEL doesn't work!'. Almost always the cause can be found in backend systems which do not function as expected. This error becomes visible when executing a service which uses a specific resource. When people start complaining about BPEL, usually this is an indication you should work on process feedback and error handling so the responsible party can quickly be identified. A trial and error mechanism is however often not what you want. A dashboard or script to monitor backend databases can help prevent such issues.

Often development and system test databases are not monitored as thoroughly as acceptance test or production environments. To be able to quickly identify for example a database which is malfunctioning (for example put down for maintenance without informing the developers) it is useful to have some tools and scripts available which you can run for the occasion. Usually this is quicker then using the Enterprise Manager. This is especially useful in complex environments where multiple systems are linked. In these scripts/tools, it is not a good idea to have the databases/users/passwords hardcoded, because that would require maintenance of the scripts in case of changes and as a lazy developer you of course don't want that.

In this article I will describe two possible options for monitoring DataSources on Weblogic servers.

- The first option is a servlet which uses JNDI to obtain JDBC DataSources. This has the drawback that if the DataSource is not loaded correctly or has been disabled, it cannot be looked up using JNDI and is not visible. It can however also be used when the Db/Aq adapter is not used. A servlet can be accessed by anyone, reducing the amount of technical knowledge required to monitor the databases.

- The second option is by using WLST to obtain DataSources defined in the DbAdapter/AqAdapter and provide statistics. This is specific to the Db/Aq adapter and it's a WLST script, so a Middleware installation and login credentials to the server are required in order to execute it.

Both methods query for available DataSources. The DataSource is used so no usernames/passwords/hostnames/sids etc are required.

Implementation

Java

The below servlet does a JNDI lookup of JDBC DataSources and does a 'select sysdate from dual' on them. If the DataSource is not available (can not be looked up via JNDI), it will not appear in the list. If for example a tablespace is full or an account is locked, you will however see it in the list as NOK (short for Not OK). It has not been extensively tested in error situations!

Output of the servlet can be for example;

 

When I lock the testuseraccount and reset the connectionpool;

Below is the servlet code. It can of course easily be improved (some people like colors and nice layouts while I tend to focus on functionality).

package ms.testapp;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;

import java.sql.Connection;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Hashtable;

import javax.naming.Binding;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

public class CheckDb extends HttpServlet {

    @SuppressWarnings("compatibility:-5693855291723951046")
    private static final long serialVersionUID = 1L;
    public CheckDb() {
        super();
    }
    public void doGet(HttpServletRequest request,
        HttpServletResponse response) throws ServletException,
            IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " +
                   "Transitional//EN\">\n" +
                "<HTML>\n" +
                "<HEAD><TITLE>Datasource status</TITLE></HEAD>\n" +
                "<BODY>\n" +
                listJDBCContextTable
                () + "</BODY></HTML>");
    }
    private Context getContext() throws NamingException {
        Hashtable myCtx = new Hashtable();
        myCtx.put(Context.INITIAL_CONTEXT_FACTORY,
                 "weblogic.jndi.WLInitialContextFactory");
        Context ctx = new InitialContext(myCtx);
        return ctx;
    }
    private String checkDataSource(DataSource ds) {
        try {
            Connection conn = ds.getConnection();
            Statement st = conn.createStatement();
            st.execute("select sysdate mydate from dual");
            st.getResultSet().next();
            Date mydate = st.getResultSet().getDate("mydate");
            conn.close();
            String date = mydate.toString();
            if (date.length() == 10 && date.indexOf("-") == 4 && date.
                lastIndexOf("-") == 7) {
                return "OK";
            } else {
                return "NOK";
            }
        } catch (Exception e) {
            return "NOK"; //getStackTrace(e);
        }
    }
    private static String getStackTrace(Throwable e) {
        StringWriter sw = new StringWriter();
        PrintWriter pw = new PrintWriter(sw);
        e.printStackTrace(pw);
        return sw.toString();
    }
    private String listJDBCContextTable() {
        String output = "<table>";
        ArrayList<String> tab = new ArrayList<String>();
        String line = "";
        try {

           tab = listContext((Context)getContext().lookup("jdbc"), "", tab);
            Collections.sort(tab);

           for (int i = 0; i < tab.size(); i++) {
                output += tab.get(i);
            }
            output += "</table>";
            return output;
        } catch (NamingException e) {
            return getStackTrace(e);
        }
    }
    private ArrayList<String> listContext(Context ctx, String indent,
        ArrayList<String> output) throws NamingException {
        String name = "";
        try {
            NamingEnumeration list = ctx.listBindings("");
            while (list.hasMore()) {
                Binding item = (Binding)list.next();
                String className = item.getClassName();
                name = item.getName();
                if (!(item.getObject() instanceof DataSource)) {
                    //output = output+indent + className + " " + name+"\n";
                } else {
                    output.add("<tr><td>" + name + "</td><td>" +
                              checkDataSource((DataSource)item.getObject()) +
                              "</td></tr>");
                }
                Object o = item.getObject();
                if (o instanceof javax.naming.Context) {
                    listContext((Context)o, indent + " ", output);
                }
            }
        } catch (NamingException ex) {
            output.add("<tr><td>" + name + "</td><td>" + getStackTrace(ex) +
                      "</td></tr>");
        }
        return output;
    }
}

You can download the JDev 11.1.1.6 project here; https://dl.dropbox.com/u/6693935/blog/DbUtils.zip 

Also I found that not all DataSources allow remote JDBC calls such as the MDS DataSource. When using a servlet, this is not a problem since the Java code runs on the server. When running a piece of Java code locally (from your laptop for example) however, it will not work and will throw; java.lang.UnsupportedOperationException: Remote JDBC disabled.

WLST

The below script is based on http://albinoraclesoa.blogspot.nl/2012/06/monitoring-jca-adapters-through-wlst.html and http://davidmichaelkarr.blogspot.nl/2008/10/make-wlst-scripts-more-flexible-with.html and was created by Marcel Bellinga. It contains an example on how to pass arguments to a WLST script and how to query / test DataSources from a WLST script. The DataSources for which statistics are printed, are determined by querying the DbAdapter and AqAdapter connectionpools.

import sys
import os
from java.lang import System

import getopt

user        = ''
credential  = ''
host        = ''
port        = ''
targetServerName  = ''

def usage():
    print "Usage:"
    print "ResourceAdapterMonitor -u user -c credential -h host -p port -s serverName"
   

def monitorDBAdapter(serverName):
    cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/DbAdapter/ComponentRuntimes/DbAdapter/ConnectionPools")
    connectionPools = ls(returnMap='true')
    print '--------------------------------------------------------------------------------'
    print 'DBAdapter Runtime details for '+ serverName
    print '--------------------------------------------------------------------------------'

    print '%10s %13s %15s %18s' % ('Connection Pool', 'State', 'Current', 'Created')
    print '%10s %10s %24s %21s' % ('', '', 'Capacity', 'Connections')
    print '--------------------------------------------------------------------------------'
    for connectionPool in connectionPools:
       if connectionPool!='eis/DB/SOADemo':
          cd('/')
          cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/DbAdapter/ComponentRuntimes/DbAdapter/ConnectionPools/"+str(connectionPool))
          print '%15s %15s %10s %20s' % (cmo.getName(), cmo.getState(), cmo.getCurrentCapacity(), cmo.getConnectionsCreatedTotalCount())

    print '--------------------------------------------------------------------------------'


def monitorAQAdapter(serverName):
    cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/AqAdapter/ComponentRuntimes/AqAdapter/ConnectionPools")
    connectionPools = ls(returnMap='true')
    print '--------------------------------------------------------------------------------'
    print 'AqAdapter Runtime details for '+ serverName
    print '--------------------------------------------------------------------------------'

    print '%10s %13s %15s %18s' % ('Connection Pool', 'State', 'Current', 'Created')
    print '%10s %10s %24s %21s' % ('', '', 'Capacity', 'Connections')
    print '--------------------------------------------------------------------------------'
    for connectionPool in connectionPools:
       if connectionPool!='eis/DB/SOADemo':
          cd('/')
          cd("ServerRuntimes/"+str(serverName)+"/ApplicationRuntimes/AqAdapter/ComponentRuntimes/AqAdapter/ConnectionPools/"+str(connectionPool))
          print '%15s %15s %10s %20s' % (cmo.getName(), cmo.getState(), cmo.getCurrentCapacity(), cmo.getConnectionsCreatedTotalCount())

    print '--------------------------------------------------------------------------------'   

def parameters():
      global user
      global credential
      global host
      global port
      global targetServerName
      try:
        opts, args    = getopt.getopt(sys.argv[1:], "u:c:h:p:s:",
                                  ["user=", "credential=", "host=", "port=",
                                   "targetServerName="])
      except getopt.GetoptError, err:
        print str(err)
        usage()
        sys.exit(2)

      for opt, arg in opts:
        if opt == "-n":
            reallyDoIt  = false
        elif opt == "-u":
            user        = arg
        elif opt == "-c":
            credential  = arg
        elif opt == "-h":
            host        = arg
        elif opt == "-p":
            port        = arg
        elif opt == "-s":
            targetServerName  = arg       
      if user == "":
        print "Missing \"-u user\" parameter."
        usage()
        sys.exit(2)
      if credential == "":
        print "Missing \"-c credential\" parameter."
        usage()
        sys.exit(2)
      if host == "":
        print "Missing \"-h host\" parameter."
        usage()
        sys.exit(2)
      if port == "":
        print "Missing \"-p port\" parameter."
        usage()
        sys.exit(2)
      if targetServerName == "":
        print "Missing \"-s targetServerName\" parameter."
        usage()
        sys.exit(2)           

   
def main():
      parameters()
      #connect(username, password, admurl)
      connect(user,credential,'t3://'+host+':'+port)
      servers = cmo.getServers()
      domainRuntime()
      cd("/ServerLifeCycleRuntimes/" + targetServerName)
      if cmo.getState() == 'RUNNING': 
        monitorAQAdapter(targetServerName)
        monitorDBAdapter(targetServerName)               
      disconnect()
   
main()


Conclusion

There are various ways to monitor backend systems and databases . It is useful to create your own dashboards, especially when there are a lot of systems involved and you don't want to (or can't) login to the Enterprise Manager on every one of them. Make sure though such unsecured dashboards don't end up on production systems. Depending on the problem with a database, a JNDI lookup might or might not work. The DbAdapter and AqAdapter have JDBC DataSources configured. It is useful to create a script which determines the DataSources based on the DbAdapter/AqAdapter configuration since that listing contains all DataSources used by the adapter, even if they are not loaded succesfully. That is the list of DataSources that should be tested. This can be done with WLST as shown in this post. Using a servlet however is more convenient then using WLST scripts since the URL of the servlet can be mailed to for example testers so they can monitor the databases. WLST requires a usuable Middleware installation and connection properties, which are not always available. I might create a Java servlet which provides the functionality of the WLST script mentioned in this post in the near future.