Multiple optional @RequestParam not fetching Data in spring boot

Question

I want to paginate response Data in Spring boot project. According to business logic, I added multiple RequestParam. All is ok, if I pass value for all request Param i.e Gender and Country. But if I don't give value either one or both, I get 500 status code, although I make gender and Country requestParam as optional. It means,

if I hit

http://localhost:8080/api/v1/users?page=1&country=Russia&gender=M,

I get response with pagination.

But if I hit

http://localhost:8080/api/v1/users?page=1&gender=M

http://localhost:8080/api/v1/users?page=1&country=Russia

http://localhost:8080/api/v1/users?page=1.

I get exception

Here is my code.

UserRepository.kt

@Repository
interface UserRepository: JpaRepository<User, Long> {

    @Query(
        nativeQuery = true,
        value = "SELECT * FROM user_info WHERE gender =:gender AND country =:country"
    )
    fun getUsers(gender: String?, country: String?, pageable: Pageable): Page<User>

}

UserServiceImpl.kt

@Service
class UserServiceImpl(
    @Autowired private val userRepository: UserRepository
): UserService {

    override fun getUsers(gender: String?, country: String?, pageable: Pageable): Page<User> {
        return userRepository.getUsers(gender, country, pageable)
    }

}

UserController.kt

@RestController
@RequestMapping(
    path = [
        "/api/v1/"
    ]
)
class UserController(
    @Autowired private val userService: UserService
) {

    @GetMapping("users")
    fun getUsers(
        @RequestParam(required = true) page: Int,
        @RequestParam(required = false) gender: String?,
        @RequestParam(required = false) country: String?
    ): Page<User> {
        return userService.getUsers(gender, country, PageRequest.of(page, 10))
    }

}

response

{
    "status": "500 INTERNAL_SERVER_ERROR",
    "message": "Internal server error occurs",
    "error": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"
}

Answer 1

It is better to use jdbcTemplate here like this:

String query = "SELECT * FROM user_info WHERE gender = :gender AND country = :country";
Map<String, Object> params = new HashMap<>();
params.put("gender", gender);
params.put("country", country);
Map<String, Long> results = new HashMap<>();
// Execute Your Query Here like: users = jdbcTemplate.query(query, params, ...

for thos params you can check if for example gender not null append gender = :gender to main query.

Answer 2

yes the error you're getting is correct because the values for your query doesn't exist i would suggest you make the following changes ...

@Repository
interface UserRepository: JpaRepository<User, Long> {

@Query(
    nativeQuery = true,
    value = "SELECT * FROM user_info WHERE country =:country"
)
fun getUsersWithoutGender( country: String?, pageable: Pageable): Page<User>

 @Query(
    nativeQuery = true,
    value = "SELECT * FROM user_info WHERE gender =:gender AND country =:country"
)
fun getUsersWithoutCountry(gender: String?, pageable: Pageable): Page<User>

 @Query(
    nativeQuery = true,
    value = "SELECT * FROM user_info WHERE gender =:gender AND country =:country"
)
fun getUsers(gender: String?, country: String?, pageable: Pageable): Page<User>

@Query(
    nativeQuery = true,
    value = "SELECT * FROM user_info"
)
fun getallUsers(): Page<User>

}

and in your controller

RestController
@RequestMapping(
path = [
    "/api/v1/"
]
)
class UserController(
@Autowired private val userService: UserService
) {

@GetMapping("users")
fun getUsers(
    @RequestParam(required = true) page: Int,
    @RequestParam(required = false) gender: String?,
    @RequestParam(required = false) country: String?
): Page<User> {

if(country == null && gender =! null){
    return userService.getUsersWithoutCountry(gender,PageRequest.of(page, 10))
} else if (gender== null && country =! null){
    return userService.getUsersWithoutGender(country,PageRequest.of(page, 10))
}else if (gender && country == null){
    return userService.getAllUsers()
}else {
    return userService.getUsers(gender, country, PageRequest.of(page, 10))    
}
}

}

this way all your queries will run as they don't have a null value.

Answer 3

Finally, I solved the problem by JpaSpecificationExecutor which helps to create dynamic query. The code is given bellow.

UserSpecification.kt

import com.example.demo.entity.User
import org.springframework.data.jpa.domain.Specification
import javax.persistence.criteria.CriteriaBuilder
import javax.persistence.criteria.CriteriaQuery
import javax.persistence.criteria.Root
import java.text.MessageFormat.format

object UserSpecification {

    fun countryContains(country: String?): Specification<User>? {
        return country?.let {
            Specification { root: Root<User>, _: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->
                criteriaBuilder.like(root.get("country"), format("%{0}%", country))
            }
        }
    }

    fun genderContains(gender: String?): Specification<User>? {
        return gender?.let {
            Specification { root: Root<User>, _: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->
                criteriaBuilder.equal(root.get<String>("gender"), gender)
            }
        }
    }

}

UserRepository.kt

@Repository
interface UserRepository: JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

}

UserServiceImpl.kt

@Service
class UserServiceImpl(
    @Autowired private val userRepository: UserRepository
): UserService {

    override fun getUsers(gender: String?, country: String?, pageable: Pageable): Page<User> {
        return userRepository.findAll(
            where(
                countryContains(country)
            ).and(
                genderContains(gender)
            ), pageable
        )
    }
}