File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes Problem with iBATIS + MySQL + auto increment keys Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Problem with iBATIS + MySQL + auto increment keys" Watch "Problem with iBATIS + MySQL + auto increment keys" New topic

Problem with iBATIS + MySQL + auto increment keys

Matteo Di Furia
Ranch Hand

Joined: Jun 20, 2008
Posts: 102
Hi everyone, this seems quite a simple problem to solve, still I can't find a solution for it, nor can find on the web some usefull info to head me in the right direction.
So, I'm using MySQL and iBATIS as my ORM tool. I got a table defined like this :

While my insert statement is defined like this in the sql mapping xml generated by iBator.

Please note that this <insert> node has not been generated by iBator, I defined it by hand starting from the default one generated by Ibator and added the <selectKey> element to retrieve the last inserted autoincrement id (this because Ibator generates an insert statement assuming the object has the key field yet assigned, thus ignoring the autoincrement option).
Inserting the first record on DB runs without any error and I can get back the generated autoincrement key (which is, by no surprise, 1!).
During the 2nd execution, anyway, I get this exception :

So, it seems that MySQL is doing something wrong when generating the 2nd key for the next record inserted. Please also note that I suspect this is an iBATIS problem since using autoincrement keys with raw JDBC code brings no errors at all and everything works fine. I didn't make test to see if I get this to work WITHOUT retrieving the last inserted key (thus deleting the <selectKey> element in mapping xml), but that would not be what I need the code to do.

Any suggestions to solve this ?

Thank you for your help and support.
Matteo Di Furia
Ranch Hand

Joined: Jun 20, 2008
Posts: 102
Sorry everyone, I figured out the problem was not in the mapping I posted, just in a very similar part later in code flow. I asked Scott Selikoff to close and delete this, just to not let anyone to loose time on an unexistent problem.
David W Brown

Joined: Feb 13, 2008
Posts: 12
Hello Matteo, I would appreciate if you would post the difference in what you thought was a problem. I am currently having a similar problem with a referenced table unable to acquire the auto-generated key. I know the key is being generated in the parent table as I set @Rollback(value=true) on the JUnit test method. And, as a result, the parent table record gets inserted with a new record primary key ID. But, then, the new key ID value is lost on the child table that needs the generated primary key value to allow the insertion of a new record into the child table. And, as a result the record insertion attempt on the child table throws the following exceptions. Any ideas are considered helpful. Regards, David.

org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation; SQL [];


--- The error occurred in com/st/data/master_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the master.insert-InlineParameterMap

Cannot add or update a child row: a foreign key constraint fails (`elblanco`.`master`, CONSTRAINT `fk_Master` FOREIGN KEY (`Id`) REFERENCES `master` (`Id`) ON UPDATE CASCADE); nested exception is com.ibatis.common.jdbc.exception.NestedSQLException

Yet some, not wise, go to the other side of the globe, to barbarous and unhealthy regions, and devote ten or twenty years, in that they may live,-that is, keep comfortably warm,- and die in New England at last. Henry David Thoreau - Walden - 1845
silvanoh silvanoh

Joined: Aug 14, 2009
Posts: 1
Matteo Di Furia wrote:

hi, I have same problem. I've resolved changing position in tag <insert> between query text and tag <selectKey>.
It's strange for me but it works
I agree. Here's the link:
subject: Problem with iBATIS + MySQL + auto increment keys
It's not a secret anymore!