java - 级联删除方法spring boot应用程序出错

Question

我正在尝试学习使用 spring 框架，所以我从头开始使用一个应用程序。现在，它应该是一个 REST API 应用程序，具有创建用户、显示所有用户、按 ID 显示用户等简单方法。

当我尝试删除群组中的用户时，我的应用程序中断并显示此错误:

2018-08-24 14:50:12.627  WARN 16804 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2018-08-24 14:50:12.627 ERROR 16804 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group group0_ where group0_.group_id=1' at line 1
2018-08-24 14:50:12.638  INFO 16804 --- [nio-8080-exec-2] o.h.e.internal.DefaultLoadEventListener  : HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet

和很长的堆栈跟踪(太大而无法包含在代码示例中)

hibernate 执行的查询是:

Hibernate: select user0_.user_id as user_id1_4_0_, user0_.is_active as is_activ2_4_0_, user0_.confirmation_token as confirma3_4_0_, user0_.date_created as date_cre4_4_0_, user0_.email_address as email_ad5_4_0_, user0_.first_name as first_na6_4_0_, user0_.last_name as last_nam7_4_0_, user0_.notification_type as notifica8_4_0_, user0_.password as password9_4_0_, user0_.phone_number as phone_n10_4_0_, user0_.username as usernam11_4_0_ from user user0_ where user0_.user_id=?
Hibernate: select groups0_.user_id as user_id2_5_0_, groups0_.group_id as group_id1_5_0_, groups0_.group_id as group_id1_5_1_, groups0_.user_id as user_id2_5_1_, groups0_.is_blocked as is_block3_5_1_, groups0_.user_type_id as user_typ4_5_1_ from user_to_group groups0_ where groups0_.user_id=?
Hibernate: select group0_.group_id as group_id1_0_0_, group0_.created_by as created_5_0_0_, group0_.date_created as date_cre2_0_0_, group0_.is_private as is_priva3_0_0_, group0_.name as name4_0_0_ from group group0_ where group0_.group_id=?

用户实体:

@Entity
@Table(name = "user")
public class User {

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

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "username")
    private String username;

    @Column(name = "email_address",
            unique = true)
    private String emailAddress;

    @Column(name = "phone_number",
            unique = true)
    private String phoneNumber;

    @Column(name = "password")
    private String password;

    @Column(name = "notification_type",
    insertable = false)
    private String notificationType;

    @Column(name = "date_created")
    private Date dateCreated;

    @Column(name = "confirmation_token",
            unique = true)
    private String confirmationToken;

    @Column(name = "is_active",
    insertable = false)
    private Boolean active;

    @OneToMany(
            mappedBy = "user",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<UserGroup> groups = new ArrayList<>();

    public User(String firstName, String lastName, String username, String emailAddress, String phoneNumber, String password) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.username = username;
        this.emailAddress = emailAddress;
        this.phoneNumber = phoneNumber;
        this.password = password;
        dateCreated = new Date();
    }

    public User() {
    }

    public void addGroup(Group group) {
        UserGroup userGroup = new UserGroup(this, group);
        groups.add(userGroup);
        group.getUsers().add(userGroup);
    }

    public void removeGroup(Group group) {
        for (Iterator<UserGroup> iterator = groups.iterator();
             iterator.hasNext(); ) {
            UserGroup userGroup = iterator.next();

            if (userGroup.getUser().equals(this) &&
                    userGroup.getGroup().equals(group)) {

                iterator.remove();
                userGroup.getGroup().getUsers().remove(userGroup);
                userGroup.setUser(null);
                userGroup.setGroup(null);
            }
        }
    }

    //Getters and setters

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return Objects.equals(firstName, user.firstName) &&
                Objects.equals(lastName, user.lastName);
    }

    @Override
    public int hashCode() {

        return Objects.hash(firstName, lastName);
    }
}

集团实体

@Entity
@Table(name = "group")
@NaturalIdCache
@Cache(
        usage = CacheConcurrencyStrategy.READ_WRITE
)
public class Group {

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

    @NaturalId
    private String name;

    @Column(name = "date_created")
    private Date dateCreated;

    @Column(name = "is_private")
    @GeneratedValue(generator = "0")
    private boolean isPrivate;

    @OneToOne(fetch = FetchType.LAZY,
        cascade = CascadeType.ALL)
    @JoinColumn(name = "created_by")
    private User createdBy;

    @OneToMany(
            mappedBy = "group",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<UserGroup> users = new ArrayList<>();

    public Group(String name) {
        this.name = name;
        this.dateCreated = new Date();
    }

    public Group() {
    }

    //Getters and setters

    @Override
    public boolean equals(Object o) {

        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Group group = (Group) o;
        return Objects.equals(name, group.name);
    }

    @Override
    public int hashCode() {

        return Objects.hash(name);
    }
}

用户组实体(连接表)是

@Entity
@Table(name = "user_to_group")
public class UserGroup {

    @EmbeddedId
    private UserGroupId id;

    @ManyToOne(fetch = FetchType.LAZY,
            cascade = CascadeType.ALL)
    @JoinColumn(name = "user_id", insertable = false, updatable = false)
    private User user;

    @ManyToOne(fetch = FetchType.LAZY,
            cascade = CascadeType.ALL)
    @JoinColumn(name = "group_id", insertable = false, updatable = false)
    private Group group;

    @ManyToOne(fetch = FetchType.LAZY,
            cascade = CascadeType.ALL)
    @JoinColumn(name = "user_type_id")
    private UserType userType;

    @Column(name = "is_blocked")
    @GeneratedValue(generator = "0")
    private boolean isBlocked;

    public UserGroup() {
    }

    public UserGroup(User user, Group group) {
        this.user = user;
        this.group = group;
        this.id = new UserGroupId(user.getId(), group.getId());
    }

    //Getters and setters
    @Override
    public boolean equals(Object o) {

        if (this == o) return true;

        if (o == null || getClass() != o.getClass()) return false;
        UserGroup userGroup = (UserGroup) o;
        return  Objects.equals(user, userGroup.user) &&
                Objects.equals(group, userGroup.group);
    }

    @Override
    public int hashCode() {

        return Objects.hash(user, group);
    }
}

UserGroupId(复合主键)是

@Embeddable
public class UserGroupId implements Serializable {

    @Column(name = "user_id")
    private Long userId;

    @Column(name = "group_id")
    private Long groupId;

    public UserGroupId() {
    }

    public UserGroupId(Long userId, Long groupId) {
        this.userId = userId;
        this.groupId = groupId;
    }

    //Getters and setters

    @Override
    public boolean equals(Object o) {

        if (this == o) return true;
        if (o == null || getClass() != o.getClass())
            return false;
        UserGroupId that = (UserGroupId) o;
        return Objects.equals(userId, that.userId) &&
                Objects.equals(groupId, that.userId);
    }

    @Override
    public int hashCode() {

        return Objects.hash(userId, groupId);
    }
}

数据库的DDL是:

USE chatapp;

CREATE TABLE user (
    user_id int NOT NULL AUTO_INCREMENT,
    confirmation_token varchar(255),
    first_name varchar(100) NOT NULL,
    last_name varchar(100) NOT NULL,
    username varchar(100) NOT NULL,
    email_address varchar(100) UNIQUE NOT NULL,
    phone_number varchar(100) NOT NULL,
    password varchar(255) NOT NULL,
    notification_type varchar(30) NOT NULL DEFAULT "email",
    date_created datetime NOT NULL,
    is_active bool NOT NULL DEFAULT false,
    CONSTRAINT user_pk PRIMARY KEY (user_id)
);

CREATE TABLE `group` (
    group_id int NOT NULL AUTO_INCREMENT,
    name varchar(100) NULL,
    date_created datetime NOT NULL,
    is_private bool NOT NULL DEFAULT false,
    created_by int NULL,
    CONSTRAINT group_pk PRIMARY KEY (group_id),
    CONSTRAINT group_user_fk FOREIGN KEY(created_by)
        REFERENCES user (user_id) ON DELETE SET NULL
);

CREATE TABLE privilege (
    id int NOT NULL,
    name varchar(50) NOT NULL,
    CONSTRAINT privilege_pk PRIMARY KEY (id)
);

CREATE TABLE user_type (
    id int NOT NULL AUTO_INCREMENT,
    type_name varchar(50) NOT NULL,
    CONSTRAINT user_type_pk PRIMARY KEY (id)
);

CREATE TABLE user_to_group (
    user_id int NOT NULL,
    group_id int NOT NULL,
    user_type_id int NOT NULL,
    is_blocked bool NOT NULL DEFAULT false,
    CONSTRAINT user_to_group_pk PRIMARY KEY (user_id,group_id),
    CONSTRAINT user_to_group_group_fk FOREIGN KEY(group_id)
        REFERENCES `group` (group_id),
    CONSTRAINT user_to_group_user_type_fk FOREIGN KEY(user_type_id)
        REFERENCES user_type (id),
    CONSTRAINT user_to_group_user_fk FOREIGN KEY(user_id)
        REFERENCES user (user_id)

);

CREATE TABLE user_type_to_privilege (
    user_type_id int NOT NULL,
    privilege_id int NOT NULL,
    CONSTRAINT user_type_to_privilege_pk PRIMARY KEY (user_type_id,privilege_id),
    CONSTRAINT user_type_to_privilege_privilege_fk FOREIGN KEY(privilege_id)
        REFERENCES privilege (id),
    CONSTRAINT user_type_to_privilege_user_type_fk FOREIGN KEY(user_type_id)
        REFERENCES user_type (id)
);

对于 CRUD 操作，我使用 spring 数据，我有一个存储库和一个用户服务，我使用默认的 deleteById 方法。

非常感谢任何形式的帮助!很抱歉发了这么长的帖子，但我试图向您展示我认为您需要的所有信息。如果您需要其他东西，请告诉我。

Answer 1

不幸的是，group 是一个保留的 SQL 关键字(例如 group by)。

考虑为您的 group 表指定另一个名称。