Join with List of inheritance example

[moifort]

I just create this issue only for documentation or maybe to know if is the best way to do it.

So this is my sample I try to use inheritance with join mapping, and I take some time to make it work. Hope this sample will help other like me!

I know is not the perfect way to implement it, but it's for the example. Hope I will help all the developers!

Implementation

• AttributeType described by List<Rule>
• Rules implemented by ListRule, MinRule, MaxRule, StepRule

Java

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

import java.util.Collections;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class AttributeType {

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class ListRule implements Rule {
        private List<String> values = Collections.emptyList();
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class MinRule implements Rule {
        private Double min;
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class MaxRule implements Rule {
        private Double max;
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class StepRule implements Rule {
        private Double step;
    }

    public interface Rule {}

    private Long id;
    private Long categoryId;
    private String label;
    private String unit;
    private Boolean indexed;
    private List<Rule> rules = Collections.emptyList();
}

DB schema and data

attribute_type table

id	category_id	label	unit	indexed
1	3	shirt_size		0
2	3	shirt_color		0
3	10004	shirt_texture		0

attribute_type_rules table

attribute_type_id	rules_order	discriminator	list_string_value	min_number_value	max_number_value	step_value
1	0	LIST_STRING	["S","M","L"]	NULL	NULL	NULL
2	0	LIST_STRING	["Red","Blue","White"]	NULL	NULL	NULL
2	1	MIN_NUMBER	NULL	1	NULL	NULL
3	0	LIST_STRING	["Wool","Cotton","Nylon"]	NULL	NULL	NULL

Code

SimpleFlatMapper

 private static final JdbcMapper<AttributeTypeView> rulesMapper = JdbcMapperFactory.newInstance()
        .addKeys("id") // For manyToOne
        .discriminator(AttributeTypeView.Rule.class) // Inherintance with Rule
        .onColumn("rules_discriminator", String.class) // Don't forget to specify 'rules' for properties mapping
        .with(builder -> builder
            .when("LIST_STRING", AttributeTypeView.ListRule.class)
            .when("MIN_NUMBER", AttributeTypeView.MinRule.class)
            .when("MAX_NUMBER", AttributeTypeView.MaxRule.class)
            .when("STEP", AttributeTypeView.StepRule.class)
        )
        .addAlias("list_string_value", "rules_values") // Don't forget to specify 'rules' for properties mapping
        .addAlias("min_number_value", "rules_min") // Don't forget to specify 'rules' for properties mapping
        .addAlias("max_number_value", "rules_max") // Don't forget to specify 'rules' for properties mapping
        .addAlias("step_value", "rules_step") // Don't forget to specify 'rules' for properties mapping
        .addColumnProperty(
            "list_string_value",
            GetterFactoryProperty.forType(List.class, (rs, i) -> mapper.readValue(((ResultSet) rs).getString(i), List.class))
        )
        .newMapper(AttributeTypeView.class);

Sample

public class AttributeTypeSqlRepository {
    private static final ObjectMapper mapper = new ObjectMapper();
    private static final JdbcMapper<AttributeTypeView> rulesMapper = JdbcMapperFactory.newInstance()
        .addKeys("id")
        .discriminator(AttributeTypeView.Rule.class)
        .onColumn("rules_discriminator", String.class)
        .with(builder -> builder
            .when("LIST_STRING", AttributeTypeView.ListRule.class)
            .when("MIN_NUMBER", AttributeTypeView.MinRule.class)
            .when("MAX_NUMBER", AttributeTypeView.MaxRule.class)
            .when("STEP", AttributeTypeView.StepRule.class)
        )
        .addAlias("list_string_value", "rules_values")
        .addAlias("min_number_value", "rules_min")
        .addAlias("max_number_value", "rules_max")
        .addAlias("step_value", "rules_step")
        .addColumnProperty(
            "list_string_value",
            GetterFactoryProperty.forType(List.class, (rs, i) -> mapper.readValue(((ResultSet) rs).getString(i), List.class))
        )
        .newMapper(AttributeTypeView.class);

    private final DSLContext dslContext;

    @Inject
    public AttributeTypeSqlRepository( DSLContext dslContext) {
        this.dslContext = dslContext;
    }

    @Override
    public List<AttributeTypeView> listFilterByCategoriesId(List<Long> categoriesId) throws Exception {
        var rulesQuery = dslContext.select(
            ATTRIBUTE_TYPE.ID,
            ATTRIBUTE_TYPE.CATEGORY_ID,
            ATTRIBUTE_TYPE.LABEL,
            ATTRIBUTE_TYPE.UNIT,
            ATTRIBUTE_TYPE.INDEXED,
            ATTRIBUTE_TYPE_RULES.DISCRIMINATOR.as("rules_discriminator"), // Important help for mapping
            ATTRIBUTE_TYPE_RULES.LIST_STRING_VALUE,
            ATTRIBUTE_TYPE_RULES.MIN_NUMBER_VALUE,
            ATTRIBUTE_TYPE_RULES.MAX_NUMBER_VALUE,
            ATTRIBUTE_TYPE_RULES.STEP_VALUE
        )
            .from(ATTRIBUTE_TYPE)
            .leftJoin(ATTRIBUTE_TYPE_RULES)
            .on(ATTRIBUTE_TYPE_RULES.ATTRIBUTE_TYPE_ID.eq(ATTRIBUTE_TYPE.ID));

        try (var rs = rulesQuery.fetchResultSet()) {
            return rulesMapper.stream(rs).collect(Collectors.toList());
        }
    }
}

Result

[
 0 = {AttributeTypeView@12435} "AttributeTypeView(id=1, categoryId=3, label=shirt_size, unit=, indexed=false, rules=[AttributeTypeView.ListRule(values=[S, M, L])])",
 1 = {AttributeTypeView@12436} "AttributeTypeView(id=2, categoryId=3, label=shirt_color, unit=, indexed=false, rules=[AttributeTypeView.ListRule(values=[Red, Blue, White]), AttributeTypeView.MinRule(min=1.0)])",
 2 = {AttributeTypeView@12437} "AttributeTypeView(id=3, categoryId=10004, label=shirt_texture, unit=, indexed=false, rules=[AttributeTypeView.ListRule(values=[Wool, Cotton, Nylon])])",
]