Hibernate query ends up in SQL syntax error

Question

I have created a UserPreferences bean. The primary key being id column. Here is the definition of the bean:

@Entity
public class UserPreferences {

    @Id
    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    @Column(unique = true)
    @ColumnDefault("")
    private String id;

    private String email;

    // Getters and Setters
}

Now I use Kotlin to insert data in this bean. Here is the code to do so:

val newUserPreferences = UserPreferences().apply { email = newUserRequest.email }
mUserPreferenceService.save(newUserPreferences)

Lets assume that newUserRequest.email is not null. The auto-dll setting in my application.yml is update. When this code is executed, I see that Hibernate generates following query for creating the table:

create table user_preferences (
   id varchar(255) default  not null
   email varchar(255),
   primary key (id)
) engine=MyISAM

At this point, the application fails to load with the following error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'not null,

Looking at the exception, it is very clear that the code is failing because instead of using an empty String as default value, hibernate is simply putting a blank space in the generated query. So here are my questions:

Should this be treated as a bug in Hibernate?
How to handle this situation where I would like to have an empty String "" as column default value?

Should there be a comma after `id varchar(255) default not null`? — Shreyas, Sep 02 '18 at 15:43
oh okk. because the error mentioned 'right syntax near not null', hence caught your attention. — Shreyas, Sep 02 '18 at 16:23

score 2 · Accepted Answer · answered Sep 02 '18 at 15:37

I'm not a Hibernate user, but I found https://github.com/hibernate/hibernate-orm/blob/master/documentation/src/test/java/org/hibernate/userguide/schema/ColumnDefaultTest.java shows:

@ColumnDefault("'N/A'")

It appears that when you define a default value for a SQL string type, you must use SQL string delimiters (single quotes) inside the Java string. In your case, since you want an empty string, you'd use:

@ColumnDefault("''")

The other way to define a default is to set an attribute in the @Column annotation, as shown in another Stack Overflow answer: How to set default value in Hibernate

So this may not be a bug, but it's a usage of Hibernate that is counter-intuitive for some users, and perhaps not documented clearly.

P.S.: I would count it as a bug that Hibernate still generates CREATE TABLE statements with engine=MyISAM unless you specify the right hibernate.dialect. That storage engine is not recommended. MyISAM is slower than InnoDB in most cases, and fails to support any ACID properties. MySQL has been phasing out MyISAM, and I don't recommend using it for any current projects.

Thanks Bill for you explanation. – Prashant Sep 02 '18 at 16:09 — Prashant, Sep 02 '18 at 16:09

Hibernate query ends up in SQL syntax error

1 Answers1