CODEWITHSUNDEEP
jpakotlinspring-data-jpajpql
louis amoros
louis amoros

Reputation: 2546

Expression `in` when empty list (spring data jpa specification)

I have this mission repository: 

@Repository
interface MissionRepository: CrudRepository<MissionEntity, String>, JpaSpecificationExecutor<MissionEntity>

In my mission service class, I would like to fetch all missions that have their countryId parts of the given argument Set:

fun findAllByCountryIdIn(countryIds: Set<String>): List<MissionEntity> =
        missionRepository.findAll(where(countryIdIn(countryIds)))
}

Where countryIdIn (using in predicate) comes from:

class MissionSpecifications {
    companion object {
        fun countryIdIn(countryIds: Set<String>): Specification<MissionEntity> =
            Specification { root, _, _ -> root.get<String>("countryId").`in`(countryIds) }
    }
}

But when the Set is empty I got a predictable sql error. Is there a way to activate the where clause only when the given set is not empty? Without a if/else check? Maybe my specification syntax could be improve to avoid this sql error?

Upvotes: 2

Views: 3074

Answers (3)

louis amoros
louis amoros

Reputation: 2546

An other solution would be:

@Repository
interface MissionRepository: JpaRepository<MissionEntity, String> {
    fun findByCountryIdIn(countryIds: Set<String>, pageable: Pageable): Page<MissionEntity>
}

where you can add pagination.

Upvotes: 0

Roland
Roland

Reputation: 23312

I would rather return earlier instead. So just don't add a where if you do not require it in the first place. You can do that in several ways, e.g. using takeIf, a simple if, a when, etc.

Just listing some samples:

  • takeIf

    fun findAllByCountryIdIn(countryIds: Set<String>) = countryIds.takeIf { it.isNotEmpty() }
             ?.let { missionRepository.findAll(where(countryIdIn(it))) } 
             ?: // what should be returned otherwise? emptyList? all? exception?

  • ifEmpty (Kotlin >=1.3)

    fun findAllByCountryIdIn(countryIds: Set<String>) = countryIds.ifEmpty {
  // what should be returned? emptyList? all entries?
}.let {
  missionRepository.findAll(where(countryIdIn(it))) }
}

  • if

    fun findAllByCountryIdIn(countryIds: Set<String>) = if (countryIds.isEmpty()) /* what should be returned? */ 
                                                    else missionRepository.findAll(where(countryIdIn(countryIds))) }

If you just solve the countryIdIn instead, e.g. by passing an empty element, you give away the control of the query itself to the helper method. If you really want that, fine... but otherwise I wouldn't do that.

Why I wouldn't do that? If I return later to that specific code and read findAll(where(countryIdIn(countryIds))). How long does it take me to understand for sure that I return all entries if the set is empty? Fact is: I can't without looking at countryIdIn itself. But that is my opinion.

Upvotes: 2

Jens Schauder
Jens Schauder

Reputation: 81990

Just put a test in the function creating the Specification if the set is empty and if it is, just return an empty Specification.

Upvotes: 0

Related Questions