I'm setting up a Spring Data JPA Repo to work with sequences in a postgresql database. I was assuming that this would be pretty simple:
@Query(nativeQuery = true, value = "CREATE SEQUENCE IF NOT EXISTS ':seq_name' START WITH :startAt")
fun createSequence(@Param("seq_name") seq_name: String, @Param("startAt") startAt: Long = 0)
@Query(nativeQuery = true, value = "SELECT nextval(':seq_name')")
fun nextSerial(@Param("seq_name") seq_name: String) : Long
@Query(nativeQuery = true, value = "DROP SEQUENCE IF EXISTS ':seq_name'")
fun dropSequence(@Param("seq_name") seq_name: String)
@Query(nativeQuery = true, value = "setval(':seq_name', :set_to, false")
fun setSequence(@Param("seq_name") seq_name: String, @Param("set_to") setTo: Long)
But for some reason I get
org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that name [seq_name] did not exist; whenever I'm trying to call the method. Any idea why this might happen?
Ok, based on the answer from @StanislavL and after some debugging around I have a working solution now. As @posz pointed out I cannot bind identifiers which means I have to hard code the queries. I moved the code from a JPA interface to an implemented service which is not as nice but works.
@Service
open class SequenceService (val entityManager: EntityManager){
@Transactional
fun createSequence(seq_name: String, startAt: Long = 0) {
val query = entityManager.createNativeQuery("CREATE SEQUENCE IF NOT EXISTS ${seq_name} START ${startAt}")
with(query){
executeUpdate()
}
}
@Transactional
fun nextSerial(seq_name: String) : Long {
val query = entityManager.createNativeQuery("SELECT nextval(:seq_name)")
with(query){
setParameter("seq_name", seq_name)
val result = singleResult as BigInteger
return result.toLong()
}
}
@Transactional
fun dropSequence(seq_name: String) {
val query = entityManager.createNativeQuery("DROP SEQUENCE IF EXISTS ${seq_name}")
with(query){
executeUpdate()
}
}
@Transactional
fun setSequence(seq_name: String, setTo: Long){
val query = entityManager.createNativeQuery("SELECT setval(:seq_name, :set_to, false)")
with(query){
setParameter("seq_name", seq_name)
setParameter("set_to", setTo)
singleResult
}
}
}
I hope this is helpful for the next person trying to directly work with sequences when using @SequenceGenerator is not an option.