How to configure a Datasource programmatically

This article covers how to define a Datasource resource programmatically using the DataSourceDefinition annotation. Then, we will show to to use the Datasource to connect to a Relational Database and execute SQL Statements.

A Datasource object is an interface to a Relational Database. It is commonly used in Enterprise applications to fetch Connections from a pool of active database connections.

A Datasource is also an Enterprise resource, therefore you can use the @Resource annotation to reference a Datasource available in your configuration:

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

However, for testing purposes, it is convenient to define a Datasource object programmatically through the @DatasourceDefinition annotation.

The following example shows how to define a DataSourceDefinition and use a Datasource which connects to an H2 Database and execute some SQL Statements:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import jakarta.annotation.PostConstruct;
import jakarta.annotation.PreDestroy;
import jakarta.annotation.Resource;
import jakarta.annotation.sql.DataSourceDefinition;
import jakarta.ejb.Singleton;
import jakarta.ejb.Startup;
import jakarta.inject.Inject;
import javax.sql.DataSource;

@DataSourceDefinition (
        className="org.hsqldb.jdbcDriver",
        name = "java:global/datasource/sampledb",
        user="sa",
        password="",
        databaseName="sampledb",
        properties = {"connectionAttributes=;create=true"},
        url = "jdbc:hsqldb:mem:sampledb"
)
@Singleton
@Startup
public class LoadDatabase {

    @Resource(lookup="java:global/datasource/sampledb")
    private DataSource dataSource;

    @PostConstruct
    public void init() {
        executeSQL(dataSource, "CREATE TABLE Publisher (id INT NOT NULL, title VARCHAR(50) NOT NULL, author VARCHAR(20) NOT NULL,  submission_date DATE) ");
    }

    @PreDestroy
    public void destroy() {
        try {
            executeSQL(dataSource, "DROP TABLE IF EXISTS Publisher");
           
        } catch (Exception e) {
           
        }
    }

    private void executeSQL(DataSource dataSource, String query) {
        try (Connection connection = dataSource.getConnection()) {
            try (PreparedStatement statement = connection.prepareStatement(query)) {
                statement.executeUpdate();
            }
        } catch (SQLException e) {
            // do nothing
        }
    }

}

This sample Class is a @Startup Bean so the Application Server will load it at start up. More about the @Startup Service here: How to create an EJB Startup Service