controller
@PostMapping("/users")
fun apisUserList(@RequestBody param: MultiValueMap<String, String>): DataTableRes {
return DataTable(param, arrayOf("id", "username", "email"), userRepo).search()
}
datatable
fun search(): DataTableRes {
val rows: List<User> = userRepo.findByDataTable(this.getQuery(), this.getOrderByQuery(), limit, offset)
return DataTableRes(rows, this.getQueryCnt(this.getQuery(), this.getOrderByQuery(), limit, offset),
this.getAllCnt(), draw)
}
repository
@Repository
interface UserRepo: JpaRepository<User, Long> {
@Query(value="SELECT * FROM users WHERE ?1 ORDER BY ?2 LIMIT ?3 OFFSET ?4", nativeQuery=true)
fun findByDataTable(where: String, orderBy: String, limit: Int, offset: Int): List<User>
}
SELECT * FROM users WHERE 1=1 AND 1=1 ORDER BY id DESC LIMIT 10 OFFSET 0
query is working, but nativeQuery is not working.
[User(id=1, email='213@gmail.com', username='aaa-2011337716', password='', provider='google', verified_email=false, group_permission_id=null, token='3d2bdda57ce84aa48de79e8ecf4b117b'), User(id=3, email='213133@daum.net', username='wyuaa043472118269', password='', provider='kakao', verified_email=false, group_permission_id=null, token='577f38e39ac04a8582533d580db6c2ce')]
result is wrong. originally, ID:3 is first.
how to fix it?