How to insert java HashMap keys and values as columns in database using mybatis

Question

I am following a SpringBoot + MyBatis tutorial.I am able to insert simple objects into the database. Eg I tried inserting an employee object:

Employee{
    private String id;
    private String name;
}


@Mapper
public interface EmployeeMapper {  

    @Insert("insert into employee(id,name) values(#{id},#{name})")
    void insert(Employee employee);
 }

Now I want to insert another Object as shown below:

Department{
    // deptId will be common for all employees in the map
    private int deptId;

    //employeeMap is a Map employees where employeeId is key and employeeName is value
    private Map<String, String> employeeMap;
  }

 //Eg. I have the following data in Department Object
 Department dept = new Department();
 dept.setId("d1");

 Map<String, String> employeeMap = new HashMap<String, String>();
 employeeMap.put("1","Jon");
 employeeMap.put("2","Doe");
 employeeMap.put("3","Sam");
 dept.setEmployeeMap(employeeMap);

 // I want to insert dept object as 3 columns in database
 //*deptId* will be common for all employees in Map
 //*employeeId* key of map 
 //*employeeName* valiue of map

I am unable to solve it, can this not be done using @Insert as in the simple Employee example. Please help as I am stuck at this.

Answer 1

Alternatively, you can use <foreach /> to iterate map entries.
The 'key' is assigned to the variable specified by index and the 'value' is assigned to the variable specified by item.

As you didn't show me the SQL, I'll assume your DB supports multi-row insert.

@Insert({ "<script>",
  "insert into employee (deptId, employeeId, employeeName) values",
  "<foreach collection='employeeMap' index='key' item='value'",
      "separator=','>",
    "(#{deptId}, #{key}, #{value})",
  "</foreach>",
  "</script>"
})
void insert(Department dept);

Then MyBatis would prepare a statement like the following.

insert into employee (deptId, employeeId, employeeName) 
  values (?, ?, ?) , (?, ?, ?) , (?, ?, ?)

Note that when there are many entries in the employeeMap, using batch insertion is recommended.