Select entity with the informed natural id, rather than trying to insert (JPA and SpringBoot)

Question

So basically I have the following need. A person is going to POST an entity called "Expense Report" through a Rest Controller.

That entity has a field Country that is actually an association with another entity.

@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "EXPENSE_REPORTS")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ExpenseReport extends BaseEntity {

    @Column(name = "TRIP_DESCRIPTION", nullable = false)
    private String tripDescription;

    @Column(name = "JUSTIFICATION")
    private String justification;

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "COUNTRY_ID")
    private Country country;

    @Column(name = "TRIP_START_DATE")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
    @JsonDeserialize(using = LocalDateDeserializer.class)
    @JsonSerialize(using = LocalDateSerializer.class)
    private LocalDate tripStartDate;

    @Column(name = "TRIP_END_DATE")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
    @JsonDeserialize(using = LocalDateDeserializer.class)
    @JsonSerialize(using = LocalDateSerializer.class)
    private LocalDate tripEndDate;

    @Column(name = "TOTAL_AMOUNT")
    private BigDecimal totalAmount;

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name="USER_ID")
    private User user;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name="EXPENSE_ITEM_ID")
    private Set<ExpenseItem> expenses;

}

@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "COUNTRIES")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Country extends BaseEntity {

    @Column(name = "NAME")
    @NaturalId
    private String name;
}

What I wanted is to check, if it is possible that the caller just sends the natural id of the country (in this case, the name) and JPA rather than trying to insert this country, he finds the country with the same name and associates it.

So, rather than sending this post:

{
    "tripDescription": "Some trip description...",
    "justification": "Some justification...",
    "country": {
      "id": 12345
    }
}

He sends this:

{
    "tripDescription": "Some trip description...",
    "justification": "Some justification...",
    "country": {
      "name": "BR"
    }
}

I can imagine how to do this either in a repository implementation, but I was wondering if there is something automatic to do this in JPA.

Thanks in advance.

Well one thing I can point out is that you can simply use `Country`'s name as a primary key and you won't have this issue — curiousdev, Mar 01 '20 at 13:09
That is indeed true, but perhaps wouldn't it be better to have the internal database ID as a surrogate id (like a number)? — Lucas Carlos Almeida da Slva, Mar 01 '20 at 14:38
A string takes a tiny bit more space but the speed when using either is almost identical. I don't see a reason why you'd need an integer. — curiousdev, Mar 01 '20 at 15:16

vanOekel · Answer 1 · 2020-03-02T00:13:55.160

As far as I could see, there is no automatic JPA way of doing this. I know that is not much of an answer, but I also do not think it is likely that there will be a way to do this automatically in JPA (for now), since Hibernate is very focussed on using primary keys and foreign keys (i.e. surrogate IDs).

As "curiousdev" mentions in the comments, using a country code like "BR" (which can be non null unique, just like a primary key) as the key (or joincolumn) is a good alternative in this case. There is a whole discussion about it here if you are interested.

For my own interest, I did some digging in how a repository implementation could look when using the surrogate ID and natural ID. The combination of second level cache and a reference lookup looks promising. You can avoid an extra select-query in that case. The code below is runnable (with the required depencencies in place) and shows what I found so far.

The reference I'm talking about is in the line s.byNaturalId(Country.class).using("code", "NL").getReference();.

The cache is in the settings (hibernate.cache.use_second_level_cache and hibernate.cache.region.factory_class) and the annotations @org.hibernate.annotations.Cache and @NaturalIdCache.

// package naturalid;

import java.util.HashMap;
import java.util.Map;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;

import org.ehcache.jsr107.EhcacheCachingProvider;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.annotations.CacheConcurrencyStrategy;
import org.hibernate.annotations.NaturalId;
import org.hibernate.annotations.NaturalIdCache;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cache.jcache.internal.JCacheRegionFactory;
import org.hibernate.dialect.H2Dialect;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;

/**
 * Using natural ID to relate to an existing record.
 * <br>https://stackoverflow.com/questions/60475400/select-entity-with-the-informed-natural-id-rather-than-trying-to-insert-jpa-an
 * <br>Dependencies:<pre>
 * org.hibernate:hibernate-core:5.4.12.Final
 * org.hibernate:hibernate-jcache:5.4.12.Final
 * org.ehcache:ehcache:3.8.1
 * com.h2database:h2:1.4.200
 * org.slf4j:slf4j-api:1.7.25
 * ch.qos.logback:logback-classic:1.2.3
 * org.projectlombok:lombok:1.18.4
 * </pre>
 */
@Slf4j
public class NaturalIdRel {

    public static void main(String[] args) {

        try {
            new NaturalIdRel().test();
        } catch (Exception e) {
            log.error("Tranactions failed.", e);
        }
    }

    void test()  throws Exception {

        // https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#bootstrap
        Map<String, Object> settings = new HashMap<>();
        // https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations
        settings.put("hibernate.dialect", H2Dialect.class.getName());
        settings.put("hibernate.connection.url", "jdbc:h2:mem:test;database_to_upper=false;trace_level_system_out=2");
        settings.put("hibernate.connection.username", "SA");
        settings.put("hibernate.connection.password", "");
        settings.put("hibernate.hbm2ddl.auto", "create");
        settings.put("hibernate.show_sql", "true");
        settings.put("hibernate.cache.use_second_level_cache", "true");
        settings.put("hibernate.cache.region.factory_class", JCacheRegionFactory.class.getName());
        settings.put("hibernate.cache.ehcache.missing_cache_strategy", "create");
        settings.put("hibernate.javax.cache.provider", EhcacheCachingProvider.class.getName());
        settings.put("hibernate.javax.cache.missing_cache_strategy", "create");

        //settings.put("", "");
        StandardServiceRegistry ssr = new StandardServiceRegistryBuilder()
            .applySettings(settings)
            .build();
        Metadata md = new MetadataSources(ssr)
            .addAnnotatedClass(ExpenseReport.class)
            .addAnnotatedClass(Country.class)
            .buildMetadata();
        SessionFactory sf = md.getSessionFactoryBuilder()
            .build();
        try {
            createCountry(sf);
            createExpense(sf);
        } finally {
            sf.close();
        }
    }

    void createCountry(SessionFactory sf) {

        Country c = new Country();
        c.setCode("NL");

        try (Session s = sf.openSession()) {
            save(s, c);
        }
    }

    void createExpense(SessionFactory sf) {

        ExpenseReport er = new ExpenseReport();
        er.setDescription("Expenses");
        er.setReason("Fun");

        // Watch (log) output, there should be no select for Country.
        try (Session s = sf.openSession()) {
            // https://www.javacodegeeks.com/2013/10/natural-ids-in-hibernate.html
            Country cer = s.byNaturalId(Country.class).using("code", "NL").getReference();
            er.setCountry(cer);
            save(s, er);
        }
    }

    void save(Session s, Object o) {

        Transaction t = s.beginTransaction();
        try {
            s.save(o);
            t.commit();
        } finally {
            if (t.isActive()) {
                t.rollback();
            }
        }
    }

    @Entity
    @Data
    static class ExpenseReport {

        @Id
        @GeneratedValue
        int id;
        @Column
        String description;
        @Column
        String reason;
        @ManyToOne
        // Can also directly map country code.
        // https://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys
        Country country;
    }

    @Entity
    @Data
    // https://vladmihalcea.com/the-best-way-to-map-a-naturalid-business-key-with-jpa-and-hibernate/
    @org.hibernate.annotations.Cache(
        usage = CacheConcurrencyStrategy.READ_WRITE
    )
    @NaturalIdCache
    static class Country {

        @Id
        @GeneratedValue
        int id;
        @NaturalId
        String code;
    }
}

Espresso · Answer 2 · 2020-03-02T03:41:01.463

Use association like below, instead of COUNTRIES.COUNTRY_ID use COUNTRIES.NAME

public class ExpenseReport extends BaseEntity {

    @Column(name = "TRIP_DESCRIPTION", nullable = false)
    private String tripDescription;

    @Column(name = "JUSTIFICATION")
    private String justification;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = “NAME”,referencedColumnName = "name" ) //Do not use COUNTRY_ID
    private Country country;
….
}

public class Country extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "NAME”,nullable = false, updatable = false, unique = true)
    @NaturalId(mutable = false)
    private String name;

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }

        if (!(o instanceof Country)) {
            return false;
        }

        Product naturalIdCountry = (Country) o;
        return Objects.equals(getName(), naturalIdCountry.getName());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getName());
    }
}

Select entity with the informed natural id, rather than trying to insert (JPA and SpringBoot)

2 Answers2