Spring Boot JPA Part 1 | JDBC Template

Introduction: Why Does Your Application Need a Database Layer?

Imagine you run a library. Every day people borrow books, return them, and new books arrive. You could keep everything in your memory, but the moment you go home the information is lost. You need a ledger — a persistent record that survives even after you close the library for the night.

A database is that ledger for your application. The moment your Spring Boot application restarts, all in memory state vanishes. Persisting data to a database ensures it survives restarts, crashes, and deployments. The question is: how does your Java application talk to a database?

The Full Stack: From Application to Database

Before diving into code, it helps to understand every layer involved when a Spring Boot application saves data.

Your Application Logic (Beans, Services)
         |
         v
      JPA Layer
         |
         v
   ORM Framework (Hibernate)
         |
         v
   JDBC (Java Database Connectivity)
         |
         v
    JDBC Driver (MySQL Driver, PostgreSQL Driver)
         |
         v
      Database

Each layer has a specific responsibility:

• Your Application Logic: Business rules, service classes, repositories.
• JPA: A specification that defines how Java objects map to database tables.
• ORM Framework (Hibernate): The actual implementation of JPA that converts objects to SQL.
• JDBC: A standard Java API for executing SQL against any relational database.
• JDBC Driver: Database specific binary that knows the wire protocol to communicate with that particular database.
• Database: MySQL, PostgreSQL, H2, Oracle, etc.

In this chapter we focus on the raw JDBC approach and then show how the JdbcTemplate abstraction simplifies it dramatically.

Plain JDBC: Maximum Boilerplate

Before Spring existed, every Java developer had to write this kind of code to insert a single row:

import java.sql.*;

public class PlainJdbcExample {

    public void createUser(String name, String phone) {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            // 1. Load the driver (older JDBC)
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. Establish a connection
            connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb",
                "root",
                "password"
            );

            // 3. Create a prepared statement
            String sql = "INSERT INTO users (name, phone) VALUES (?, ?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setString(2, phone);

            // 4. Execute the statement
            statement.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. Close resources (MUST happen or you leak connections)
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

This is just for one insert. You repeat this pattern for every query, every table, and every method. The problems are clear:

• Tedious resource management — connections and statements must always be closed.
• Exception handling noise drowns out the actual business logic.
• Every developer writes slightly different boilerplate, making codebases inconsistent.
• Connection pooling has to be set up manually.

Enter JdbcTemplate: The Spring Solution

Spring's JdbcTemplate solves all of these problems. It wraps the raw JDBC calls, handles resource management automatically, translates SQLException into Spring's DataAccessException hierarchy, and integrates with the Spring DataSource (which already provides connection pooling via HikariCP by default).

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

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Injecting JdbcTemplate

JdbcTemplate is already a Spring bean when spring-boot-starter-jdbc is on the classpath. Simply inject it:

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // methods below...
}

Spring automatically provides a JdbcTemplate bean wired to the configured DataSource. You never manually manage connections.

Core JdbcTemplate Methods

1. execute(String sql) — DDL Statements

Use execute for statements that do not return data, typically DDL such as CREATE TABLE:

public void createTable() {
    String sql = """
        CREATE TABLE IF NOT EXISTS users (
            id    INT AUTO_INCREMENT PRIMARY KEY,
            name  VARCHAR(100) NOT NULL,
            phone VARCHAR(20)
        )
        """;
    jdbcTemplate.execute(sql);
    System.out.println("Table created successfully");
}

2. update(String sql, Object... args) — INSERT, UPDATE, DELETE

update returns the number of rows affected. Use it for any DML statement that modifies data:

// INSERT a new user
public int insertUser(String name, String phone) {
    String sql = "INSERT INTO users (name, phone) VALUES (?, ?)";
    return jdbcTemplate.update(sql, name, phone);
}

// UPDATE an existing user's phone
public int updatePhone(int id, String newPhone) {
    String sql = "UPDATE users SET phone = ? WHERE id = ?";
    return jdbcTemplate.update(sql, newPhone, id);
}

// DELETE a user
public int deleteUser(int id) {
    String sql = "DELETE FROM users WHERE id = ?";
    return jdbcTemplate.update(sql, id);
}

The ? placeholders are positional. Arguments are passed in order after the SQL string. Spring handles the PreparedStatement creation and parameter binding internally.

3. queryForObject(String sql, Class<T> type, Object... args) — Single Value

Use queryForObject when you expect exactly one row and one column (a scalar value):

// Count how many users exist
public int getUserCount() {
    String sql = "SELECT COUNT(*) FROM users";
    return jdbcTemplate.queryForObject(sql, Integer.class);
}

// Get a single user's name by ID
public String getUserName(int id) {
    String sql = "SELECT name FROM users WHERE id = ?";
    return jdbcTemplate.queryForObject(sql, String.class, id);
}

If the query returns zero rows or more than one row, Spring throws an exception. This method is designed for scalar lookups only.

4. queryForObject(String sql, RowMapper<T>, Object... args) — Single Row as Object

When you want to map a full row to a Java object, provide a RowMapper:

public class UserDetail {
    private int id;
    private String name;
    private String phone;
    // getters and setters
}

public UserDetail getUserById(int id) {
    String sql = "SELECT id, name, phone FROM users WHERE id = ?";
    return jdbcTemplate.queryForObject(sql,
        (rs, rowNum) -> {
            UserDetail user = new UserDetail();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setPhone(rs.getString("phone"));
            return user;
        },
        id
    );
}

The RowMapper lambda receives a ResultSet and the current row number. You extract columns and populate your object.

5. query(String sql, RowMapper<T>, Object... args) — Multiple Rows

Use query when the result can be multiple rows:

public List<UserDetail> getAllUsers() {
    String sql = "SELECT id, name, phone FROM users";
    return jdbcTemplate.query(sql,
        (rs, rowNum) -> {
            UserDetail user = new UserDetail();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setPhone(rs.getString("phone"));
            return user;
        }
    );
}

Complete Working Example

Here is a full service that demonstrates all operations together:

@Service
public class UserJdbcService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // Create the table if it does not exist
    public void initTable() {
        jdbcTemplate.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id    INT AUTO_INCREMENT PRIMARY KEY,
                name  VARCHAR(100) NOT NULL,
                phone VARCHAR(20)
            )
            """);
    }

    // Insert a user and return rows affected
    public int createUser(String name, String phone) {
        String sql = "INSERT INTO users (name, phone) VALUES (?, ?)";
        return jdbcTemplate.update(sql, name, phone);
    }

    // Fetch single user by ID
    public UserDetail getUserById(int id) {
        String sql = "SELECT id, name, phone FROM users WHERE id = ?";
        return jdbcTemplate.queryForObject(sql,
            (rs, rowNum) -> new UserDetail(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("phone")
            ),
            id
        );
    }

    // Fetch all users
    public List<UserDetail> getAllUsers() {
        String sql = "SELECT id, name, phone FROM users";
        return jdbcTemplate.query(sql,
            (rs, rowNum) -> new UserDetail(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("phone")
            )
        );
    }

    // Update phone number
    public int updatePhone(int id, String phone) {
        String sql = "UPDATE users SET phone = ? WHERE id = ?";
        return jdbcTemplate.update(sql, phone, id);
    }

    // Delete user
    public int deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
}

JdbcTemplate vs Plain JDBC: Side by Side

Concern	Plain JDBC	JdbcTemplate
Connection management	Manual	Automatic
Statement creation	Manual	Automatic
Resource cleanup	Manual try/finally	Automatic
Exception translation	Raw SQLException	DataAccessException hierarchy
Connection pooling	Manual setup	HikariCP via Spring Boot
Boilerplate volume	Very high	Minimal

Where JdbcTemplate Falls Short

JdbcTemplate is excellent for simple use cases, but as your application grows you encounter limitations:

1. No object mapping: You write RowMapper for every query. For complex objects this becomes tedious.
2. No relationship handling: Joins between tables require custom SQL and mapping code.
3. No caching: Every call goes to the database.
4. No change tracking: You must explicitly write UPDATE statements; there is no automatic dirty checking.
5. No lazy loading: All data is fetched when the query runs.

These are exactly the problems that JPA and Hibernate solve, which is why the next chapters move from raw JDBC to the full ORM layer.

Summary

• Raw JDBC requires extensive boilerplate: manual connection, statement, result set, and exception handling.
• JdbcTemplate eliminates the boilerplate while keeping SQL explicit and readable.
• The key methods are execute (DDL), update (DML), queryForObject (single value or row), and query (multiple rows).
• Spring Boot auto configures JdbcTemplate when spring-boot-starter-jdbc is on the classpath.
• JdbcTemplate is a great fit for simple or legacy schemas but lacks the power of a full ORM for complex domains.

---

Interview Questions

Q1: What is the difference between JdbcTemplate.update() and JdbcTemplate.execute()?

execute() is designed for DDL statements such as CREATE TABLE or DROP TABLE that return no data. update() is designed for DML statements such as INSERT, UPDATE, and DELETE that modify data and return the number of affected rows.

Q2: How does JdbcTemplate handle connection management?

JdbcTemplate uses the DataSource injected by Spring Boot (backed by HikariCP connection pool). For each operation it borrows a connection from the pool, executes the statement, and returns the connection automatically in a finally block. You never call connection.close() manually.

Q3: What is a RowMapper and when do you use it?

A RowMapper<T> is a functional interface with one method: T mapRow(ResultSet rs, int rowNum). You use it with queryForObject and query to convert each database row into a Java object. It receives the ResultSet positioned at the current row.

Q4: What exception does JdbcTemplate throw when a query returns no rows but one is expected?

It throws EmptyResultDataAccessException, which is a subclass of Spring's DataAccessException. This is a runtime exception, so you do not need to declare it in a throws clause.

Q5: How is JdbcTemplate different from raw JDBC in terms of exception handling?

Raw JDBC forces you to handle checked SQLException everywhere, which clutters code with catch blocks. JdbcTemplate translates SQLException into Spring's DataAccessException hierarchy (unchecked exceptions), letting you handle only the exceptions you genuinely care about.

Q6: What is the role of the ? placeholder in JdbcTemplate queries?

The ? is a positional parameter placeholder used in PreparedStatement. Arguments you pass after the SQL string are bound to these placeholders in order. This protects against SQL injection because the values are never concatenated into the SQL string.

Q7: What is the difference between queryForObject and query in JdbcTemplate?

queryForObject expects exactly one row and throws an exception if zero or more than one row is returned. query expects zero or more rows and returns a List<T>. Use queryForObject for primary key lookups and query for searches that may return multiple results.