back to all blogsSee all blog posts

Testing database connections in Open Liberty apps with REST APIs

image of author
Nathan Mittlestat on May 24, 2019

Looking for a simple way to test your database connections? You can now validate your connections by invoking REST endpoints provided by the Configuration Validator (configValidator-1.0) beta feature (download our latest development builds to try it). In addition to being simple, these REST endpoints will exercise the same code paths as your applications, giving you confidence in your server configuration.

By itself, the Configuration Validator feature allows viewing of a server’s current configuration via the /ibm/api/config endpoint. REST endpoints capable of validating specific resources become available under the /ibm/api/validation parent endpoint, when the Configuration Validator feature is combined with features that implement configuration validation. Validation of JDBC, JCA, Cloudant, and JMS configuration elements are currently supported. Looking for more validation support? Start a thread on our Groups.io account and let us know.

Both the validation and config endpoints are generated using a combination of the config element name, and unique ID:

/ibm/api/config/{configElementName}/{uid}
/ibm/api/validation/{configElementName}/{uid}

Now, let’s take a look at how you can use this new REST API to validate your configuration.

Here is a sample server.xml configuration that uses the Configuration Validator and JDBC features:

<server>
  <featureManager>
    <!-- configValidator-1.0 is a temporary feature needed to enable this functionality -->
    <feature>configValidator-1.0</feature>
    <feature>jdbc-4.2</feature>
  </featureManager>

  <library id="derby">
    <file name="${server.config.dir}/derby/derby.jar"/>
  </library>

  <dataSource id="DefaultDataSource">
    <jdbcDriver libraryRef="derby"/>
    <!-- Example properties referencing an in-memory Derby Embedded database -->
    <properties.derby.embedded databaseName="memory:defaultdb" createDatabase="create" user="dbuser" password="dbpass"/>
  </dataSource>
...
</server>

Validating a data source

Let’s say we want to directly test a database connection that an application depends on. We can do this using a REST endpoint found under https://localhost:9443/ibm/api/validation/dataSource/{uid}`. For our case this endpoint is https://localhost:9443/ibm/api/validation/dataSource/DefaultDataSource.

When the data source is working properly, a success message is shown:

{
   "uid": "DefaultDataSource",
   "id": "DefaultDataSource",
   "successful": true,
   "info": {
      "databaseProductName": "Apache Derby",
      "databaseProductVersion": "10.11.1.1 - (1616546)",
      "jdbcDriverName": "Apache Derby Embedded JDBC Driver",
      "jdbcDriverVersion": "10.11.1.1 - (1616546)",
      "schema": "DBUSER",
      "user": "dbuser"
   }
}

If there is a problem, details about the failure are displayed. For instance if the <jdbcDriver> element references a <library> that does not contain a valid DataSource implementation, the following result is given:

{
  "uid": "DefaultDataSource",
  "id": "DefaultDataSource",
  "failure": {
    "class": "java.sql.SQLNonTransientException",
    "message": "DSRA4000E: A valid JDBC driver implementation class was not found for the jdbcDriver dataSource[DefaultDataSource]/jdbcDriver[default-0] using the library jdbcLib. []",
    "stack": [
      "com.ibm.ws.jdbc.internal.JDBCDriverService.classNotFound(JDBCDriverService.java:195)",
      "com.ibm.ws.jdbc.internal.JDBCDriverService.create(JDBCDriverService.java:297)",
      "com.ibm.ws.jdbc.internal.JDBCDriverService.createDefaultDataSource(JDBCDriverService.java:396)",
      // stack trace cut short
      "java.lang.Thread.run(Thread.java:785)"
    ],
    "cause": {
      "class": "java.lang.ClassNotFoundException",
      "message": "org.apache.derby.jdbc.EmbeddedXADataSource40",
      "stack": [
        "com.ibm.ws.classloading.internal.AppClassLoader.findClassCommonLibraryClassLoaders(AppClassLoader.java:499)",
        // stack trace cut short
        "java.lang.Thread.run(Thread.java:785)"
      ]
    }
  }
}

Viewing data source configuration

To view all data source configurations, we start the server and visit the following endpoint https://localhost:9443/ibm/api/config/dataSource/. In this case there is only one data source to view, the DefaultDataSource:

[
   {
      "configElementName": "dataSource",
      "uid": "DefaultDataSource",
      "id": "DefaultDataSource",
      "beginTranForResultSetScrollingAPIs": true,
      "beginTranForVendorAPIs": true,
      "connectionSharing": "MatchOriginalRequest",
      "enableConnectionCasting": false,
      "jdbcDriverRef": [
         {
            "configElementName": "jdbcDriver",
            "uid": "dataSource[DefaultDataSource]/jdbcDriver[default-0]",
            "libraryRef": [
               {
                  "configElementName": "library",
                  "uid": "derby",
                  "id": "derby",
                  "apiTypeVisibility": "spec,ibm-api,api,stable",
                  "fileRef": [
                     {
                        "configElementName": "file",
                        "uid": "library[derby]/file[default-0]",
                        "name": "/home/nmittles/git/liberty/open-liberty/dev/build.image/wlp/usr/servers/blog/derby/derby.jar"
                     }
                  ]
               }
            ]
         }
      ],
      "statementCacheSize": 10,
      "syncQueryTimeoutWithTransactionTimeout": false,
      "transactional": true,
      "properties.derby.embedded": [
         {
            "createDatabase": "create",
            "databaseName": "memory:defaultdb",
            "password": "******",
            "user": "dbuser"
         }
      ],
      "api": [
         "/ibm/api/validation/dataSource/DefaultDataSource"
      ]
   }
]

To view an individual data source, append the data source’s uid as seen from viewing the configuration. In our case this is DefaultDataSource, and results in a URL of: https://localhost:9443/ibm/api/config/dataSource/DefaultDataSource

{
   "configElementName": "dataSource",
   "uid": "DefaultDataSource",
   "id": "DefaultDataSource",
   "beginTranForResultSetScrollingAPIs": true,
   "beginTranForVendorAPIs": true,
   "connectionSharing": "MatchOriginalRequest",
   "enableConnectionCasting": false,
   "jdbcDriverRef": [
      {
         "configElementName": "jdbcDriver",
         "uid": "dataSource[DefaultDataSource]/jdbcDriver[default-0]",
         "libraryRef": [
            {
               "configElementName": "library",
               "uid": "derby",
               "id": "derby",
               "apiTypeVisibility": "spec,ibm-api,api,stable",
               "fileRef": [
                  {
                     "configElementName": "file",
                     "uid": "library[derby]/file[default-0]",
                     "name": "/home/nmittles/git/liberty/open-liberty/dev/build.image/wlp/usr/servers/blog/derby/derby.jar"
                  }
               ]
            }
         ]
      }
   ],
   "statementCacheSize": 10,
   "syncQueryTimeoutWithTransactionTimeout": false,
   "transactional": true,
   "properties.derby.embedded": [
      {
         "createDatabase": "create",
         "databaseName": "memory:defaultdb",
         "password": "******",
         "user": "dbuser"
      }
   ],
   "api": [
      "/ibm/api/validation/dataSource/DefaultDataSource"
   ]
}

Validating a JCA connection factory

Here is a sample server.xml configuration that uses the Configuration Validator and JCA features:

<server>
  <featureManager>
    <!-- configValidator-1.0 is a temporary feature needed to enable this functionality -->
    <feature>configValidator-1.0</feature>
    <feature>jca-1.7</feature>
  </featureManager>

  <authData id="auth2" user="containerAuthUser2" password="2containerAuthUser"/>

  <connectionFactory id="cf1" jndiName="eis/cf1">
    <containerAuthData user="containerAuthUser1" password="1containerAuthUser"/>
    <properties.TestValidationAdapter.ConnectionFactory hostName="myhost.openliberty.io" portNumber="9876"/>
  </connectionFactory>
...
</server>

The REST endpoints for validating a connection factory can be found at https://localhost:9443/ibm/api/validation/connectionFactory/{uid}. To test cf1 using container authentication we can use the following URL: https://localhost:9443/ibm/api/validation/connectionFactory/cf1?auth=container:

{
   "uid": "cf1",
   "id": "cf1",
   "jndiName": "eis/cf1",
   "successful": true,
   "info": {
      "resourceAdapterName": "TestValidationAdapter",
      "resourceAdapterVersion": "28.45.53",
      "resourceAdapterJCASupport": "1.7",
      "resourceAdapterVendor": "OpenLiberty",
      "resourceAdapterDescription": "This tiny resource adapter doesn't do much at all.",
      "eisProductName": "TestValidationEIS",
      "eisProductVersion": "33.56.65",
      "user": "containerAuthUser1"
   }
}

Validation of a connection factory supports both container and application authentication by the auth parameter being included on the URL. Additionally, when using ?auth=application, a user can be specified by including the X-Validation-User and X-Validation-Password headers. Finally, the authentication alias can be specified using the authAlias parameter. For example, this could look like https://localhost:9443/ibm/api/validation/connectionFactory/cf1?auth=container&authAlias=auth2.

Viewing JCA Connection Factories

JCA connection factory configuration can be viewed similar to that of data sources. The endpoint to view all connection factories becomes https://localhost:9443/ibm/api/config/connectionFactory. Again, our simple example only has one config element:

[
   {
      "configElementName": "connectionFactory",
      "uid": "cf1",
      "id": "cf1",
      "jndiName": "eis/cf1",
      "containerAuthDataRef": [
         {
            "configElementName": "containerAuthData",
            "uid": "connectionFactory[cf1]/containerAuthData[default-0]",
            "password": "******",
            "user": "containerAuthUser1"
         }
      ],
      "properties.TestValidationAdapter.ConnectionFactory": [
         {
            "hostName": "myhost.openliberty.io",
            "password": "******",
            "portNumber": 9876,
            "userName": "DefaultUserName"
         }
      ]
   }
]

To view an individual connection factory append the uid. In our case this will be https://localhost:9443/ibm/api/config/connectionFactory/cf1:

{
   "configElementName": "connectionFactory",
   "uid": "cf1",
   "id": "cf1",
   "jndiName": "eis/cf1",
   "containerAuthDataRef": [
      {
         "configElementName": "containerAuthData",
         "uid": "connectionFactory[cf1]/containerAuthData[default-0]",
         "password": "******",
         "user": "containerAuthUser1"
      }
   ],
   "properties.TestValidationAdapter.ConnectionFactory": [
      {
         "hostName": "myhost.openliberty.io",
         "password": "******",
         "portNumber": 9876,
         "userName": "DefaultUserName"
      }
   ]
}

Cloudant Database

Cloudant databases can be viewed and validated as well.

Let’s use the following server config snippets:

<server>
  <featureManager>
    <feature>cloudant-1.0</feature>
    <feature>configValidator-1.0</feature>
  </featureManager>

  <library id="CloudantLib">
    <fileset dir="${server.config.dir}/cloudant"/>
  </library>

  <authData id="cloudantAuthData" user="${CLOUDANT_USER}" password="${CLOUDANT_PASS}"/>

  <cloudant id="myCloudant" jndiName="cloudant/myCloudant" libraryRef="CloudantLib" url="http://localhost:5984">
    <containerAuthData user="cloudantUser" password="cloudantPass"/>
  </cloudant>

  <cloudantDatabase id="myCloudantDB" jndiName="cloudant/myCloudantDB" cloudantRef="myCloudant" databaseName="testdb" create="true"/>

  <keyStore id="defaultKeyStore" password="Liberty"/>
  <quickStartSecurity userName="adminuser" userPassword="adminpwd"/>
</server>

The Cloudant config can be viewed at https://localhost:9443/ibm/api/config/cloudantDatabase, while the myCloudantDB database can be tested using container authentication at https://localhost:9443/ibm/api/validation/cloudantDatabase/myCloudantDB?auth=container. Similar to JCA connection factories, Cloudant validation supports auth types of container and application. The authentication alias can be specified as a parameter as well. For example, https://localhost:9443/ibm/api/validation/cloudantDatabase/myCloudantDB?auth=Application&authAlias=cloudantAuthData can be used to test the myCloudantDB database using Application authentication with an authentication alias of cloudantAuthData.

So there you have it. We’re still developing this capability to test database connections using REST APIs. It’s currently in 'beta' status download our latest development builds to try it. If you have any feedback, questions, or suggestions about further support that would be useful, let us know by posting to our Groups.io account.

Tags