2020年10月10日 02:03:38go评论77阅读模式

英文:

Duplicate key value violates unique constraint, but it shouldn't [Spring Boot, PostgreSQL]

问题

代码简要描述：

用户可以创建分类。其他用户可以看到这些分类。然后，他们可以点击“关注”某个分类，然后信息会被保存到数据库中。下方有一张图片显示数据库中的样子。到目前为止，一切工作正常。

用户可以创建多个分类，每个分类只能有一个所有者。

分类可以有多个关注者，用户可以关注多个分类。

问题：

然而，当用户点击“关注”另一个分类（具有不同的分类 ID，如 HTML 和浏览器截图所示）时，我的程序崩溃，并且我会收到以下错误（摘录）：

2020-10-09 20:12:21.526  WARN 25512 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23505
2020-10-09 20:12:21.526 ERROR 25512 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: duplicate key value violates unique constraint "followed_categories_pkey"
  Detail: Key (user_id, category_id)=(37, 5) already exists.
2020-10-09 20:12:21.526  INFO 25512 --- [io-8080-exec-10] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2020-10-09 20:12:21.529 ERROR 25512 --- [io-8080-exec-10] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [followed_categories_pkey]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "followed_categories_pkey"
  Detail: Key (user_id, category_id)=(37, 5) already exists.

还指出崩溃发生在：

（这指向控制器中的 userService.updateUser(user);）

at com.blog.reviewwebsite.controller.CategoryController.followCategory(CategoryController.java:59) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_252]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_252]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_252]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_252]

尽管调试器和 HTML 显示我传递的是 user_id = 37，category_id = 6。

然而，如果我停止我的 IDE 并重新启动它（数据库仍然显示第一次关注，就像图片中所示），然后使用相同的用户登录（相同的 user_id）并在另一个分类上点击关注，一切正常，数据库会得到更新（参见下方的图片）。

然后，如果我尝试关注另一个分类，同样的崩溃会再次发生，我必须重新启动 IDE 才能使其正常工作。

目标：

用户应该能够在不崩溃的情况下关注多个分类。

我尝试过的方法（没有帮助）：

我尝试在 User 类中添加 cascade = {CascadeType.PERSIST, CascadeType.MERGE}。

在两个实体类中重写 Equals 和 Hashcode 方法。

实体类

User

@Getter
@Setter
@Entity
@Table(name = "users")
public class User implements UserDetails {

    //其他字段

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Long id;

    //用户创建新分类时使用
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private Set<Category> categories;

    //用户关注现有分类时使用
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "followedCategories",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "category_id")
    )
    private Set<Category> followedCategories = new HashSet<Category>();
}

Category

@Entity
@Getter
@Setter
@Table(name = "Category")
public class Category {

    //其他字段

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "category_id")
    private Long id;

    //用户创建新分类时使用
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "id", nullable = false)
    private User user;

    //用户关注现有分类时使用
    @ManyToMany(mappedBy = "followedCategories")
    private Set<User> users;
}

控制器

@Controller
@RequestMapping("/categories")
public class CategoryController {

    private CategoryService categoryService;
    private UserService userService;

    public CategoryController(CategoryService categoryService, UserService userService) {
        this.categoryService = categoryService;
        this.userService = userService;
    }

    //其他方法

    @PostMapping("/follow/{id}")
    public String followCategory(@PathVariable Long id, @AuthenticationPrincipal User user) {
        Category category = categoryService.getOneById(id);
        user.getFollowedCategories().add(category);
        userService.updateUser(user);

        return "redirect:/categories/all";
    }
}

服务

UserService

@Service
public class UserService implements UserDetailsService {

    private UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    //其他方法

    public User updateUser(User user) {
        return userRepository.save(user);
    }
}

Category Service

@Service
public class CategoryService {

    private CategoryRepository categoryRepository;

    public CategoryService(CategoryRepository categoryRepository) {
        this.categoryRepository = categoryRepository;
    }

    //其他方法

    public Category getOneById(Long id) {
        return categoryRepository.getOne(id);
    }
}

仓库

User Repository

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findUserByUsername(String username);
}

Category Repository

@Repository
public interface CategoryRepository extends JpaRepository<Category, Long>

<details>
<summary>英文:</summary>

**A short description of the code:**

User can create a category. Other users then can see that and other categories. They can then press &#39;follow&#39; on a category and then they are saved to a database. There is picture bellow which shows how it looks in the DB. Everything works fine up to this point.

User can create many categories and category can have one owner only.

Categories can have many followers, User can follow many categories.

**The problem**

However, when user then presses &#39;follow&#39; on another category (with different category ID and all (see HTML and browser screenshot below)) my program crashes and I get this error (excerpt):

2020-10-09 20:12:21.526 WARN 25512 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 23505
2020-10-09 20:12:21.526 ERROR 25512 --- [io-8080-exec-10] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: duplicate key value violates unique constraint "followed_categories_pkey"
Detail: Key (user_id, category_id)=(37, 5) already exists.
2020-10-09 20:12:21.526 INFO 25512 --- [io-8080-exec-10] o.h.e.j.b.internal.AbstractBatchImpl : HHH000010: On release of batch it still contained JDBC statements
2020-10-09 20:12:21.529 ERROR 25512 --- [io-8080-exec-10] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [followed_categories_pkey]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "followed_categories_pkey"
Detail: Key (user_id, category_id)=(37, 5) already exists.


And also indicates that it crashes at:

(this points to a ```userService.updateUser(user);``` in the controller)

at com.blog.reviewwebsite.controller.CategoryController.followCategory(CategoryController.java:59) ~[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_252]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_252]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_252]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_252]


**despite both debugger and html showing that I am passing user_id = 37, category_id = 6.**

**However**, if I stop my IDE and start it again (DB still shows first follow, like in the picture) and then I sign in with the same user (same user_id) and press follow on another category, everything works and database gets updated (see picture below).

Then, if I try to follow yet another category, same crash happens again, I would have to restart IDE for it to work again.

**Goal**

User should be able to follow multiple categories without crashes

**What I have tried** (nothing helped):

I&#39;ve tried adding ```cascade = {CascadeType.PERSIST, CascadeType.MERGE}``` in the User class.

Overriding Equals and Hashcode in both entity classes.




**Entities**

 - User

@Getter
@Setter
@Entity
@Table(name = "users")
public class User implements UserDetails {

//other fields

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = &quot;user_id&quot;)
private Long id;

//This one is used when a user makes a new category
@OneToMany(mappedBy = &quot;user&quot;, fetch = FetchType.LAZY)
private Set&lt;Category&gt; categories;


//This one is used when a user follows existing category
@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(
        name = &quot;followedCategories&quot;,
        joinColumns = @JoinColumn(name = &quot;user_id&quot;),
        inverseJoinColumns = @JoinColumn(name = &quot;category_id&quot;)
)
private Set&lt;Category&gt; followedCategories = new HashSet&lt;Category&gt;();

}


 - Category

@Entity
@Getter
@Setter
@Table(name = "Category")
public class Category {

//other fields

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = &quot;category_id&quot;)
private Long id;

//This one is used when a user makes a new category
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = &quot;id&quot;, nullable = false)
private User user;

//This one is used when a user follows existing category
@ManyToMany(mappedBy = &quot;followedCategories&quot;)
private Set&lt;User&gt; users;

}


**Controller**

@Controller
@RequestMapping("/categories")
public class CategoryController {

private CategoryService categoryService;
private UserService userService;

public CategoryController(CategoryService categoryService, UserService userService) {
    this.categoryService = categoryService;
    this.userService = userService;
}

//other methods

@PostMapping(&quot;/follow/{id}&quot;)
public String followCategory(@PathVariable Long id, @AuthenticationPrincipal User user) {
    Category category = categoryService.getOneById(id);
    user.getFollowedCategories().add(category);
    userService.updateUser(user);

    return &quot;redirect:/categories/all&quot;;
}

}


***Service***

 - UserService

@Service
public class UserService implements UserDetailsService {

private UserRepository userRepository;

public UserService(UserRepository userRepository) {
    this.userRepository = userRepository;
}

//other methods

public User updateUser(User user) {
return userRepository.save(user);
}
}


 - Category Service

@Service
public class CategoryService {

private CategoryRepository categoryRepository;

public CategoryService(CategoryRepository categoryRepository) {
    this.categoryRepository = categoryRepository;
}

//other methods

public Category getOneById(Long id) {
    return categoryRepository.getOne(id);
}

}


**Repository**

 - User Repository

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
Optional<User> findUserByUsername(String username);
}


 - Category Repository

@Repository
public interface CategoryRepository extends JpaRepository<Category, Long> {
}


**Relevant HTML code (with thymeleaf)**

&lt;p&gt; Category:
    &lt;span&gt;&lt;a th:href=&quot;@{/reviews/(categoryId=${category.id})}&quot; th:text=&quot;${category.name}&quot;&gt;&lt;/a&gt;
    &lt;span th:text=&quot;${category.user.username}&quot;&gt;&lt;/span&gt;
    &lt;span th:text=&quot;${category.id}&quot;&gt;&lt;/span&gt;
    &lt;/span&gt;
&lt;/p&gt;
&lt;form method=&quot;post&quot; th:action=&quot;@{/categories/follow/{id}/(id=${category.id})}&quot;&gt;
    &lt;span&gt;follow&lt;/span&gt;
    &lt;input type=&quot;submit&quot;&gt;
&lt;/form&gt;

</div>

How it looks in the browser:

[![categories with who created it and category id][1]][1]


How DB looks after following once

[![enter image description here][2]][2]

How DB looks after following once, restarting IDE, and then following another category

[![enter image description here][3]][3]


  [1]: https://i.stack.imgur.com/S0AFN.png
  [2]: https://i.stack.imgur.com/9agji.png
  [3]: https://i.stack.imgur.com/jKoZb.png

</details>


# 答案1
**得分**: 1

(edited) SOLVED:

找到了实际的答案和解决方案：

https://stackoverflow.com/a/59067006/12826439

不需要从控制器传递用户，而是通过其 ID 从存储库获取用户。

<details>
<summary>英文:</summary>

(edited) SOLVED:

found actual answer and solution:

https://stackoverflow.com/a/59067006/12826439

needed not to pass user from controller, but to get user by its id from repository.


</details>



# 答案2
**得分**: 0

问题很可能是您没有为“Category”实现equals/hashCode方法，因此将其添加到集合中会添加一个重复项，然后尝试持久化该重复项。

<details>
<summary>英文:</summary>

The problem is most probably that you didn&#39;t implement equals/hashCode for `Category` so adding it to the set will add a duplicate which is then tried to be persisted.

</details>

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

重复的键值违反了唯一约束，但不应该发生 [Spring Boot，PostgreSQL]

问题

组织Java类以避免使用instanceOf

是使用Rust Diesel时是否可以更新限制为1。

Using org.springframework.data.jdbc.core.convert.SqlGenerator in my own package throw composition design

Java 二维数组的组件类型

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论