Configure Database connectivity in WebSphere Liberty

Jun 1, 2020 websphere liberty database connect
Share on:

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>
      html

  • 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>
    html

  • 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)

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

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>
...
html

Define a data source configuration.

  • 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
  • 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