You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Westhveg <we...@gmail.com> on 2009/07/09 10:34:13 UTC
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
Re: Retrieve inserted id in mass insert using SqlMapExecutor.
Posted by Jeff Butler <je...@gmail.com>.
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 <we...@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
>
>
RE: Retrieve inserted id in mass insert using SqlMapExecutor.
Posted by Westhveg <we...@gmail.com>.
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
RE: Retrieve inserted id in mass insert using SqlMapExecutor.
Posted by meindert <me...@eduflex.com>.
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
RE: Retrieve inserted id in mass insert using SqlMapExecutor.
Posted by Westhveg <we...@gmail.com>.
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
RE: Retrieve inserted id in mass insert using SqlMapExecutor.
Posted by meindert <me...@eduflex.com>.
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
RE: Retrieve inserted id in mass insert using SqlMapExecutor.
Posted by Westhveg <we...@gmail.com>.
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
RE: Retrieve inserted id in mass insert using SqlMapExecutor.
Posted by meindert <me...@eduflex.com>.
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