Master Spring Boot JDBC: Configure, Query, and Test MySQL with JdbcTemplate

In the previous chapter we introduced how to implement HTTP interfaces with Spring Boot, but storing user data only in memory is insufficient for real applications. This chapter focuses on relational database access using Spring Boot and JdbcTemplate.

Data Source Configuration

When accessing a database you must first configure a data source. Several configuration methods are presented.

First, add JDBC support in pom.xml:

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

Embedded Database Support

Embedded databases are useful for development and testing but not recommended for production. Spring Boot can auto‑configure H2, HSQL, or Derby without any connection settings.

Example: add HSQL dependency in pom.xml:

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>runtime</scope>
</dependency>

Connecting to a Production Data Source

Using MySQL as an example, add the MySQL connector dependency:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

Then configure the connection in src/main/resources/application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

You can also use a JNDI data source by setting spring.datasource.jndi-name=java:jboss/datasources/customers.

Using JdbcTemplate to Operate the Database

JdbcTemplate is auto‑configured by Spring Boot. It can be injected with @Autowired or via constructor injection.

Prepare Database

Create a User table with name and age columns:

CREATE TABLE `User` (
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Write Domain Object

@Data
@NoArgsConstructor
public class User {
    private String name;
    private Integer age;
}

Write Data Access Object

Define an interface UserService that declares CRUD methods.

public interface UserService {
    int create(String name, Integer age);
    List<User> getByName(String name);
    int deleteByName(String name);
    int getAllUsers();
    int deleteAllUsers();
}

Implement the interface using JdbcTemplate:

@Service
public class UserServiceImpl implements UserService {
    private JdbcTemplate jdbcTemplate;
    UserServiceImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; }
    @Override
    public int create(String name, Integer age) {
        return jdbcTemplate.update("insert into USER(NAME, AGE) values(?, ?)", name, age);
    }
    @Override
    public List<User> getByName(String name) {
        return jdbcTemplate.query("select NAME, AGE from USER where NAME = ?",
            (rs, i) -> {
                User user = new User();
                user.setName(rs.getString("NAME"));
                user.setAge(rs.getInt("AGE"));
                return user;
            }, name);
    }
    @Override
    public int deleteByName(String name) {
        return jdbcTemplate.update("delete from USER where NAME = ?", name);
    }
    @Override
    public int getAllUsers() {
        return jdbcTemplate.queryForObject("select count(1) from USER", Integer.class);
    }
    @Override
    public int deleteAllUsers() {
        return jdbcTemplate.update("delete from USER");
    }
}

Write Unit Test Cases

Create tests for UserService that verify create, delete, and query operations:

@RunWith(SpringRunner.class)
@SpringBootTest
public class Chapter31ApplicationTests {
    @Autowired
    private UserService userSerivce;
    @Before
    public void setUp() {
        // clear table
        userSerivce.deleteAllUsers();
    }
    @Test
    public void test() throws Exception {
        userSerivce.create("Tom", 10);
        userSerivce.create("Mike", 11);
        userSerivce.create("Didispace", 30);
        userSerivce.create("Oscar", 21);
        userSerivce.create("Linda", 17);
        List<User> userList = userSerivce.getByName("Oscar");
        Assert.assertEquals(21, userList.get(0).getAge().intValue());
        Assert.assertEquals(5, userSerivce.getAllUsers());
        userSerivce.deleteByName("Tom");
        userSerivce.deleteByName("Mike");
        Assert.assertEquals(3, userSerivce.getAllUsers());
    }
}

The examples above demonstrate the most basic JdbcTemplate operations; for more advanced usage refer to the JdbcTemplate API documentation.

Overall, Spring Boot makes database access straightforward: add the dependency, configure the connection, and inject JdbcTemplate without needing to define a bean manually.