simpleJdbcTemplate. - insert and retrieve ID

Question

I'm putting the data into database with simpleJdbcTemplate.

simpleJdbcTemplate.update("insert into TABLE values(default)");

I dont want to put any data because i dont need it for my unit test purpose.

How can i get the id from the inserted row? I can retriev the current sequence value but if somebody else will do a insert then i will be get a next sequence value.

Is there any way to use simpleJdbcTemplate to insert a row and get id? The update method retuns the number of inserted rows and i would like to have the id. Thank you for your help.

Answer 1

You should first query the id from the appropriate sequence, and then provide that id in your insert statement. As simple as that.

Further, we call it integration test, instead of unit test, arguably. You might like to refer to this SO thread to have an idea regarding integration tests and ids.

[Edited after comment]

In that case, get rid of that trigger. And retrieve the id from the sequence directly, prior to make a insert.

Well, you can fire a SELECT... FOR UPDATE on the table, and grab the last id, and increment that by 1. If your id is not sequential, which I guess wouldn't be the case, you can hold the ROWID, specific to Oracle AFAIK. And then query for id using that. Indeed, its all kinda work around.

Note: I strongly suggest you to look at Aaron Digulla's post. See if any of that suffice.

Answer 2

You need to manually handle the sequence to get the id easily without tying yourself into any specific RDBMS product.

This means that you have to specify a deployment-specific DataFieldMaxValueIncrementer bean and inject that to your database handling class just as you most likely do with your DataSource. The bean definition should look something like this (this example is for PostgreSQL):

<bean id="incrementer" class="org.springframework.jdbc.support.incrementer.PostgreSQLSequenceMaxValueIncrementer">
    <property name="dataSource" ref="dataSource" />
    <property name="incrementerName" value="seq_name" />
</bean>

Then when you have the incrementer in your class, you can use it in your code to get the id value somewhat like this:

public long saveBeanAndReturnId(Bean b) {
    long id = incrementer.nextLongValue();
    simpleJdbc.update("...");
    return id;
}

Answer 3

Answer this question: What are you trying to achieve with your test? Check that the update runs without error? That you get a new ID every time? That the table exists?

Depending on the answer, you must modify your test. If you just want to know that the syntax of the statement is correct, you don't need to do anything but run the statement (it will throw an exception if there is an error making the test fail).

If you want to make sure you get a new ID every time, you must query the sequence two times and check that the second value is different from the first.

If you want to check that a row with a new unique ID is inserted, just run the insert and check that it returns 1. If it works, you'll know that the primary key (the ID) wasn't violated and that a row was inserted. Hence, the "add with unique ID" mechanism must work.

[EDIT] There is no way to test a trigger which adds an ID to a new row because Oracle has no means to return the ID it just created. You could read the sequence but there is no guarantee that nextval-1 will give you the same result that the trigger saw.

You could try select max(ID) but that can fail if anyone else inserts another row and commits it before you can run the query (using the default transaction level READ_COMMITTED).

Therefore I strongly suggest to get rid of the trigger and use the standard 2-step ("get new ID" plus "insert with new ID") algorithm that anyone else uses. It will make your tests more simple and less brittle.