How to get a value from the last inserted row? - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/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(); |