+- 0Day Forums (https://zeroday.vip)
+-- Forum: Coding (https://zeroday.vip/Forum-Coding)
+--- Forum: Database (https://zeroday.vip/Forum-Database)
+--- Thread: How to get a value from the last inserted row? (/Thread-How-to-get-a-value-from-the-last-inserted-row)
Pages:
1
2
How to get a value from the last inserted row? - waipkxgb - 07-20-2023
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.
RE: How to get a value from the last inserted row? - lend517927 - 07-20-2023
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())
RE: How to get a value from the last inserted row? - sheelahiqb - 07-20-2023
See the API docs for [java.sql.Statement][1].
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();
[1]:
[To see links please register here]
RE: How to get a value from the last inserted row? - intercombine763626 - 07-20-2023
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 :
[To see links please register here]
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);
}
RE: How to get a value from the last inserted row? - myriosporous326566 - 07-20-2023
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));
}
RE: How to get a value from the last inserted row? - smukler393 - 07-20-2023
Don't use SELECT currval('MySequence') - the value gets incremented on inserts that fail.
RE: How to get a value from the last inserted row? - Mrbetsykjjvtaen - 07-20-2023
The sequences in postgresql are transaction safe. So you can use the
currval(sequence)
[Quote:][1]
> 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.
[1]:
[To see links please register here]
RE: How to get a value from the last inserted row? - cliffcliffes99 - 07-20-2023
Since PostgreSQL JDBC driver version [8.4-701][1] the [`PreparedStatement#getGeneratedKeys()`][2] 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][3] (which is at the moment still 8.4-701).
[1]:
[To see links please register here]
[2]:[To see links please register here]
[3]:[To see links please register here]
RE: How to get a value from the last inserted row? - beatee552547 - 07-20-2023
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.
RE: How to get a value from the last inserted row? - radford425 - 07-20-2023
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();