Hibernate unsuccesfull create table, sql syntax error

Question

Im trying to create table using hibernate, but I get sql syntax error and I dont know why.

User

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

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToMany
    @JoinTable(name="user_role", joinColumns = @JoinColumn(name="user_id"),
    inverseJoinColumns = @JoinColumn(name="role_id)"))
    private Set<Role> roles;
    }

Role

@Entity
@Table(name="role")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    private String name;
    @ManyToMany(mappedBy="roles")
    private Set<User> users;
    }

application.properties

spring.datasource.url = jdbc:mysql://localhost/basket
spring.datasource.username = root
spring.datasource.password =
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = create
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000388: Unsuccessful: create table user_role (user_id bigint not null, role_id) bigint not null, primary key (user_id, role_id)))

org.hibernate.tool.hbm2ddl.SchemaUpdate : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') bigint not null, primary key (user_id, role_id)))' at line 1

org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000388: Unsuccessful: alter table user_role add constraint FK_75dvukqj0muwehqn7y8qi9j5c foreign key (role_id)) references role (id)

org.hibernate.tool.hbm2ddl.SchemaUpdate : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') references role (id)' at line 1

org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000388: Unsuccessful: alter table user_role add constraint FK_apcc8lxk2xnug8377fatvbn04 foreign key (user_id) references users (id)

org.hibernate.tool.hbm2ddl.SchemaUpdate : Table 'basket.user_role' doesn't exist

Im using MariaDB 10.1.16.

Your mappings look fine to me, seems the generated sql is strange. It could be an issue with dialect or something, though even then the syntax seems way off. For one thing it has more ending parenthesis than starting, and also they seem misplaced.. — Tobb
Yea, definitely parenthesis are a bit strange. It should be like: create table user_role (user_id bigint not null, role_id bigint not null, primary key (user_id, role_id)) It's like after every role id there is a one unnecessary parenthesis but i dont know where i made mistake and how to fix it. — crooked

Suresh A Suresh A · Accepted Answer · 2017-03-24T22:07:14

There is a problem on the line :

@JoinTable(name="user_role", joinColumns = @JoinColumn(name="user_id"),
inverseJoinColumns = @JoinColumn(name="role_id)"))

in your User class. Remove ')' after role_id. That's why you are getting sql syntax error.

And also you are not defining sets 'roles' and 'users' in both entities.

Change your declarations:

private Set<Role> roles;
private Set<User> users;

To

private Set<Role> roles=new HashSet<Role>();
private Set<User> users=new HashSet<User>();

Hibernate unsuccesfull create table, sql syntax error

1 Answers