« Return to Thread: Retrieve inserted id in mass insert using SqlMapExecutor.

Re: Retrieve inserted id in mass insert using SqlMapExecutor.

by Jeff Butler-2 :: Rate this Message:

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

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 <westhstudios@...> 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:westhstudios@...]
> Sent: 09 July 2009 02:27 PM
> To: user-java@...
> 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:westhstudios@...]
>> Sent: 09 July 2009 11:47 AM
>> To: user-java@...
>> 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=".." >
>>> 
>>> 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:westhstudios@...]
>>> Sent: 09 July 2009 10:34 AM
>>> To: user-java@...
>>> 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-unsubscribe@...
>>> For additional commands, e-mail: user-java-help@...
>>>
>>>
>>> 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-unsubscribe@...
>>> For additional commands, e-mail: user-java-help@...
>>>
>>>
>>>
>>
>> --
>> 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-unsubscribe@...
>> For additional commands, e-mail: user-java-help@...
>>
>>
>> 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-unsubscribe@...
>> For additional commands, e-mail: user-java-help@...
>>
>>
>>
>
> --
> 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-unsubscribe@...
> For additional commands, e-mail: user-java-help@...
>
>
> 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-unsubscribe@...
> For additional commands, e-mail: user-java-help@...
>
>
>

--

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-unsubscribe@...
For additional commands, e-mail: user-java-help@...

« Return to Thread: Retrieve inserted id in mass insert using SqlMapExecutor.