JPQL query by foreign key

Question

There are two tables PersonEntity and cityentity. PersonEntity in the database is linked to cityentity by the external key fk_cityid. I need to select all the records (names) of the PersonEntity table with the given CityId. Join is used everywhere for this, but in this case I don't need data from the cityentity table, only the name field of the PersonEntity table. Here is a description of the classes:

@Entity
public class PersonEntity {
    private Long id;
    private String name;
    private CityEntity cityId;
}
@Entity
public class CityEntity {
    private Long id;
    private String name;
}

Here is the HQL query:

@Repository
public interface PersonEntityRepository extends JpaRepository<PersonEntity, Long> {
  @Query("select p.name FROM PersonEntity p where (p.name = :name or :name is null) " +
        "and (p.cityId = :cityId or :cityId is null)")
    List<PersonEntity> findByNameAndCity (
        @Param("name") String name,
        @Param("cityId") CityEntity cityId);
}

tried by id:

@Query("select p.name FROM PersonEntity p where (p.name = :name or :name is null) " +
        "and (p.cityId.id = :cityId or :cityId is null)")
    List<PersonEntity> findByNameAndCity (
        @Param("name") String name,
        @Param("cityId") Long cityId);

In both cases, the error is: "the data type could not be determined".

options for calling the function:

servisPerson.findByNameAndCity (null, cityId);

or

servisPerson.findByNameAndCity (name, null);

In fact, there are more than two parameters. I only show two for simplification.

servisPerson.findByNameAndCity (name, age, ..., cityId);

Answer 1

Person entity should look something like this

@Entity
public class PersonEntity {
     private Long id;
     private String name;

     @OneToOne
     @JoinColumn(name = "city_id")
     private CityEntity city;
}

Than you can write your query like this

List<PersonEntity> findByNameAndCityOrNameIsNullOrCityIsNull(String name, CityEntity city);

Spring Data JPA is so smart to generate the query for you under the hood.

BTW, you attempted to write JPQL, not HQL.

EDIT (reaction on comment about long method names):

I would suggest to avoid creating JPQL query, because is is more error prone. If you don't like lengthy method name, you can wrap it into shorter default method within repository:

@Repository
public interface PersonEntityRepository extends  JpaRepository<PersonEntity, Long> {
     List<PersonEntity> findByNameAndCityOrNameIsNullOrCityIsNull(String name, CityEntity city);
     
     default List<PersonEntity> shortName(String name, CityEntity city) {
         return findByNameAndCityOrNameIsNullOrCityIsNull(name, city);
     }
}