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.

Consider the following table as an example.

Employee Department SubDepartment
id PK id PK employee_id FK
name name department_id FK
mail_address
department_id FK
department_name

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 ...

Recommended Posts

Use MyBatis to get Map with key as identifier and value as Entity
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
Try to get redmine API key with ruby
[Ruby] How to use the map method. How to process the value of an object and get it by hash or symbol.
[Swift] Get key as well as value in Realtime database
How to use RealSense with ubuntu 20.04 and ROS Noetic
I want to get along with Map [Java beginner]
How to use Map
How to use Map
How to add another project as Maven library with CircleCI and use it for build
With podman in docker, everyone wants to get along and use docker on a shared computer
[Ruby] How to get the value by specifying the key. Differences between hashes, symbols and fetch
[Java] Get Map key / value pairs using extended for statement
How to use a foreign key with FactoryBot ~ Another solution
How to batch run JUnit and get coverage as well
[Spring Boot] I want to add my own property file and get the value with env.getProperty ().
How to use the certificate and private key created by Docker's BASIC authentication with AWS ALB
[Java] How to use Map
[Java] How to use Map
Use swift Filter and Map
How to use Java Map
How to get the id of PRIMAY KEY auto_incremented in MyBatis
How to get boolean value with jQuery in rails simple form
How to encrypt and decrypt with RSA public key in Java