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
– ave, Commented Jun 23, 2021 at 5:29
@ave have edited the question, is that what you asked for, please check — Akash Sharma
– Akash Sharma, Commented Jun 23, 2021 at 6:24

ave · Accepted Answer · 2021-06-23 10:33:08Z

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.

answered Jun 23, 2021 at 10:33

ave

3,7242 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dusan Todorovic · Accepted Answer · 2021-06-23 08:27:49Z

1

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

answered Jun 23, 2021 at 8:27

Dusan Todorovic

4702 silver badges12 bronze badges

1 Comment

Akash Sharma Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related