Advertise with us

Search Mastertheboss

Our eBooks

How to configure a Datasource with JBoss / WildFly

A Datasource is the component used by Enterprise applications to connect to the database. The datasource, in turn, uses a Driver to communicate with the underlying database. For this reason, for WildFly to provide database integration, it needs a Driver and a Datasource.

Out of the box, WildFly ships with a default configuration, which includes the ExampleDS datasource, bound to the "H2" driver. H2 is a Java SQL database, used mainly as an in-memory DB for testing purpose with SQL support.

WildFly automatically recognizes any JDBC 4 compliant driver. For this reason, a driver can be installed as a module (that is, static deployment) or it can be deployed as any normal application.

Install a Datasource as a module on WildFly

For our example, we will use PostgreSQL Database. If you don't have installed the DB on your machine, you can use Docker to start an instance of PostgreSQL:

docker run -it --rm=true --name postgresdb -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 5432:5432 postgres:10.5

The Database will start as soon as you have pulled the Image from PostgreSQL. Then, as we said, we need a driver to connect to PostgreSQL. We can get it directly from the Maven repo

wget https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.9/postgresql-42.2.9.jar

Now we have everything to configure a Datasource on WildFly. Start WildFly application server and connect with the CLI

Creating the Datasource using the CLI

Installing the data source using the Command Line Interface can be used to quickly create the module structure containing the JDBC Driver. It is the recommended option if you plan to create a CLI script so that you can replicate it across your installations. Launch the jboss-cli.sh script and connect as usual.

The following command will install the org.postgres module creating for you the module directory structure with the required configuration:

module add --name=org.postgres --resources=postgresql-42.2.9.jar --dependencies=javax.api,javax.transaction.api

Next, we need to install the JDBC driver using the above defined module:

/subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgres",driver-class-name=org.postgresql.Driver)

Finally, install the data source by using the data-source shortcut command, which requires as input the Pool name, the JNDI bindings, the JDBC Connection parameters and finally the security settings:

data-source add --jndi-name=java:jboss/datasources/PostGreDS --name=PostgrePool --connection-url=jdbc:postgresql://localhost:5432/postgres --driver-name=postgres --user-name=postgres --password=postgres

The outcome of the CLI session is the following structure in the $JBOSS_HOME/modules/org folder:

org
└── postgres
    └── main
        ├── module.xml
        └── postgresql-42.2.9.jar

The modules.xml file has been created with all the requires resources and dependencies needed by the JDBC Driver:

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="org.postgres">

    <resources>
        <resource-root path="postgresql-42.2.9.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

You can check that the datasource has been installed correctly by issuing the following command:

/subsystem=datasources/data-source=PostgrePool:test-connection-in-pool
{
    "outcome" => "success",
    "result" => [true]
}

Creating a Datasource in Domain mode

In order to create a Datasource in domain mode, make sure at first that you have installed the module, as discussed in the earlier section. Next, we need to install the JDBC driver on a server Profile:

/profile=full-ha/subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgres",driver-class-name=org.postgresql.Driver)

Finally, install the data source by using the data-source shortcut command, which requires also the --profile additional option:

data-source add --jndi-name=java:/PostGreDS --name=PostgrePool --connection-url=jdbc:postgresql://localhost:5432/postgres --driver-name=postgres --user-name=postgres --password=postgres --profile=full-ha

Creating an XA Datasource

If you are going to use an XA Datasource in your applications there are some changes that you need to apply to your CLI scripts. Start as usual by creating the module at first:

module add --name=org.postgres --resources=postgresql-42.2.9.jar --dependencies=javax.api,javax.transaction.api

Next, install the JDBC driver using the above module:

/subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgres",driver-class-name=org.postgresql.Driver)

The twist now is to use the xa-data-source shortcut command in order to create the XA Datasource. This command requires that you specify the Datasource name, its JNDI Bindings, the XA Datasource class, the Security settings and, finally, at least one property must be specified (in our case we have specified the Server host name):

xa-data-source add --name=PostGresXA --jndi-name=java:/PostGresXA --driver-name=postgres --xa-datasource-class=org.postgresql.xa.PGXADataSource --user-name=postgres --password=postgres --xa-datasource-properties=[{ServerName=localhost}]

Next, you can add additional properties needed for your Database connections, such as the Database schema:

/subsystem=datasources/xa-data-source=PostGresXA/xa-datasource-properties=DatabaseName:add(value="postgres")

Using the Datasource in your applications

When using ORM frameworks like Hibernate/JPA the Datasource configuration is generally resolved at configuration level, so you don't deal with Datasource or Connection objects directly:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.2"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
    <!-- Define persistence unit -->
    <persistence-unit name="my-persistence-unit">
        <jta-data-source>java:jboss/datasources/PostGreDS</jta-data-source>
    </persistence-unit>
</persistence>

However, a Datasource as any other resource bound in JNDI, can also be looked up and used in your code. As an example, here is a Servlet which uses the PostgreSQL Datasource to perform a simple Database query:

@WebServlet("/demo")
public class DemoServlet extends HttpServlet {

   @Resource(lookup="java:jboss/datasources/PostGreDS")
   private DataSource ds;

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html");
        PrintWriter out = resp.getWriter();
        out.write("<h1>Datasource example</h1>");

        try (Connection con = ds.getConnection();
             PreparedStatement ps = createPreparedStatement(con);
             ResultSet rs = ps.executeQuery()) {

            while(rs.next()) {
                out.write("Time from Database: " +rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
    private PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        String sql = "SELECT NOW();";
        PreparedStatement ps = con.prepareStatement(sql);
        return ps;
    }
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

If you need the source code for this example, it's available on GitHub at: https://github.com/fmarchioni/mastertheboss/tree/master/javaee/datasource-demo

Now that you are able to configure a Datasource, let's learn how to set the attributes of the Connection Pool: WildFly Connection Pool configuration

© 2020 mastertheboss.com. All Rights Reserved.

Please publish modules in offcanvas position.