Java insert and get id

How can I get the autoincremented id when I insert a record in a table via jdbctemplate

When the above query inserts a record, the ID column in the table autoincrements. Is there a way to get this auto incremented ID back at the time of the insertion. So in this example the return value of my method would be int

4 Answers 4

Check the Chapter 11. Data access using JDBC reference. You can use jdbcTemplate.update as:

EDIT Added imports as asked

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; 

following is the code usage:

final String INSERT_SQL = "insert into my_test (name) values(?)"; final String name = "Rob"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update( new PreparedStatementCreator() < public PreparedStatement createPreparedStatement(Connection connection) throws SQLException < PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] ); ps.setString(1, name); return ps; > >, keyHolder); // keyHolder.getKey() now contains the generated key 

I get id generated by database (MSSQL) after insert like below, imports:

 import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.core.SqlReturnResultSet; import org.springframework.jdbc.core.simple.SimpleJdbcCall; 

 final String INSERT_SQL = "INSERT INTO [table]\n" + " ([column_1]\n" + " ,[column_2])\n" + " VALUES\n" + " (?, ?)"; Connection connection = jdbcTemplate.getDataSource().getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(INSERT_INVOICE_SQL, Statement.RETURN_GENERATED_KEYS); preparedStatement.setString(1, "test 1"); preparedStatement.setString(2, "test 2"); preparedStatement.executeUpdate(); ResultSet keys = preparedStatement.getGeneratedKeys(); if (keys.next()) < Integer generatedId = keys.getInt(1); //id returned after insert execution >

Источник

insert row and get generated ID

I’m trying to use Spring’s JdbcTemplate class to insert a row into a MySQL table named transaction and get the generated ID. The relevant code is:

public Transaction insertTransaction(final Transaction tran) < // Will hold the ID of the row created by the insert KeyHolder keyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() < public PreparedStatement createPreparedStatement(Connection connection) throws SQLException < PreparedStatement ps = connection.prepareStatement(INSERT_TRAN_SQL); ps.setString(1, tran.getTransactionType().toString()); Date sqlDate = new Date(tran.getDate().getTime()); ps.setDate(2, sqlDate); ps.setString(3, tran.getDescription()); return ps; >>, keyHolder); tran.setId(keyHolder.getKey().longValue()); return tran; > 

java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().

Can I insert the row and get the generated ID, without abandoning JdbcTemplate ? I’m using Spring 2.5, MySQL 5.5.27 and MySQL Connector 5.1.26.

3 Answers 3

There is an easier way to get that behaviour:

protected JdbcTemplate jdbcTemplate; private SimpleJdbcInsert insert; this.jdbcTemplate = new JdbcTemplate(this.databaseSetup.getDataSource()); this.insert = new SimpleJdbcInsert(this.jdbcTemplate).withTableName(this.tableName).usingGeneratedKeyColumns(this.pkColumn); 

Then you create a Map called parameters which conmtains the values for each column name in your table and insert a record like this:

 final Map parameters = new HashMap<>(); parameters.put("empName", employee.getName()); // store the String name of employee in the column empName parameters.put("dept", employee.getDepartment()); // store the int (as Integer) of the employee in the column dept final Number key = this.insert.executeAndReturnKey(parameters); final long pk = key.longValue(); 

Источник

Java JDBC Retrieve ID After Insert

I use triggers to set PK column values of all tables so i do not do any operation about IDs in java but i need the ID after insert. How can i get the ID?

stat.execute("INSERT INTO TPROJECT_PROCESS_GROUP(NPROJECT_ID,VDESCRIPTION) " + "VALUES(" + "'" + projectID + "'," + "'" + description + "'" + ""); 

Edit: Hi again I read the question, now I get an exception like ‘unsupported operation'(i translated from my native language the exact english form might be different). i guess this is about oracle’s support for GetGeneratedKeys? Do you know anything about this? Solution: As mentioned in a book about callablestatements This statement can be used to execute stored procedures and functions. Unlike the PreparedStatement, most databases do not perform any preparation for the call,because it is such a simple command. The CallableStatement instances can be used toreturn the object that the stored procedure—or function, to be more exact—returned.

OracleConnection conn = null; //OraclePreparedStatement pstat = null; OracleCallableStatement cstat = null; String sql = "BEGIN INSERT INTO TPROJECT P (VPROJECT_TITLE,VPROJECT_DESC) VALUES(. ) RETURNING P.NPROJECT_ID INTO ?; END;"; try < conn = ConnectionUtility.GetConnection(); cstat = (OracleCallableStatement)conn.prepareCall(sql); cstat.setString(1, title); cstat.setString(2, description); cstat.registerOutParameter(3, OracleTypes.NUMBER); cstat.execute(); int returnedID = cstat.getInt(3); // System.out.println(returnedID); conn.close(); return returnedID; 

Источник

Obtain id of an insert in the same statement [duplicate]

Is there any way of insert a row in a table and get the new generated ID, in only one statement? I want to use JDBC, and the ID will be generated by a sequence or will be an autoincrement field. Thanks for your help. John Pollancre

Of course. Consider void table A(id[autoincrement],field). I would like something like: long into A(field) values ('blah')"); or long into A(id,field) values (sequence.nextval,'blah')"); I want and all made in only one access to DB

8 Answers 8

resultSet = pstmt.getGeneratedKeys(); if (resultSet != null && resultSet.next())

Terrific! I've never heard of it! Do you think it work with ids generated by a sequence? Thanks, dfa!

The Oracle JDBC drivers return the ROWID for getGeneratedKeys, because Oracle doesn't have the actual concept of an auto-generated key -- it has sequences, but you have to explicitly use them to populate the field either in your INSERT or in a trigger. I would use the RETURNING method described by Vincent.

You can use the RETURNING clause to get the value of any column you have updated or inserted into. It works with trigger (i-e: you get the values actually inserted after the execution of triggers). Consider:

SQL> CREATE TABLE a (ID NUMBER PRIMARY KEY); Table created SQL> CREATE SEQUENCE a_seq; Sequence created SQL> VARIABLE x NUMBER; SQL> BEGIN 2 INSERT INTO a VALUES (a_seq.nextval) RETURNING ID INTO :x; 3 END; 4 / PL/SQL procedure successfully completed x --------- 1 SQL> / PL/SQL procedure successfully completed x --------- 2 

Actually, I think nextval followed by currval does work. Here's a bit of code that simulates this behaviour with two threads, one that first does a nextval, then a currval, while a second thread does a nextval in between.

public void checkSequencePerSession() throws Exception < final Object semaphore = new Object(); Runnable thread1 = new Runnable() < public void run() < try < Connection con = getConnection(); Statement s = con.createStatement(); ResultSet r = s.executeQuery("SELECT SEQ_INV_BATCH_DWNLD.nextval AS val FROM DUAL "); r.next(); System.out.println("Session1 nextval is: " + r.getLong("val")); synchronized(semaphore)< semaphore.notify(); >synchronized(semaphore) < semaphore.wait(); >r = s.executeQuery("SELECT SEQ_INV_BATCH_DWNLD.currval AS val FROM DUAL "); r.next(); System.out.println("Session1 currval is: " + r.getLong("val")); con.commit(); > catch (Exception e) < e.printStackTrace(); >> >; Runnable thread2 = new Runnable() < public void run()< try< synchronized(semaphore)< semaphore.wait(); >Connection con = getConnection(); Statement s = con.createStatement(); ResultSet r = s.executeQuery("SELECT SEQ_INV_BATCH_DWNLD.nextval AS val FROM DUAL "); r.next(); System.out.println("Session2 nextval is: " + r.getLong("val")); con.commit(); synchronized(semaphore) < semaphore.notify(); >>catch(Exception e) < e.printStackTrace(); >> >; Thread t1 = new Thread(thread1); Thread t2 = new Thread(thread2); t1.start(); t2.start(); t1.join(); t2.join(); > 

The result is as follows: Session1 nextval is: 47 Session2 nextval is: 48 Session1 currval is: 47

I couldn't comment otherwise I would have added to Vinko Vrsalovic's post:

The id generated by a sequence can be obtained via insert into table values (sequence.NextVal, otherval) select sequence.CurrVal ran in the same transaction as to get a consistent view. 

Updating de sequence after getting a nextval from it is an autonomous transaction. Otherwise another session would get the same value from the sequence. So getting currval will not get the inserted id if anothers sesssion has selected from the sequence in between the insert and select.

The value of the auto-generated ID is not known until after the INSERT is executed, because other statements could be executing concurrently and the RDBMS gets to decide how to schedule which one goes first.

Any function you call in an expression in the INSERT statement would have to be evaluated before the new row is inserted, and therefore it can't know what ID value is generated.

I can think of two options that are close to what you're asking:

• Write a trigger that runs AFTER INSERT, so you have access to the generated ID key value.
• Write a procedure to wrap the insert, so you can execute other code in the procedure and query the last generated ID.

However, I suspect what you're really asking is whether you can query for the last generated ID value by your current session even if other sessions are also inserting rows and generating their own ID values. You can be assured that every RDBMS that offers an auto-increment facility offers a way to query this value, and it tells you the last ID generated in your current session scope. This is not affected by inserts done in other sessions.

Источник

Tech Tutorials

Tutorials and posts about Java, Spring, Hadoop and many more. Java code examples and interview questions. Spring code examples.

Monday, August 16, 2021

How to Get The Inserted ID (Generated ID) in JDBC

In this tutorial we’ll see how to get the ID of the newly inserted record in the DB using JDBC. Getting the ID of the inserted record is useful in the scenario when you are using auto-generated ID in the table (Auto_increment, Sequence, Serial) and you want to insert records in the table having Primary Key – Foreign key relationship.

1. user_master with fields as id (PK), name
2. accounts with fields as id (PK), acct_number and user_id (FK referencing id of user_master)

You want to insert user record and then using that generated id of the user you want to make an entry in accounts where the same id is passed for the user_id column. In this scenario after inserting the user record you will want to get hold of the generated user id.

Getting inserted record’s ID JDBC

In order to get the ID of the inserted records you need to do the following-

1. While creating statement you need to pass the static field RETURN_GENERATED_KEYS which is defined in the Statement interface. This constant indicates that generated keys should be made available for retrieval.

connection.prepareStatement(insertUserSQL, Statement.RETURN_GENERATED_KEYS);

Getting inserted record’s ID Java Program

Here is an example showing the same scenario of inserting User record and then getting the generated ID and insert an account record passing the user_id as the retrieved ID.

private void insertUserAndAccountRecord(Connection connection, String name, int acctNum) throws SQLException < String insertUserSQL = "Insert into user_master (name) values (?)"; String insertAccountSQL = "Insert into accounts (acct_number, user_id) values (. )"; connection.setAutoCommit(false); int userId; PreparedStatement prepStmt = null; try < prepStmt = connection.prepareStatement(insertUserSQL, Statement.RETURN_GENERATED_KEYS); prepStmt.setString(1, name); prepStmt.executeUpdate(); try (ResultSet generatedKeys = prepStmt.getGeneratedKeys()) < if (generatedKeys.next()) < userId = generatedKeys.getInt(1); System.out.println("UserId is- " + userId); >else < connection.rollback(); throw new SQLException("User insertion has problema. No ID returned."); >> prepStmt.close(); prepStmt = connection.prepareStatement(insertAccountSQL); prepStmt.setInt(1, acctNum); prepStmt.setInt(2, userId); prepStmt.executeUpdate(); connection.commit(); >finally < if(prepStmt != null)< prepStmt.close(); >> >

That's all for this topic How to Get The Inserted ID (Generated ID) in JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

Источник