Showing posts with label Spring JDBC. Show all posts

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.

public class ReturnGeneratedKeysPreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
  private final String sql;
  private String[] generatedColumnNames;

  public ReturnGeneratedKeysPreparedStatementCreator(String sql) {
    this(sql, Collections.emptyList());
  }

  public ReturnGeneratedKeysPreparedStatementCreator(String sql, List generatedColumnNames) {
    this.sql = sql;
    this.generatedColumnNames = Objects.nonNull(generatedColumnNames)
    ? generatedColumnNames.toArray(new String[generatedColumnNames.size()])
    : new String[0];
  }

  @Override
  public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    return generatedColumnNames.length > 0 ? con.prepareStatement(this.sql, this.generatedColumnNames)
    : con.prepareStatement(this.sql, Statement.RETURN_GENERATED_KEYS);
  }

  @Override
  public String getSql() {
    return this.sql;
  }

}

There are two options which you can use to retrieve generated keys. One is to get all the generated keys and other is to pass the columsn you want to retrieve.

What is PreparedStatementCallback?

In normal usage, you might never need to implement this interface but will provide implementation only if you need to execute some other code e.g. retrieval of auto-generated keys. Let's see how you can do this with implementing this interface.

class GeneratedKeysPreparedStatementCallback implements PreparedStatementCallback {

  @Override
  public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
    int updated = ps.executeUpdate();
    if (updated > 0) {
      try (ResultSet rs = ps.getGeneratedKeys()) {
        if (rs.next())
          return rs.getInt("id");
      }
   
      throw new DataRetrievalFailureException("There was no key auto generated by the database");
    }
    throw new DataRetrievalFailureException("Nothing was updated");
  }
}

Usage

Integer key = jdbcTemplate.execute(new ReturnGeneratedKeysPreparedStatementCreator(
    "insert into product(name, category, description) values('Acer Laptop', 'laptop', 'Predator series')"),
    new GeneratedKeysPreparedStatementCallback());
log.info(() -> String.format("Product saved in database with key: %d", key));

That's it. You can find the complete code on Github.

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 = new ArrayList<>(products);
    this.products = products;
  }

  @Override
  public void setValues(PreparedStatement ps, int i) throws SQLException {
    Product product = products.get(i);
    ps.setString(1, product.getName());
    ps.setString(2, product.getCategory());
    ps.setString(3, product.getDescription());
  }

  @Override
  public int getBatchSize() {
    return products.size();
  }

}

Usage

int[] results = jdbcTemplate.batchUpdate("insert into product (name, category, description) values(?,?,?)", 
    new ProductBatchPreparedStatementSetter(Arrays.asList(new Product("Lenovo Laptop", "laptop", "Thinkpad series laptop"),
 new Product("Acer Laptop", "laptop", "Predator series laptop"))));
log.info(() -> String.format("Inserted rows: %s", Arrays.toString(results)));

What is ParameterizedPreparedStatementSetter?

It is an interface used by JdbcTemplate to execute batch updates. It has only one method which takes PreparedStatement and Typed object as parameters. Using this, JdbcTemplate can execute multiple batch based on the batch size passed in the #batchUpdate method.

How to use ParameterizedPreparedStatementSetter?

Let's create a pretty straightforward implementation of this interface for our Product example.

ParameterizedPreparedStatementSetter<Product> pss = (ps, product) -> {
    ps.setString(1, product.getName());
    ps.setString(2, product.getCategory());
    ps.setString(3, product.getDescription());
  };

Usage

int batchSize = 5;
int[][] result = jdbcTemplate.batchUpdate("insert into product (name, category, description) values(?,?,?)",
    products, batchSize, pss);
log.info(Arrays.deepToString(result));

#batchUpdate method which uses BatchPreparedStatementSetter returns 1-D int array whereas #batchUpdate method which uses ParameterizedPreparedStatementSetter returns 2-D array. This means that BatchPreparedStatementSetter executed single batch whereas ParameterizedPreparedStatementSetter executed multiple batches.

That's it. You can find the complete code of this example on Github.

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");
  });
  
  log.info(() -> String.format("Product inserted: %d", updateCount));

You can get the full code of this example from here.

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(rs.next()) {
      products.add(productRowMapper.mapRow(rs, rowNum));
      rowNum++;
    }

    return ProductResponse.of(products);
  }
}

Now, we will use #query method of JdbcTemplate to use this ProductResultSetExtractor to return result.

ProductResponse productResponse = jdbcTemplate.query("select * from product", new ProductResultSetExtractor(new ProductRowMapper()));

log.info(productResponse::toString);

That's it. You can find the full example code on github.

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 ProductRowMapper());
log.info(product::toString);

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);
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.