MySQL Configuration

Overview

The Blackjack API uses MySQL with R2DBC (Reactive Relational Database Connectivity) to store player data reactively. MySQL provides reliable persistent storage for player profiles, including names, wins, and losses.

Dependencies

The project uses Spring Data R2DBC with MySQL driver, configured in pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-r2dbc</artifactId>
</dependency>
<dependency>
    <groupId>io.asyncer</groupId>
    <artifactId>r2dbc-mysql</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

Connection Configuration

Local Development

For local development, configure R2DBC connection in application-local.yml:

spring:
  r2dbc:
    url: r2dbc:mysql://localhost:3307/blackjack
    username: blackjack
    password: blackjack

Docker Environment

For Docker deployments, use service names in application-docker.yml:

spring:
  r2dbc:
    url: r2dbc:mysql://mysql:3306/blackjack
    username: blackjack
    password: blackjack

Production Environment

For production, use environment variables in application-prod.yml:

spring:
  r2dbc:
    url: ${SPRING_R2DBC_URL}
    username: ${SPRING_R2DBC_USERNAME}
    password: ${SPRING_R2DBC_PASSWORD}

Configuration Properties

Property	Description	Example
`spring.r2dbc.url`	R2DBC connection URL	`r2dbc:mysql://localhost:3307/blackjack`
`spring.r2dbc.username`	Database username	`blackjack`
`spring.r2dbc.password`	Database password	`blackjack`

R2DBC URL Format

r2dbc:mysql://[host]:[port]/[database][?options]

Common options:

useSSL=false - Disable SSL for local development
serverTimezone=UTC - Set server timezone

Example with options:

r2dbc:mysql://localhost:3307/blackjack?useSSL=false&serverTimezone=UTC

Database Schema

The players table stores player information:

CREATE TABLE players (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  external_id VARCHAR(36) NOT NULL UNIQUE,
  name VARCHAR(30) NOT NULL UNIQUE,
  wins INT NOT NULL,
  losses INT NOT NULL
);

Table Structure

id: Auto-incrementing internal ID (primary key)
external_id: UUID for external references (unique)
name: Player name, 1-30 characters (unique)
wins: Total number of wins
losses: Total number of losses

Entity Model

Players are mapped to R2DBC entities using Spring Data:

@Table("players")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class PlayerRow {

    @Id
    private Long id;

    private String externalId;
    private String name;
    private int wins;
    private int losses;
}

Reactive R2DBC Repository

The application uses Spring Data’s ReactiveCrudRepository for reactive database operations:

public interface SpringDataR2dbcPlayerRepository 
    extends ReactiveCrudRepository<PlayerRow, Long> {
    
    Mono<PlayerRow> findByName(String name);
    Mono<PlayerRow> findByExternalId(String externalId);

    @Query("""
    SELECT *
    FROM players
    ORDER BY (wins - losses) DESC, wins DESC, losses ASC, name ASC
    """)
    Flux<PlayerRow> findRanking();
}

Custom Queries

The ranking query uses custom SQL to order players by:

Win/loss differential (descending)
Total wins (descending)
Total losses (ascending)
Player name (ascending)

Repository Adapter

The adapter pattern connects the R2DBC repository to the domain layer:

@Component
public class MySqlPlayerRepositoryAdapter implements PlayerRepositoryPort {

    private final SpringDataR2dbcPlayerRepository repo;

    public MySqlPlayerRepositoryAdapter(SpringDataR2dbcPlayerRepository repo) {
        this.repo = repo;
    }

    @Override
    public Mono<Player> save(Player player) {
        return repo.findByExternalId(player.id().value())
                .defaultIfEmpty(new PlayerRow(
                        null,
                        player.id().value(),
                        player.name().value(),
                        player.wins(),
                        player.losses()
                ))
                .flatMap(existing -> {
                    existing.setName(player.name().value());
                    existing.setWins(player.wins());
                    existing.setLosses(player.losses());
                    return repo.save(existing).map(this::toDomain);
                });
    }

    @Override
    public Mono<Player> findById(PlayerId id) {
        return repo.findByExternalId(id.value())
                .map(this::toDomain);
    }

    @Override
    public Mono<Player> findOrCreateByName(PlayerName name) {
        return repo.findByName(name.value())
                .map(this::toDomain)
                .switchIfEmpty(Mono.defer(() -> save(Player.newPlayer(name))));
    }

    @Override
    public Flux<Player> findRanking() {
        return repo.findRanking().map(this::toDomain);
    }

    private Player toDomain(PlayerRow row) {
        return new Player(
                new PlayerId(row.getExternalId()),
                new PlayerName(row.getName()),
                row.getWins(),
                row.getLosses()
        );
    }
}

Reactive Operations

All R2DBC operations return reactive types:

Mono<Player> - Single player or empty
Flux<Player> - Stream of multiple players
Reactive operations are non-blocking and composable

Example Operations

Save a player:

Mono<Player> savedPlayer = playerRepository.save(player);

Find player by ID:

Mono<Player> player = playerRepository.findById(playerId);

Find or create player:

Mono<Player> player = playerRepository.findOrCreateByName(playerName);

Get player ranking:

Flux<Player> ranking = playerRepository.findRanking();

MySQL Setup

Local Development with Docker

docker run -d \
  --name blackjack-mysql \
  -p 3307:3306 \
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_DATABASE=blackjack \
  -e MYSQL_USER=blackjack \
  -e MYSQL_PASSWORD=blackjack \
  mysql:8.0

Verify Connection

Connect using MySQL client:

mysql -h localhost -P 3307 -u blackjack -p blackjack

View player data:

SELECT * FROM players;

R2DBC vs JDBC

Why R2DBC?

Feature	JDBC	R2DBC
Blocking	Yes	No
Reactive	No	Yes
Thread Model	One per connection	Event-loop based
Backpressure	No	Yes
Spring WebFlux	Not optimal	Fully compatible

R2DBC enables fully reactive, non-blocking database access that integrates seamlessly with Spring WebFlux.

Best Practices

Use Reactive Types: Always return Mono or Flux for non-blocking operations
Connection Pooling: Configure appropriate pool sizes for your workload
Transactions: Use @Transactional with reactive transaction manager when needed
Error Handling: Use reactive operators like onErrorResume for graceful error handling
Indexes: Create indexes on external_id and name columns for fast lookups
Mapping: Keep entity-to-domain mapping logic in repository adapters

Connection Pool Configuration

For production deployments, configure connection pooling:

spring:
  r2dbc:
    pool:
      initial-size: 10
      max-size: 50
      max-idle-time: 30m
      validation-query: SELECT 1

Troubleshooting

Connection Issues

If R2DBC connection fails:

Verify MySQL is running: docker ps | grep mysql
Check R2DBC URL format (must start with r2dbc:mysql://)
Ensure database exists and credentials are correct
Review application logs for connection errors

Common Errors

“Unknown database”: Create the database before starting the application:

CREATE DATABASE IF NOT EXISTS blackjack;

“Access denied”: Verify username and password in configuration match database user.

Get Started

Architecture

Game Operations

Player Management

Database

Deployment

Development

Overview

Dependencies

Connection Configuration

Local Development

Docker Environment

Production Environment

Configuration Properties

R2DBC URL Format

Database Schema

Table Structure

Entity Model

Reactive R2DBC Repository

Custom Queries

Repository Adapter

Reactive Operations

Example Operations

MySQL Setup

Local Development with Docker

Verify Connection

R2DBC vs JDBC

Why R2DBC?

Best Practices

Connection Pool Configuration

Troubleshooting

Connection Issues

Common Errors

Build docs developers (and LLMs) love

Get Started

Architecture

Game Operations

Player Management

Database

Deployment

Development

​Overview

​Dependencies

​Connection Configuration

​Local Development

​Docker Environment

​Production Environment

​Configuration Properties

​R2DBC URL Format

​Database Schema

​Table Structure

​Entity Model

​Reactive R2DBC Repository

​Custom Queries

​Repository Adapter

​Reactive Operations

​Example Operations

​MySQL Setup

​Local Development with Docker

​Verify Connection

​R2DBC vs JDBC

​Why R2DBC?

​Best Practices

​Connection Pool Configuration

​Troubleshooting

​Connection Issues

​Common Errors

​Related Documentation

Build docs developers (and LLMs) love

Overview

Dependencies

Connection Configuration

Local Development

Docker Environment

Production Environment

Configuration Properties

R2DBC URL Format

Database Schema

Table Structure

Entity Model

Reactive R2DBC Repository

Custom Queries

Repository Adapter

Reactive Operations

Example Operations

MySQL Setup

Local Development with Docker

Verify Connection

R2DBC vs JDBC

Why R2DBC?

Best Practices

Connection Pool Configuration

Troubleshooting

Connection Issues

Common Errors

Related Documentation