[JAVA] Use MyBatis to get Map with key as identifier and value as Entity

As the title says.

Possible usage scenes are as follows, for example.

• Cannot table join due to performance concerns due to too many joins
• Cannot join table because the database is different such as other system

Consider the following table as an example.

Entity(Omit package and import)

//Employee Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmployeeEntity {

    private int id;

    private String name;

    private String mailAddress;

    private int departmentId;

    private String departmentName;

    //Concurrent organization
    private List<DepartmentEntity> subDepartments;
}

//Affiliation organization Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DepartmentEntity {

    private int id;

    private String name;
}

When you want to get the following structure, it seems that it is not possible to get it in one shot only with the function of MyBatis.

Required structure

{
  "1" : {
    "id" : 1,
    "name" : "John",
    "mailAddress" : "[email protected]",
    "departmentId" : 1,
    "departmentName" : "Planning",
    "subDepartments" : [ ]
  },
  "2" : {
    "id" : 2,
    "name" : "Ken",
    "mailAddress" : "[email protected]",
    "departmentId" : 1,
    "departmentName" : "Planning",
    "subDepartments" : [ {
      "id" : 2,
      "name" : "Legal"
    }, {
      "id" : 3,
      "name" : "Investor Relations"
    } ]
  }
}

Therefore, mediate the MappingHelper class so that the above structure can be obtained. Information is obtained from the database as a list of MappingHelper, and it is converted to Map using MappingHelper # toMap. Keep the key and value data types generic so that you can specify them when defining the interface for the query you are issuing.

MappingHelper.java

package com.example.domain.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MappingHelper<K, V> {

    private K key;

    private V value;

    public static <K, V> Map<K, V> toMap(List<MappingHelper<K, V>> list) {
        if (list == null) {
            return Collections.emptyMap();
        }
        return list.parallelStream().collect(Collectors.toMap(MappingHelper::getKey, MappingHelper::getValue));
    }
}

Use this MappingHelper class to create the following MyBatis Mapper.

EmployeeRepository.java

package com.example.domain.repository;

import com.example.domain.model.EmployeeEntity;
import com.example.domain.model.MappingHelper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface EmployeeRepository  {

    List<MappingHelper<Integer, EmployeeEntity>> findAllByIds(@Param("ids") List<Integer> ids);
}

EmployeeRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.domain.repository.EmployeeRepository">

    <select id="findAllByIds" resultMap="employeeMapHelper">
        SELECT
          e.id employeeId,
          e.name employeeName,
          e.mail_address mailAddress,
          d.id departmentId,
          d.name departmentName,
          sd.id subDepartmentId,
          sd.name subDepartmentName
        FROM
          employee e
        LEFT JOIN
          department d
        ON
          e.department_id = d.id
        LEFT JOIN (
          SELECT
            d2.id,
            d2.name,
            sd2.employee_id
          FROM
            sub_department sd2
          INNER JOIN
            department d2
          ON
            d2.id = sd2.department_id
          WHERE
            sd2.employee_id IN
            <foreach collection="ids" item="id" open="(" close=")" separator=",">
              #{id}
            </foreach>
        ) sd
        ON
          e.id = sd.employee_id
        WHERE
          e.id IN
          <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
          </foreach>
    </select>

    <resultMap id="employeeMapHelper" type="MappingHelper">
        <id property="key" column="employeeId" javaType="String"/>
        <association property="value" resultMap="employeeMap"/>
    </resultMap>

    <resultMap id="employeeMap" type="EmployeeEntity">
        <id property="id" column="employeeId"/>
        <result property="name" column="employeeName"/>
        <result property="mailAddress" column="mailAddress"/>
        <result property="departmentId" column="departmentId"/>
        <result property="departmentName" column="departmentName"/>
        <collection property="subDepartments" ofType="DepartmentEntity">
            <id property="id" column="subDepartmentId"/>
            <result property="name" column="subDepartmentName"/>
        </collection>
    </resultMap>
</mapper>

You can get the target structure by executing the following processing using the above.

SandboxRestController.java

package com.example.web;

import com.example.domain.model.EmployeeEntity;
import com.example.domain.model.MappingHelper;
import com.example.domain.repository.EmployeeRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequiredArgsConstructor
public class SandboxRestController {

    private final EmployeeRepository employeeRepository;

    @RequestMapping("/mybatisToMap")
    public ResponseEntity<Map> mybatisToMap() {
        List<MappingHelper<Integer, EmployeeEntity>> mapperList = employeeRepository.findAllByIds(Arrays.asList(1, 2));
        Map<Integer, EmployeeEntity> employeeMap = MappingHelper.toMap(mapperList);
        return ResponseEntity.ok().body(employeeMap);
    }
}

With the above, you can get a Map with the key as the identifier and the value as the Entity using MyBatis. It may be useful when implementing with Java when table join is not possible.

Sample code: https://github.com/tnemotox/sandbox Reference: https://stackoverflow.com/questions/36400538/mybatis-resultmap-is-hashmapstring-object

Postscript: 18/6/14

With Java, even if you don't bother to do your best with MyBatis, you can get a List and do your best with Stream ... There are times when you want a List, so you can create it in one shot from the results of that query.

From list to map with stream

Map<Integer, EmployeeEntity> employeeMap = employees.stream().collect(toMap(EmployeeEntity::getEmployeeId, e -> e, (e1, e2) -> e1);

Well, it might be useful ...