JAVA with SQL: the return value of insert command?

Question

I have a table of authors : authorID, authorName. authorID is a pk with auto increment.

I'd like to write a method in java that gets a name from user and adds it to the table. however i need to return the id of the author. is there a way to do that with 1 sql statement?

for example if my code has the command:

stmt.executeUpdate("INSERT INTO authors " + "VALUES (, '"+ string.get(1) +"')");

which string.get(1) is the author name.

Now if i write:

ResultSet rs =stmt.executeUpdate("INSERT INTO authors " + "VALUES (, '"+ string.get(1) +"')");

it says error as rs is resultset but the returned value is int. is this int the pk of the row that i have inserted?

No, its the number of rows that has been modified according to your query. A primary key is not always a single integer you know. — Gimby
– Gimby, Commented Jan 9, 2013 at 11:32
int is the number rows affected means if you have executed one insert then int will be 1. — amicngh
– amicngh, Commented Jan 9, 2013 at 11:32
where does string.get(1) come from? Keep Little Bobby Tables in your mind! — jlordo
– jlordo, Commented Jan 9, 2013 at 11:36
OKI thx, but i still don't know if i can insert the row and get it's pk with 1 sql statment? — jeff ranz
– jeff ranz, Commented Jan 9, 2013 at 11:37

Evgeniy Dorofeev · Accepted Answer · 2013-01-09 11:37:07Z

11

try

stmt.executeUpdate("INSERT INTO authors VALUES (, '"+ string.get(1) +"')", Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
long pk = rs.getLong(1);

answered Jan 9, 2013 at 11:37

Evgeniy Dorofeev

137k31 gold badges209 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jeff ranz Over a year ago

thank you! works like t a charm. only diffrent is that first parameter should be null like: VALUES (null, '"+ string.get(1) +"')

Mark Rotteveel Over a year ago

Or better yet, the OP should use a parametrized query

Andrew Over a year ago

Please use a parameterized query. This code as presented is an SQL injection attack waiting to happen.

Swapnil Gangrade Over a year ago

what if stmt.getGeneratedKeys() throws SQLException, how to delete inserted record?..

Subhrajyoti Majumder · Accepted Answer · 2013-01-09 11:36:25Z

5

Pass Statement.RETURN_GENERATED_KEYS while creating PreparedStatement conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

and PreparedStatement#getGeneratedKeys() returns auto generated key after inert.

answered Jan 9, 2013 at 11:36

Subhrajyoti Majumder

41.3k13 gold badges84 silver badges103 bronze badges

2 Comments

Sajib Acharya Over a year ago

What will it it return if the SQL fails, like, for example, the SQL query was wrong?

Subhrajyoti Majumder Over a year ago

on that case it will throw SQL Exception, find more documentation here. docs.oracle.com/javase/8/docs/api/java/sql/…

Mark Rotteveel · Accepted Answer · 2013-01-09 12:27:57Z

5

In order to get last inserted id look at the below code:

PreparedStatement stmnt = Conn.preparedStatement("INSERT INTO authors(col1) VALUES (?)", Statement. RETURN_GENERATED_KEYS );
stmnt.setString(col1val);
stmnt.executeUpdate();
ResultSet rs=stmnt.getGeneratedKeys();
if(rs.next()){
   System.out.println(rs.getInt(1));
}

edited Jan 9, 2013 at 12:27

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

answered Jan 9, 2013 at 11:37

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

Comments

BigMike · Accepted Answer · 2013-01-09 11:35:15Z

0

I don't know if this is supported by JDBC (haven't used it in a while), but I guess you could try:

INSERT INTO TABLE_NAME (FIELD1, FIELD2) VALUES ('foo', 'bar') RETURNING FIELD3

via executeQuery() or, if your jdbc driver doesn't support RETURNING write a simple stored function.

Which RDMBS are you using?

answered Jan 9, 2013 at 11:35

BigMike

6,8931 gold badge25 silver badges27 bronze badges

Comments

Mark Rotteveel · Accepted Answer · 2013-01-09 12:53:48Z

0

Try:

INSERT INTO authors VALUES (...) returning authorID

When you execute it, do so with executeQuery. You should get back a ResultSet with one row and one column — it will be the ID.

edited Jan 9, 2013 at 12:53

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

answered Jan 9, 2013 at 11:32

Andrew

12k12 gold badges72 silver badges86 bronze badges

3 Comments

jeff ranz Over a year ago

ResultSet rs =stmt.executeUpdate("INSERT INTO authors " + "VALUES (, '"+ string.get(1) +"' returning authorID)"); doesn't work

BigMike Over a year ago

@jeffranz use executeQuery

Andrew Over a year ago

... and move the closing bracket from after authorID to before returning.

Collectives™ on Stack Overflow

JAVA with SQL: the return value of insert command?

5 Answers 5

4 Comments

2 Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related