File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with iBATIS + MySQL + auto increment keys

 
Matteo Di Furia
Ranch Hand
Posts: 102
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 102
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 12
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 [];


com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException

--- 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
 
silvanoh silvanoh
Greenhorn
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic