Not able to execute ALTER SEQUENCE command through spring boot code

user_1649217 0
Viewed 388

I am writing a code in spring boot 1.5.22 with java 8 and oracle 11g. Here, In my repository class, I have tried to call one native query as-

@Query(value = "ALTER SEQUENCE <SEQ_NAME> RESTART START WITH 0", nativeQuery = true)
void resetSequence();

when I try to call this method in my service Impl class, I get the following error:-

java.lang.NegativeArraySizeException:-1

However, I can execute select sequence commands using java code as-

    @Query(value = "select <Seq_name>.nextVal from dual", nativeQuery = true)
    int getNextCount();

I don't know how exactly I can command to reset my sequence using java code/job here.

spring-data java-8 spring-boot oracle spring
4 Answers

Try to add @Modifying annotation

user_121999150

Well, ALTER SEQUENCEis not a query, so it is not expected to work in the way you call it.

What will work is to fall back to plain JDBC and call something like this

con.createStatement().execute "ALTER SEQUENCE  SEQ RESTART START WITH 0"

Two additionally remarks

  • the ALTER SEQUENCEis not a typical use case for spring-data so it should be used only in some supporting code for JUnit etc.

  • You'll have to create the sequence with MINVALUE 0 to be able to reset it (default is 1). Otherwise you get exception ORA-04006: START WITH cannot be less than MINVALUE

user_48081220

You can use the Entity manager to modify a sequence and execute SQL queries that the spring boot shortcuts do not allow

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;      
@PersistenceContext
      EntityManager entityManager;
        public void resetSequence(String data) {
             entityManager
                    
                     .createNativeQuery("SELECT setval('sequence_value', "+data+", FALSE);")
                    .getSingleResult()
                    ;
        }
user_97165950

you can use entity manager to reset your sequence

ORACLE

entityManager.createNativeQuery("DROP SEQUENCE " + seqName).executeUpdate();
entityManager.createNativeQuery("CREATE SEQUENCE " + seqName + " START WITH " + size).executeUpdate();

POSTGRESQL

entityManager.createNativeQuery("SELECT setval(?, ?, true)")
                .setParameter(1, seqName)
                .setParameter(2, size)
                .getResultList();
user_108670760
Related