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.

Please add an example of the INSERT statement you ultimately want to execute. — ave, Jun 23 '21 at 05:29
@ave have edited the question, is that what you asked for, please check — Akash Sharma, Jun 23 '21 at 06:24
@DusanTodorovic thank you for answering. Forgive my ignorance I want to ask you 2 more questions on this. 1) There is no way we can do this without looping, right. 2) If I go for mapper.xml, will it be more efficient, or will there be any difference in the number of times connection is made to database. — Akash Sharma, Jun 23 '21 at 08:32
Well, im not sure for second answer, so i will not answer it. I don't want to tell you something wrong, but for the first one, if you wish to put all elements in any place, you will have to iterate trough map, either you or predefined mapper. Time complexity remains same. — Dusan Todorovic, Jun 23 '21 at 08:38

score 1 · Answer 1 · answered Jun 23 '21 at 08:27

If you wish to iterate trough map and get every key and value of that key the easiest way would be

Iterator hmIterator = employeeMap.entrySet().iterator();
while (hmIterator.hasNext()){
    Map.Entry mapElement = (Map.Entry)hmIterator.next();
    String name = (String)mapElement.GetValue();
    String number = (String)mapElement.getKey();
    insert(number,name);
}

Or without iterator

for (Map.Entry<String,String> entry : map.entrySet()){
    String key = entry.getKey(); //this is your number
    String value = entry.getValue(); //this is your name
    insert(key,value);
}

Or one more way to do it would be

for (String key : employeeMap.keySet()){
    String name = employeeMap.get(key);
    insert(key, name);
}

Note: I wrote just function insert as i do not know how you call your insert but replace that will method you wish

thank you for answering. Forgive my ignorance I want to ask you 2 more questions on this. 1) There is no way we can do this without looping, right. 2) If I go for mapper.xml, will it be more efficient, or will there be any difference in the number of times connection is made to database. — Akash Sharma, Jun 23 '21 at 08:32

score 1 · Accepted Answer · answered Jun 23 '21 at 10:33

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.

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

2 Answers2