[FIXED] Problems with creating MySQL table with hibernate

Issue

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

Solution

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 By - Salandur
Answer Checked By - Willingham (JavaFixing Volunteer)