Spring Data/Hibernate/JPA join by non-primary keys
There are two related entities Account and AccountInfo. These entities connected by non-primary keys (number_ff, number_ff2). I see that Hibernate fetching three entities(Account, AccountInfo, AccountInfo) by one sql query. But I see that hibernate do additional query to database as well. Why does hibernate do additional call(query2)? How to avoid it?
Also I tried to use JPQL query. It works the same way (2 sql queries).
Database schema(postgres):
CREATE TABLE public.accounts (
user_id serial PRIMARY KEY,
number_ff varchar NULL,
username varchar(50) NULL,
number_ff2 varchar NULL,
CONSTRAINT accounts_pkey PRIMARY KEY (user_id)
);
CREATE TABLE public.account_info (
account_info_id serial4 NOT NULL,
number_ff varchar NULL,
info varchar(1000) NULL,
CONSTRAINT account_info_pkey PRIMARY KEY (account_info_id)
);
Hibernate enitity 1:
@Entity
@Table(name = "accounts")
@Getter
@Setter
@EqualsAndHashCode
public class Account implements Serializable {
@Id
private Long userId;
@Column(name = "number_ff")
private String numberFf;
@Column(name = "number_ff2")
private String numberFf2;
private String username;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "number_ff", referencedColumnName = "number_ff", insertable = false, updatable = false, unique = true)
private AccountInfo accountInfos;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "number_ff2", referencedColumnName = "number_ff", insertable = false, updatable = false, unique = true)
private AccountInfo accountInfo2s;
}
Entity 2:
@Entity
@Table(name = "account_info")
@Getter
@Setter
public class AccountInfo implements Serializable {
@Id
private Long accountInfoId;
@NaturalId
@Column(name = "number_ff")
private String numberFf;
private String info;
}
Service:
@Service
public class ServiceInfo {
@Autowired
private AccountRepository accountRepository;
@PostConstruct
public void init() {
Optional<Account> account = accountRepository.findById(1L);
System.out.println(account);
}
}
SQL queries: Query 1 (Query that retrieves all the necessary data)
select
account0_.user_id as user_id1_1_0_,
account0_.number_ff2 as number_f3_1_0_,
account0_.number_ff as number_f2_1_0_,
account0_.username as username4_1_0_,
accountinf1_.account_info_id as account_1_0_1_,
accountinf1_.info as info2_0_1_,
accountinf1_.number_ff as number_f3_0_1_,
accountinf2_.account_info_id as account_1_0_2_,
accountinf2_.info as info2_0_2_,
accountinf2_.number_ff as number_f3_0_2_
from
accounts account0_
left outer join account_info accountinf1_ on
account0_.number_ff2 = accountinf1_.number_ff
left outer join account_info accountinf2_ on
account0_.number_ff = accountinf2_.number_ff
where
account0_.user_id =?
Query 2: (Additional query, why does it do it?)
select
accountinf0_.account_info_id as account_1_0_0_,
accountinf0_.info as info2_0_0_,
accountinf0_.number_ff as number_f3_0_0_
from
account_info accountinf0_
where
accountinf0_.number_ff =?
I think second call is redundant. Why does hibernate do additional call (query 2)? How to avoid it? It looks like bug for me.