ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Butler <jeffgbut...@gmail.com>
Subject Re: Retrieve inserted id in mass insert using SqlMapExecutor.
Date Thu, 09 Jul 2009 16:35:18 GMT
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 <westhstudios@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:westhstudios@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:westhstudios@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:westhstudios@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-unsubscribe@ibatis.apache.org
> >>> For additional commands, e-mail: user-java-help@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-unsubscribe@ibatis.apache.org
> >>> For additional commands, e-mail: user-java-help@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-unsubscribe@ibatis.apache.org
> >> For additional commands, e-mail: user-java-help@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-unsubscribe@ibatis.apache.org
> >> For additional commands, e-mail: user-java-help@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-unsubscribe@ibatis.apache.org
> > For additional commands, e-mail: user-java-help@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-unsubscribe@ibatis.apache.org
> > For additional commands, e-mail: user-java-help@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-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>

Mime
View raw message