使用Mybatis在两个Table中插入数据 [英] Insert data in two Table using Mybatis

问题描述

我对 Mybatis 很陌生，遇到了一些问题

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

完整的场景是我需要读取和excel文件并将excel数据插入数据库中的两个具有主键和外键关系的不同表中.我能够读取 excel 数据并能够在主表中插入，但实际上无法在第二个表中插入数据，问题是我有两个不同的 pojo 类，每个表都有两个不同的映射器的单独数据.

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.

我通过在父类的 pojo 中定义子表的 pojo 来实现关联有没有办法在两个不同的表中插入数据.是否可以在单个标签中运行 2 个插入查询

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

任何帮助都将是可观的

推荐答案

有很多方法可以做到这一点.

There are lot of ways to do that.

这里演示了最直接的方法之一 - 使用单独的插入.确切的解决方案可能变化不大，主要取决于主键是从 excel 中获取还是在插入数据库期间生成.这里我假设密钥是在插入过程中生成的(因为这是一个稍微复杂的情况)

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)

假设您有这些 POJO:

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
} 

然后你在mapper中定义两个方法:

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

并使用它们

MyMapper myMapper = createMapper();

Parent parent = getParent();

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

可以有一个集合，而不是单个孩子.在这种情况下，createChild 会在每个孩子的循环中执行.

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

在某些数据库中(posgresql, sql server) 您可以在一个语句中插入两个表.但是查询会更复杂.

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

另一种可能性是在一个映射器方法中使用多个插入语句.我在 postgresql 中使用了与此类似的代码，并在 xml 中进行了映射:

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>

以及mapper接口中的方法定义:

and method definition in mapper interface:

void createParentWIthChild(Parent parent);

这篇关于使用Mybatis在两个Table中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！