Spring Boot with MySQL

MySQL is widely used in enterprise applications. This chapter covers MySQL setup with Docker, Spring Boot configuration, and key differences from PostgreSQL.

---

MySQL with Docker

docker run -d \
  --name bookstore-mysql \
  -e MYSQL_DATABASE=bookstore \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_USER=admin \
  -e MYSQL_PASSWORD=secret \
  -p 3306:3306 \
  mysql:8.3

Docker Compose:

services:
  mysql:
    image: mysql:8.3
    environment:
      MYSQL_DATABASE: bookstore
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_USER: admin
      MYSQL_PASSWORD: secret
    ports:
      - "3306:3306"
    volumes:
      - mysqldata:/var/lib/mysql

volumes:
  mysqldata:

---

Spring Boot Configuration

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

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/bookstore?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: admin
    password: secret
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQLDialect

---

MySQL vs PostgreSQL

Feature	PostgreSQL	MySQL
JSONB	✅ Native, indexed	JSON (no JSONB)
SERIAL/IDENTITY	BIGSERIAL	BIGINT AUTO_INCREMENT
Case sensitivity	Case-sensitive by default	Case-insensitive (table names)
Full-text search	Built-in tsvector	FULLTEXT index
Upsert	ON CONFLICT	ON DUPLICATE KEY UPDATE

---

Flyway Migration (MySQL)

-- V1__create_books_table.sql
CREATE TABLE books (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(200) NOT NULL,
    isbn VARCHAR(17) UNIQUE,
    price DECIMAL(8, 2),
    status ENUM('DRAFT', 'PUBLISHED', 'OUT_OF_PRINT') DEFAULT 'DRAFT',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_author (author),
    INDEX idx_isbn (isbn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Always use ENGINE=InnoDB (transactions) and utf8mb4 (full Unicode).

---

MySQL-Specific JPA Config

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQLDialect
        # Batch inserts for performance
        jdbc:
          batch_size: 50
        order_inserts: true
        order_updates: true

---

Key Takeaways

1. Use mysql-connector-j (not the old mysql-connector-java).
2. Add ?serverTimezone=UTC to the JDBC URL.
3. Use InnoDB engine and utf8mb4 charset.
4. PostgreSQL is generally preferred for new projects — better JSONB, better standards compliance.
5. Switch between databases by changing profile config and driver dependency.

---

What’s Next?

Next: Spring Boot with MongoDB — NoSQL document database.