How to renumber primary index of a table using spring annotation?

I have a spring boot project which uses MySQL database. I have a table called Employee it has a primary index (id) is not numbered one by one (1, 31, 35, 100 etc.). I want them to be numbered like (1, 2, 3, 4)

there is also similar question here

I got the query form above link SET @i=0; UPDATE Employee SET id=(@i:=@i+1); and it is working fine if execute it directly in MySQL workbench

my question is how can I execute the above query using the spring annotation?

EmployeeRepository.java

public interface EmployeeRepositoryextends JpaRepository<Employee, Integer> {
    
    @Transactional
    @Modifying
    @Query(value = "SET @i=0; UPDATE Employee e SET e.id=(@i:=@i+1)", nativeQuery = true)
    public void renumberingId();    
}

EmployeeService.java

   @Service
   public class EmployeeService {

        @Autowired
        private EmployeeRepository repo;
        
        public void renumber() {
             repo.renumberingId();
        }
   }

When test the renumber() method using junit it gives me the below exception

org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [SET @i=0; UPDATE Employee e SET e.id=(@i:=@i+1)];

please help how can execute this query SET @i=0; UPDATE Employee SET id=(@i:=@i+1); using renumberingId() method

> Your queries set SET @i=0; UPDATE Employee e SET e.id=(@i:=@i+1) is correct. Simply alter it slightly - move variable initialization into UPDATE: UPDATE Employee e, (SELECT @i:=0) var SET e.new_column =(@i:=@i+1);

> your query working fine in MySQL workbench. the problem is that the @Query() annotation always search for a parameter after :

If so you may fill new column with enumeration by

UPDATE Employee t0
  JOIN ( SELECT t1.id, COUNT(t2.id) cnt
         FROM Employee t1
         JOIN Employee t2 ON t1.id >= t2.id
         GROUP BY t1.id ) t3 ON t0.id = t3.id
SET t0.new_column = t3.cnt;

modelling fiddle

You don't need all this for that, this is how it is done :

import javax.persistance.*;

@Entity   
public class Employee {   

  @Id   
  @GeneratedValue(strategy=GenerationType.IDENTITY)   
  public long id;

} 

And it should automacaly do what you are expecting