多个可选@requestparam在spring boot中不获取数据

fslejnso  于 2021-07-23  发布在  Java
关注(0)|答案(3)|浏览(645)

我想在springboot项目中对响应数据进行分页。根据业务逻辑,我添加了多个requestparam。如果我传递所有请求参数的值,即性别和国家,则一切正常。但如果我不给出一个或两个值,我会得到500个状态码,尽管我将gender和country requestparam作为可选的。意思是,
如果我击中
http://localhost:8080/api/v1/users?page=1&country=russia&gender=m,
我得到了分页的回应。
但如果我打了
http://localhost:8080/api/v1/users?page=1&gender=m
http://localhost:8080/api/v1/users?page=1&country=俄罗斯
http://localhost:8080/api/v1/users?第1页。
我有例外
这是我的密码。 UserRepository.kt ```
@Repository
interface UserRepository: JpaRepository<User, Long> {

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

}
`UserServiceImpl.kt`
@Service
class UserServiceImpl(
@Autowired private val userRepository: UserRepository
): UserService {

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

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

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

}
`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"
}

sqserrrh

sqserrrh1#

在这里最好使用jdbctemplate,如下所示:

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

对于thos参数,您可以检查例如gender not null append gender=:gender to main query。

juzqafwq

juzqafwq2#

是的,您得到的错误是正确的,因为您的查询值不存在,我建议您进行以下更改。。。

  1. @Repository
  2. interface UserRepository: JpaRepository<User, Long> {
  3. @Query(
  4. nativeQuery = true,
  5. value = "SELECT * FROM user_info WHERE country =:country"
  6. )
  7. fun getUsersWithoutGender( country: String?, pageable: Pageable): Page<User>
  8. @Query(
  9. nativeQuery = true,
  10. value = "SELECT * FROM user_info WHERE gender =:gender AND country =:country"
  11. )
  12. fun getUsersWithoutCountry(gender: String?, pageable: Pageable): Page<User>
  13. @Query(
  14. nativeQuery = true,
  15. value = "SELECT * FROM user_info WHERE gender =:gender AND country =:country"
  16. )
  17. fun getUsers(gender: String?, country: String?, pageable: Pageable): Page<User>
  18. @Query(
  19. nativeQuery = true,
  20. value = "SELECT * FROM user_info"
  21. )
  22. fun getallUsers(): Page<User>
  23. }

在你的控制器里

  1. RestController
  2. @RequestMapping(
  3. path = [
  4. "/api/v1/"
  5. ]
  6. )
  7. class UserController(
  8. @Autowired private val userService: UserService
  9. ) {
  10. @GetMapping("users")
  11. fun getUsers(
  12. @RequestParam(required = true) page: Int,
  13. @RequestParam(required = false) gender: String?,
  14. @RequestParam(required = false) country: String?
  15. ): Page<User> {
  16. if(country == null && gender =! null){
  17. return userService.getUsersWithoutCountry(gender,PageRequest.of(page, 10))
  18. } else if (gender== null && country =! null){
  19. return userService.getUsersWithoutGender(country,PageRequest.of(page, 10))
  20. }else if (gender && country == null){
  21. return userService.getAllUsers()
  22. }else {
  23. return userService.getUsers(gender, country, PageRequest.of(page, 10))
  24. }
  25. }
  26. }

这样,所有查询都将运行,因为它们没有空值。

展开查看全部
uqxowvwt

uqxowvwt3#

最后,我用jpaspecificationexecutor解决了这个问题,它有助于创建动态查询。代码如下。 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 {

  1. fun countryContains(country: String?): Specification<User>? {
  2. return country?.let {
  3. Specification { root: Root<User>, _: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->
  4. criteriaBuilder.like(root.get("country"), format("%{0}%", country))
  5. }
  6. }
  7. }
  8. fun genderContains(gender: String?): Specification<User>? {
  9. return gender?.let {
  10. Specification { root: Root<User>, _: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->
  11. criteriaBuilder.equal(root.get<String>("gender"), gender)
  12. }
  13. }
  14. }

}
`UserRepository.kt`
@Repository
interface UserRepository: JpaRepository<User, Long>, JpaSpecificationExecutor {

}
`UserServiceImpl.kt`
@Service
class UserServiceImpl(
@Autowired private val userRepository: UserRepository
): UserService {

  1. override fun getUsers(gender: String?, country: String?, pageable: Pageable): Page<User> {
  2. return userRepository.findAll(
  3. where(
  4. countryContains(country)
  5. ).and(
  6. genderContains(gender)
  7. ), pageable
  8. )
  9. }

}

展开查看全部

相关问题