Tag Archives: wildfly

How to setup PostgreSql datasource with Wildfly ?

Pretty much all applications require some sort of persistence. Strangely enough, it is not always trivial to set up properly connection between your application server and your database, or to do it in the best way. In particular, putting together Wildfly, one of the most common JavaEE application server, with PostgreSQL, one of the most … Continue reading How to setup PostgreSql datasource with Wildfly ?

Tech tips: Get a clean PostgreSql - Wildfly configuration

Pretty much all applications require some sort of persistence. Strangely enough, it is not always trivial to set up properly connection between your application server and your database, or to do it in the best way. In particular, putting together Wildfly, one of the most common JavaEE application server, with PostgreSQL, one of the most common open source SQL database, should be done in a heartbeat.

With Wildfly, there are 3 ways to setup this connection using a datasource, from easy and manual to more complex but automatable. In this article, I will explain step by step these different options for the standalone Wildfly mode.

You can also put directly the database configuration in the source code of your application but it's not really an option for an industrialized system (we will cover this in later articles).

First, here is a reminder of the elements involved in this setup. Simply put you have:

  • - Your application server, ie Wildfly
  • - Your application
  • - Your database, ie PostgreSQL
  • - The driver, handling the technical implementation of the connection with your DB and providing a Java API for interacting with it
  • - Your datasource, providing connection configuration

In each option we are going to dig into, you will always have to do two things:

  • 1. Load the JDBC driver into Wildfly
  • 2. Configure the datasource

Prerequisites

Before starting to follow any of the options, you need:

  • - A running Wildfly instance installed and configured with a user
  • - A PostgreSQL instance running a database. In our example it will be called "StenusysDemo".
  • - To download the binaries of the JDBC driver. In our case it is PostgreSQL, and you can find it here: https://jdbc.postgresql.org/download.html

Versions and naming

Here are the different versions used in our example. Adaptations may be required with later versions (nothing major until now).

Wildfly
version : 9.0.2-Final, 10.1.0-Final and 11.0.0-Final
management console default url : http://127.0.0.1:9990/

PostgreSQL
version : 9.6
database name : StenusysDemo
username : postgres
password : admin
driver : postgresql-42.2.1.jar

Option #1 Using the WildFly management console

Open the console app (http://127.0.0.1:9990/ is the default url if Wildfly is running on your computer)

Deploy the JDBC driver
  • Go to "Deployments" tabs (in the top menu bar)
  • Click on "Add" button
  • Select "Upload a new deployment" and click on "Next"
  • Click on "Choose File" and select the JDBC driver you've downloaded (for me it's postgresql-42.2.1.jar) and click on "Next"
  • Check the Name and Runtime name. Name is an unique identifier of the deployment and must be unique across all deployments. Runtime name is the name by which the deployment should be known within a server's runtime. In most cases, it will be the same as the name but in sometimes, users may wish to have two deployments with the same 'runtime-name' (e.g. two versions of "module.war"). Both are available in the deployment content repository, in which case the deployments would need to have distinct 'name' values but would have the same 'runtime-name'. Do not forget to check "Enable" checkbox and click on "Finish"

The JDBC driver is now deployed and available.

Create the datasource
  • Go to the "Configuration" (in the top menu bar)
  • Select "Subsystems" then "Datasources" then "Non-xa" and click on "Add" button
  • Select “PostgreSQL datasource” and click on "Next"
  • Now you can write the Name and JNDI name. Both name must be unique. Name is the unique identifier of the datasource and JNDI name is how the application will find the datasource (With JPA, JNDI name is the name in the persistence.xml for the configuration) (for me name is 'StenusysDemoDS' and JNDI name is 'java:jboss/datasources/StenusysDemoDS') and click on "Next"
  • To select the driver, click on "Detected driver" and select the one previously installed and click on "Next"
  • Change the "Connection url" (I will put jdbc:postgresql://localhost:5432/StenusysDemo). Username and password are the credentials of PostgreSQL.
  • Click on "Test connection". You will have a success popup but if it is failed, a "Details" button will be available to have more information about the problem.
  • Click on "Done".

Option #2 Configuration file edition

Standalone.xml is the configuration file for the server. The management console is just a friendly UI to edit this file.

Deploy the JDBC driver
  • Open a file explorer and go to /modules/ directory in your Wildfly installation directory.
  • Create folders /org/postgresql/main/. These folder need to match the hierarchy package of the JDBC driver.
  • Copy the JDBC driver into the 'main' directory you have created.
  • In this directory, create a "module.xml" file with this
<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.3" name="org.postgresql">
    <resources>
        <resource-root path="postgresql-42.2.1.jar"/>
        <!-- Make sure this matches the name of the JAR you are installing -->
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>
Create the datasource
  • Go to /standalone/configuration directory in your Wildfly installation directory.
  • Open standalone.xml (it's the default configuration file used by the standalone server)
  • Search 'datasource' to go to the right part.
  • In the <datasources> element, you need to add both <driver> for PostgreSQL and <datasource>
<drivers>
    <driver name="postgresql" module="org.postgresql">
        <!-- for xa datasource -->
        <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
        <!-- for non-xa datasource -->
        <driver-class>org.postgresql.Driver</driver-class>
    </driver>
</drivers>

<datasources>
    <datasource jndi-name="java:jboss/datasources/StenusysDemoDS" pool-name="StenusysDemoDS" enabled="true" use-java-context="true">
        <connection-url>jdbc:postgresql://localhost:5432/StenusysDemo</connection-url>
        <driver>postgresql</driver>
        <security>
            <user-name>postgres</user-name>
            <password>admin</password>
        </security>
    </datasource>
</datasources>

A restart of Wildfly is needed and you can validate your change by testing the connection in the management console.

Option #3 Using Wildfly CLI

The last way to add datasource is using the console line interface (CLI). Again, the process is divided into two steps.

Deploy the JDBC driver
  • Go to /bin directory in your Wildfly installation directory.
  • Open a terminal on this directory and run
./jboss-cli.sh --connect controller=127.0.0.1

(or jboss-cli.bat if you are on Windows)

  • To install the module, run this command
module add --name=org.postgresql --resources=/tmp/postgresql-42.2.1.jar --dependencies=javax.api,javax.transaction.api
Create the datasource
  • The driver creation is done with this command
/subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgresql",driver-class-name=org.postgresql.Driver)
  • And then, the last command to add the datasource
data-source add --jndi-name=java:jboss/datasources/StenusysDemoDS --name=StenusysDemoDS --connection-url=jdbc:postgresql://localhost:5432/StenusysDemo --driver-name=postgres --user-name=postgres --password=admin

You can validate your change by testing the connection in the management console.

Conclusion

Here we go !

We have seen the 3 ways to setup connectivity between Wildfly and PostgreSQL. If you want to use another database (Oracle, MySQL,...), just follow the same procedure with the corresponding driver.

The management console is the easiest one, just clicking on a UI. It is well suited for a proof of concept, your local environment or equivalent. It is however not suitable for production environment, where you do not want manual operations like this.

The edition of the configuration files is very common and a good solution. You can add the needed files in the delivery of your application and have it deployed on your server. It works only if you have one application running in the server (you will override the files) and it requires a restart, which is not suitable for high availability services.

The CLI method have multiple advantages: you can easily add the necessary elements to your deployment scripts and do the updates with no outage. It is convenient as well to add this execution to your docker container if you use that technology. It's the best option from a DevOps perspective and respects the principle of Configuration As Code.

Lost in software engineering practices ? We can help !

 Visit our website for more information on our offers