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

Jack · Accepted Answer · 2012-09-07 02:15:59Z

14

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

edited Sep 7, 2012 at 2:15

Jack

11k13 gold badges52 silver badges65 bronze badges

answered Sep 7, 2012 at 2:04

behoph

1411 silver badge2 bronze badges

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

1 Comment

Ian Newland Over a year ago

I was using 'key' and 'value' as column names. Woops.

Bill Karwin · Accepted Answer · 2009-01-14 22:47:01Z

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.

Stepan Yakovenko · Accepted Answer · 2012-11-10 10:11:07Z

9

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.

answered Nov 10, 2012 at 10:11

Stepan Yakovenko

9,33631 gold badges127 silver badges214 bronze badges

1 Comment

kailash gaur Over a year ago

This was really a big hint.

Makatun · Accepted Answer · 2020-04-09 19:08:44Z

0

Make sure spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialecthas correct version of MySQL

answered Apr 9, 2020 at 19:08

Makatun

1,02711 silver badges12 bronze badges

Collectives™ on Stack Overflow

Hibernate generates invalid SQL query with MySQL

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related