Spring JDBC và JdbcTemplate

01-jdbctemplate/
  docker-compose.yml           MySQL 8.4, database shop, user app (cổng 3307)
  pom.xml                       parent Boot 4.1.1, starter jdbc, mysql-connector-j, test, testcontainers
  src/main/java/dev/canhdominich/boot/jdbc/
    JdbcApplication.java        @SpringBootApplication
    Student.java                record sinh viên
    StudentDao.java             truy cập dữ liệu bằng JdbcTemplate
    RegistrationService.java    hai phương thức: có và không có @Transactional
    JdbcTemplateDemo.java       chạy mọi thí nghiệm

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>4.1.1</version>
        <relativePath/>
    </parent>

    <groupId>dev.canhdominich.javabackend</groupId>
    <artifactId>spring-jdbctemplate</artifactId>
    <version>1.0.0</version>
    <name>Spring JDBC - JdbcTemplate</name>

    <properties>
        <java.version>17</java.version>
        <main.class>dev.canhdominich.boot.jdbc.JdbcTemplateDemo</main.class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</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-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers-mysql</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <configuration>
                    <executable>java</executable>
                    <arguments>
                        <argument>-classpath</argument>
                        <classpath/>
                        <argument>${main.class}</argument>
                    </arguments>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

services:
  mysql:
    image: mysql:8.4
    container_name: jdbc-lab-mysql
    environment:
      MYSQL_ROOT_PASSWORD: root_pw
      MYSQL_DATABASE: shop
      MYSQL_USER: app
      MYSQL_PASSWORD: app_pw
    ports:
      - "${MYSQL_PORT:-3307}:3306"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "-uroot", "-proot_pw"]
      interval: 3s
      timeout: 3s
      retries: 30

docker compose up -d
docker compose ps

package dev.canhdominich.boot.jdbc;

public record Student(Long id, String name, String email) {
}

package dev.canhdominich.boot.jdbc;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.List;
import java.util.Optional;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

@Repository
public class StudentDao {

    private final JdbcTemplate jdbc;

    public StudentDao(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    public void createTable() {
        jdbc.execute("drop table if exists student");
        jdbc.execute("create table student (id bigint auto_increment primary key, name varchar(100) not null, email varchar(100) not null unique)");
    }

    public long insert(String name, String email) {
        KeyHolder keys = new GeneratedKeyHolder();
        jdbc.update(connection -> {
            PreparedStatement statement = connection.prepareStatement("insert into student (name, email) values (?, ?)", Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, name);
            statement.setString(2, email);
            return statement;
        }, keys);
        return keys.getKey().longValue();
    }

    public Student findById(long id) {
        return jdbc.queryForObject("select id, name, email from student where id = ?", (rs, row) -> new Student(rs.getLong("id"), rs.getString("name"), rs.getString("email")), id);
    }

    public Optional<Student> findByEmail(String email) {
        try {
            return Optional.of(jdbc.queryForObject("select id, name, email from student where email = ?", (rs, row) -> new Student(rs.getLong("id"), rs.getString("name"), rs.getString("email")), email));
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }

    public List<Student> findAll() {
        return jdbc.query("select id, name, email from student order by id", (rs, row) -> new Student(rs.getLong("id"), rs.getString("name"), rs.getString("email")));
    }

    public int rename(long id, String name) {
        return jdbc.update("update student set name = ? where id = ?", name, id);
    }

    public int count() {
        return jdbc.queryForObject("select count(*) from student", Integer.class);
    }
}

package dev.canhdominich.boot.jdbc;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class JdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(JdbcApplication.class, args);
    }
}

package dev.canhdominich.boot.jdbc;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class RegistrationService {

    private final StudentDao dao;

    public RegistrationService(StudentDao dao) {
        this.dao = dao;
    }

    public void registerTwoWithoutTransaction(String first, String second) {
        dao.insert(first, first + "@example.com");
        throw new IllegalStateException("failure after the first insert");
    }

    @Transactional
    public void registerTwoInTransaction(String first, String second) {
        dao.insert(first, first + "@example.com");
        throw new IllegalStateException("failure after the first insert");
    }
}

package dev.canhdominich.boot.jdbc;

import com.zaxxer.hikari.HikariDataSource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.boot.SpringApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateDemo {

    public static void main(String[] args) {
        run(env("JDBC_URL", "jdbc:mysql://localhost:3307/shop"), env("DB_USER", "app"), env("DB_PASSWORD", "app_pw"));
    }

    public static List<String> run(String url, String user, String password) {
        Map<String, Object> properties = new HashMap<>();
        properties.put("spring.datasource.url", url);
        properties.put("spring.datasource.username", user);
        properties.put("spring.datasource.password", password);
        properties.put("spring.main.web-application-type", "none");
        properties.put("spring.main.banner-mode", "off");
        properties.put("logging.level.root", "OFF");
        SpringApplication application = new SpringApplication(JdbcApplication.class);
        application.setDefaultProperties(properties);
        List<String> lines = new ArrayList<>();
        try (ConfigurableApplicationContext context = application.run()) {
            StudentDao dao = context.getBean(StudentDao.class);
            JdbcTemplate jdbc = context.getBean(JdbcTemplate.class);
            RegistrationService registration = context.getBean(RegistrationService.class);
            HikariDataSource pool = (HikariDataSource) context.getBean(DataSource.class);
            dao.createTable();
            lines.add("-- writing and reading");
            long an = dao.insert("An", "an@example.com");
            long binh = dao.insert("Binh", "binh@example.com");
            lines.add("generated keys: " + an + ", " + binh);
            lines.add("findById(" + an + ") -> " + dao.findById(an));
            lines.add("findAll -> " + dao.findAll().size() + " students");
            lines.add("rename existing id -> rows updated " + dao.rename(an, "An Nguyen"));
            lines.add("rename missing id -> rows updated " + dao.rename(9999, "Nobody"));
            lines.add("count -> " + dao.count());
            lines.add("-- connections: borrowed and returned for you");
            lines.add("active connections after the calls above: " + pool.getHikariPoolMXBean().getActiveConnections());
            lines.add("-- exceptions with a clear meaning");
            lines.add("queryForObject, no row -> " + describe(() -> dao.findById(9999)));
            lines.add("findByEmail wrapper for the same case -> " + dao.findByEmail("nobody@example.com"));
            lines.add("queryForObject, two rows -> " + describe(() -> jdbc.queryForObject("select name from student where name like ?", String.class, "%n%")));
            lines.add("duplicate email -> " + describe(() -> dao.insert("Another", "an@example.com")));
            lines.add("bad SQL -> " + describe(() -> jdbc.queryForObject("select nope from student", Integer.class)));
            jdbc.setQueryTimeout(1);
            lines.add("query timeout of 1 s on select sleep(3) -> " + describe(() -> jdbc.queryForObject("select sleep(3)", Integer.class)));
            jdbc.setQueryTimeout(-1);
            lines.add("-- transactions");
            int before = dao.count();
            lines.add("without @Transactional, failure after one insert: " + describe(() -> registration.registerTwoWithoutTransaction("c1", "c2")) + "; rows added " + (dao.count() - before));
            int middle = dao.count();
            lines.add("with @Transactional, same failure: " + describe(() -> registration.registerTwoInTransaction("d1", "d2")) + "; rows added " + (dao.count() - middle));
            lines.add("active connections at the end: " + pool.getHikariPoolMXBean().getActiveConnections());
        }
        lines.forEach(System.out::println);
        return lines;
    }

    private static String describe(Runnable action) {
        try {
            action.run();
            return "no exception";
        } catch (RuntimeException e) {
            Throwable root = e;
            while (root.getCause() != null) {
                root = root.getCause();
            }
            String message = e.getMessage() == null ? "" : e.getMessage().split("\n")[0];
            if (message.length() > 110) {
                message = message.substring(0, 110) + "...";
            }
            return e.getClass().getSimpleName() + " (root cause " + root.getClass().getSimpleName() + ")" + (message.isEmpty() ? "" : ": " + message);
        }
    }

    private static String env(String name, String fallback) {
        String value = System.getenv(name);
        return value == null || value.isBlank() ? fallback : value;
    }
}

-- writing and reading
generated keys: 1, 2
findById(1) -> Student[id=1, name=An, email=an@example.com]
findAll -> 2 students
rename existing id -> rows updated 1
rename missing id -> rows updated 0
count -> 2
-- connections: borrowed and returned for you
active connections after the calls above: 0
-- exceptions with a clear meaning
queryForObject, no row -> EmptyResultDataAccessException (root cause EmptyResultDataAccessException): Incorrect result size: expected 1, actual 0
findByEmail wrapper for the same case -> Optional.empty
queryForObject, two rows -> IncorrectResultSizeDataAccessException (root cause IncorrectResultSizeDataAccessException): Incorrect result size: expected 1, actual 2
duplicate email -> DuplicateKeyException (root cause SQLIntegrityConstraintViolationException): PreparedStatementCallback; Duplicate entry 'an@example.com' for key 'student.email'
bad SQL -> BadSqlGrammarException (root cause SQLSyntaxErrorException): StatementCallback; bad SQL grammar [select nope from student]
query timeout of 1 s on select sleep(3) -> QueryTimeoutException (root cause MySQLTimeoutException): StatementCallback; SQL [select sleep(3)]; Statement cancelled due to timeout or client request
-- transactions
without @Transactional, failure after one insert: IllegalStateException (root cause IllegalStateException): failure after the first insert; rows added 1
with @Transactional, same failure: IllegalStateException (root cause IllegalStateException): failure after the first insert; rows added 0
active connections at the end: 0