Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel - Insert data in two Table using Mybatis

I am very new to Mybatis and stuck in a situation I have some questions

The complete scenario is I need to read and excel file and insert the excel data in database in two different tables having primary and foreign key relationship . I am able to read the excel data and able to insert in primary table but not getting how to insert data in second table actually the problem is I have two different pojo classes having separate data for for each table two different mappers.

I am achiving association by defining the pojo of child table inside the pojo of parent class Is there any way to insert data in two different table. Is is possible to run 2 insert queries in single tag

Any help would be appreciable

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

There are lot of ways to do that.

Here is demonstration of one of the most straightforward ways to do that - using separate inserts. The exact solution may vary insignificantly depending mainly on whether primary keys are taken from excel or are generated during insertion into database. Here I suppose that keys are generated during insertion (as this is a slightly more complicated case)

Let's assume you have these POJOs:

class Parent {
   private Integer id;
   private Child child;
   // other fields, getters, setters etc
}

class Child {
   private Integer id;
   private Parent parent;
   // other fields, getters, setters etc
} 

Then you define two methods in mapper:

public interface MyMapper {

    @Insert("Insert into parent (id, field1, ...) 
         values (#{id}, #{field1}, ...)")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void createParent(Parent parent);

    @Insert("Insert into child(id, parent_id, field1, ...) 
      values (#{id}, #{parent.id}, #{field1}, ...)")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void createChild(Child child);
}

and use them

MyMapper myMapper = createMapper();

Parent parent = getParent();

myMapper.createParent(parent);
myMapper.createChild(parent.getChild());

Instead of single child there can be a collection. In that case createChild is executed in the loop for every child.

In some databases (posgresql, sql server) you can insert into two tables in one statement. The query however will be more complex.

Another possibility is to use multiple insert statements in one mapper method. I used code similar to this in postgresql with mapping in xml:

<insert id="createParentWithChild">
    insert into parent(id, field1, ...) 
      values (#{id}, #{field1}, ...);
    insert into child(id, parent_id, field1, ...) 
      values (#{child.id}, #{id}, #{child.field1},...)
</insert>

and method definition in mapper interface:

void createParentWIthChild(Parent parent);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...