We help IT Professionals succeed at work.

Problems retrieving the autogenerated key with Oracle and Spring using SimpleJdbcDaoSupport

2,309 Views
Last Modified: 2012-05-11
Hello,

The following code works perfectly with MySQL versions 5.1 and 5.5. When I run it I can get the autogenerated field value with keyholder.

However, when I do the same agains an Oracle 10 database it returns null.

I do not wish to rewrite the whole thing with prepared statements instead.

So, anyone had the same issue with SimpleJdbcDaoSupport on Spring and Oracle?

Thanks.
public long insertCustomer(Customer aCustomer) {
		long result = -1;
		SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(aCustomer);
		KeyHolder keyholder = new GeneratedKeyHolder();
		try {
			result = getSimpleJdbcTemplate().getNamedParameterJdbcOperations()
					.update(insertCustomerSQL, parameterSource, keyholder);

			if (result > -1) {
				result = keyholder.getKey().longValue();
			}
		} catch (Exception e) {
			// e.printStackTrace();
		}
		return result;
	}

Open in new window

Comment
Watch Question

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
I don't believe you can, its a limitation with Oracle.
You may be stuck with using JdbcTemplate and PreparedStatement
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
make sure you're driver supports jdbc 3.0

using a PreparedStatement would look something like:
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(SQL, new String[] {"id"});
            ps.set.....
            return ps;
        }
    },
    keyHolder);

Open in new window

Author

Commented:
Yep Savant. I saw that before. However I was trying to avoid doing prepared statements. It is not as elegant as the other way I have wrote it.

Author

Commented:
It works with MySQL and Postgres but NOT with Oracle. Dang.
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Its certainly not, but you may be stuck with it if oracle does not support the cleaner method
are you using the latest driver?
Awarded 2011
Awarded 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2009

Commented:
You should be able to get it via using "RETURNING INTO" clause.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/returninginto_clause.htm

Author

Commented:
Perfect for_yan! I saw that page 1 hour ago and did not pay attention to the last part of it. With Oracle you need to specifiy the key name. If you don't it will not match the value to it.

It now works! Thanks for pinpointing that again to me.

Author

Commented:
Here's the change that yields the correct solution
result = getSimpleJdbcTemplate().getNamedParameterJdbcOperations()
			.update(insertCustomerSQL, parameterSource, keyholder, new String[] {"custid"});

Open in new window

Awarded 2011
Awarded 2011

Commented:
Great!
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> Here's the change that yields the correct solution

good stuff, will certainly update my sites with that detail.

Author

Commented:
Objects, which sites? I would like to read those.
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
See my profile, also have a couple more scheduled to go live later this year
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.