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.

Scauper

thakns for answer. yes it is oracle. – Scauper

Answer 1

Did you find the answer yet? If not, try to use SimpleJdbcInsert instead. For example:

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

then retrieve

sji.executeAndReturnKey(args).longValue();

Outward

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

Bowens

Answer 3

I dun think its as tough as it seems.. :-O

Y dont you try something like :

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

Now newID wil contains the newly Inserted row ID.

CHEERS..!! :)

Charades

I have tired this and got following error; "No results were returned by the query." May be I'm doing something wrong. – Mutz

Answer 4

Using NamedParameterJdbcTemplate you have a keyholder. It abstracts DBMS key generation. Check create 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;
    }


}

Beanery

For me using the KeyHolder only returns the ROWID not the actual sequence value. The code as shown produces an exception: org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]. – Thunderpeal

Answer 5

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.

Bonin

The id is generated by trigger in the database. – Scauper

trigger must be using some sequence deep down somewhere, I suppose. BTW, these are the things that make testing hairy. – Bonin

Answer 6

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.

Fra

Yes, the question was begging for questions. +1. I highly recommend the same. Excellent suggestions. – Bonin

I have a implementation of Temporal Object design (see Martin Folwer web page for details). I have a continuum object and temporal object and method dao.load(Continuum.class, continnumID). This method returns me a temporal object. There are many temporal objects which can be connected to one continnum object. Every temporal object has a continuum to it belongs to. So in my test i would like to first put continuum object into database with insert statetment. Then i would like to get the id of inserted object to load the temporal object. – Scauper

In that case, get rid of that trigger. And retrieve the id first, prior to make a insert. – Bonin

I found other solution. Using SimpleJdbcInsert.executeAndReturnKey method. This works for me as i needed. Tanks everyone. – Scauper

Answer 7

simpleJdbcTemplate is deprecated in favour of NamedParameterJdbcTemplate.

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

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

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 will return the only generated key in the update or -1 if more than 1 row was affected.

Note that since there was only 1 generated key I didn't care about the column name.

If there is more than 1 key that is generated, look into http://docs.spring.io/spring/docs/3.2.7.RELEASE/javadoc-api/org/springframework/jdbc/support/KeyHolder.html#getKeys%28%29

Oeillade

Answer 8

With Spring's JdbcTemplate you can use its update method with a PreparedStatementCreator and a GeneratedKeyholder to hold the primary key of the newly inserted row.

public class SomeDao(){
   @Autowired
   private JdbcTemplate jdbcTemplate;
   //example of a insertion returning the primary key
   public long save(final String name){
       final KeyHolder holder = new GeneratedKeyHolder();//the newly generated key will be contained in this Object
       jdbcTemplate.update(new PreparedStatementCreator() {
      @Override
      public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement("INSERT INTO `names` (`name`) VALUES (?)",
            Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, name);
        return ps;
      }
    }, holder);
    return holder.getKey().longValue();//the primary key of the newly inserted row
   }
}

Kauppi