Skip to main content

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.

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<String> 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<Integer> {

  @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.

Comments

Popular posts from this blog

Data Analytics: Watching and Alerting on real-time changing data in Elasticsearch using Kibana and SentiNL

In the previous post , we have setup ELK stack and ran data analytics on application events and logs. In this post, we will discuss how you can watch real-time application events that are being persisted in the Elasticsearch index and raise alerts if condition for watcher is breached using SentiNL (Kibana plugin). Few examples of alerting for application events ( see previous posts ) are: Same user logged in from different IP addresses. Different users logged in from same IP address. PermissionFailures in last 15 minutes. Particular kind of exception in last 15 minutes/ hour/ day. Watching and alerting on Elasticsearch index in Kibana There are many plugins available for watching and alerting on Elasticsearch index in Kibana e.g. X-Pack , SentiNL . X-Pack is a paid extension provided by elastic.co which provides security, alerting, monitoring, reporting and graph capabilities. SentiNL is free extension provided by siren.io which provides alerting and reporting function

React Ecosystem: Building BlogPost application with React and React Hooks

Building a Blog Post application Let's create a blog post application. It will have below features: Option to search blog posts. Option to list blog posts. Option to show blog post. Create a new project with npx create-react-app react-blog-posts --template typescript . Create BlogPosts.tsx component under src/components folder and IBlogPost model under src/models . import React from 'react'; import IBlogPost from '../models/IBlogPost'; interface IBlogPostsProps { posts: Array<IBlogPost> } function BlogPosts(props: IBlogPostsProps) { return ( <div className="blog-container"> <ul className="blog-posts"> { props.posts.map(post => <li key={post.id}>{post.title}</li>) } </ul> </div> ); } export default BlogPosts; interface IBlogPost { id: number title: string content: string author: string postedOn: string tags: string[]

Java 8 - Lambda expressions

In this post, we will cover following topics. What are Lambda expressions? Syntax for Lambda expression. How to define no parameter Lambda expression? How to define single/ multi parameter Lambda expression? How to return value from Lambda expression? Accessing local variables in Lambda expression. Target typing in Lambda expression. What are Lambda expressions? Lambda expressions are the first step of Java towards functional programming. Lambda expressions enable us to treat functionality as method arguments, express instances of single-method classes more compactly. Syntax for Lambda expression Lambda has three parts: comma separated list of formal parameters enclosed in parenthesis. arrow token -> . and, body of expression (which may or may not return value). (param) -> { System.out.println(param); } Lambda expression can only be used where the type they are matched are functional interfaces . How to define no parameter Lambda expression? If the la