Skip to main content


Showing posts from 2019

Spring JDBC returning auto-generated keys using PreparedStatementCreator and PreparedStatementCallback

In this post, we will look into how to retrieve auto-generated keys in JDBC. We will also explore usage of PreparedStatementCreator and PreparedStatementCallback in JdbcTemplate . There are cases when you rely on Database server to auto generate values for some columns of the table. E.g. auto increment primary key, creation_date or any other column while inserting records. There is a way with which you can retrieve those auto-generated keys when you execute the insert statement. Let's see how you can do this using Spring JDBC but first we will see what PreparedStatementCreator and PreparedStatementCallback interfaces are. What is PreparedStatementCreator? There are cases when you want to create PreparedStatement yourself. One use case is to return auto generated keys. In that case, Spring JDBC provides you an option to do so by providing implementation for PreparedStatementCreator . Let's create an implementation of PreparedStatementCreator which sets those options.

Spring JDBC performing batch update example

There are many different variants of #batchUpdate methods available in JdbcTemplate . We will specifically look into those who uses BatchPreparedStatementSetter and ParameterizedPreparedStatementSetter . What is BatchPreparedStatementSetter? It is an interface used by JdbcTemplate to execute batch updates. This has methods to determine the batch size and method to set parameters in the PreparedStatement. Using this, JdbcTemplate will run only execute single batch based on the batch size returned by implementation this interface. How to use BatchPreparedStatementSetter? Let's create a ProductBatchPreparedStatementSetter which can set parameters in the statement. public class ProductBatchPreparedStatementSetter implements BatchPreparedStatementSetter { private final List products; public ProductBatchPreparedStatementSetter(List products) { Objects.requireNonNull(products); // Ideally you should do a defensive copy of this list. // this.products = ne

Spring JDBC PreparedStatementSetter example

What is PreparedStatementSetter? It is a callback interface used by JdbcTemplate after PreparedStatement is created to set the values in the statement object. How to use it? PreparedStatementSetter is also functional interface, so we will use lambda expression in this example to demonstrate PreparedStatementSetter's usage. We will use it in #update method of JdbcTemplate. int updateCount = jdbcTemplate.update("insert into product(name, category, description) values(?,?,?)", ps -> { ps.setString(1, "Lenovo Bag"); ps.setString(2, "bag"); ps.setString(3, "Handcrafted bags by Lenovo"); }); -> String.format("Product inserted: %d", updateCount)); You can get the full code of this example from here .

Spring JDBC ResultSetExtractor example

What is ResultSetExtractor? It is an interface used by #query methods of JdbcTemplate . It is better suitable if you want to map one result object per ResultSet otherwise RowMapper is simpler choice to map one row of ResultSet with one object. How to use it? Let's first create a ResultSetExtractor which maps all the rows of ResultSet to single object. For this we will create a ProductResultSetExtractor which returns ProductResponse . public class ProductResultSetExtractor implements ResultSetExtractor { private final RowMapper productRowMapper; public ProductResultSetExtractor(RowMapper productRowMapper) { super(); this.productRowMapper = productRowMapper; } @Override public ProductResponse extractData(ResultSet rs) throws SQLException { final List products = new ArrayList<>(); int rowNum = 0; while( { products.add(productRowMapper.mapRow(rs, rowNum)); rowNum++; } return ProductResponse.of(prod

Spring JDBC RowMapper example

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 { @Override public Product mapRow(ResultSet rs, int rowNum) throws SQLException { Product product = new Product(); product.setId(rs.getInt("id")); product.setName(rs.getString("name")); product.setDescription(rs.getString("description")); product.setCategory(rs.getString("category")); return product; } } Now, we will use this ProductRowMapper in #queryForObject of JdbcTemplate . Product product = jdbcTemplate.queryForObject("select * from product where id=1", new Prod

Spring basic JdbcTemplate example

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 ma