spring:: JPA Repository custom query not working

Viewed 257

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?

0 Answers
Related