Hibernate generates invalid SQL query with MySQL

Question

I have the following JPA entity classes (example case). A House belongs on a single Street. A Street has many Houses.

@Entity
public class House {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    public Integer id;

    public String name    

    @ManyToOne
    public Street street;
}

@Entity
public class Street {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    public Integer id;

    @OneToMany(mappedBy="street")
    public Set<House> houses;
}

I have the generation type set to identity, which is supposed to auto assign a new ID.

When creating a new House with a new Street, I have to first create and persist Street, followed by House. This is because I do not have CascadeType set to PERSIST, so it has to be done manually [1]. However, while inserting a newly created Street:

Street street = new Street();
entityManager.persist(street);

Hibernate/JPA generates the following SQL query:

insert into Street default values

which MySQL doesn't like.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'default values' at line 1 

Any ideas why? I'm using Java 6, MySQL 5.0.67 with Hibernate's implementation of JPA (version 3.2.1.ga).

[1] EJB 3 in Action pages 318-319

Answer 1

Another situation when you might have this exception is when you have reserved words as columns for the table. For example 'to' and 'from' are not suitable clumn names for MySQL. It is obviously bug in Hibernate, that it doesn't check such columns and moreover continues to work with no error even if table is not created.

Answer 2

If you don't have an error in your SQL statement, then you might want to check your table column name as it can contain a predefined name that Mysql uses; for example 'column' which can't be used as a table column name

Answer 3

Standard SQL specifies this optional syntax for INSERT:

INSERT INTO <Table Name> DEFAULT VALUES

This is legal SQL syntax, supported, for example, by Microsoft SQL Server, PostgreSQL, and SQLite, but not by Oracle, IBM DB2, or MySQL.

MySQL supports other syntax that achieve the same result:

INSERT INTO <Table Name> () VALUES ()

INSERT INTO <Table Name> (col1, col2, col3) VALUES (DEFAULT, DEFAULT, DEFAULT)

In Hibernate, you should configure the SQL dialect properly, and it's up to Hibernate to generate valid SQL for the target RDBMS brand.

import org.hibernate.cfg.Configuration;

Configuration cfg = new Configuration();
cfg.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLInnoDBDialect");

You can also specify properties by placing a file named hibernate.properties in a root directory of the classpath.