Problems with creating MySQL table with hibernate

Question

At first I try to create table with names USERS or USER, but google said me that it is a reserved words in MySQL and I try with QWERTY, the table is not created anyway.

Entity Class

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="QWERTY")
public abstract class User {
    @Id
    @GeneratedValue
    protected Long id;
    protected String username;
    protected String firstname;
    protected String secondname;
    protected String middlename;
    protected String password;

    @Type(type = "org.hibernate.type.NumericBooleanType")
    protected Boolean accountIsNotLocked;

    public User(){  
    }   

    // getters and setters...
}

Creating table

import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

import by.grsu.epam.domain.User;

public class TestUser {

    public static void main(String[] args) {
        AnnotationConfiguration config = new AnnotationConfiguration();
        config.addAnnotatedClass(User.class);
        config.configure();

        new SchemaExport(config).create(true, true);
    }

}

Console output

INFO : org.hibernate.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {4.0.2.Final}
INFO : org.hibernate.Version - HHH000412: Hibernate Core {4.2.7.Final}
INFO : org.hibernate.cfg.Environment - HHH000206: hibernate.properties not found
INFO : org.hibernate.cfg.Environment - HHH000021: Bytecode provider name : javassist
INFO : org.hibernate.cfg.Configuration - HHH000043: Configuring from resource: /hibernate.cfg.xml
INFO : org.hibernate.cfg.Configuration - HHH000040: Configuration resource: /hibernate.cfg.xml
INFO : org.hibernate.cfg.Configuration - HHH000041: Configured SessionFactory: null
INFO : org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.HSQLDialect
INFO : org.hibernate.tool.hbm2ddl.SchemaExport - HHH000227: Running hbm2ddl schema export
INFO : org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl - HHH000402: Using Hibernate built-in connection pool (not for production use!)
INFO : org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl - HHH000115: Hibernate connection pool size: 2
INFO : org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl - HHH000006: Autocommit mode: false
INFO : org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl - HHH000401: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/MySQL]
INFO : org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl - HHH000046: Connection properties: {user=root, password=****}

    drop table QWERTY if exists
ERROR: org.hibernate.tool.hbm2ddl.SchemaExport - HHH000389: Unsuccessful: drop table QWERTY if exists
ERROR: org.hibernate.tool.hbm2ddl.SchemaExport - 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 'if exists' at line 1

    create table QWERTY (
        id bigint generated by default as identity (start with 1),
        accountIsNotLocked integer,
        firstname varchar(255),
        middlename varchar(255),
        password varchar(255),
        secondname varchar(255),
        username varchar(255),
        primary key (id)
    )
ERROR: org.hibernate.tool.hbm2ddl.SchemaExport - HHH000389: Unsuccessful: create table QWERTY (id bigint generated by default as identity (start with 1), accountIsNotLocked integer, firstname varchar(255), middlename varchar(255), password varchar(255), secondname varchar(255), username varchar(255), primary key (id))
ERROR: org.hibernate.tool.hbm2ddl.SchemaExport - 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 'generated by default as identity (start with 1),
        accountIsNotLocked inte' at line 2
INFO : org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl - HHH000030: Cleaning up connection pool [jdbc:mysql://localhost:3306/MySQL]
INFO : org.hibernate.tool.hbm2ddl.SchemaExport - HHH000230: Schema export complete

Salandur · Accepted Answer · 2013-11-10 09:08:33Z

3

The logging shows that you are not setting the correct dialect (Using dialect: org.hibernate.dialect.HSQLDialect). So, you have to set the correct dialect: org.hibernate.dialect.MySqlDialect. Then Hibernate will generate the correct statements for MySQL.

answered Nov 10, 2013 at 9:08

Salandur

6,4732 gold badges24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jericho Over a year ago

Man you saved my time till SpringBoot has problem with this!

Sanj · Accepted Answer · 2013-11-10 08:50:46Z

0

Mysql doesnt have native boolean datatype - tinyint(1) is used as boolean. Annotate accountIsNotLocked as below

@Type(type = "org.hibernate.type.NumericBooleanType")
protected boolean accountIsNotLocked;

edited Nov 10, 2013 at 8:50

answered Nov 10, 2013 at 8:45

Sanj

4,0572 gold badges26 silver badges26 bronze badges

Collectives™ on Stack Overflow

Problems with creating MySQL table with hibernate

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related