Spring Boot

MySQL Integration

MySQL is the most widely used relational database in Spring Boot applications. Spring Boot auto-configures a HikariCP connection pool, Hibernate dialect, and Spring Data JPA repositories with a single starter dependency and a datasource URL. This entry covers setup, connection pooling, dialect configuration, migrations, and MySQL-specific query patterns.

Dependencies and Setup

Adding the MySQL JDBC driver and spring-boot-starter-data-jpa to the classpath is all that is required. Spring Boot auto-configures Hibernate, HikariCP, and the MySQL dialect from the datasource URL.

XML

<!-- pom.xml: -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

# ── application.yml — minimum configuration: ─────────────────────────
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
    username: root
    password: ${DB_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    open-in-view: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        format_sql: true

# ── Connection URL parameters: ────────────────────────────────────────
# useSSL=false           — disable SSL for local dev (enable in prod)
# serverTimezone=UTC     — avoid timezone mismatch errors
# useUnicode=true        — enable Unicode support
# characterEncoding=UTF-8
# allowPublicKeyRetrieval=true  — required for some MySQL 8 auth configs
# rewriteBatchedStatements=true — improves batch INSERT performance

# ── Production URL: ───────────────────────────────────────────────────
spring:
  datasource:
    url: >-
      jdbc:mysql://prod-db.internal:3306/mydb
      ?useSSL=true
      &requireSSL=true
      &serverTimezone=UTC
      &useUnicode=true
      &characterEncoding=UTF-8
      &rewriteBatchedStatements=true

HikariCP Connection Pool

Spring Boot uses HikariCP as the default connection pool. HikariCP is fast and production-ready with sensible defaults. Tuning the pool size to match the database's max_connections and the application's concurrency is the single most impactful configuration choice.

yaml

# ── application.yml — HikariCP tuning: ───────────────────────────────
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
    username: root
    password: ${DB_PASSWORD}
    hikari:
      # Pool sizing — set to (CPU cores * 2) + effective disk spindles
      # For most cloud VMs: 10 is a good starting point
      maximum-pool-size: 10
      minimum-idle: 5

      # Timeouts:
      connection-timeout: 20000    # max wait for a connection (ms) — default 30s
      idle-timeout: 300000         # how long idle connections live (ms) — default 10m
      max-lifetime: 1200000        # max connection lifetime (ms) — default 30m
                                   # must be less than MySQL wait_timeout
      keepalive-time: 60000        # send keepalive to prevent idle disconnect

      # Naming (appears in logs and JMX):
      pool-name: myapp-db-pool

      # Connection validation:
      connection-test-query: SELECT 1   # MySQL-specific keepalive query
      validation-timeout: 3000

      # Leak detection (dev only — logs if connection held > threshold):
      leak-detection-threshold: 5000   # 5 seconds

# ── MySQL wait_timeout interaction: ───────────────────────────────────
# MySQL drops idle connections after wait_timeout (default 8 hours).
# Set max-lifetime below the MySQL wait_timeout to prevent
# "Communications link failure" errors on connection reuse.
# Rule: max-lifetime < MySQL wait_timeout - 1 minute

# ── Check pool settings at runtime (Actuator): ────────────────────────
management:
  endpoints:
    web:
      exposure:
        include: health,metrics
# GET /actuator/metrics/hikaricp.connections.active
# GET /actuator/metrics/hikaricp.connections.pending

Schema Management with Flyway

Flyway manages database schema evolution through versioned SQL migration scripts. It is the recommended approach for production MySQL schemas — DDL changes are version-controlled, repeatable, and auditable.

XML

<!-- pom.xml — Flyway dependency: -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

# ── application.yml — Flyway configuration: ───────────────────────────
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration      # migration script location
    baseline-on-migrate: true              # use when adding Flyway to existing DB
    validate-on-migrate: true              # fail if checksums don't match
    out-of-order: false                    # reject out-of-sequence migrations
  jpa:
    hibernate:
      ddl-auto: validate                   # let Flyway manage DDL, not Hibernate

# ── Migration script naming convention: ───────────────────────────────
# V{version}__{description}.sql
# src/main/resources/db/migration/
# ├── V1__create_users_table.sql
# ├── V2__create_orders_table.sql
# ├── V3__add_user_role_column.sql
# └── V4__create_indexes.sql

# ── V1__create_users_table.sql: ───────────────────────────────────────
CREATE TABLE users (
    id          BIGINT          NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100)    NOT NULL,
    email       VARCHAR(255)    NOT NULL,
    role        VARCHAR(20)     NOT NULL DEFAULT 'USER',
    active      TINYINT(1)      NOT NULL DEFAULT 1,
    created_at  DATETIME(6)     NOT NULL,
    updated_at  DATETIME(6)     NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_users_email (email),
    INDEX idx_users_role (role),
    INDEX idx_users_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# ── V3__add_user_role_column.sql: ─────────────────────────────────────
ALTER TABLE users
    ADD COLUMN bio TEXT NULL AFTER email,
    ADD INDEX idx_users_active (active);

MySQL-Specific JPA Mappings

MySQL has specific data types and behaviours that affect JPA entity mappings. UTF-8 encoding, JSON columns, full-text indexes, and AUTO_INCREMENT generation all require specific configuration.

Java

@Entity
@Table(
    name = "products",
    indexes = {
        @Index(name = "idx_products_category", columnList = "category"),
        @Index(name = "idx_products_active_price", columnList = "active, price")
    }
)
public class Product {

    // AUTO_INCREMENT — maps to GenerationType.IDENTITY in MySQL:
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // MySQL VARCHAR with explicit length:
    @Column(nullable = false, length = 200)
    private String name;

    // TEXT for longer strings:
    @Column(columnDefinition = "TEXT")
    private String description;

    // DECIMAL for monetary values — never use FLOAT/DOUBLE for money:
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    // MySQL TINYINT(1) for booleans:
    @Column(nullable = false)
    private boolean active = true;

    // MySQL JSON column (MySQL 5.7.8+):
    @Column(columnDefinition = "JSON")
    @Convert(converter = JsonAttributeConverter.class)
    private Map<String, Object> metadata;

    // DATETIME(6) for microsecond precision:
    @Column(nullable = false, updatable = false,
            columnDefinition = "DATETIME(6)")
    private LocalDateTime createdAt;

    @PrePersist
    void onCreate() { createdAt = LocalDateTime.now(); }
}

// ── JSON attribute converter: ─────────────────────────────────────────
@Converter
public class JsonAttributeConverter
        implements AttributeConverter<Map<String, Object>, String> {

    private static final ObjectMapper mapper = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(Map<String, Object> attribute) {
        try { return attribute == null ? null : mapper.writeValueAsString(attribute); }
        catch (JsonProcessingException e) { throw new RuntimeException(e); }
    }

    @Override
    public Map<String, Object> convertToEntityAttribute(String dbData) {
        try { return dbData == null ? null : mapper.readValue(dbData, Map.class); }
        catch (JsonProcessingException e) { throw new RuntimeException(e); }
    }
}

MySQL-Specific Native Queries

MySQL-specific SQL features — ON DUPLICATE KEY UPDATE, FULLTEXT search, JSON functions, and window functions — require native queries since JPQL does not support them.

Java

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // ── MySQL UPSERT (ON DUPLICATE KEY UPDATE): ────────────────────────
    @Modifying
    @Transactional
    @Query(value = """
            INSERT INTO product_views (product_id, view_date, view_count)
            VALUES (:productId, CURDATE(), 1)
            ON DUPLICATE KEY UPDATE
                view_count = view_count + 1,
                updated_at = NOW()
            """,
           nativeQuery = true)
    void incrementDailyView(@Param("productId") Long productId);

    // ── MySQL FULLTEXT search (requires FULLTEXT index on name, description):
    @Query(value = """
            SELECT * FROM products
            WHERE MATCH(name, description) AGAINST (:query IN NATURAL LANGUAGE MODE)
              AND active = true
            ORDER BY MATCH(name, description) AGAINST (:query) DESC
            """,
           nativeQuery = true)
    List<Product> fullTextSearch(@Param("query") String query);

    // ── MySQL JSON functions: ──────────────────────────────────────────
    @Query(value = """
            SELECT * FROM products
            WHERE JSON_EXTRACT(metadata, '$.featured') = true
            """,
           nativeQuery = true)
    List<Product> findFeatured();

    // ── MySQL window functions (8.0+): ────────────────────────────────
    @Query(value = """
            SELECT p.*,
                   RANK() OVER (PARTITION BY p.category ORDER BY p.price ASC)
                       AS price_rank
            FROM products p
            WHERE p.active = true
            """,
           nativeQuery = true)
    List<Object[]> findWithPriceRank();

    // ── MySQL INSERT IGNORE (skip duplicates silently): ────────────────
    @Modifying
    @Transactional
    @Query(value = "INSERT IGNORE INTO user_tags (user_id, tag_id) VALUES (:userId, :tagId)",
           nativeQuery = true)
    void addTagIgnoreDuplicate(@Param("userId") Long userId,
                               @Param("tagId") Long tagId);
}

Docker and Testing

Running MySQL in Docker for local development and using Testcontainers for integration tests are the standard approaches for consistent, reproducible environments.

yaml

# ── docker-compose.yml — MySQL for local development: ─────────────────
services:
  mysql:
    image: mysql:8.3
    container_name: myapp-mysql
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydb
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d  # runs .sql files on first start
    command: --character-set-server=utf8mb4
             --collation-server=utf8mb4_unicode_ci

volumes:
  mysql_data:

# ── application-dev.yml (profile for local Docker MySQL): ─────────────
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC
    username: appuser
    password: apppassword
  jpa:
    hibernate:
      ddl-auto: create-drop  # recreate schema on each start in dev

# ── Testcontainers — real MySQL in integration tests: ─────────────────
<!-- pom.xml: -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>mysql</artifactId>
    <scope>test</scope>
</dependency>

@SpringBootTest
@Testcontainers
class UserRepositoryTest {

    @Container
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.3")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", mysql::getJdbcUrl);
        registry.add("spring.datasource.username", mysql::getUsername);
        registry.add("spring.datasource.password", mysql::getPassword);
    }

    @Autowired
    private UserRepository userRepository;

    @Test
    void savesAndRetrievesUser() {
        User user = new User("Alice", "alice@example.com");
        userRepository.save(user);
        assertThat(userRepository.findByEmail("alice@example.com")).isPresent();
    }
}

Criteria API

PostgreSQL Integration