Configure Database connectivity in WebSphere Liberty

Jun 1, 2020 websphere liberty database connect
Share on:

Database Connection

If you want to configure manually to connect Database using WebSphere Liberty, Please follow the below steps.

In the below example I am using MySQL database, I have created one free database of size 5MB in freemysqlhosting (www.freemysqlhosting.net) and try to connect the database from WebSphere Liberty.

  • After registering the freemysqlhosting.net site, they will send a newly created database, SQL hosting information over mail. Details would be looks like below,
1Server: sql2.freemysqlhosting.net
2Name: sql2340129
3Username: sql2340129
4Password: xxxx
5Port number: 3306

  • To connect Database we need a Driver file. As we are using MySQL database, download the appropriate jar file from MySQL connector (https://dev.mysql.com/downloads/connector/j/), eg., mysql-connector-java-8.0.20.zip.

    • Extract the zip file and copy the jar file to the WebSphere Liberty resource directory, $LIBERTY_HOME/wlp/usr/shared/resources/mysql. Create an appropriate resource directory if it does not exist.

  • Define JNDI name, resource information in server.xml file.

    • Declare Driver information (There are few predefined variables in liberty like ${shared.resource.dir} is the home directory of resource folder $LIBERTY_HOME/wlp/usr/shared/resources, we will understand more variables) 
      1<library id="MySQLLib">
2    <fileset dir="${shared.resource.dir}/mysql" includes="*.jar" />
3</library>

  • Define datasource 

    1<dataSource jndiName="jdbc/MySqlDS" transactional="false">
2        <jdbcDriver libraryRef="MySQLLib" />
3        <properties databaseName="sql2343508"
4                serverName="sql2.freemysqlhosting.net" portNumber="3306"
5                user="sql2343508" password="lT3*dN9%" />
6</dataSource>

  • Deploy an application that using database jndi datasource.

    • Write a simple java program connect database
 1  String query = "SHOW TABLES";
 2   try {
 3          Context initContext = new InitialContext();
 4          DataSource ds = (DataSource) initContext.lookup("jdbc/MySqlDS");
 5          Connection conn =  ds.getConnection();
 6          Statement stm = conn.createStatement();
 7          ResultSet rs = stm.executeQuery(query);
 8          while (rs.next()) {
 9                  int count = rs.getInt(1);
10                  System.out.println("Total number of table : " + count);
11          }
12      }catch(Exception e) {
13              System.out.println(e); }

Download complete source code at DBConnect.war (https://github.com/middlewaretrace/wlp/blob/master/DBConnect.war). Copy war file in dropins directory and just access the URL: http://localhost:9080/DBConnect. As soon as access to the servlet, DB connect will happen.

Detailed information of logs can show as:

1[5/26/20 19:52:41:407 CEST] 0000004c com.ibm.tx.jta.impl.RecoveryManager                          I WTRN0135I: Transaction service recovering no transactions.
2[5/26/20 19:52:42:661 CEST] 00000041 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8203I: Database product name : MySQL
3[5/26/20 19:52:42:666 CEST] 00000041 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8204I: Database product version : 5.5.54-0ubuntu0.12.04.1
4[5/26/20 19:52:42:667 CEST] 00000041 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8205I: JDBC driver name  : MySQL Connector/J
5[5/26/20 19:52:42:668 CEST] 00000041 com.ibm.ws.rsadapter.impl.DatabaseHelper                     I DSRA8206I: JDBC driver version  : mysql-connector-java-8.0.20 (Revision: afc0a13cd3c5a0bf57eaa809ee0ee6df1fd5ac9b)

More information on JDBC connectivity:

Different databases provide their own JDBC drivers. To access the database application, the code must use javax.sql.DataSource interface. The Datasource or Driver implementations come in the following varieties

  • javax.sql.DataSource
  • javax.sql.ConnectionPoolDataSource
  • javax.sql.XADataSource

In most of the cases you need to tell liberty that where is driver only no need to mention driver type.

If you are using jdbc-4.3 or above, liberty chooses driver type based on availability in the following order,

  1. javax.sql.XADataSource
  2. javax.sql.ConnectionPoolDataSource
  3. javax.sql.DataSource

Oracle RAC Database configuration:

Using JDBC driver, you can configure failover support, load balancing, or both, in Oracle Real Application Clusters (RAC) environment.

following server.xml to configure Oracel RAC for liberty: 

 1<dataSource id="ds_id" jndiName="jdbc/ds_id ">
 2  <jdbcDriver>
 3    <library>
 4      <fileset dir="path_to_oracle_jar" includes="ojdbcX.jar"/>
 5    </library>
 6  </jdbcDriver>
 7  <properties.oracle
 8  URL="jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=OFF)(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=port1))
 9  (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=port2))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=name)))"
10  user="username" password="password" />
11</dataSource>

DataSource Configuration elements in server.xml:

dataSource

Define a data source configuration.

Attributes
  • jndiName

    JNDI name for a data source.

    Required: true
    Data type: string
  • jdbcDriverRef

    JDBC driver for a data source.

    Required: false
    Data type: Configuration ID of type jdbcDriver (string).
  • connectionManagerRef

    Conection manager for a data source.

    Required: false
    Data type: Configuration ID of type connectionManager (string).
  • type

    Type of data source.

    Range:
    javax.sql.XADataSource
    javax.sql.ConnectionPoolDataSource
    javax.sql.DataSource
    Required: false
    Data type: string
  • connectionSharing

    How connections are matched for sharing.

    Default: MatchOriginalRequest
    Range:
    MatchOriginalRequest
    When sharing connections, match based on the original connection request.
    MatchCurrentState
    When sharing connections, match based on the current state of the connection.
    Required: false
    Data type: string
  • isolationLevel

    Default transaction isolation level.

    Range:
    TRANSACTION_READ_UNCOMMITTED
    Dirty reads, non-repeatable reads and phantom reads can occur.
    TRANSACTION_READ_COMMITTED
    Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
    TRANSACTION_REPEATABLE_READ
    Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
    TRANSACTION_SERIALIZABLE
    Dirty reads, non-repeatable reads and phantom reads are prevented.
    TRANSACTION_SNAPSHOT
    Snapshot isolation for Microsoft SQL Server JDBC Driver and DataDirect Connect for JDBC driver.
    Required: false
    Data type: string
  • statementCacheSize

    Maximum number of cached statements per connection.

    Default: 10
    Required: false
    Data type: int
  • transactional

    Enable participation in transactions that are managed by the application server.
    Default: true
    Required: false
    Data type: boolean
  • beginTranForResultSetScrollingAPIs

    Attempt transaction enlistment when result set scrolling interfaces are used.

    Default: true
    Required: false
    Data type: boolean
  • beginTranForVendorAPIs

    Attempt transaction enlistment when vendor interfaces are used.

    Default: true
    Required: false
    Data type: boolean
  • commitOrRollbackOnCleanup

    Determines how to clean up connections that might be in a database unit of work (AutoCommit=false) when the connection is closed or returned to the pool.

    Range:
    commit
    Clean up the connection by committing.
    rollback
    Clean up the connection by rolling back.
    Required: false
    Data type: string
  • queryTimeout

    Default query timeout for SQL statements. In a JTA transaction, syncQueryTimeoutWithTransactionTimeout can override this default. Specify a positive integer followed by a unit of time, which can be hours (h), minutes (m), or seconds (s). For example, specify 30 seconds as 30s. We can include multiple values in a single entry. For example, 1m30s is equivalent to 90 seconds.

    Required: false
    Data type: string
  • recoveryAuthDataRef

    Authentication data for transaction recovery.

    Required: false
    Data type: Configuration ID of type authData (string).
  • syncQueryTimeoutWithTransactionTimeout

    Use the time remaining (if any) in a JTA transaction as the default query timeout for SQL statements.

    Default: false
    Required: false
    Data type: boolean
  • supplementalJDBCTrace

    Supplements the JDBC driver trace logged when JDBC driver trace is enabled in bootstrap.properties. JDBC driver trace specifications include: com.ibm.ws.database.logwriter, com.ibm.ws.db2.logwriter, com.ibm.ws.derby.logwriter, com.ibm.ws.informix.logwriter, com.ibm.ws.oracle.logwriter, com.ibm.ws.sqlserver.logwriter, com.ibm.ws.sybase.logwriter.

    Required: false
    Data type: boolean
Sub-elements
  • jdbcDriver

    JDBC driver for a data source.

    Required: false
    Data type: Element of type jdbcDriver.
  • connectionManager

    Connection manager for a data source.

    Required: false
    Data type: Element of type connectionManager.
  • recoveryAuthData

    Authentication data for transaction recovery.

    Required: false
    Data type: Element of type authData.
comments powered by Disqus