Best IT Recipes

Primary key from jdbc inserted string?

Is there a way to cross-database to get the primary key of the record you just inserted?

I noted that this answer says that you can get it by calling SELECT LAST_INSERT_ID() , and I think you can call SELECT @@IDENTITY AS 'Identity'; is there a general way to do this through databases in jdbc?

If you hadn’t invited me to implement this for a piece of code that could access any SQL Server, MySQL, or Oracle?

+34
java identity jdbc
Oct. 14 '08 at 16:29
source share
7 answers

Copied from my code:

 pInsertOid = connection.prepareStatement(INSERT_OID_SQL, Statement.RETURN_GENERATED_KEYS);

where pInsertOid is a prepared statement.

you can get the key:

 // fill in the prepared statement and pInsertOid.executeUpdate(); ResultSet rs = pInsertOid.getGeneratedKeys(); if (rs.next()) { int newId = rs.getInt(1); oid.setId(newId); }

Hope this gives you a good starting point.

+45
Oct. 14 '08 at 19:40
source share

extraneon answer, although correct, does not work for Oracle .

How do you do this for Oracle:

 String key[] = {"ID"}; //put the name of the primary key column ps = con.prepareStatement(insertQuery, key); ps.executeUpdate(); rs = ps.getGeneratedKeys(); if (rs.next()) { generatedKey = rs.getLong(1); }
+19
Dec 14 '12 at 6:19 06:19
source share

Have you tried Statement.executeUpdate () and Statement.getGeneratedKeys () methods? There is a developerWorks article that mentions this approach.

In addition, a row_id function has been added to JDBC 4.0 Sun, which allows you to get a unique handle to a string. This feature is supported by Oracle and DB2. For sql server, you probably need a third-party driver like this one .

Good luck

+3
Oct 14 '08 at 21:26
source share

for oracle, Hibernate uses NEXT_VALUE from the sequence if you displayed the sequence to generate the PKEY value.

Not sure what it does for MySQL or MS SQL Server

+1
Oct 14 '08 at 21:01
source share

Spring provides some useful support for this operation , and the reference manual seems to answer your question:

There is no standard single way to create a suitable PreparedStatement (which explains why the method signature is the way it is). An example that works in Oracle and might not work on other platforms is ...

I tested this example in MySQL and works there too, but I can't speak on other platforms.

+1
Oct 17 '08 at 16:16
source share

For databases conforming to SQL-99, you can use the identifier columns: CREATE TABLE sometable (id INTEGER GENERATED ALWAYS AS IDENTIFICATION (START WITH 101) PRIMARY KEY, ...

Use getGeneratedKeys () to retrieve the key that was just inserted using executeUpdate (String sql, int autoGeneratedKeys) . Use Statement.RETURN_GENERATED_KEYS for the second parameter to executeUpdate ()

+1
Sep 27 2018-11-11T00:
source share

Just declare the id column as an integer id, not the primary key NULL auto_increment

after that execute this code

 ResultSet ds=st.executeQuery("select * from user"); while(ds.next()) { ds.last(); System.out.println("please note down your registration id which is "+ds.getInt("id")); } ds.close();

the above code will show you the current row id

if you delete ds.last() than display all the id column values

-2
Jul 31 '15 at 7:45
source share


More articles:


All Articles