英文:
How to renumber primary index of a table using spring annotation?
问题
以下是您要翻译的内容：
我有一个使用MySQL数据库的Spring Boot项目。我有一个名为Employee的表，它有一个主索引（id），其编号不是逐一递增的（1、31、35、100等）。我希望它们的编号变成（1、2、3、4）。
这里也有类似的问题链接
我从上面的链接中得到了这个查询 SET @i=0; UPDATE Employee SET id=(@i:=@i+1);，如果在MySQL Workbench中直接执行它，它可以正常工作。
我的问题是如何使用Spring注解执行上述查询？
EmployeeRepository.java
public interface EmployeeRepository extends 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();
}
}
当使用JUnit测试renumber()方法时，它给出了以下异常：
org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [SET @i=0; UPDATE Employee e SET e.id=(@i:=@i+1)];
请帮忙，如何使用`renumberingId()`方法执行此查询 `SET @i=0; UPDATE Employee SET id=(@i:=@i+1);`
英文:
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
答案1
得分: 1
> 你的查询集 SET @i=0; UPDATE Employee e SET e.id=(@i:=@i+1) 是正确的。只需稍微修改一下 - 将变量初始化移到 UPDATE 语句中：UPDATE Employee e, (SELECT @i:=0) var SET e.new_column =(@i:=@i+1);
> 你的查询在 MySQL Workbench 中运行良好。问题在于 @Query() 注解总是在 : 后面寻找参数。
如果需要，你可以通过以下方式将新列填充为枚举值：
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;
英文:
> 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;
答案2
得分: 0
你不需要所有这些来做那个，这是操作的方式：
import javax.persistence.*;
@Entity
public class Employee {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
public long id;
}
它应该会自动执行你所期望的操作。
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论