Spring JDBC

To simplify the hassle surrounding raw JDBC, Spring provides the Spring JDBC module, which is pretty old but well described. This module offers a few versions of the JdbcTemplate class that helps to execute queries and maps relational rows into entities. It also handles the creation and release of resources, helping to omit common errors such as forgetting to close a prepared statement or connection. JdbcTemplate also catches JDBC exceptions and translates them to generic org.springframework.dao exceptions.

Let's say we have a collection of books in an SQL database and the entity is represented by the following Java class:

class Book {
   private int id;
   private String title;

   public Book() { }

   public Book(int id, String title) {
      this.id = id;
      this.title = title;
   }
   //getters and setters ...
}

With JdbcTemplate and a generic BeanPropertyRowMapper, we may create a Spring repository in the following way:

@Repository
class BookJdbcRepository {

   @Autowired
   JdbcTemplate jdbcTemplate;

   public Book findById(int id) {
      return jdbcTemplate.queryForObject(
         "SELECT * FROM book WHERE id=?",
         new Object[] { id },
         new BeanPropertyRowMapper<>(Book.class));
   }
}

Alternatively, we may provide our own mapper class to instruct Spring on how a ResultSet should be translated into a domain entity:

class BookMapper implements RowMapper<Book> {
    @Override
    public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Book(rs.getInt("id"), rs.getString("title"));
    }
}

Let's implement the BookJdbcRepository.findAll() method using the BookMapper class:

public List<Book> findAll() {
    return jdbcTemplate.query("SELECT * FROM book", new BookMapper());
}

One more improvement over JdbcTemplate is implemented by the NamedParameterJdbcTemplate class. This adds support for passing JDBC parameters using human-readable names as opposed to ? placeholders. Consequently, a prepared SQL query and its corresponding Java code may look like this:

SELECT * FROM book WHERE title = :searchtitle

Here is a classic prepared SQL statement:

SELECT * FROM book WHERE title = ?

This may only seem to be a minor improvement, but named parameters give better readability for code than indexes, especially when a query requires half a dozen parameters.

In summary, the Spring JDBC module consists of utilities, helper classes, and tools used by higher-level abstractions. Higher-level APIs do not limit the Spring JDBC module, so it can absorb the required reactive support relatively easily given that the underlying API also supports it.

Table of Contents for Spring JDBC

Create new playlist

Sign In

Sign Up

Table of Contents for
Spring JDBC