多个可选的@RequestParam在spring启动时不获取数据

Question

我想在Spring启动项目中分页响应数据。根据业务逻辑，我添加了多个RequestParam。一切都好，如果我为所有请求参数即性别和国家传递值。但如果我不给出其中一个或两个的值，我将得到500状态码，尽管我将gender和Country requestParam设为可选。这意味着,

如果我点击

http://localhost: 8080/api/v1/用户? page = 1,国家= Russia&性别=,

我得到了分页响应。

但是如果我点击

http://localhost: 8080/api/v1/用户? page = 1,性别= M

http://localhost: 8080/api/v1/用户? page = 1,俄罗斯国家=

http://localhost: 8080/api/v1/用户? page = 1。

I get exception

这是我的代码。

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

最好像这样使用jdbcTemplate:

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, ...

对于这些参数，你可以检查，例如，如果性别不是null附加性别=:性别主查询。

Answer 2

是你得到的错误是正确的，因为你的查询值不存在，我建议你做以下更改…

@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>
}

和控制器

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))    
}
}
}

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

Answer 3

最后，我通过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 {
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
)
}
}