Check out our Angular Book Series.

How do you get the ID of the inserted row with Java and SQL Server?

I've been playing around with the Learn With series and building the backend services in Java. I was going right along until I got to the chapter to create and update data. The query is something like this:


String SQL = "insert into tasks(taskCategoryID, userID, description, completed, dateCreated) values(?,?, ?, 0, ? )
SELECT SCOPE_IDENTITY() as taskID";

This is a super common approach to creating a new item in SQL Server, and then immediately selecting the newly created ID with SCOPE_IDENTITY(). I'd expect it to work easily.

The rest of the code:


PreparedStatement pstmt = connection.prepareStatement(SQL);

// set parameters
pstmt.setInt(1, taskCategoryID);
pstmt.setInt(2, userID);
pstmt.setString(3, description);
LocalDate dateCreated = LocalDate.now();
pstmt.setObject(4, dateCreated);
ResultSet rs = pstmt.executeQuery();

The last line will throw an error claiming that the query returned no results sets and blow up. I had been swimming along smoothly until I ran into that issue. It turns out that, for updates and inserts, that is the wrong approach. Instead of using executeQuery(), I need to use executeUpdate(), like this:


pstmt.executeUpdate();

After that, I can get the second result set using this:


ResultSet rs = pstmt.getGeneratedKeys();

Then things are good to go.

You'll probably find the LearnWith series supporting Java next month.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
All Content Copyright 2005, 2006, 2007, 2008, 2009 Jeffry Houser. May not be reused without permission
BlogCFC was created by Raymond Camden. This blog is running version 5.9.2.002.