Showing posts with label Querying with JdbcTemplate. Show all posts

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);
jdbcTemplate.afterPropertiesSet();

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);
log.info(() -> 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");
log.info(() -> 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);
log.info(() -> String.format("Name of mobiles: %s", mobileNames.toString()));

You can get the full example code here.