CODEWITHSUNDEEP
javasqljdbc
jeff ranz
jeff ranz

Reputation: 55

JAVA with SQL: the return value of insert command?

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?

Upvotes: 5

Views: 28849

Answers (5)

Andrew
Andrew

Reputation: 12009

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.

Upvotes: 0

PermGenError
PermGenError

Reputation: 46428

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));
}

Upvotes: 5

Evgeniy Dorofeev
Evgeniy Dorofeev

Reputation: 136062

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);

Upvotes: 11

Subhrajyoti Majumder
Subhrajyoti Majumder

Reputation: 41220

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

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

Upvotes: 5

BigMike
BigMike

Reputation: 6873

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?

Upvotes: 0

Related Questions