Spring JDBC - How to retrieve the auto-generated database key after an INSERT

Spring JDBC FAQ: How do I retrieve the auto-generated database key for a serial field (also known as an auto_increment field in MySQL) after I perform a SQL INSERT using Spring JDBC?

(I'm not phrasing that well, but by this question I mean the value of the primary key for the record I just inserted. This field is known as an auto increment field in MySQL, and either an identity or serial field in other databases like SQL Server or Postgresql. In either case, the problem is the same: How to get the value of the primary key field following your SQL INSERT statement.)

Java Spring JDBC - Auto-generated database key source code

Solution: Here's some example Spring JDBC source code for a method I wrote to retrieve the auto-generated key following a SQL INSERT. Specifically, this method uses some Spring JDBC classes and methods to (a) execute a SQL INSERT statement, (b) get the generated id from the database for the record I just inserted (i.e., the value of the auto_increment field in a MySQL database table), and (c) return that integer value at the end of the method.

Here's the Java source code for this Spring JDBC method:

public int insertSubscriberRecord(int websiteId, 
                                  String firstName, 
                                  String lastName, 
                                  String password, 
                                  String email)
{
  Subscriber subscriber = new Subscriber(websiteId, firstName, lastName, password, email);
  String insertFileString = "INSERT INTO subscribers "
    + "(website_id, first_name, last_name, password, email_address) VALUES "
    + "(:websiteId, :firstName, :lastName, :password, :emailAddress) ";
  // see http://static.springframework.org/spring/docs/2.5.x/reference/jdbc.html
  SqlParameterSource fileParameters = new BeanPropertySqlParameterSource(subscriber);
  KeyHolder keyHolder = new GeneratedKeyHolder();
  getNamedParameterJdbcTemplate().update(insertFileString, fileParameters, keyHolder);
  return keyHolder.getKey().intValue();
}

I haven't looked at the Spring JDBC docs in a while now, but if I remember right there are only a few Spring JDBC methods that support returning the value of the auto-generated key, and each of those methods use this KeyHolder class.

Source code for the complete Spring DAO class

To get this to work you'll have to do all the usual Spring wiring things in your application, which hopefully I've already provided examples for out here already (or that you are hopefully already familiar with). Your Spring DAO class also needs to extend one of the classes provided in the Spring Framework ... I was going to ramble on, but let me just give you all the code for my class:

package com.devdaily.spring.jdbc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

public class SubscriberDao extends NamedParameterJdbcDaoSupport
{
  protected SimpleJdbcTemplate simpleJdbcTemplate;
  protected TransactionTemplate transactionTemplate;  
    
  // spring-injected
  public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate)
  {
    this.simpleJdbcTemplate = simpleJdbcTemplate;
  }

  // spring-injected
  public void setTransactionTemplate(TransactionTemplate transactionTemplate) 
  {
    this.transactionTemplate = transactionTemplate;
  }
  
  class Subscriber
  {
    int id;
    int websiteId;
    String firstName;
    String lastName;
    String password;
    String emailAddress;
    
    public Subscriber () {}
    public Subscriber (int websiteId, String firstName, String lastName, String password, String email) 
    {
      this.websiteId = websiteId;
      this.firstName = firstName;
      this.lastName = lastName;
      this.password = password;
      this.emailAddress = email;
    }
    
    public int getId()
    {
      return id;
    }
    public void setId(int id)
    {
      this.id = id;
    }
    public int getWebsiteId()
    {
      return websiteId;
    }
    public void setWebsiteId(int websiteId)
    {
      this.websiteId = websiteId;
    }
    public String getFirstName()
    {
      return firstName;
    }
    public void setFirstName(String firstName)
    {
      this.firstName = firstName;
    }
    public String getLastName()
    {
      return lastName;
    }
    public void setLastName(String lastName)
    {
      this.lastName = lastName;
    }
    public String getPassword()
    {
      return password;
    }
    public void setPassword(String password)
    {
      this.password = password;
    }
    public String getEmailAddress()
    {
      return emailAddress;
    }
    public void setEmailAddress(String email)
    {
      this.emailAddress = email;
    }
  }
  
  public int insertSubscriberRecord(int websiteId, 
                                    String firstName, 
                                    String lastName, 
                                    String password, 
                                    String email)
  {
    Subscriber subscriber = new Subscriber(websiteId, firstName, lastName, password, email);
    String insertFileString = "INSERT INTO subscribers "
      + "(website_id, first_name, last_name, password, email_address) VALUES "
      + "(:websiteId, :firstName, :lastName, :password, :emailAddress) ";
    // see http://static.springframework.org/spring/docs/2.5.x/reference/jdbc.html
    SqlParameterSource fileParameters = new BeanPropertySqlParameterSource(subscriber);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    getNamedParameterJdbcTemplate().update(insertFileString, fileParameters, keyHolder);
    return keyHolder.getKey().intValue();
  }

  // other methods removed

}

I hope it helps to have the source code for a complete Java Spring DAO class like this that you can copy and paste.

It's probably also worth noting that I don't often embed what is essentially a "model" class inside a Spring DAO class. I just did that because this class comes from a prototype that I was developing, and I didn't really need this Subscriber class anywhere else ... at least not yet.

Permalink

Hi,
I am doing exactly the same thing as mentioned above but when I do a keyholder.getKey(), i get a null pointer exception. I am using mysql 5.1.37 with connector 5.1.8. What could the problem be?
The id that I pass in the insert statement is an int field in my bean with default value as 0.
Please help asap!!

Hi, Thanks for your reply. The error was actually because I had not set my id field as AUTO_INCREMENT in the mySQL table. This was causing the keyholder.getKeys() method to return a null.
thanks again...and probably you could add a line or two about the mysql table definition in your article so that it helps dumb people like me :)
thanks again,
rabbit.

Permalink

Thanks for this. I love when I can find a clear concise straightforward example for those of us who have not yet mastered the java language. Much appreciated.

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.