JPA Part 1: From JDBC to JdbcTemplate

So far everything has been application logic (chapter 13 ran a single SQL statement to show transactions, nothing more). Now the data has to go into a database. This is the first of ten chapters on data access, and it starts at the bottom: the layers between your code and the database, plain JDBC and its problems, and how Spring Boot solves them with JdbcTemplate. Before JPA, it helps to see every layer involved.

The layers

• JDBC (Java Database Connectivity) is an interface for connecting to a relational database, querying it and processing the results. It does not do the work itself.
• A database driver implements JDBC for one database. The driver for MySQL knows how to talk to MySQL, and so on. Your code calls the JDBC API (getConnection, ...), so switching from MySQL to PostgreSQL changes the driver, not your code.
• JPA (Java Persistence API) is also mostly an interface, for ORM (object-relational mapping): a bridge between Java objects and relational tables. With it you work with Java objects instead of writing SQL.
• Hibernate is the most popular JPA implementation (others: EclipseLink, OpenJPA). Internally it uses JDBC.

Database	Driver	Driver class
MySQL	Connector/J	com.mysql.cj.jdbc.Driver
PostgreSQL	PostgreSQL JDBC Driver	org.postgresql.Driver
H2 (in-memory)	H2 Database Engine	org.h2.Driver

The examples use H2 in its in-memory mode: nothing to install, and the data lives only while the application runs.

Plain JDBC, without Spring Boot

public class DatabaseConnection {

    public Connection getConnection() {
        try {
            Class.forName("org.h2.Driver");            // 1. load the driver
            return DriverManager.getConnection(         // 2. connect
                    "jdbc:h2:mem:userDB", "sa", "");    //    creates userDB if it does not exist
        } catch (ClassNotFoundException | SQLException e) {
            // handle exception
        }
        return null;
    }
}

sa with an empty password is H2's default user.

public class UserDAO {

    public void createUserTable() {
        try {
            Connection connection = new DatabaseConnection().getConnection();
            Statement statementQuery = connection.createStatement();
            String sql = "CREATE TABLE users(user_id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(100), age INT)";
            statementQuery.executeUpdate(sql);
        } catch (SQLException e) { /* handle exception */ }
        finally { /* close statementQuery and db connection */ }
    }

    public void createUser(String userName, int userAge) {
        try {
            Connection connection = new DatabaseConnection().getConnection();
            String sqlQuery = "INSERT INTO users(user_name, age) VALUES (?, ?)";
            PreparedStatement preparedQuery = connection.prepareStatement(sqlQuery);
            preparedQuery.setString(1, userName);
            preparedQuery.setInt(2, userAge);
            preparedQuery.executeUpdate();
        } catch (SQLException e) { /* handle exception */ }
        finally { /* close preparedQuery and db connection */ }
    }

    public void readUsers() {
        try {
            Connection connection = new DatabaseConnection().getConnection();
            PreparedStatement preparedQuery = connection.prepareStatement("SELECT * FROM users");
            ResultSet output = preparedQuery.executeQuery();
            while (output.next()) {
                System.out.println(output.getInt("user_id") + ":" + output.getString("user_name") + ":" + output.getInt("age"));
            }
        } catch (SQLException e) { /* handle exception */ }
        finally { /* close preparedQuery and db connection */ }
    }
}

Connection, Statement, PreparedStatement and ResultSet are JDBC interfaces; the driver implements them. PreparedStatement fills the ? placeholders by index.

The boilerplate:

1. Loading the driver class.
2. Making the connection by hand, every time.
3. Exception handling: everything is one vague SQLException, whether it was a duplicate key, a timeout or something else.
4. Closing the connection, statements and result sets in finally, or you leak resources.
5. Connection pooling: every call opens a fresh connection. A pool of ready connections, reused and returned, would be far better, but you have to build it yourself.

JDBC with Spring Boot: JdbcTemplate

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

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

spring.datasource.url=jdbc:h2:mem:userDB
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true

spring.h2.console.enabled=true gives a web console at /h2-console to look at the data.

public class User {
    int userId;
    String userName;
    int age;
    // getters and setters
}

@Repository
public class UserRepository {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public void createTable() {
        jdbcTemplate.execute("CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, " +
                             "user_name VARCHAR(100), age INT)");
    }

    public void insertUser(String name, int age) {
        String insertQuery = "INSERT INTO users (user_name, age) VALUES (?, ?)";
        jdbcTemplate.update(insertQuery, name, age);
    }

    public List<User> getUsers() {
        String selectQuery = "SELECT * FROM users";
        return jdbcTemplate.query(selectQuery, (rs, rowNum) -> {
            User user = new User();
            user.setUserId(rs.getInt("user_id"));
            user.setUserName(rs.getString("user_name"));
            user.setAge(rs.getInt("age"));
            return user;
        });
    }
}

@Repository is a @Component for the data layer.

@Service
public class UserService {

    @Autowired
    UserRepository userRepository;

    public void createTable() {
        userRepository.createTable();
    }

    public void insertUser(String userName, int age) {
        userRepository.insertUser(userName, age);
    }

    public List<User> getUsers() {
        List<User> users = userRepository.getUsers();
        for (User user : users) {
            System.out.println(user.getUserId() + ":" + user.getUserName() + ":" + user.getAge());
        }
        return users;
    }
}

The service holds business logic; the repository talks to the database. No connections, no finally blocks.

How each problem is solved

Spring Boot creates a DataSource from the spring.datasource.* properties. It hands out connections and manages the connection pool; JdbcTemplate uses it.

1. Driver loading: done at startup from the properties.
2. Connections: JdbcTemplate gets one from the DataSource whenever it runs a query (new or from the pool).
3. Exceptions: instead of a vague SQLException, you get specific exceptions from the org.springframework.dao package: DuplicateKeyException, QueryTimeoutException, EmptyResultDataAccessException, DataIntegrityViolationException, CannotAcquireLockException, ... You can handle each one as in the exception-handling chapter.
4. Closing: after each update or query, success or failure, JdbcTemplate closes the resources and closes the connection or returns it to the pool.
5. Connection pool: Spring Boot's default DataSource is HikariCP, a fast pool. By default it keeps 10 connections (maximum pool size 10, minimum idle 10). To change that:

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5

To configure the pool (or use a different one) in code instead of properties, define your own DataSource bean:

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setJdbcUrl("jdbc:h2:mem:userDB");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }
}

JdbcTemplate methods you will use most

update(String sql, Object... args): insert, update, delete. Returns the number of rows affected.

int rows = jdbcTemplate.update("INSERT INTO users (user_name, age) VALUES (?, ?)", "X", 27);
jdbcTemplate.update("UPDATE users SET age = ? WHERE user_id = ?", 29, 1);
jdbcTemplate.update("DELETE FROM users WHERE user_id = ?", 1);

update(String sql, PreparedStatementSetter pss): the same, but you set each ? yourself. PreparedStatementSetter is a functional interface, so a lambda works. Useful for more complex queries.

jdbcTemplate.update("INSERT INTO users (user_name, age) VALUES (?, ?)", ps -> {
    ps.setString(1, "X");
    ps.setInt(2, 25);
});

query(String sql, RowMapper<T> rowMapper): many rows. RowMapper is a functional interface; its mapRow(rs, rowNum) runs once per row and turns it into an object. The results come back as a List.

List<User> users = jdbcTemplate.query("SELECT * FROM users", (rs, rowNum) -> {
    User user = new User();
    user.setUserId(rs.getInt("user_id"));
    user.setUserName(rs.getString("user_name"));
    user.setAge(rs.getInt("age"));
    return user;
});

queryForList(String sql, Class<T> elementType): one column of many rows.

List<String> userNames = jdbcTemplate.queryForList("SELECT user_name FROM users", String.class);

queryForObject(String sql, Class<T> requiredType): a single value.

int userCount = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class);

queryForObject(String sql, RowMapper<T> rowMapper, Object... args): a single row.

User user = jdbcTemplate.queryForObject("SELECT * FROM users WHERE user_id = ?",
        new BeanPropertyRowMapper<>(User.class), 1);

A Class argument works only for a single column. queryForObject("SELECT * FROM users WHERE user_id = ?", User.class, 1) fails with "Incorrect column count: expected 1, actual 3". To map a whole row, pass a RowMapper: your own lambda, or BeanPropertyRowMapper, which matches columns to setters by name (user_name → setUserName). If no row matches, queryForObject throws EmptyResultDataAccessException.

Next: JdbcTemplate still means writing SQL and mapping rows by hand. Chapter 24 lets JPA and Hibernate do both: you work with Java objects, and the SQL is generated for you.

Check yourself

1. Put these in order from your code down to the database: JDBC API, Hibernate, DB driver, JPA.
2. What does a database driver do, and why does switching databases not change your JDBC code?
3. What is ORM?
4. List the five kinds of boilerplate in plain JDBC.
5. Which object does Spring Boot build from spring.datasource.*, and what two jobs does it do?
6. What exception do you get from JdbcTemplate when inserting a duplicate primary key?
7. What connection pool does Spring Boot use by default, and how many connections does it keep?
8. Which JdbcTemplate method would you use to: insert a row; read all rows as objects; read one column of all rows; count rows?
9. Why does queryForObject(sql, User.class, 1) fail for SELECT *, and how do you fix it?

Answers

1. JPA, Hibernate, JDBC API, DB driver.
2. It implements the JDBC interfaces for one database. Your code only calls the JDBC API, so only the driver changes.
3. Object-relational mapping: working with Java objects that map to relational tables, instead of writing SQL.
4. Loading the driver, making connections, vague SQLException handling, closing resources, connection pooling.
5. A DataSource: it provides database connections and manages the connection pool.
6. DuplicateKeyException (from org.springframework.dao).
7. HikariCP, 10 connections (max pool size 10, minimum idle 10).
8. update; query with a RowMapper; queryForList(sql, String.class); queryForObject(sql, Integer.class).
9. A Class argument maps a single column only (it says "expected 1, actual 3"). Pass a RowMapper, e.g. new BeanPropertyRowMapper<>(User.class).