Map database record to entity without knowing its id

Question

Basically I have the system where there is entity called Dictionary, where all dictionary entries are stored (like countries in example below), these entries are prefilled in database using database scripts, so Im not adding/modyfying/deleting these from application level. I have one problem with this structure, when Im trying to add new record which is using dictionary, I have to know the dictionary surrogate key id beforehand, which I do not know from application level, so I have to fetch it in separate query before saving my record, let have a look at example.

User entity which have country_dictionary_id column pointing to dictionary table:

@Entity
@Table(name = "user")
public class User {
  @Id
  private long id;
 
  @Column
  private String name;

  @ManyToOne
  @JoinColumn(name = "country_dictionary_id")
  private CountryDictionary country;

  public User(String name, String country) {
    this.name = name;
    this.country = new CountryDictionary(country);
  }

  public User(String name, CountryDictionary country) {
    this.name = name;
    this.country = country;
  }
}

CountryDictionary (which is inherited dictionary mapped using single table strategy (I have more such classes extending Dictionary class)):

@Entity
@SuperBuilder
public class CountryDictionary extends Dictionary {
  public CountryDictionary(String code) {
    super(code);
  }
}

Dictionary entity itself, where code and dtype together compose unique constraint, so you cannot have two records with same code and dtype:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Table(name = "dictionary")
public abstract class Dictionary {
  @Id
  private long id;

  @Column
  private String code;

  @Column
  private String dtype;

  public Dictionary(String code) {
    this.code = code;
  }
}

And here my example code which will not work and throw an exception:

User adam = new User("Adam", "FR");
userRepository.save(adam); // will throw something like: Not-null property references a transient value - transient instance must be saved before current operation : com.example.User.country -> com.example.CountryDictionary

Which makes sence. To make it work, something like this will be required:

CountryDictionary france = dictionaryRepository.get("FR", "CountryDictionary");
User adam = new User("Adam", france);
userRepository.save(adam);

But this will obviously execute two separate queries:

SELECT d FROM dictionary d WHERE d.code='FR' AND d.dtype='CountryDictionary';
INSERT INTO user(name, country_dictioanry_id) VALUES ("Adam", 543); -- 543 is an id of France dictionary entry in this example

And I imagine hibernate to give me a way to somehow execute this in one query like:

INSERT INTO user(name, country_dictioanry_id) SELECT "Adam", d.id FROM dictionary d WHERE d.code='FR' AND d.dtype='CountryDictionary';

Is there a way to do so? Does it make any sence at all, maybe I should redesign my schema somehow?

Answer 1

Hibernate will throw the transient exception because CountryDictionary is never passed to persist or save method since it is only created in the constructor of the User object.Also hibernate can't be sure that the CountryDictionary object exists in database because it has no primary key and there's no AUTO Generation so it assumes that the id is set manually ,which in this case, it is not ,so what you can try is set the id of the CountryDictionary object ,if it is known to you since it is supposed to be set manually ,hibernate will issue a select to make sure if the CountryDictionary object exists in the database before insertion,and if it does ,it will insert the user object.