How to get a value from the last inserted row

Question

Is there some way to get a value from the last inserted row   I am inserting a row where the PK will automatically increase  and I would like to get this PK  Only the PK is guaranteed to be unique in the table   I am using Java with a JDBC and PostgreSQL

User · Answer

If you re using JDBC 3 0  then you can get the value of the PK as soon as you inserted it    Here s an article that talks about how   https   www ibm com developerworks java library j-jdbcnew   Statement stmt   conn createStatement       Obtain the generated key that results from the query  stmt executeUpdate  INSERT INTO authors                          first name  last name                          VALUES   George    Orwell                        Statement RETURN GENERATED KEYS   ResultSet rs   stmt getGeneratedKeys    if   rs next              Retrieve the auto generated key s       int key   rs getInt 1

User · Answer

If you re using JDBC 3 0  then you can get the value of the PK as soon as you inserted it    Here s an article that talks about how   https   www ibm com developerworks java library j-jdbcnew   Statement stmt   conn createStatement       Obtain the generated key that results from the query  stmt executeUpdate  INSERT INTO authors                          first name  last name                          VALUES   George    Orwell                        Statement RETURN GENERATED KEYS   ResultSet rs   stmt getGeneratedKeys    if   rs next              Retrieve the auto generated key s       int key   rs getInt 1

User · Answer

If you are in a transaction you can use SELECT lastval   after an insert to get the last generated id

User · Answer

Since PostgreSQL JDBC driver version 8 4-701 the PreparedStatement getGeneratedKeys   is finally fully functional  We use it here almost one year in production to our full satisfaction   In  plain JDBC  the PreparedStatement needs to be created as follows to make it to return the keys   statement   connection prepareStatement SQL  Statement RETURN GENERATED KEYS     You can download the current JDBC driver version here  which is at the moment still 8 4-701

User · Answer

The sequences in postgresql are transaction safe  So you can use the   currval sequence    Quote      currval           Return the value most recently obtained by nextval for this sequence     in the current session   An error is     reported if nextval has never been     called for this sequence in this     session   Notice that because this is     returning a session-local value  it     gives a predictable answer even if     other sessions are executing nextval     meanwhile

User · Answer

See the API docs for java sql Statement   Basically  when you call executeUpdate   or executeQuery    use the Statement RETURN GENERATED KEYS constant   You can then call getGeneratedKeys to get the auto-generated keys of all rows created by that execution   Assuming your JDBC driver provides it    It goes something along the lines of this   Statement stmt   conn createStatement    stmt execute sql  Statement RETURN GENERATED KEYS   ResultSet keyset   stmt getGeneratedKeys

User · Answer

See the API docs for java sql Statement   Basically  when you call executeUpdate   or executeQuery    use the Statement RETURN GENERATED KEYS constant   You can then call getGeneratedKeys to get the auto-generated keys of all rows created by that execution   Assuming your JDBC driver provides it    It goes something along the lines of this   Statement stmt   conn createStatement    stmt execute sql  Statement RETURN GENERATED KEYS   ResultSet keyset   stmt getGeneratedKeys

User · Answer

for example     Connection conn   null              PreparedStatement sth   null              ResultSet rs  null              try                   conn   delegate getConnection                    sth   conn prepareStatement INSERT SQL                   sth setString 1  pais getNombre                     sth executeUpdate                    rs sth getGeneratedKeys                    if rs next                         Integer id    Integer  rs getInt 1                       pais setId id                                      with  Statement RETURN GENERATED KEYS    no found

User · Answer

For MyBatis 3 0 4 with Annotations and Postgresql driver 9 0-801 jdbc4 you define an interface method in your Mapper like  public interface ObjectiveMapper     Select  insert into objectives               code title description  values                 code     title     description   returning id   int insert Objective anObjective     Note that  Select is used instead of  Insert

User · Answer

Here is how I solved it  based on the answers here   Connection conn   ConnectToDB      ConnectToDB establishes a connection to the database  String sql    INSERT INTO   TableName                  Column1      Column2     Column3     Column4                VALUES   value1  value2   value3    value4   RETURNING             TableName     TableId     PreparedStatement prpState   conn prepareStatement sql   ResultSet rs   prpState executeQuery    if rs next           System out println rs getInt 1

User · Answer

With PostgreSQL you can do it via the RETURNING keyword   PostgresSQL - RETURNING  INSERT INTO mytable  field 1  field 2       VALUES   value 1  value 2   RETURNING anyfield   It will return the value of  anyfield    anyfield  may be a sequence or not   To use it with JDBC  do   ResultSet rs   statement executeQuery  INSERT     RETURNING ID    rs next    rs getInt 1

User · Answer

Here is how I solved it  based on the answers here   Connection conn   ConnectToDB      ConnectToDB establishes a connection to the database  String sql    INSERT INTO   TableName                  Column1      Column2     Column3     Column4                VALUES   value1  value2   value3    value4   RETURNING             TableName     TableId     PreparedStatement prpState   conn prepareStatement sql   ResultSet rs   prpState executeQuery    if rs next           System out println rs getInt 1

User · Answer

Use sequences in postgres for id columns   INSERT mytable myid  VALUES  nextval  MySequence      SELECT currval  MySequence      currval will return the current value of the sequence in the same session    In MS SQL  you would use   identity or SCOPE IDENTITY

User · Answer

Since PostgreSQL JDBC driver version 8 4-701 the PreparedStatement getGeneratedKeys   is finally fully functional  We use it here almost one year in production to our full satisfaction   In  plain JDBC  the PreparedStatement needs to be created as follows to make it to return the keys   statement   connection prepareStatement SQL  Statement RETURN GENERATED KEYS     You can download the current JDBC driver version here  which is at the moment still 8 4-701

User · Answer

If you are in a transaction you can use SELECT lastval   after an insert to get the last generated id

User · Answer

See the API docs for java sql Statement   Basically  when you call executeUpdate   or executeQuery    use the Statement RETURN GENERATED KEYS constant   You can then call getGeneratedKeys to get the auto-generated keys of all rows created by that execution   Assuming your JDBC driver provides it    It goes something along the lines of this   Statement stmt   conn createStatement    stmt execute sql  Statement RETURN GENERATED KEYS   ResultSet keyset   stmt getGeneratedKeys

User · Answer

Use sequences in postgres for id columns   INSERT mytable myid  VALUES  nextval  MySequence      SELECT currval  MySequence      currval will return the current value of the sequence in the same session    In MS SQL  you would use   identity or SCOPE IDENTITY

User · Answer

With PostgreSQL you can do it via the RETURNING keyword   PostgresSQL - RETURNING  INSERT INTO mytable  field 1  field 2       VALUES   value 1  value 2   RETURNING anyfield   It will return the value of  anyfield    anyfield  may be a sequence or not   To use it with JDBC  do   ResultSet rs   statement executeQuery  INSERT     RETURNING ID    rs next    rs getInt 1

User · Answer

For MyBatis 3 0 4 with Annotations and Postgresql driver 9 0-801 jdbc4 you define an interface method in your Mapper like  public interface ObjectiveMapper     Select  insert into objectives               code title description  values                 code     title     description   returning id   int insert Objective anObjective     Note that  Select is used instead of  Insert

User · Answer

PreparedStatement stmt   getConnection PROJECTDB   2       prepareStatement  INSERT INTO fonts  font size  VALUES    RETURNING fonts      stmt setString 1   986    ResultSet res   stmt executeQuery    while  res next          System out println  Generated key      res getLong 1        System out println  Generated key      res getInt 2        System out println  Generated key      res getInt 3      stmt close

User · Answer

With PostgreSQL you can do it via the RETURNING keyword   PostgresSQL - RETURNING  INSERT INTO mytable  field 1  field 2       VALUES   value 1  value 2   RETURNING anyfield   It will return the value of  anyfield    anyfield  may be a sequence or not   To use it with JDBC  do   ResultSet rs   statement executeQuery  INSERT     RETURNING ID    rs next    rs getInt 1

User · Answer

The sequences in postgresql are transaction safe  So you can use the   currval sequence    Quote      currval           Return the value most recently obtained by nextval for this sequence     in the current session   An error is     reported if nextval has never been     called for this sequence in this     session   Notice that because this is     returning a session-local value  it     gives a predictable answer even if     other sessions are executing nextval     meanwhile

User · Answer

for example     Connection conn   null              PreparedStatement sth   null              ResultSet rs  null              try                   conn   delegate getConnection                    sth   conn prepareStatement INSERT SQL                   sth setString 1  pais getNombre                     sth executeUpdate                    rs sth getGeneratedKeys                    if rs next                         Integer id    Integer  rs getInt 1                       pais setId id                                      with  Statement RETURN GENERATED KEYS    no found

User · Answer

With PostgreSQL you can do it via the RETURNING keyword   PostgresSQL - RETURNING  INSERT INTO mytable  field 1  field 2       VALUES   value 1  value 2   RETURNING anyfield   It will return the value of  anyfield    anyfield  may be a sequence or not   To use it with JDBC  do   ResultSet rs   statement executeQuery  INSERT     RETURNING ID    rs next    rs getInt 1

User · Answer

If you are using Statement  go for the following    MY NUMBER is the column name in the database  String generatedColumns       MY NUMBER    Statement stmt   conn createStatement       String sql holds the insert query stmt executeUpdate sql  generatedColumns    ResultSet rs   stmt getGeneratedKeys        The generated id  if rs next    long key   rs getLong 1     If you are using PreparedStatement  go for the following  String generatedColumns       MY NUMBER    PreparedStatement pstmt   conn prepareStatement sql generatedColumns   pstmt setString 1   qwerty     pstmt execute    ResultSet rs   pstmt getGeneratedKeys    if rs next    long key   rs getLong 1

User · Answer

The sequences in postgresql are transaction safe  So you can use the   currval sequence    Quote      currval           Return the value most recently obtained by nextval for this sequence     in the current session   An error is     reported if nextval has never been     called for this sequence in this     session   Notice that because this is     returning a session-local value  it     gives a predictable answer even if     other sessions are executing nextval     meanwhile

User · Answer

See the API docs for java sql Statement   Basically  when you call executeUpdate   or executeQuery    use the Statement RETURN GENERATED KEYS constant   You can then call getGeneratedKeys to get the auto-generated keys of all rows created by that execution   Assuming your JDBC driver provides it    It goes something along the lines of this   Statement stmt   conn createStatement    stmt execute sql  Statement RETURN GENERATED KEYS   ResultSet keyset   stmt getGeneratedKeys

User · Answer

The sequences in postgresql are transaction safe  So you can use the   currval sequence    Quote      currval           Return the value most recently obtained by nextval for this sequence     in the current session   An error is     reported if nextval has never been     called for this sequence in this     session   Notice that because this is     returning a session-local value  it     gives a predictable answer even if     other sessions are executing nextval     meanwhile

User · Answer

Don t use SELECT currval  MySequence   - the value gets incremented on inserts that fail

User · Answer

Use that simple code      Do your insert code  myDataBase execSQL  INSERT INTO TABLE NAME  FIELD NAME1 FIELD NAME2     VALUES  VALUE1 VALUE2             Use the sqlite function  last insert rowid   Cursor last id inserted   yourBD rawQuery  SELECT last insert rowid     null       Retrieve data from cursor   last id inserted moveToFirst       Don t forget that   ultimo id   last id inserted getLong 0       For Java  the result is returned on Long type   64

User · Answer

Use sequences in postgres for id columns   INSERT mytable myid  VALUES  nextval  MySequence      SELECT currval  MySequence      currval will return the current value of the sequence in the same session    In MS SQL  you would use   identity or SCOPE IDENTITY

User · Answer

Use that simple code      Do your insert code  myDataBase execSQL  INSERT INTO TABLE NAME  FIELD NAME1 FIELD NAME2     VALUES  VALUE1 VALUE2             Use the sqlite function  last insert rowid   Cursor last id inserted   yourBD rawQuery  SELECT last insert rowid     null       Retrieve data from cursor   last id inserted moveToFirst       Don t forget that   ultimo id   last id inserted getLong 0       For Java  the result is returned on Long type   64

User · Answer

Use sequences in postgres for id columns   INSERT mytable myid  VALUES  nextval  MySequence      SELECT currval  MySequence      currval will return the current value of the sequence in the same session    In MS SQL  you would use   identity or SCOPE IDENTITY

User · Answer

If you are using Statement  go for the following    MY NUMBER is the column name in the database  String generatedColumns       MY NUMBER    Statement stmt   conn createStatement       String sql holds the insert query stmt executeUpdate sql  generatedColumns    ResultSet rs   stmt getGeneratedKeys        The generated id  if rs next    long key   rs getLong 1     If you are using PreparedStatement  go for the following  String generatedColumns       MY NUMBER    PreparedStatement pstmt   conn prepareStatement sql generatedColumns   pstmt setString 1   qwerty     pstmt execute    ResultSet rs   pstmt getGeneratedKeys    if rs next    long key   rs getLong 1

User · Answer

Don t use SELECT currval  MySequence   - the value gets incremented on inserts that fail

User · Answer

Here is how I solved it  based on the answers here   Connection conn   ConnectToDB      ConnectToDB establishes a connection to the database  String sql    INSERT INTO   TableName                  Column1      Column2     Column3     Column4                VALUES   value1  value2   value3    value4   RETURNING             TableName     TableId     PreparedStatement prpState   conn prepareStatement sql   ResultSet rs   prpState executeQuery    if rs next           System out println rs getInt 1

User · Answer

If you re using JDBC 3 0  then you can get the value of the PK as soon as you inserted it    Here s an article that talks about how   https   www ibm com developerworks java library j-jdbcnew   Statement stmt   conn createStatement       Obtain the generated key that results from the query  stmt executeUpdate  INSERT INTO authors                          first name  last name                          VALUES   George    Orwell                        Statement RETURN GENERATED KEYS   ResultSet rs   stmt getGeneratedKeys    if   rs next              Retrieve the auto generated key s       int key   rs getInt 1

User · Answer

PreparedStatement stmt   getConnection PROJECTDB   2       prepareStatement  INSERT INTO fonts  font size  VALUES    RETURNING fonts      stmt setString 1   986    ResultSet res   stmt executeQuery    while  res next          System out println  Generated key      res getLong 1        System out println  Generated key      res getInt 2        System out println  Generated key      res getInt 3      stmt close

User · Answer

Here is how I solved it  based on the answers here   Connection conn   ConnectToDB      ConnectToDB establishes a connection to the database  String sql    INSERT INTO   TableName                  Column1      Column2     Column3     Column4                VALUES   value1  value2   value3    value4   RETURNING             TableName     TableId     PreparedStatement prpState   conn prepareStatement sql   ResultSet rs   prpState executeQuery    if rs next           System out println rs getInt 1

User · Answer

If you re using JDBC 3 0  then you can get the value of the PK as soon as you inserted it    Here s an article that talks about how   https   www ibm com developerworks java library j-jdbcnew   Statement stmt   conn createStatement       Obtain the generated key that results from the query  stmt executeUpdate  INSERT INTO authors                          first name  last name                          VALUES   George    Orwell                        Statement RETURN GENERATED KEYS   ResultSet rs   stmt getGeneratedKeys    if   rs next              Retrieve the auto generated key s       int key   rs getInt 1

[java] How to get a value from the last inserted row?

Examples related to java

Examples related to database

Examples related to postgresql

Examples related to jdbc