asked on
Problems retrieving the autogenerated key with Oracle and Spring using SimpleJdbcDaoSupport
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.
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;
}
JavaJava EEJava App Servers
Last Comment
Mick Barry
make sure you're driver supports jdbc 3.0
using a PreparedStatement would look something like:
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);
ASKER
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.
ASKER
It works with MySQL and Postgres but NOT with Oracle. Dang.
Its certainly not, but you may be stuck with it if oracle does not support the cleaner method
are you using the latest driver?
are you using the latest driver?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/returninginto_clause.htm
ASKER
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.
It now works! Thanks for pinpointing that again to me.
ASKER
Here's the change that yields the correct solution
result = getSimpleJdbcTemplate().getNamedParameterJdbcOperations()
.update(insertCustomerSQL, parameterSource, keyholder, new String[] {"custid"});
Great!
> Here's the change that yields the correct solution
good stuff, will certainly update my sites with that detail.
good stuff, will certainly update my sites with that detail.
ASKER
Objects, which sites? I would like to read those.
See my profile, also have a couple more scheduled to go live later this year
You may be stuck with using JdbcTemplate and PreparedStatement