How to connect to MySQL 8 in a Helidon project?

To connect to a MySQL 8 database in a Helidon project, you can use the Helidon SE (Helidon Server Edition) framework. Helidon provides a convenient way to configure and use database connections with JDBC. Here’s a step-by-step guide to connecting to MySQL 8 in a Helidon SE project:

Step 1: Create a Helidon SE Project

If you don’t already have a Helidon SE project, you can create one using the Helidon CLI tool or your preferred IDE. For this tutorial, we’ll use the Helidon CLI:

helidon init my-helidon-project
cd my-helidon-project

This command initializes a Helidon SE project named my-helidon-project.

Step 2: Add Database Configuration

In your Helidon SE project, create a conf directory if it doesn’t already exist, and add a application.yaml file within it. This file will hold your MySQL database configuration. Replace the placeholders with your MySQL database information:

# Database Configuration
db:
  dataSource:
    url: jdbc:mysql://your_database_host:3306/your_database_name
    username: your_username
    password: your_password
  jdbcUrl: jdbc:mysql://your_database_host:3306/your_database_name
  driverClassName: com.mysql.cj.jdbc.Driver
  poolName: MySQLPool
  connectionTimeoutMillis: 20000
  idleTimeoutMinutes: 10
  maxLifetimeMinutes: 30
  maxPoolSize: 20

Step 3: Configure Database Connection

In your Helidon SE application, create a configuration class to read the database configuration from the application.yaml file. For example:

import io.helidon.config.Config;
import io.helidon.config.ConfigValue;

public class DatabaseConfig {

    private final String url;
    private final String username;
    private final String password;
    private final String driverClassName;

    public DatabaseConfig(Config config) {
        Config dbConfig = config.get("db");
        this.url = dbConfig.get("dataSource.url").asString().get();
        this.username = dbConfig.get("dataSource.username").asString().get();
        this.password = dbConfig.get("dataSource.password").asString().get();
        this.driverClassName = dbConfig.get("driverClassName").asString().get();
    }

    public String getUrl() {
        return url;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public String getDriverClassName() {
        return driverClassName;
    }
}

Step 4: Set Up a Database Client

In your Helidon SE application, create a database client class that initializes and manages database connections using the Helidon DbClient class. Here’s an example:

import io.helidon.dbclient.DbClient;
import io.helidon.dbclient.DbClientProvider;
import io.helidon.dbclient.DbExecute;

import javax.inject.Inject;
import javax.inject.Singleton;

@Singleton
public class DatabaseClient {

    private final DbClient dbClient;

    @Inject
    public DatabaseClient(DatabaseConfig databaseConfig) {
        dbClient = DbClientProvider.builder()
                .config(databaseConfig.getUrl())
                .build();
    }

    public DbExecute execute() {
        return dbClient.execute();
    }
}

Step 5: Use the Database Client

You can now use the DatabaseClient class to interact with your MySQL 8 database in your Helidon SE application. For example, you can inject it into your resources and use it to execute database queries.

import io.helidon.dbclient.DbExecute;
import io.helidon.dbclient.DbRow;

import javax.inject.Inject;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.util.List;

@Path("/example")
public class ExampleResource {

    private final DatabaseClient databaseClient;

    @Inject
    public ExampleResource(DatabaseClient databaseClient) {
        this.databaseClient = databaseClient;
    }

    @GET
    public List<String> getExampleData() {
        DbExecute execute = databaseClient.execute();
        return execute
                .namedQuery("SELECT name FROM your_table_name")
                .toList(DbRow::asString)
                .await();
    }
}

Step 6: Run Your Helidon SE Application

You can run your Helidon SE application using the following command:

mvn clean package
java -jar target/my-helidon-project.jar

Your Helidon SE application will start, and you can access the defined endpoint (/example in this example) to interact with your MySQL 8 database.

This step-by-step guide should help you connect to a MySQL 8 database in a Helidon SE project. Make sure to replace the placeholders in the application.yaml file with your actual database information. You can expand your application by adding more endpoints and database operations as needed.