JAVA:org.hibernate.exception.SQLGrammarException: could not execute statement

Question

I tried the easy program at Hibernate and got a bunch of exception.

I could not find out what precisely is wrong.

I am having three classes - Book, Reader and Using. The last is binding the first two with dependency one to many.

Here is my main():

public class Appl {
    public static void main(String[] args) {
        Book book = new Book();
        book.setTitle("book01155");
        //
        Reader reader = new Reader();
        reader.setName("reader2");
        //
        Using using = new Using();
        using.setIdBook(book);
        using.setIdReader(reader);
        //
        List<Book> elements = new ArrayList<Book>();
        //
        Session session = null;     
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            session.beginTransaction();
            session.save(book);
            session.save(reader);
            session.save(using);
            elements = session.createCriteria(Book.class).list();
            session.getTransaction().commit();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        for (Book b : elements) {
            System.out.println("book: id=" + b.getIdBook() + " Title="
                    + b.getTitle());
        }
        System.out.println("\nThe END.\n");
    }
}

Here is the exception message:

ERROR: 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 'USING (IDBOOK, IDREADER) values (2, 2)' at line 1
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)

A snippet of hiberante.cfg.xml:

<hibernate-configuration>
    <session-factory>
        <property name="eclipse.connection.profile">097Hibernate</property>

        <property name="connection.url">jdbc:mysql://localhost/_097_Library</property>
        <property name="connection.username">root</property>
        <property name="connection.password">secret</property>

        <!-- property name="hbm2ddl.auto">create</property -->
        <property name="hbm2ddl.auto">update</property>

        <property name="connection.driver_class">
            com.mysql.jdbc.Driver
        </property>

        <property name="dialect">
            org.hibernate.dialect.MySQLDialect
        </property>

        <mapping class="com.softserve.edu.Book" />
        <mapping class="com.softserve.edu.Reader" />
        <mapping class="com.softserve.edu.Using" />
    </session-factory>
</hibernate-configuration>

All the tables at the database are created but are empty.

Where am I doing wrong?

Answer 1

In the MySQL, USING is a reserved word.

So simply rename the table by using @javax.persistence.Table annotation on your Using entity. Something like

@Entity
@Table(name = "TB_USING")
public class Using {
    ...
}

I thought you have a table for USING, however, you stated that it is a one-to-many relationship, thus you can omit the table, and model it using simply a single foreign key in Reader table.

Usually, hibernate doesn't force you to create a new entity for many-to-many join tables (It doesn't have any more attribute but the foreign keys). But I think that it is a great practice to have an entity for that relationship because most of the times some attributes will be defined for the relation in the future.

Are you interested to learn SQL? Sign up for this SQL Course to know more.

Answer 2

The issue you are facing arises from the inclusion of the keyword USING in your SQL statement. Since USING is a reserved word in SQL, utilizing it as a name during table creation or while executing particular operations can result in syntax errors.

Solution:

1. Change the Class Name: Modify the name of the Using class to something that is not a reserved keyword (for example, BookReader, BookUsage, etc.).

public class BookReader {  // change Using to BookReader }

    // your fields and methods

}

2. Update Your Code: Ensure that your main() method is adjusted to reference the new class name.

3. Verify Mappings: Make sure that your Hibernate mappings are updated to reflect the class name change.