SpringBoot and MySQL

Spring Boot has become the go‑to framework for building lightweight, production‑ready microservices in Java. When paired with a relational database such as MySQL, it offers a fast path to a fully functional, data‑driven application.
Below is a step‑by‑step guide that covers everything from setting up the development environment to deploying a Docker‑based container, all while keeping the code concise and idiomatic.
Before writing any code, make sure the following components are available on your machine:

• JDK 17+ (OpenJDK is recommended)
• Maven 3.8+ or Gradle 7+ (the examples here use Maven)
• MySQL 8.0+ – create a test database, e.g. springboot_demo
• An IDE such as IntelliJ IDEA or VS Code for quick project scaffolding

If you prefer containers, a single Docker command can spin up a MySQL instance:

docker run --name mysql-demo \
  -e MYSQL_ROOT_PASSWORD=Root@123 \
  -e MYSQL_DATABASE=springboot_demo \
  -p 3306:3306 \
  -d mysql:8.0

2. Scaffold a Spring Boot Project

Use Spring Initializr (https://start.spring.io) to generate a minimal project. Select the following starters:

• Spring Web
• Spring Data JPA
• MySQL Driver
• Spring Boot DevTools (optional, for hot reloading)

After downloading, unzip the archive and open it in your IDE. The pom.xml will contain the core dependencies:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <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>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

3. Configure the Data Source

Add MySQL connection details to src/main/resources/application.yml (or application.properties):

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springboot_demo?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
    username: root
    password: Root@123
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update   # use 'none' in production
    show-sql: true
    properties:
      hibernate:
        format_sql: true

ddl-auto options

4. Define the Entity

Create a simple User entity to illustrate mapping, constraints, and Lombok usage:

package com.example.demo.entity;

import jakarta.persistence.*;
import lombok.*;

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

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 50)
    private String username;

    @Column(nullable = false)
    private String password;

    @Column(name = "email", unique = true, length = 100)
    private String email;

    @Column(name = "created_at", updatable = false)
    private java.time.LocalDateTime createdAt = java.time.LocalDateTime.now();
}

The GenerationType.IDENTITY strategy lets MySQL handle auto‑incrementing primary keys, avoiding extra sequence tables.

5. Create the Repository

Spring Data JPA provides CRUD operations out of the box. Extend JpaRepository and add custom finder methods:

package com.example.demo.repository;

import com.example.demo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
import java.util.Optional;

public interface UserRepository extends JpaRepository<User, Long> {

    Optional<User> findByUsername(String username);

    List<User> findByEmailContaining(String keyword);
}

6. Implement the Service Layer

The service encapsulates business logic, validation, and transaction boundaries:

package com.example.demo.service;

import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
import java.util.Optional;

@Service
public class UserService {

    private final UserRepository userRepo;

    public UserService(UserRepository userRepo) {
        this.userRepo = userRepo;
    }

    @Transactional
    public User createUser(User user) {
        if (userRepo.findByUsername(user.getUsername()).isPresent()) {
            throw new IllegalArgumentException("Username already exists");
        }
        return userRepo.save(user);
    }

    public Optional<User> getUser(Long id) {
        return userRepo.findById(id);
    }

    public List<User> listAll() {
        return userRepo.findAll();
    }

    @Transactional
    public void deleteUser(Long id) {
        userRepo.deleteById(id);
    }
}

7. Expose REST Endpoints

Map service methods to HTTP routes with @RestController:

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/api/users")
public class UserController {

    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    @PostMapping
    public ResponseEntity<User> create(@RequestBody User user) {
        return ResponseEntity.ok(userService.createUser(user));
    }

    @GetMapping("/{id}")
    public ResponseEntity<User> get(@PathVariable Long id) {
        return userService.getUser(id)
                .map(ResponseEntity::ok)
                .orElse(ResponseEntity.notFound().build());
    }

    @GetMapping
    public List<User> list() {
        return userService.listAll();
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> delete(@PathVariable Long id) {
        userService.deleteUser(id);
        return ResponseEntity.noContent().build();
    }
}

Run the application and test the endpoints with Postman or curl:

curl -X POST http://localhost:8080/api/users \
     -H "Content-Type: application/json" \
     -d '{"username":"alice","password":"pass123","email":"alice@example.com"}'

8. Common Issues & Performance Tuning

8.1 Connection Pool Settings

Spring Boot uses HikariCP by default. Adjust pool size for higher concurrency:

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
      connection-timeout: 30000

8.2 Avoiding N+1 Queries

When loading related entities, use @EntityGraph or eager fetching sparingly:

@EntityGraph(attributePaths = {"roles", "profile"})
List<User> findAll();

8.3 Pagination & Sorting

Leverage Spring Data’s paging support:

Page<User> page = userRepository.findAll(
    PageRequest.of(0, 10, Sort.by(Sort.Direction.DESC, "createdAt"))
);

8.4 SQL Logging & Slow Query Detection

• spring.jpa.show-sql=true prints every executed statement.
• Enable MySQL’s slow_query_log to capture queries exceeding a threshold, aiding in pinpointing bottlenecks.

8.5 Transaction Management

By default, @Transactional rolls back on unchecked exceptions. To include checked exceptions:

@Transactional(rollbackFor = Exception.class)
public void riskyOperation() { … }

9. Build & Deploy

9.1 Maven Packaging

mvn clean package
java -jar target/demo-0.0.1-SNAPSHOT.jar --spring.datasource.password=NewPass

9.2 Docker Containerization

Create a Dockerfile:

FROM openjdk:17-jdk-slim
VOLUME /tmp
COPY target/demo-0.0.1-SNAPSHOT.jar app.jar
ENTRYPOINT ["java","-jar","/app.jar"]

Build and run:

docker build -t springboot-mysql-demo .
docker run -d -p 8080:8080 --name sb-demo springboot-mysql-demo

10. Takeaway

This walkthrough covered:

1. Environment setup and project scaffolding
2. Data source and JPA configuration
3. Entity, repository, service, and controller layers
4. Performance tuning for connection pools, N+1 avoidance, pagination, and transaction handling
5. Packaging into a JAR and deploying via Docker

With these building blocks, you can rapidly prototype a Spring Boot application backed by MySQL, then iterate and optimize for production workloads. Happy coding!