How i map query result to List<Map<String, Object>

Question

I have a native query in JPA

String sql1 = """
                       SELECT tier_id, tier_list, vm.id AS model_id, option_list, price, sku, stock
                       FROM (
                           SELECT *
                           FROM ecommerce.tier_variations tv
                           WHERE tv.product_id = ?1 AND is_deleted = false
                       ) AS tv
                       JOIN ecommerce.variation_models vm ON vm.tier_id = tv.id
              """;
@Query(value = sql1, nativeQuery = true)
List<VariationProjection> findVariations(String productId);

The result of option_list is [{"image": "image1", "option": "L"}, {"image": "image2", "option": "X"}] in db

I use projection to map

public interface VariationProjection {
    List<String> getTier_list();
    List<Map<String, Object>> getOption_list();
}

List getTier_list() is work, but List<Map<String, Object>> getOption_list() not work Please help me this problem T_T

Don't use images for text. Copy code and logs into the question as text using code block format. — aled
– aled, Commented Dec 8, 2024 at 4:25

nhuquynh · Accepted Answer · 2024-12-09 08:37:12Z

Cause

JPA does not support automatic JSON mapping: The native query returns the option_list value as a JSON getOption_list, and JPA doesn't know how to convert this value into a List<Map<String, Object>>.

Solution using Jackson

Add the Jackson dependency to your project:

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
</dependency>

Create a custom converter:

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.util.List;
import java.util.Map;

@Converter
public class JsonToListMapConverter implements AttributeConverter<String, List<Map<String, Object>>> {

    private static final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public List<Map<String, Object>> convertToEntityAttribute(String dbData) {
        try {
            return objectMapper.readValue(dbData, new TypeReference<List<Map<String, Object>>>() {});
        } catch (Exception e) {
            ...
        }
    }

}

Update your interface VariationProjection:

@Convert(converter = JsonToListMapConverter.class)
private List<Map<String, Object>> getOption_list;

Now, JPA will automatically use the converter for the option_list field when fetching data.

Collectives™ on Stack Overflow

How i map query result to List<Map<String, Object>

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related