Bulk Insert/Update

[Pete-2]

I have a common Java object design like the following:

package com.projectA;
public class Company {
    private int companyId;
    private String name;
    private List locations;
}

package com.projectA;
public class Location {
    private int locationId;
    private String address;
    private String city;
    private String state;
    private String zipCode;
    private String phoneNumber;
}

There are 3 tables behind these classes: company, location and
company_location. I am trying to determine if MyIBATis can provide
bulk inserts/updates and maintain all all three tables with a single
java call to MyIBATis or if I have to write a wrapper function that
calls the insert into each table.

Thanks,
Pete

[Larry Meadors]

You have to write a wrapper function that calls the insert into each table.

Larry

[Tim]

You used to be able to do this using the iterators in the maps (and
therefore just using a collection to your insert handler dao) but I
don't know if that feature is available in mybatis 3 now.

On Tue, Sep 21, 2010 at 12:22 PM, Larry Meadors <[hidden email]> wrote:
> You have to write a wrapper function that calls the insert into each table.
>
> Larry
>

[Larry Meadors]

Yeah, I bet you could do that still with 3.

However, using iterators would essentially generate a single giant sql
statement.

That might work if you don't need the generated keys or if you can
craft the sql in such a way as to be able to use it.

IMO, the value of doing so would not justify the cost.

We're talking about replacing a few lines of pretty simple java code
with xml and sql that you can't debug if it goes sideways.

Larry



On Tue, Sep 21, 2010 at 11:33 AM, Tim <[hidden email]> wrote:

> You used to be able to do this using the iterators in the maps (and
> therefore just using a collection to your insert handler dao) but I
> don't know if that feature is available in mybatis 3 now.
>
> On Tue, Sep 21, 2010 at 12:22 PM, Larry Meadors <[hidden email]> wrote:
>> You have to write a wrapper function that calls the insert into each table.
>>
>> Larry
>>
>

[Tim]

oops sry the iterators approach is geared towards mysql mainly since
it supports a special:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
syntax.

http://dev.mysql.com/doc/refman/5.1/en/insert.html
In which case I usually care less about what is returned with each
than I do about actual insertion speed.

On Tue, Sep 21, 2010 at 1:01 PM, Larry Meadors <[hidden email]> wrote:

> Yeah, I bet you could do that still with 3.
>
> However, using iterators would essentially generate a single giant sql
> statement.
>
> That might work if you don't need the generated keys or if you can
> craft the sql in such a way as to be able to use it.
>
> IMO, the value of doing so would not justify the cost.
>
> We're talking about replacing a few lines of pretty simple java code
> with xml and sql that you can't debug if it goes sideways.
>
> Larry
>
>
>
> On Tue, Sep 21, 2010 at 11:33 AM, Tim <[hidden email]> wrote:
>> You used to be able to do this using the iterators in the maps (and
>> therefore just using a collection to your insert handler dao) but I
>> don't know if that feature is available in mybatis 3 now.
>>
>> On Tue, Sep 21, 2010 at 12:22 PM, Larry Meadors <[hidden email]> wrote:
>>> You have to write a wrapper function that calls the insert into each table.
>>>
>>> Larry
>>>
>>
>

[Larry Meadors]

On Tue, Sep 21, 2010 at 12:07 PM, Tim <[hidden email]> wrote:
> oops sry the iterators approach is geared towards mysql mainly since
> it supports a special:
> INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
> syntax.

Ah, yeah - and that wouldn't work for the case Pete was asking about.

Larry

[asherwin]

This isn't limited to MySQL, this works in (at least) PostgreSQL as
well...

We've written a complete DAO Mapper framework with inheritance from a
base class which generates sql for .. insert, bulk insert, update,
delete, select by pk, select all, create/drop table, create/drop
schema, create/drop functions, create/drop triggers create/drop
indexes, create/drop constraints all based on simple custom
annotations we put on our POJOs.  Basically, created all the JPA/
HIbernate annotation style ORM framework stuff while leveraging the
power of MyBatis to do the SQL work with the dynamic features and
control that we like.

Everything you want to do it possible with a little ingenuity..

On Sep 21, 2:07 pm, Tim <[hidden email]> wrote:

> oops sry the iterators approach is geared towards mysql mainly since
> it supports a special:
> INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
> syntax.
>
> http://dev.mysql.com/doc/refman/5.1/en/insert.html
> In which case I usually care less about what is returned with each
> than I do about actual insertion speed.
>
>
>
> On Tue, Sep 21, 2010 at 1:01 PM, Larry Meadors <[hidden email]> wrote:
> > Yeah, I bet you could do that still with 3.
>
> > However, using iterators would essentially generate a single giant sql
> > statement.
>
> > That might work if you don't need the generated keys or if you can
> > craft the sql in such a way as to be able to use it.
>
> > IMO, the value of doing so would not justify the cost.
>
> > We're talking about replacing a few lines of pretty simple java code
> > with xml and sql that you can't debug if it goes sideways.
>
> > Larry
>
> > On Tue, Sep 21, 2010 at 11:33 AM, Tim <[hidden email]> wrote:
> >> You used to be able to do this using the iterators in the maps (and
> >> therefore just using a collection to your insert handler dao) but I
> >> don't know if that feature is available in mybatis 3 now.
>
> >> On Tue, Sep 21, 2010 at 12:22 PM, Larry Meadors <[hidden email]> wrote:
> >>> You have to write a wrapper function that calls the insert into each table.
>
> >>> Larry

[Pete-2]

The issue is that this becomes a nightmare to manage when you start
dealing with updates. You either have to 'clear and reload' or perform
a diff to determine what should be added and what should be removed
from the association table. It would be cool if I could simply say
save this object, and the framework performed these operations. I
guess I am looking for something similar to Hibernate and cascading.
What is the recommended way using iBATis?

Pete

On Sep 21, 2:15 pm, Larry Meadors <[hidden email]> wrote:
> On Tue, Sep 21, 2010 at 12:07 PM, Tim <[hidden email]> wrote:
> > oops sry the iterators approach is geared towards mysql mainly since
> > it supports a special:
> > INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
> > syntax.
>
> Ah, yeah - and that wouldn't work for the case Pete was asking about.
>
> Larry