Add Datasource to JBoss Wildfly using JBoss CLI

Written by Jamie van Bezooijen

This article describes how to create a MySQL datasource in JBoss Wildfly application server via the CLI. It also covers a simple servlet example where the configured datasource is used.

We use JBoss Wildfly version 8.2.

Sample MySQL database schema

This example uses the following mysql schema:

    CREATE TABLE IF NOT EXISTS `books` (
      `id` int(11) NOT NULL,
      `title` varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    -- Some sample data.
    INSERT INTO `books` (`id`, `title`) VALUES
    (1, 'Java is cool.'),
    (2, 'Clean Code');

To run the example, create a mysql database schema with following configuration:

• Username: books_user
• Password: secret
• Schema: books

Installing the MySQL driver

The first step in creating a MySQL datasource in Wildfly is installing the MySQL driver. First download the jar from the Maven repository. For this example we use the 5.1.34 version. When you have downloaded the jar, remember the location as this is necessary in the JBoss CLI. Next open the JBoss CLI and make sure you are connected.

To install the driver, run the command below:

deploy ~/Downloads/mysql-connector-java-5.1.34.jar

To check if the driver installation was successful, run the following command:

/subsystem=datasources:installed-drivers-list

Creating the datasource

The next step is creating the datasource in JBoss. To list the current datasources present in Wildfly, run the following command:

/subsystem=datasources:read-resource(recursive=true)

For this example, we create a datasource with the following parameters:

• Name: books-datasource
• JNDI-name: /jdbc/books-database
• Host: 127.0.0.1
• Port: 3306
• Username: books_user
• Password: secret
• Schema: books

The command to create this datasource is:

data-source add --name=books-datasource --jndi-name=java:/jdbc/books-database --driver-name=mysql-connector-java-5.1.34.jar_com.mysql.jdbc.Driver_5_1 --connection-url=jdbc:mysql://127.0.0.1:3306/books --user-name=books_user --password=secret

To check whether the datasource is able to connect to the database, run the following command:

/subsystem=datasources/data-source=books-datasource:test-connection-in-pool

When all is OK, it should say:

{
    "outcome" => "success",
    "result" => [true]
}

If it says “Outcome failed”, check the connection details. To remove the faulty datasource, run:

data-source remove --name=books-datasource

Then try again.

Using the datasource

There is a sample project (download url available at the end). It has the following servlet:

package com.squins;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DataSourceServlet extends HttpServlet {

    @Resource(lookup = "java:/jdbc/books-database")
    private DataSource dataSource;

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setContentType("text/html");

        PrintWriter out = response.getWriter();
        out.println("<h1>Hello world!</h1>");

        try (
                Connection connection = dataSource.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) FROM books");
                ResultSet resultSet = preparedStatement.executeQuery();
        ) {

            while (resultSet.next()) {
                out.println("You have " + resultSet.getInt(1) + " record(s) in your table.");
            }
        } catch (SQLException e) {
            throw new IllegalStateException("Failed to fetch number of books", e);
        }
    }

}

To continue, include this servlet in your own war, or deploy the demo war. To deploy the sample war, build the maven project and execute the following command in JBoss CLI:

deploy /path/to/project/target/demo.war

When deployed successfully, open a browser and go to http://localhost:8080/demo/test-datasource. it should output:

Hello world!

You have 2 record(s) in your table.

Congratulations, you have successfully configured your MySQL datasource in the JBoss CLI!

Get code for this example

Download sample code of war that uses the JBoss datasource.

Kees van Dieren, CEO

We've years of experience helping startups and scale-ups to work more efficient by creating apps that are fit their needs.

Let’s talk. We look forward to exploring the opportunity to help your company too.

Go ahead and send us a message. We look forward to exploring the opportunity to help you too.