Rapid Data Access with SpringBoot and JdbcTemplate

Why Choose JdbcTemplate?

Many developers associate SpringBoot database access with MyBatis or JPA, but for simple single‑table operations, micro‑services, quick prototypes, or when direct SQL control is desired without the overhead of an ORM, JdbcTemplate offers a lightweight, zero‑configuration solution that performs close to native JDBC.

Required Dependencies

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

<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.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
</dependency>

Configuration (application.yml)

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

Entity Definition

package com.demo.entity;

import lombok.Data;
import java.time.LocalDateTime;

@Data
public class User {
    private Long id;
    private String username;
    private Integer age;
    private String email;
    private LocalDateTime createTime;
}

Core JdbcTemplate Operations

Common methods include update() for insert/update/delete, queryForObject() for a single record, query() for lists, and batchUpdate() for bulk operations.

1. Add User

@Autowired
private JdbcTemplate jdbcTemplate;

public int addUser(User user) {
    String sql = "insert into user(username, age, email) values(?, ?, ?)";
    return jdbcTemplate.update(sql, user.getUsername(), user.getAge(), user.getEmail());
}

2. Delete by ID

public int deleteById(Long id) {
    String sql = "delete from user where id=?";
    return jdbcTemplate.update(sql, id);
}

3. Update User

public int updateUser(User user) {
    String sql = "update user set username=?, age=?, email=? where id=?";
    return jdbcTemplate.update(sql, user.getUsername(), user.getAge(), user.getEmail(), user.getId());
}

4. Find by ID

public User findById(Long id) {
    String sql = "select * from user where id=?";
    return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
}

5. List All Users

public List<User> findAll() {
    String sql = "select * from user";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}

6. Conditional Query (by age)

public List<User> findByAge(Integer age) {
    String sql = "select * from user where age=?";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), age);
}

7. Count Records

public Long count() {
    String sql = "select count(*) from user";
    return jdbcTemplate.queryForObject(sql, Long.class);
}

8. Batch Insert

public void batchAdd(List<User> userList) {
    String sql = "insert into user(username, age, email) values(?,?,?)";
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            User user = userList.get(i);
            ps.setString(1, user.getUsername());
            ps.setInt(2, user.getAge());
            ps.setString(3, user.getEmail());
        }
        @Override
        public int getBatchSize() { return userList.size(); }
    });
}

Service Layer Encapsulation

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional(rollbackFor = Exception.class)
    public boolean add(User user) {
        String sql = "insert into user(username,age,email) values(?,?,?)";
        return jdbcTemplate.update(sql, user.getUsername(), user.getAge(), user.getEmail()) > 0;
    }

    @Transactional
    public boolean delete(Long id) {
        String sql = "delete from user where id=?";
        return jdbcTemplate.update(sql, id) > 0;
    }

    @Transactional
    public boolean update(User user) {
        String sql = "update user set username=?,age=?,email=? where id=?";
        return jdbcTemplate.update(sql, user.getUsername(), user.getAge(), user.getEmail(), user.getId()) > 0;
    }

    public User findById(Long id) {
        String sql = "select * from user where id=?";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<User> list() {
        String sql = "select * from user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }
}

Controller Endpoints

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @PostMapping("/add")
    public Result add(@RequestBody User user) {
        userService.add(user);
        return Result.success("添加成功");
    }

    @DeleteMapping("/delete/{id}")
    public Result delete(@PathVariable Long id) {
        userService.delete(id);
        return Result.success("删除成功");
    }

    @PutMapping("/update")
    public Result update(@RequestBody User user) {
        userService.update(user);
        return Result.success("修改成功");
    }

    @GetMapping("/{id}")
    public Result findById(@PathVariable Long id) {
        return Result.success(userService.findById(id));
    }

    @GetMapping("/list")
    public Result list() {
        return Result.success(userService.list());
    }
}

Transaction Support

Annotating a method with @Transactional ensures that multiple updates either all succeed or all roll back, mirroring MyBatis behavior.

@Transactional(rollbackFor = Exception.class)
public void testTransaction() {
    jdbcTemplate.update("update user set age=20 where id=1");
    int i = 1 / 0; // simulate exception
    jdbcTemplate.update("update user set age=30 where id=2");
}

When to Use JdbcTemplate vs MyBatis

Simple single‑table or quick‑development scenarios → JdbcTemplate

Complex queries, multi‑table joins, dynamic SQL → MyBatis

Enterprise admin systems where maintainability matters → MyBatis

Small interfaces or lightweight services → JdbcTemplate

Key Considerations

Empty results : queryForObject throws EmptyResultDataAccessException; handle with try‑catch.

Type matching : Ensure database column types align with entity fields.

SQL injection : Always use ? placeholders instead of string concatenation.

Batch performance : For large data sets, split inserts into smaller batches.

Conclusion

Integrating JdbcTemplate into SpringBoot provides the simplest data‑access solution: just add spring-boot-starter-jdbc, let Spring auto‑configure, and write plain SQL to perform all CRUD operations without XML, extra plugins, or steep learning curves—ideal for small projects and rapid development.