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

What is the contents of your Hibernate config? Have you set the SQL dialect to MySQL?

Answer 2

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.

Answer 3

I am facing the same problem --

"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 "

I have also set the contents of configuration file to use with MySQL but no progress.