Unable to fetch results with associations in spring boot 2.5.0

Question

I am trying to upgrade my spring boot project from 2.4.3 to 2.5.0. The strange thing happened to me is I am unable to fetch the results when entities are associated.

For example, I have two simple entities:

UserProfile entity:

@Entity
@Table(name = "user_profile")
public class UserProfile {

@Id
private String id;

@Column(name = "first_name")
private String firstName;

@Column(name = "last_name")
private String lastName;

private String email;

@ManyToOne(optional = false, fetch = FetchType.LAZY)
@JoinColumn(name = "tenantId")
private Tenant tenant;

public Tenant getTenant() {
    return tenant;
}

public void setTenant(Tenant tenant) {
    this.tenant = tenant;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String getId() {
    return id;
}

public void setId(String id) {
    this.id = id;
}

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}
}

Tenant entity:

@Entity
public class Tenant {

@Id
private String id;

public String getId() {
    return id;
}

public void setId(String id) {
    this.id = id;
}
}

UserProfile entity has many to one association with Tenant entity. My repository class is

public interface UserProfileRepository extends CrudRepository<UserProfile, String> {

UserProfile findByEmailAndTenantId(String email, String tenantId);
}

Now, the method findByEmailAndTenantId from the above repository always returns null.

The SQLs generated with spring boot 2.5.0 is

select
    userprofil0_.id as id1_1_,
    userprofil0_.email as email2_1_,
    userprofil0_.first_name as first_na3_1_,
    userprofil0_.last_name as last_nam4_1_,
    userprofil0_.tenant_id as tenant_i5_1_ 
from
    pr.user_profile userprofil0_ 
inner join
    pr.tenant tenant1_ 
        on userprofil0_.tenant_id=tenant1_.id 
where
    userprofil0_.email=? 
    and (
        tenant1_.id is null
    )

The SQL generated for the same entities with spring boot 2.4.3 is

select
    userprofil0_.id as id1_1_,
    userprofil0_.email as email2_1_,
    userprofil0_.first_name as first_na3_1_,
    userprofil0_.last_name as last_nam4_1_,
    userprofil0_.tenant_id as tenant_i5_1_ 
from
    pr.user_profile userprofil0_ 
where
    userprofil0_.email=? 
    and (
        userprofil0_.tenant_id is null
    )

Is this intended behavior in Spring Boot 2.5.0? Can anyone please help me in finding the solution to this problem?

Could you set "spring.jpa.show-sql" to true and paste the result? Also, your Join column name seems out of place, does `user_profile` table have column named `tenantId` or `tenant_id`? — Kamil Bęben, May 25 '21 at 21:52
@KamilBęben, thanks for your response. I have edited the question with sql queries. — Prashanth, May 26 '21 at 04:43

score 1 · Answer 1 · answered May 26 '21 at 07:39

I couldn't find anything in the documentation about it, but read this What is the difference between @ManyToOne(optional=false) vs. @Column(nullable=false)

It seems that optional = false results in INNER JOIN to tenant, that combined with WHERE tenant_id IS NULL can't return any rows.

So if you really need to fetch entities with tenant_id set to null, you've got to remove optional = false from the @JoinColumn annotation.

Unable to fetch results with associations in spring boot 2.5.0

1 Answers1