Showing posts with label JdbcTemplate. Show all posts

In this post, we will discuss what RowMapper is and how to use it when writing Jdbc code using Spring JDBC module.

What is RowMapper?

It is an interface of Spring JDBC module which is used by JdbcTemplate to map rows of java.sql.ResultSet. It is typically used when you query data.

Example usage of RowMapper

Let's first create a RowMapper which can map products.

class ProductRowMapper implements RowMapper {

    public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
      Product product = new Product();
      return product;

Now, we will use this ProductRowMapper in #queryForObject of JdbcTemplate.

Product product = jdbcTemplate.queryForObject("select * from product where id=1", new ProductRowMapper());;

You can find the github code here.

What is Spring JdbcTemplate?

JdbcTemplate is the core class of Spring JDBC. It simplifies your interaction with low-level error prone details of JDBC access. You only pass the SQL statement to execute, parameters and processing logic for the returned data and rest is handled by it i.e. Opening Connection, transaction handling, error handling and closing Connection, Statement and Resultset.

How to create object of JdbcTemplate?

1. Calling no args constructor.

JdbcTemplate jdbcTemplate = new JdbcTemplate();

// You need to set datasource in later point in time and also have to call afterPropertiesSet.
jdbcTemplate.setDataSource(DataSource ds);

2. By Calling constructor with datasource.

JdbcTemplate jdbcTemplate = new JdbcTemplate(Datasource ds);

3. By Calling constructor with datasource and lazyInit parameter.

JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSource dataSource, boolean lazyInit);

Querying with JdbcTemplate

There are many variants of querying using JdbcTemplate. We will look into queryForObject and queryForList method.

JdbcTemplate.queryForObject(String sql, Class<T> requiredType)

We will use this variant of #queryForObject when the ResultSet returns only single column.

Integer count = jdbcTemplate.queryForObject("select count(*) from product", Integer.class); -> String.format("There are total %d products", count));

JdbcTemplate.queryForObject(String sql, Class requiredType, @Nullable Object... args)

We will use this variant when we need to pass sql binding parameters.

Integer mobileProducts = jdbcTemplate.queryForObject("select count(*) from product where category=?", Integer.class, "mobile"); -> String.format("There are total %d mobile products", mobileProducts));

JdbcTemplate.queryForList(String sql, Class elementType)

This variant is useful when query return list of values but for single column.

// E.g. getting list of product names
List mobileNames = jdbcTemplate.queryForList("select name from product where category='mobile'", String.class); -> String.format("Name of mobiles: %s", mobileNames.toString()));

You can get the full example code here.