Easy database access with Apache DeltaSpike
Published: 2021-06-28 14:29:01

[Apache DeltaSpike](https://deltaspike.apache.org/) is a project that includes several modules for increased productivity when developing Java applications that use CDI (from the [Jakarta EE](https://jakarta.ee) specifications). This tutorial shows how to set up a MySQL database, define a connection resource, configure JPA, and use the Data module of Apache DeltaSpike to access the database.

You can watch a version of this tutorial on [YouTube](https://www.youtube.com/watch?v=7JcVXpWcdvw) if you prefer:

{% youtube 7JcVXpWcdvw %}

## Setting up the database
We need something to play with, so let's connect to MySQL: and create a database with a table in it:

```sql
mysql -u root -p

create database jakartaee;
use jakartaee

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255),
    birth_date DATE,
    PRIMARY KEY (id)
);
```

Let's also insert some sample data:

```sql
INSERT INTO users(email, birth_date) VALUES("test1@test.com", "2000-10-15");
INSERT INTO users(email, birth_date) VALUES("test2@test.com", "2001-11-16");
INSERT INTO users(email, birth_date) VALUES("test3@test.com", "2002-12-17");
```

## Setting up the connection in the server

When you develop a Jakarta EE (or Java EE) application you deploy it to a compatible server. There are many Jakarta EE implementations available. For example Eclipse GlassFish, Oracle WebLogic Server, WildFly, Apache TomEE, Payara Server, IBM WebSphere Liberty, JBoss Enterprise Application Platform, and others. Configurations are done in different ways depending on the server you use. This includes database connections.

In Jakarta EE compatible implementations (or servers) you can define resources such as database connection pools using graphical user interfaces or configuration files. This allows you to decouple the resource definition from the application code, which makes sense, for example, if you realize that you most likely use different databases in your development machine and the production environment. In this tutorial, we'll use Apache TomeEE as a Maven plugin. This is useful during development because you don't have to manually download and install the server making it easier to set up the development environment.

You can add the Apache TomEE Maven plugin in <build> section of the `pom.xml` file:

```xml
<build>
    ...
    <plugins>
        <plugin>
            <groupId>org.apache.tomee.maven</groupId>
            <artifactId>tomee-maven-plugin</artifactId>
            <version>8.0.7</version>
            <configuration>
                <context>ROOT</context>
                <libs>mysql:mysql-connector-java:8.0.25</libs>
            </configuration>
        </plugin>
	</plugins>
</build>
```

Notice how we also add the MySQL JDBC driver dependency to the configuration of the TomEE Maven plugin.

To define the connection resource for the TomEE server, create a new `tomee.xml` configuration file inside the `src/main/tomee/conf/` directory. Place the following content in the new file:

```xml
<tomee>
    <Resource id="mysqlResource" type="DataSource">
        JdbcDriver com.mysql.jdbc.Driver
        JdbcUrl jdbc:mysql://localhost/jakartaee
        UserName root
        Password password
    </Resource>
</tomee>
```

Replace the username and password accordingly.

With all this in place, the server is able to create a connection pool that can be referenced by name (`mysqlResource`) in the application code.

## Referencing the database resource from the application code

The first thing we need is a Persistence Unit, which defines a collection of classes (or entities) managed by an `EntityManger` (that we'll create later), and a data source (the database connection). Create a new `persistence.xml` file in the `src/main/resource/META-INF/` directory and add the following to the file:

```xml
<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">

    <persistence-unit name="jakarataee-pu" transaction-type="RESOURCE_LOCAL">
        <jta-data-source>mysqlResource</jta-data-source>
        <class>com.example.app.User</class>
    </persistence-unit>

</persistence>
```

As you can see, we are using the name of the database resource we defined in the TomEE server. A production server should define a data source with the same name so you don't have to worry about the configuration details of the database when you deploy the application. As long as there's a data source with the name `mysqlResoure`, the application will be able to connect to it.

## Using Apache DeltaSpike Data

The Data module of Apache DeltaSpike allows us to define repository interfaces with query methods without having to implement them. Let's take advantage of this.

First, add the dependencies to the `pom.xml` file:

```xml
<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.apache.deltaspike.distribution</groupId>
            <artifactId>distributions-bom</artifactId>
            <version>1.9.4</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

<dependencies>
    <dependency>
        <groupId>org.apache.deltaspike.core</groupId>
        <artifactId>deltaspike-core-api</artifactId>
        <scope>compile</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.deltaspike.core</groupId>
        <artifactId>deltaspike-core-impl</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.deltaspike.modules</groupId>
        <artifactId>deltaspike-data-module-api</artifactId>
        <scope>compile</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.deltaspike.modules</groupId>
        <artifactId>deltaspike-data-module-impl</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>
```

Then, create a CDI producer that creates new `EntityManager` instances used by Apache DeltaSpike:

```java
import javax.enterprise.inject.Disposes;
import javax.enterprise.inject.Produces;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceUnit;

public class EntityManagerProducer {

    @PersistenceUnit(name = "jakarataee-pu")
    private EntityManagerFactory emf;

    @Produces // you can also make this @RequestScoped
    public EntityManager create() {
        return emf.createEntityManager();
    }

    public void close(@Disposes EntityManager em) {
        if (em.isOpen()) {
            em.close();
        }
    }
}
```

Notice how we used the name of the Persistence Unit we previously defined in the `persistence.xml` file.

Now, implement the `User` entity mapped to the `users` SQL table:

```java
import javax.persistence.*;
import java.time.LocalDate;
import java.util.Objects;

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "email")
    private String email;

    @Column(name = "birth_date")
    private LocalDate birthDate;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return Objects.equals(id, user.id);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public LocalDate getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(LocalDate birthDate) {
        this.birthDate = birthDate;
    }
}
```

Finally, add the following repository interface:

```java
import org.apache.deltaspike.data.api.EntityRepository;
import org.apache.deltaspike.data.api.Repository;

@Repository
public interface UserRepository extends EntityRepository<User, Integer> {
}
```

We don't need to implement this interface. Instead, Apache DeltaSpike Data will create instances of this type and dynamically add query methods to it at runtime. In fact, the `UserRepository` interface inherits useful methods already. For example, we can create a [Vaadin Flow](https://vaadin.com/flow) view that uses this repository to show a notification in the browser with the count of users in the database:

```java
import com.vaadin.flow.component.notification.Notification;
import com.vaadin.flow.component.orderedlayout.VerticalLayout;
import com.vaadin.flow.router.Route;

import javax.inject.Inject;

@Route("")
public class MainView extends VerticalLayout {

    @Inject
    public MainView(UserRepository userRepository) {
        Long count = userRepository.count();
        Notification.show("Users: " + count);
    }

}
```

You can run the application using `mvn package tomee:run` and request it at [http://localhost:8080](http://localhost:8080). Here's a screenshot:

![A Vaadin Flow view connected consuming data from MySQL through an Apache DeltaSpike repository](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n4z9sh76ele8tf3orse2.png)