excel - Insert data in two Table using Mybatis

Question

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

Answer 1

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);