{"id":32428,"date":"2014-11-04T16:00:20","date_gmt":"2014-11-04T14:00:20","guid":{"rendered":"http:\/\/www.javacodegeeks.com\/?p=32428"},"modified":"2014-11-07T00:45:30","modified_gmt":"2014-11-06T22:45:30","slug":"tomcat-wildfly-configuring-database-connectivity","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2014\/11\/tomcat-wildfly-configuring-database-connectivity.html","title":{"rendered":"Tomcat to Wildfly: Configuring Database connectivity"},"content":{"rendered":"

This excerpt has been taken from the \u201cFrom Tomcat to WildFly<\/a>\u201d book in which you\u2019ll learn how to port your existing Tomcat architectures to WildFly, including both the server configuration and the applications running on the top of it.<\/em><\/p>\n

WildFly is a fully compliant Java Enterprise Edition 7 container with a much wider set of available services and options compared to Tomcat.<\/em><\/p>\n

The book will also give you exposure to the most common pitfalls and drawbacks, which might happen during the migration.<\/em><\/p>\n

 <\/p>\n

\n

Table Of Contents<\/h3>\n
\n
1. Introduction<\/a><\/dt>\n
2. Installing the JDBC Driver as module<\/a><\/dt>\n
3. Registering the JDBC driver on the application server<\/a><\/dt>\n
4. Configuring a datasource which uses the JDBC driver<\/a><\/dt>\n
5. Configuration output<\/a><\/dt>\n
6. Porting Datasource parameters to WildFly<\/a><\/dt>\n
\n
\n
6.1. Minimum and Maximum pool size<\/a><\/dt>\n
6.2. Dealing with Idle connections<\/a><\/dt>\n
6.3. Setting a timeout when acquiring connections<\/a><\/dt>\n
6.4. Handling Connection leaks<\/a><\/dt>\n
6.5. Configuring Statement Cache<\/a><\/dt>\n<\/dl>\n<\/dd>\n<\/dl>\n<\/div>\n

<\/a>1. Introduction<\/h1>\n\n\n\n
Problem<\/strong>: I have a Datasource<\/strong> configuration on Tomcat, which is used to collect database connections from a pool. I need to port my configuration on WildFly.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

On Apache Tomcat, the datasource configuration can be included in the global section of your server.xml<\/em><\/strong> file. Here is for example a configuration for the popular MySQL database:<\/p>\n

\r\n<Resource name="jdbc\/mysqlds" auth="Container" type="javax.sql.DataSource"\r\n          maxActive="100" maxIdle="30" maxWait="10000"\r\n          username="tomcat" password="tomcat"\r\n          driverClassName="com.mysql.jdbc.Driver"\r\n          url="jdbc:mysql:\/\/localhost:3306\/mydatabase"\/> <\/pre>\n
In addition, the following lines should be placed on the WEB-INF\/web.xml<\/em><\/strong> for the application-specific content.<\/p>\n
\r\n<web-app xmlns="http:\/\/java.sun.com\/xml\/ns\/j2ee" xmlns:xsi="http:\/\/www.w3.org\/2001\/XMLSchema-instance" xsi:schemaLocation="http:\/\/java.sun.com\/xml\/ns\/j2ee http:\/\/java.sun.com\/xml\/ns\/j2ee\/web-app_2_4.xsd" version="2.4">\r\n   <description>Tomcat DB<\/description>\r\n   <resource-ref>\r\n      <description>Database Connection<\/description>\r\n      <res-ref-name>jdbc\/mysqlds<\/res-ref-name>\r\n      <res-type>javax.sql.DataSource<\/res-type>\r\n      <res-auth>Container<\/res-auth>\r\n   <\/resource-ref>\r\n<\/web-app> <\/pre>\n
In order to be able to load the MySQL JDBC driver, you need to include the JAR library in the CATALINA_HOME\/lib<\/em><\/strong> folder:<\/p>\n
\"image001\"<\/a><\/p>\n
Let\u2019s see how to configure a Datasource configuration in WildFly<\/strong>. This can be accomplished in several ways, yet all possible solutions involve the following steps:<\/p>\n
    \n
  1. Installing the JDBC driver as module<\/li>\n
  2. Registering the JDBC driver on the application server<\/li>\n
  3. Configuring a datasource which uses the JDBC driver<\/li>\n<\/ol>\n
    We will see the recommended approach, which requires using the Command Line Interface, although we will mention some alternatives available.<\/p>\n
    <\/a>2. Installing the JDBC Driver as module<\/h1>\n
    WildFly is based on the assumption that every library is itself a module. So, we will turn at first the JDBC driver into a module. This can be done by creating a file path structure under the JBOSS_HOME\/modules<\/em><\/strong> directory. For example, in order to install a MySQL JDBC driver, create a directory structure as follows: JBOSS_HOME\/modules\/com\/mysql\/main<\/em><\/strong>.<\/p>\n
      \n
    1. Copy the JDBC driver JAR into the main<\/strong> subdirectory.<\/li>\n
    2. In the main<\/strong> subdirectory, create a module.xml<\/em><\/strong> file containing the following definitions (just adapt the JDBC driver name to your case):<\/li>\n<\/ol>\n
      \r\n<?xml version="1.0" encoding="UTF-8"?>\r\n<module xmlns="urn:jboss:module:1.0" name="com.mysql">\r\n\r\n  <resources>\r\n    <resource-root path="mysql-connector-java-5.1.24-bin.jar"\/>\r\n  <\/resources>\r\n\r\n  <dependencies>\r\n    <module name="javax.api"\/>\r\n    <module name="javax.transaction.api"\/>\r\n  <\/dependencies>\r\n\r\n<\/module> <\/pre>\n
      The above procedure can be completed as well using the Command Line Interface<\/strong>, with a single command, which will create the file system structure, copy the JDBC driver into the main folder and configure a module.xml<\/em><\/strong> based on the options provided to the CLI.<\/p>\n
      Here is how to do it, supposing that MySQL JDBC driver is available in the \/home\/wildfly<\/em><\/strong> folder:<\/p>\n
      \r\nmodule add --name=com.mysql --resources=\/home\/wildfly\/mysql-connector-java-5.1.24-bin.jar --dependencies=javax.api,javax.transaction.api <\/pre>\n
      <\/a>3. Registering the JDBC driver on the application server<\/h1>\n
      Now that your MySQL is available as module on the application server, we will register it as JDBC driver. When using the CLI, this is a one-step operation:
       
      <\/div> <\/div><\/p>\n
      \r\n\/subsystem=datasources\/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql) <\/pre>\n
      Now, you can check that your MySQL driver is enlisted through the available JDBC Drivers. Here is how you can achieve it:<\/p>\n
      \r\n[standalone@localhost:9990 \/] \/subsystem=datasources:installed-drivers-list\r\n{\r\n         . . . . . . . . . . .\r\n         "driver-name" => "mysql",\r\n         "deployment-name" => undefined,\r\n         "driver-module-name" => "com.mysql",\r\n         "module-slot" => "main",\r\n         "driver-datasource-class-name" => "",\r\n         "driver-xa-datasource-class-name" => "",\r\n         "driver-class-name" => "com.mysql.jdbc.Driver",\r\n         "driver-major-version" => 5,\r\n         "driver-minor-version" => 1,\r\n         "jdbc-compliant" => false        }\r\n    ]\r\n} <\/pre>\n
      <\/a>4. Configuring a datasource which uses the JDBC driver<\/h1>\n
      The last step will actually create a datasource to be used by your applications. For this purpose, we will use the CLI data-source<\/strong> shortcut command, which requires as input the Pool name, the JNDI bindings, the JDBC Connection parameters and finally the security settings (username and password):<\/p>\n
      \r\ndata-source add --jndi-name=java:\/jdbc\/mysqlds --name=MySQLPool --connection-url=jdbc:mysql:\/\/localhost:3306\/mydatabase --driver-name=mysql --user-name=jboss --password=jboss  <\/pre>\n\n\n\n
      Please note that the JNDI name for the Datasource must use the prefix java:\/ to be accepted as valid. Therefore the binding used in tomcat (\u201cjdbc\/mysqlds\u201d) has been changed to \u201cjava:\/ jdbc\/mysqlds\u201d.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      <\/a>5. Configuration output<\/h1>\n
      If you have followed the above steps, you should have the following datasource configuration available in your datasource section:<\/p>\n
      \r\n<datasources>\r\n\t<datasource jndi-name="java:\/jdbc\/mysqlds" \r\n                   pool-name="MySQLPool" enabled="true">\r\n\t\t<connection-url>jdbc:mysql:\/\/localhost:3306\/mydatabase<\/connection-url>\r\n\t\t<driver>mysql<\/driver>\r\n\t\t<security>\r\n\t\t\t<user-name>jboss<\/user-name>\r\n\t\t\t<password>jboss<\/password>\r\n\t\t<\/security>\r\n\t<\/datasource>\r\n\t<drivers>\r\n\t\t<driver name="mysql" module="com.mysql"\/>\r\n\t<\/drivers>\r\n <\/datasources> <\/pre>\n
      <\/a>6. Porting Datasource parameters to WildFly<\/h1>\n
      Configuring the datasource on the application server is the first milestone for porting your applications on WildFly. Odds are however that you are using some specific connection pool settings that need to be ported on the application server. Some of these parameters have an identical match on WildFly, some others are based on different pooling strategies; therefore you need to adapt the configuration when porting them to the application server. Let\u2019s see how to port the most common pool options to WildFly:<\/p>\n
      <\/a>6.1. Minimum and Maximum pool size<\/h2>\n
      Choosing the right pool size is a must for the performance of your applications.\u00a0 Tomcat minimum pool size is determined by the minIdle<\/strong> parameter and the max pool size is configured through maxActive<\/strong>. The initial size of the pool, on the other hand, is configured with the initialSize<\/strong> parameter. Here is a sample configuration:<\/p>\n
      \r\n<Resource name="jdbc\/mysqlds" auth="Container" type="javax.sql.DataSource"\r\n          maxActive="100" minIdle="30" initialSize=\u201d15\u201d \r\n          username="tomcat" password="tomcat"\r\n          driverClassName="com.mysql.jdbc.Driver"\r\n          url="jdbc:mysql:\/\/localhost:3306\/mydatabase"\/> <\/pre>\n
      When porting to WildFly, the parameters are named respectively min-pool-size<\/strong> and max-pool-size<\/strong> and can be set with any management instrument. Here\u2019s how to change them for the default datasource:<\/p>\n
      \r\n\/subsystem=datasources\/data-source=ExampleDS\/:write-attribute(name=min-pool-size,value=30)\r\n\/subsystem=datasources\/data-source=ExampleDS\/:write-attribute(name=max-pool-size,value=100) <\/pre>\n
      The initial pool size<\/strong>, on the other hand, can be set using:<\/p>\n
      \r\n\/subsystem=datasources\/data-source=ExampleDS\/:write-attribute(name=initial-pool-size,value=5) <\/pre>\n
      Additionally note that WildFly has also an attribute named pool-prefill<\/strong>, which determines whether to attempt to prefill the connection pool to the minimum number of connections:<\/p>\n
      \r\n\/subsystem=datasources\/data-source=ExampleDS\/:write-attribute(name=pool-prefill,value=true) <\/pre>\n
      <\/a>6.2. Dealing with Idle connections<\/h2>\n
      Connections that have been created but haven\u2019t been used by your applications are classified as idle connections. WildFly and Tomcat have a different way to deal with idle connections. More in detail, Tomcat uses both a minIdle<\/strong> and maxIdle<\/strong> parameter to determine respectively the minimum and maximum of idle connections that should be kept in the pool. We have already discussed about the minIdle parameter, which can be turned to be WildFly min-pool-size<\/strong>. On the other hand, the maxIdle parameter has not corresponding match on WildFly. The closest match is the i<\/strong>dle-timeout-minutes<\/strong>, which is the number of minutes after which unused connections are closed (default 15 minutes). You can actually vary this parameter let\u2019s say to 10 minutes as follows:<\/p>\n
      \r\n\/subsystem=datasources\/data-source=ExampleDS\/:write-attribute(name=idle-timeout-minutes,value=10) <\/pre>\n
      <\/a>6.3. Setting a timeout when acquiring connections<\/h2>\n
      If all your connections in a pool are busy, your applications will obviously have to wait for a connection to be released. As you can imagine, there is a timeout for this scenario, which is handled by the maxWait<\/strong> parameter in Tomcat. In the following example the timeout is set to 30 seconds:<\/p>\n
      \r\n<Resource name="jdbc\/mysqlds" auth="Container" type="javax.sql.DataSource"\r\n          maxWait="30000" \r\n . . . . \/> <\/pre>\n
      WildFly has a corresponding parameter named blocking-timeout-wait-millis<\/strong> ; in the following CLI command we are setting it to 1 second (1000 ms):<\/p>\n
      \r\n\/subsystem=datasources\/data-source=ExampleDS\/:write-attribute(name=blocking-timeout-wait-millis,value=1000) <\/pre>\n
      <\/a>6.4. Handling Connection leaks<\/h2>\n
      A connection leak is called in Tomcat terms an \u201cAbandoned connection<\/strong>\u201d, which happens when you are creating a Statement, or a PreparedStatement or CallableStatement and you miss to close the connection or the statement (or more often, you don\u2019t include the Connection close<\/em> in a finally<\/em> block).<\/p>\n
      You can handle abandoned connections in Tomcat by enabling the removeAbandoned<\/strong> parameter. If set to true a connection is considered abandoned and eligible for removal if it has not been used for longer than the removeAbandonedTimeout<\/strong> (default 300 seconds).<\/p>\n
      \r\n<Resource name="jdbc\/mysqlds" auth="Container" type="javax.sql.DataSource"\r\n          removeAbandoned ="true"  removeAbandonedTimeout=\u201d300\u201d\r\n          . . . . \/> <\/pre>\n
      On the WildFly side, there is no corresponding tweak for abruptly closing connections that are qualified as abandoned. On the other hand, there are some useful parameters, which can be used to detect or trace the issue.<\/p>\n
      If you are concerned about Statements (and PreparedStatements), you can use the track-statements parameter that checks for unclosed statements when a connection is returned to the pool and result sets are closed when a statement is closed\/return to the prepared statement cache. Valid values are:<\/p>\n