SQL Exception while saving to database - Spring MySQL Auto Generated Id-CodePudding

I have created a table. I want its id to increment automatically with every record. So, i selected id as Auto Incremental while creating table in MySQL.

Here my table is:

But it gives error while saving to database. The error is : "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"

My entity class:

@Entity
@Data
@Table(name="groups")
public class GroupsEntity {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "GROUP_ID")
private int groupId;

@Column(name = "GROUP_NAME")
private String groupName;

@Column(name = "GROUP_ICON")
private String groupIcon;

@Column(name = "USER_ID")
private int userId;
}

My Service class:

    public GroupsResponse setGroups(GroupsRequest request){
    int userId = request.getUserId();
    GroupsEntity groupsEntity = new GroupsEntity();
    groupsEntity.setUserId(userId);
    groupsEntity.setGroupIcon(request.getGroupIcon());
    groupsEntity.setGroupName(request.getGroupName());
    GroupsResponse response = new GroupsResponse();
    try{
        groupsRepository.save(groupsEntity);
    } catch (Exception e){
        log.error("There is an error while saving groups to Groups table. userId: "   userId);
        response.setStatus(false);
        response.setMessage(e.getMessage());
        return response;

    }
    response.setStatus(true);
    response.setMessage("SUCCESS");
    return response;
}

My repository class:

@Repository
public interface GroupsRepository extends JpaRepository<GroupsEntity, Integer> {

List<GroupsEntity> findByUserId(int userId);
}

And my request is below:

{
    "groupName": "abc",
    "groupIcon": "",
    "userId": 123
}

Auto generation for id is working when i insert new rows with insert into query manually. But not working with Spring.

It says "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 'groups (group_icon, group_name, user_id) values (' ', 'abc', 123)" in logs.

How can i fix this? Thank you.

EDIT:

I have solved the problem.

There was a warning in logs: "SQL Error: 1064, SQLState: 42000". That means column names or table name may be reserved words in MySQL.

My table's name was "groups" which is a reserved word in MySQL too. When i changed the name as "groups_table", it worked.

CodePudding user response：

this code will auto generate the id .

@Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

but then no need to set groupId groupsEntity.setGroupId(1);

CodePudding user response：

I have solved the problem.

There was a warning in logs: "SQL Error: 1064, SQLState: 42000". That means column names or table name may be reserved words in MySQL.

My table's name was "groups" which is a reserved word in MySQL too. When i changed the name as "groups_table", it worked.