How to fetch generated sequence ID using JdbcTemplate on SQL Server 2012+

Question

I have a table in SQL Server 2012 that uses a Sequence to generate the primary key using a default constraint.

CREATE TABLE [vfm].[ChangeEvent](
    [ChangeEventId] [int] NOT NULL,
    [Description] [varchar](100) NOT NULL,
    [LegalEffectiveDate] [date] NOT NULL,
    [StatusCode] [char](1) NOT NULL,
 CONSTRAINT [PK_ChangeEvent] PRIMARY KEY CLUSTERED 
(
    [ChangeEventId] ASC
)

ALTER TABLE [vfm].[ChangeEvent] ADD  CONSTRAINT [DF_ChangeEvent_1]  DEFAULT 
(NEXT VALUE FOR [vfm].[SEQ_ChangeEventId]) FOR [ChangeEventId]

I'm using NamedParameterJdbcTemplate in Spring 5.x to insert a new record into the table.

StringBuilder sql = new StringBuilder()
    .append("insert into vfm.ChangeEvent(Description, LegalEffectiveDate, StatusCode)")
    .append(" values (:description, :legalEffectiveDate, :status)");

SqlParameterSource parameters = new MapSqlParameterSource()
    .addValue("description", changeEvent.getDescription())
    .addValue("legalEffectiveDate", changeEvent.getLegalEffectiveDate())
    .addValue("status", "S");

KeyHolder keyHolder = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update(sql.toString(), parameters, keyHolder, new String[] { "ChangeEventId" });

From what I've seen so far, my KeyHolder comes back empty, GENERATED_KEYS = null. Is it possible to configure the .update call to retrieve the generated ChangeEventId? Or am I stuck because the table is using a sequence object?

I believe this is possible with Oracle, but I haven't seen any answers for SQL Server. The SQL Server questions I've seen have used identity columns, not sequences.

Why not just use an identity? Would make this a non-issue completely. — Sean Lange
I may have to result to an identity, but was hoping I could get an answer using a sequence (even if that answer is "not possible"). My company prefers the use of sequences over identity columns. This article suggests sequences are more flexible and much faster than identities. — ithinkisam
I would agree that a sequence is more flexible but not faster. And the flexibility is being able to use the sequential number across multiple tables. You are using it for a single table so there is no benefit there. It does have the potential to make things a lot slower for you. Consider if you need to insert 10 rows. Are you going to do those inserts as 10 insert statements so you can get a new sequence value for each row? Using an identity in that situation is painless as it handles multiple rows right out of the box. Just curious, why do you prefer a sequence over identity? — Sean Lange
Inserting multiple rows at once isn't a use-case for this system - in short, performance is not a concern here. Our current environment doesn't allow us to manage our own databases, unfortunately. We have to submit all our db requests through a team of DBAs, so I couldn't tell you exactly why they like sequences so much more than identities. — ithinkisam

ithinkisam ithinkisam · Accepted Answer · 2018-02-06T18:33:20

Here's what ended up working for me.

Use output insert.<id> in the SQL query to return the inserted value
Use queryForObject instead of update on NamedParameterJdbcTemplate
Make sure the sequence is set up as the column default

StringBuilder sql = new StringBuilder()
    .append("insert into vfm.ChangeEvent(Description, LegalEffectiveDate, StatusCode)")
    .append(" output inserted.ChangeEventId")
    .append(" values (:description, :legalEffectiveDate, :status)");

SqlParameterSource parameters = new MapSqlParameterSource()
    .addValue("description", changeEvent.getDescription())
    .addValue("legalEffectiveDate", changeEvent.getLegalEffectiveDate())
    .addValue("status", "S");

Integer changeEventId = namedParameterJdbcTemplate.queryForObject(sql.toString(), parameters, Integer.class);

How to fetch generated sequence ID using JdbcTemplate on SQL Server 2012+

1 Answers