As you've seen, <selectKey> is really not compatible with batch execution. Batches are for executing the same statement over and over again. <selectKey> interferes with that. So instead of insert insert insert insert
You have insert select insert select insert select insert select iBATIS makes a transaction boundary after each group of unique statements. In the first case above there is one transaction, in the second there are 8 transactions. And...the generated keys don't survive past a transaction boundary. This may be more than you wanted to know, but that's why it is failing. If you are doing a mass insert, it's probably better to turn of the generated keys and generate them yourself anyway. Jeff Butler On Thu, Jul 9, 2009 at 8:22 AM, Westhveg <westhstud...@gmail.com> wrote: > > Yes, I know. I specify the 'keyProperty' in 'selectKey' and it works well > for > single execution. The problem is when I use the Batch process, then, it > returns 0 and record doesn't be updated. > > > meindert-3 wrote: > > > > I was saying the id (keyProperty) on your records... > > As far as I know the id property is updated by ibatis (when you tell it > > what the key property is) > > Eg; <selectKey resultClass="java.lang.Integer" keyProperty="uploadLogId" > > > > The uploadLogId property of the record you provided as insert parameter > > should have been updated to the generated primary key > > So you can loop through the list and check this property after the batch > > has been executed > > > > > > -----Original Message----- > > From: Westhveg [mailto:westhstud...@gmail.com] > > Sent: 09 July 2009 02:27 PM > > To: user-java@ibatis.apache.org > > Subject: RE: Retrieve inserted id in mass insert using SqlMapExecutor. > > > > > > No, I'm not able. > > > > There are two ways to execute batch process, 'executeBatch()' and > > 'executeBatchDetailed()'. The first return an Integer with the total > > number > > of affected rows. The second returns a List of 'BatchResult', where it > > have > > one BatchResult for each iBatis batched operation (in my case we have > only > > one, 'insertRecord'). But the BatchResult object doesn't contain > > information > > about the retrieved ID or Object, it's methods are: > > > > · java.lang.String getSql() > > · java.lang.String getStatementId() > > · int[] getUpdateCounts() > > · void setUpdateCounts(int[] updateCounts) > > > > see: > > > http://ibatis.apache.org/docs/java/dev/com/ibatis/sqlmap/engine/execution/SqlExecutor.html > > > http://ibatis.apache.org/docs/java/dev/com/ibatis/sqlmap/engine/execution/BatchResult.html > > > > > > > > Thanks, > > > > Westhveg > > > > > > > > > > > > > > meindert-3 wrote: > >> > >> Ok, don't know for sure, but wouldn't you be able to loop through the > >> list > >> after the batch has executed and check the id (keyProperty) on your > >> records? > >> > >> > >> -----Original Message----- > >> From: Westhveg [mailto:westhstud...@gmail.com] > >> Sent: 09 July 2009 11:47 AM > >> To: user-java@ibatis.apache.org > >> Subject: RE: Retrieve inserted id in mass insert using SqlMapExecutor. > >> > >> > >> Hello meindert-3, > >> > >> Thanks for your reply. > >> > >> This is correct and in my case is already working. But my problem is > when > >> I > >> use batch processes. Remembering the code: > >> > >> public List<T> createRecordsMassively(final List<T> records) { > >> > >> getSqlMapClientTemplate().execute(new SqlMapClientCallback() { > >> > >> public Object doInSqlMapClient(SqlMapExecutor executor) > >> throws > >> SQLException { > >> executor.startBatch(); > >> > >> for (T record : records) { > >> executor.insert("insertRecord", record); > >> } > >> > >> //List<BatchResult> result = > >> executor.executeBatchDetailed(); > >> int rowsAffected = executor.executeBatch(); > >> return new Integer(rowsAffected); > >> } > >> }); > >> > >> /*Deal with result*/ > >> > >> } > >> > >> In the line 'executor.insert("insertRecord", record);' I always recieve > >> 0. > >> I > >> think this is because is a batch process and it's being executed after, > >> in > >> line 'executor.executeBatch()'. > >> So, just for clarify, if I execute 'insertRecord' WITHOUT batch process, > >> it > >> returns the ID. If I use Batch process (the reason of this post) I > always > >> recieve 0. > >> > >> > >> Thanks, > >> > >> Westhveg > >> > >> > >> meindert-3 wrote: > >>> > >>> Put the name of the property that hold the ID in the sql map and the > >>> record get's updated with the id and your insert statement returns the > >>> id > >>> object > >>> > >>> Here is MS SQl example, just replace SELECT @@IDENTITY with the correct > >>> query for Mysql to fetch the ID > >>> <insert id="ibatorgenerated_insert" parameterClass=".." > > >>> <!-- > >>> WARNING - This element is automatically generated by Apache > iBATIS > >>> ibator, do not modify. > >>> This element was generated on Fri Mar 06 08:29:29 GMT 2009. > >>> --> > >>> insert into online_upload_log (id, userid, actmonth, basemonth, > >>> datestamp, result) > >>> values (#uploadLogId:INTEGER#, #userid:INTEGER#, > #actmonth:INTEGER#, > >>> #basemonth:BIT#, > >>> #datestamp:TIMESTAMP#, #result:LONGVARCHAR#) > >>> <selectKey resultClass="java.lang.Integer" keyProperty="uploadLogId" > >>> > > >>> SELECT @@IDENTITY > >>> </selectKey> > >>> </insert> > >>> > >>> Meindert > >>> > >>> -----Original Message----- > >>> From: Westhveg [mailto:westhstud...@gmail.com] > >>> Sent: 09 July 2009 10:34 AM > >>> To: user-java@ibatis.apache.org > >>> Subject: Retrieve inserted id in mass insert using SqlMapExecutor. > >>> > >>> > >>> Hello, > >>> > >>> My environtment is: > >>> > >>> Java 1.6 > >>> iBatis 2.3.4 > >>> Spring 2.5.6 > >>> MySQL > >>> > >>> > >>> I've a method to create records massively (I write directly, it can has > >>> sintax errors; just take the idea): > >>> > >>> public List<T> createRecordsMassively(final List<T> records) { > >>> > >>> getSqlMapClientTemplate().execute(new SqlMapClientCallback() { > >>> > >>> public Object doInSqlMapClient(SqlMapExecutor executor) > >>> throws > >>> SQLException { > >>> executor.startBatch(); > >>> > >>> for (T record : records) { > >>> executor.insert("insertRecord", record); > >>> } > >>> > >>> //List<BatchResult> result = > executor.executeBatchDetailed(); > >>> int rowsAffected = executor.executeBatch(); > >>> return new Integer(rowsAffected); > >>> } > >>> }); > >>> > >>> /*Deal with result*/ > >>> > >>> } > >>> > >>> It works well. The problem is: I want to know which records are > >>> correctly > >>> inserted and which aren't. I need the Id in order to execute some logic > >>> with > >>> each inserted record. ¿What's the way? ¿How can I retrieve the id of > the > >>> inserted records? > >>> > >>> > >>> Thanks in advance, > >>> > >>> Westhveg > >>> -- > >>> View this message in context: > >>> > http://www.nabble.com/Retrieve-inserted-id-in-mass-insert-using-SqlMapExecutor.-tp24406261p24406261.html > >>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com. > >>> > >>> > >>> --------------------------------------------------------------------- > >>> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > >>> For additional commands, e-mail: user-java-h...@ibatis.apache.org > >>> > >>> > >>> Checked by AVG - www.avg.com > >>> Version: 8.5.375 / Virus Database: 270.13.8/2223 - Release Date: > >>> 07/08/09 > >>> 21:51:00 > >>> > >>> > >>> --------------------------------------------------------------------- > >>> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > >>> For additional commands, e-mail: user-java-h...@ibatis.apache.org > >>> > >>> > >>> > >> > >> -- > >> View this message in context: > >> > http://www.nabble.com/Retrieve-inserted-id-in-mass-insert-using-SqlMapExecutor.-tp24406261p24407224.html > >> Sent from the iBATIS - User - Java mailing list archive at Nabble.com. > >> > >> > >> --------------------------------------------------------------------- > >> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > >> For additional commands, e-mail: user-java-h...@ibatis.apache.org > >> > >> > >> Checked by AVG - www.avg.com > >> Version: 8.5.375 / Virus Database: 270.13.8/2223 - Release Date: > 07/08/09 > >> 21:51:00 > >> > >> > >> --------------------------------------------------------------------- > >> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > >> For additional commands, e-mail: user-java-h...@ibatis.apache.org > >> > >> > >> > > > > -- > > View this message in context: > > > http://www.nabble.com/Retrieve-inserted-id-in-mass-insert-using-SqlMapExecutor.-tp24406261p24409209.html > > Sent from the iBATIS - User - Java mailing list archive at Nabble.com. > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > > For additional commands, e-mail: user-java-h...@ibatis.apache.org > > > > > > Checked by AVG - www.avg.com > > Version: 8.5.375 / Virus Database: 270.13.8/2223 - Release Date: 07/08/09 > > 21:51:00 > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > > For additional commands, e-mail: user-java-h...@ibatis.apache.org > > > > > > > > -- > View this message in context: > http://www.nabble.com/Retrieve-inserted-id-in-mass-insert-using-SqlMapExecutor.-tp24406261p24410041.html > Sent from the iBATIS - User - Java mailing list archive at Nabble.com. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > For additional commands, e-mail: user-java-h...@ibatis.apache.org > >
