Hibernate many-to-many syntax error in sql statement

Question

I have an issue with an hibernate many-to-many relation: when I want to read data from database SQL syntax error occurs. I found this in every tutorial so I guess it should work.

My classes in many-to-many relation:

User:

package cz.cvut.fit.bi_tjv.fiedlpe1.server.domain;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.OneToOne;
import javax.persistence.PrimaryKeyJoinColumn;

@Entity
public class User implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false)
    private String userName;

    @Column
    private String firstName;

    @Column
    private String lastName;

    @OneToOne
    @PrimaryKeyJoinColumn
    private Password password;

    @ManyToMany(cascade = CascadeType.ALL, mappedBy = "users")
    private List<Group> groups = new ArrayList();

    public User() {
    }

    public User(String userName, Password password) {
        this(userName, null, null, password);
    }

    public User(String userName, String firstName, String lastName, Password password) {
        this.userName = userName;
        this.lastName = lastName;
        this.firstName = firstName;
        this.password = password;
    }

    public String getFirstName() {
        return firstName;
    }

    public List<Group> getGroups() {
        return groups;
    }

    public String getLastName() {
        return lastName;
    }

    public Password getPassword() {
        return password;
    }

    public String getUserName() {
        return userName;
    }

    @Override
    public String toString() {
        return getFirstName() + " " + getLastName();
    }
}

Group:

package cz.cvut.fit.bi_tjv.fiedlpe1.server.domain;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;

@Entity
public class Group implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false)
    private String groupName;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name="user_groups",
            joinColumns=@JoinColumn(name="group_id", referencedColumnName = "id"),
            inverseJoinColumns=@JoinColumn(name="user_id"))
    private List<User> users = new ArrayList();

    public Group() {
    }

    public Group(String groupName) {
        this.groupName = groupName;
    }

    public String getGroupName() {
        return groupName;
    }

    public List<User> getUsers() {
        return users;
    }

    @Override
    public String toString() {
        return getGroupName();
    }
}

And my terminal output:

2016-12-13 08:29:08.088  INFO 10220 --- [nio-8080-exec-4] 
o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2016-12-13 08:29:08.233 DEBUG 10220 --- [nio-8080-exec-5] org.hibernate.SQL                        : select count(user0_.id) as col_0_0_ from user user0_
Hibernate: select count(user0_.id) as col_0_0_ from user user0_
2016-12-13 08:29:08.233 DEBUG 10220 --- [nio-8080-exec-4] org.hibernate.SQL                        : select count(user0_.id) as col_0_0_ from user user0_
Hibernate: select count(user0_.id) as col_0_0_ from user user0_
2016-12-13 08:29:08.238 DEBUG 10220 --- [nio-8080-exec-4] org.hibernate.SQL                        : select user0_.id as id1_3_, user0_.first_name as first_na2_3_, user0_.last_name as last_nam3_3_, user0_.user_name as user_nam4_3_ from user user0_ limit ?
Hibernate: select user0_.id as id1_3_, user0_.first_name as first_na2_3_, user0_.last_name as last_nam3_3_, user0_.user_name as user_nam4_3_ from user user0_ limit ?
2016-12-13 08:29:08.238 DEBUG 10220 --- [nio-8080-exec-5] org.hibernate.SQL                        : select user0_.id as id1_3_, user0_.first_name as first_na2_3_, user0_.last_name as last_nam3_3_, user0_.user_name as user_nam4_3_ from user user0_ limit ?
Hibernate: select user0_.id as id1_3_, user0_.first_name as first_na2_3_, user0_.last_name as last_nam3_3_, user0_.user_name as user_nam4_3_ from user user0_ limit ?
2016-12-13 08:29:08.247 DEBUG 10220 --- [nio-8080-exec-4] org.hibernate.SQL                        : select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
Hibernate: select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
2016-12-13 08:29:08.247 DEBUG 10220 --- [nio-8080-exec-5] org.hibernate.SQL                        : select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
Hibernate: select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
2016-12-13 08:29:08.257 DEBUG 10220 --- [nio-8080-exec-5] org.hibernate.SQL                        : select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
Hibernate: select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
2016-12-13 08:29:08.257 DEBUG 10220 --- [nio-8080-exec-4] org.hibernate.SQL                        : select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
Hibernate: select password0_.id as id1_1_0_, password0_.hash as hash2_1_0_, password0_.hash_function as hash_fun3_1_0_, password0_.salt as salt4_1_0_, password0_.user_id as user_id5_1_0_, user1_.id as id1_3_1_, user1_.first_name as first_na2_3_1_, user1_.last_name as last_nam3_3_1_, user1_.user_name as user_nam4_3_1_ from password password0_ left outer join user user1_ on password0_.user_id=user1_.id where password0_.id=?
2016-12-13 08:29:08.334 DEBUG 10220 --- [nio-8080-exec-4] org.hibernate.SQL                        : select groups0_.user_id as user_id2_4_0_, groups0_.group_id as group_id1_4_0_, group1_.id as id1_0_1_, group1_.group_name as group_na2_0_1_ from user_groups groups0_ inner join group group1_ on groups0_.group_id=group1_.id where groups0_.user_id=?
Hibernate: select groups0_.user_id as user_id2_4_0_, groups0_.group_id as group_id1_4_0_, group1_.id as id1_0_1_, group1_.group_name as group_na2_0_1_ from user_groups groups0_ inner join group group1_ on groups0_.group_id=group1_.id where groups0_.user_id=?
2016-12-13 08:29:08.334 DEBUG 10220 --- [nio-8080-exec-5] org.hibernate.SQL                        : select groups0_.user_id as user_id2_4_0_, groups0_.group_id as group_id1_4_0_, group1_.id as id1_0_1_, group1_.group_name as group_na2_0_1_ from user_groups groups0_ inner join group group1_ on groups0_.group_id=group1_.id where groups0_.user_id=?
Hibernate: select groups0_.user_id as user_id2_4_0_, groups0_.group_id as group_id1_4_0_, group1_.id as id1_0_1_, group1_.group_name as group_na2_0_1_ from user_groups groups0_ inner join group group1_ on groups0_.group_id=group1_.id where groups0_.user_id=?
2016-12-13 08:29:08.335  WARN 10220 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42001, SQLState: 42001
2016-12-13 08:29:08.335  WARN 10220 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42001, SQLState: 42001
2016-12-13 08:29:08.335 ERROR 10220 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : Chyba syntaxe v SQL příkazu "SELECT GROUPS0_.USER_ID AS USER_ID2_4_0_, GROUPS0_.GROUP_ID AS GROUP_ID1_4_0_, GROUP1_.ID AS ID1_0_1_, GROUP1_.GROUP_NAME AS GROUP_NA2_0_1_ FROM USER_GROUPS GROUPS0_ INNER JOIN GROUP[*] GROUP1_ ON GROUPS0_.GROUP_ID=GROUP1_.ID WHERE GROUPS0_.USER_ID=? "; očekáváno "identifier"
Syntax error in SQL statement "SELECT GROUPS0_.USER_ID AS USER_ID2_4_0_, GROUPS0_.GROUP_ID AS GROUP_ID1_4_0_, GROUP1_.ID AS ID1_0_1_, GROUP1_.GROUP_NAME AS GROUP_NA2_0_1_ FROM USER_GROUPS GROUPS0_ INNER JOIN GROUP[*] GROUP1_ ON GROUPS0_.GROUP_ID=GROUP1_.ID WHERE GROUPS0_.USER_ID=? "; expected "identifier"; SQL statement:
select groups0_.user_id as user_id2_4_0_, groups0_.group_id as group_id1_4_0_, group1_.id as id1_0_1_, group1_.group_name as group_na2_0_1_ from user_groups groups0_ inner join group group1_ on groups0_.group_id=group1_.id where groups0_.user_id=? [42001-193]
2016-12-13 08:29:08.335 ERROR 10220 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : Chyba syntaxe v SQL příkazu "SELECT GROUPS0_.USER_ID AS USER_ID2_4_0_, GROUPS0_.GROUP_ID AS GROUP_ID1_4_0_, GROUP1_.ID AS ID1_0_1_, GROUP1_.GROUP_NAME AS GROUP_NA2_0_1_ FROM USER_GROUPS GROUPS0_ INNER JOIN GROUP[*] GROUP1_ ON GROUPS0_.GROUP_ID=GROUP1_.ID WHERE GROUPS0_.USER_ID=? "; očekáváno "identifier"
Syntax error in SQL statement "SELECT GROUPS0_.USER_ID AS USER_ID2_4_0_, GROUPS0_.GROUP_ID AS GROUP_ID1_4_0_, GROUP1_.ID AS ID1_0_1_, GROUP1_.GROUP_NAME AS GROUP_NA2_0_1_ FROM USER_GROUPS GROUPS0_ INNER JOIN GROUP[*] GROUP1_ ON GROUPS0_.GROUP_ID=GROUP1_.ID WHERE GROUPS0_.USER_ID=? "; expected "identifier"; SQL statement:
select groups0_.user_id as user_id2_4_0_, groups0_.group_id as group_id1_4_0_, group1_.id as id1_0_1_, group1_.group_name as group_na2_0_1_ from user_groups groups0_ inner join group group1_ on groups0_.group_id=group1_.id where groups0_.user_id=? [42001-193]
2016-12-13 08:29:08.341  WARN 10220 --- [nio-8080-exec-4] .w.s.m.s.DefaultHandlerExceptionResolver : Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException: Could not write content: could not prepare statement (through reference chain: org.springframework.data.domain.PageImpl["content"]->java.util.Collections$UnmodifiableRandomAccessList[0]->cz.cvut.fit.bi_tjv.fiedlpe1.server.domain.User["groups"]); nested exception is com.fasterxml.jackson.databind.JsonMappingException: could not prepare statement (through reference chain: org.springframework.data.domain.PageImpl["content"]->java.util.Collections$UnmodifiableRandomAccessList[0]->cz.cvut.fit.bi_tjv.fiedlpe1.server.domain.User["groups"])
2016-12-13 08:29:08.341  WARN 10220 --- [nio-8080-exec-5] .w.s.m.s.DefaultHandlerExceptionResolver : Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException: Could not write content: could not prepare statement (through reference chain: org.springframework.data.domain.PageImpl["content"]->java.util.Collections$UnmodifiableRandomAccessList[0]->cz.cvut.fit.bi_tjv.fiedlpe1.server.domain.User["groups"]); nested exception is com.fasterxml.jackson.databind.JsonMappingException: could not prepare statement (through reference chain: org.springframework.data.domain.PageImpl["content"]->java.util.Collections$UnmodifiableRandomAccessList[0]->cz.cvut.fit.bi_tjv.fiedlpe1.server.domain.User["groups"])

Answer 1

You don't have a problem with many-to-many, your problem is the fact that group is reserved keyword in sql. Try to rename your table and entity class to groups or something..