SimpleJdbcTemplate. - insert and get identifier

Question

I put data into a database using simpleJdbcTemplate.

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

I do not want to put any data because I do not need it for my unit test purpose.

How can I get the id from the inserted row? I can get the current value of the sequence, but if someone else does an insert, then I get the next value of the sequence.

Is it possible to use simpleJdbcTemplate to insert a row and get an identifier? The update method reconfigures the number of rows inserted, and I would like to have an identifier. Thank you for your help.

Answer 1

You need to manually process the sequence to easily get the identifier without binding yourself to any particular RDBMS product.

This means that you need to specify the DataFieldMaxValueIncrementer bean for deployment and inject it into the database processing class in the same way as you most likely will 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 increment in your class, you can use it in your code to get the id value something like this:

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

Answer 2

Have you found the answer yet? If not, try using SimpleJdbcInsert . For example:

 SimpleJdbcInsert sji = new SimpleJdbcInsert(dataSource) .withTableName(TableName) .usingColumns(new String[]{your columns}) .usingGeneratedKeyColumns(you auto-increment id colums); 

then remove

 sji.executeAndReturnKey(args).longValue(); 

Answer 3

I think it is as difficult as it seems ..: -O

Do not try something like:

 int newID = simpleJdbcTemplate.queryForInt("INSERT INTO TABLE(Column_Names) values (default) RETURNING ID"); 

Now newID wil contains the newly entered string identifier.

CHEERS .. !! :)

Answer 4

Using NamedParameterJdbcTemplate, you have a keychain. It abstracts the generation of DBMS keys. Check the creation method.

 package info.pello.spring.persistence; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.jdbc.support.GeneratedKeyHolder; import java.util.ArrayList; import java.util.List; /** * */ /** * DAO for customer entity * @author Pello Xabier Altadill Izura * @greetz Blue Mug * */ public class CustomerDAO { // I use both jdbcTemplate/namedParameterJdbcTemplate depending on needs private JdbcTemplate jdbcTemplate; private NamedParameterJdbcTemplate namedParameterJdbcTemplate; private final static String CREATE_SQL = "insert into customer (name,address,email) values(:name,:address,:email)"; /** * gets Customer data from DataBase * @param customerId * @return */ public Customer read (int customerId) { Customer customer = null; return customer; } /** * gets all Customer data from DataBase * @return list of customers */ public List<Customer> readAll () { List<Customer> customerList = new ArrayList<Customer>(); return customerList; } /** * creates new Customer * @param newCustomer * @return */ public int create (Customer newCustomer) { GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder(); MapSqlParameterSource namedParameters = new MapSqlParameterSource(); namedParameters.addValue("name", newCustomer.getName()); namedParameters.addValue("address", newCustomer.getAddress()); namedParameters.addValue("email", newCustomer.getEmail()); namedParameterJdbcTemplate.update(CREATE_SQL, namedParameters, generatedKeyHolder); newCustomer.setId(generatedKeyHolder.getKey().intValue()); return newCustomer.getId(); } /** * updates customer information * @param customer * @return */ public int update (Customer customer) { int result = 0; return result; } /** * delete customer * @param customerId * @return */ public int delete (int customerId) { int result = 0; return result; } /** * @return the jdbcTemplate */ public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } /** * @param jdbcTemplate the jdbcTemplate to set */ public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } /** * @return the namedParameterJdbcTemplate */ public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() { return namedParameterJdbcTemplate; } /** * @param namedParameterJdbcTemplate the namedParameterJdbcTemplate to set */ public void setNamedParameterJdbcTemplate( NamedParameterJdbcTemplate namedParameterJdbcTemplate) { this.namedParameterJdbcTemplate = namedParameterJdbcTemplate; } } 

Answer 5

You must first request the id from the corresponding sequence , and then provide the id in the insert statement. So simple.

In addition, we call this an integration test, not a unit test, perhaps. You can refer to this SO topic to get an idea of ​​integration tests and identifiers.

[Edited after comment]

In this case, get rid of this trigger. And extract id from sequence directly before doing insert .

Well, you can run SELECT... FOR UPDATE in the table and grab the last id , and increment it by 1. If your id not sequential, which I think is wrong, you may contain a Oracle AFAIK-specific ROWID. And then a request for id using this. In fact, it all works.

Note: I highly recommend you take a look at Aaron Digullah's post. See if that’s enough.

Answer 6

Answer this question: what are you trying to achieve with your test? Make sure the update works without errors? What every time you get a new ID? What table exists?

Depending on the answer, you should change your test. If you just want to know that the syntax of the instruction is correct, you do not need to do anything except run the statement (it throws an exception if there is an error due to which the test failed).

If you want to receive a new identifier each time, you must query the sequence twice and verify that the second value is different from the first.

If you want to check that a row with a new unique identifier is inserted, just start the insert and make sure it returns 1. If it works, you will find out that the primary key (identifier) ​​has not been violated and that the row has been inserted. Therefore, the "add with unique identifier" mechanism should work.

[EDIT] It is not possible to test a trigger that adds an identifier to a new line because Oracle does not have the means to return the identifier that it just created. You can read the sequence, but there is no guarantee that nextval-1 will give you the same result as the trigger.

You can try select max(ID) , but it may fail if someone else inserts another row and executes it before you can run the query (using the default transaction level READ_COMMITTED ).

Therefore, I highly recommend to get rid of the trigger and use the standard two-stage algorithm ("get a new identifier" plus "insert with a new identifier"), which any other user uses. This will make your tests simpler and less fragile.

Answer 7

simpleJdbcTemplate is deprecated in favor of NamedParameterJdbcTemplate.

Pello X has the correct answer, but its presentation is too cumbersome to understand. Simplified:

If you have a very simple table called SAMPLE with a NAME column and a primary key that is generated with a name like bigint ID:

 MapSqlParameterSource namedParameters = new MapSqlParameterSource().addValue("name", name); KeyHolder keyHolder = new GeneratedKeyHolder(); int numberOfAffectedRows = namedParameterJdbcTemplate.update("insert into SAMPLE(name) values(:name)", namedParameters, keyHolder); return numberOfAffectedRows == 1 ? keyHolder.getKey().longValue() : -1L; 

This only returns the generated key in the update, or -1 if more than 1 row is affected.

Please note that since I did not like the only 1 generated key, the column name.

If more than one key is created, check out http://docs.spring.io/spring/docs/3.2.7.RELEASE/javadoc-api/org/springframework/jdbc/support/KeyHolder.html#getKeys%28%29