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.
books by alvin
 |
 |
 |
 |