JPA Exception: org.hibernate.exception.SQLGrammarException

Question

I'm developing a Spring MVC application with MySQL DB. Two of the tables in the database have a one to many relationship.

One of this tables is Quiz

@Entity
@Table(name="Quiz", schema = "QuizOwo")
public class QuizEntity
{
   @Id
   @GeneratedValue(strategy= GenerationType.IDENTITY)
   @Column(name = "idQuiz")
    private int idQuiz;

   @Column(name = "nameQuiz")
    private String nameQuiz;

   @Column(name = "descriptionQuiz")
    private String descriptionQuiz;

   @ManyToOne(optional=false)
   @JoinColumn(name = "idCategory")
    private CategoryQuizEntity categoryQuizEntity;

   @Column(name = "idPortal")
    private int idPortal;

The other table is

@Entity
@Table(name="CategoryQuiz", schema = "QuizOwo")
public class CategoryQuizEntity
{
   @Id
   @GeneratedValue(strategy= GenerationType.IDENTITY)
   @Column(name = "idCategoryQuiz")
    private int idCategoryQuiz;

   @Column(name = "nameCategory")
    private String nameCategory;

   @OneToMany(mappedBy="categoryQuizEntity", targetEntity=QuizEntity.class, fetch=FetchType.LAZY)
   @LazyCollection(LazyCollectionOption.FALSE)
     private List<QuizEntity> quizEntities;

My problem is that when a SQL statement is released (using hibernate JPA) the org.hibernate.exception.SQLGrammarException is thrown with this information.

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

¿Why?

---EDIT---

My mysql configuration is

@Configuration
@EnableTransactionManagement
public class PersistenceConfig
{
    /** Bean para obtener el dataSource */
    @Bean(name = "dataSource")
    public DataSource getDataSource(){
       DataSource dataSource = createDataSource();
       return dataSource;
     }

    /** Generador del datasource */
    private DriverManagerDataSource createDataSource() {
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setDriverClassName(ConfigurationManager.getProperty(ConfigurationManager.DATABASE_DIVERCLASSNAME));
        driverManagerDataSource.setUrl(ConfigurationManager.getProperty(ConfigurationManager.DATABASE_URL));
        driverManagerDataSource.setUsername(ConfigurationManager.getProperty(ConfigurationManager.DATABASE_USERNAME));
        driverManagerDataSource.setPassword(ConfigurationManager.getProperty(ConfigurationManager.DATABASE_PASSWORD));
        driverManagerDataSource.setConnectionProperties(databaseProperties());
        return driverManagerDataSource;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }


    /** Generamos un entityManagerFactory con lo paquetes donde tiene que escanear para buscar a las entidades, el datasource
 * las propiedades de hibernate... */
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws ClassNotFoundException, PropertyVetoException {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(createDataSource());
        emf.setPackagesToScan(new String[] { "com.owo.quiz.models" });
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        emf.setJpaVendorAdapter(vendorAdapter);
        emf.setJpaProperties(hibernateProperties());
        return emf;
    }

    /** Propiedades para hibernate/JPA */
    Properties hibernateProperties() {
        return new Properties() {
            {
                setProperty("hibernate.dialect", ConfigurationManager.getProperty(ConfigurationManager.HIBERNATE_DIALECT));
                setProperty("hibernate.globally_quoted_identifiers", "true");
            }
        };
    }

    /** Propiedades para la conexión con la base de datos */
    Properties databaseProperties() {
        return new Properties() {
            {
                setProperty("characterEncoding", "iso8859-1");
            }
        };
    }
}

=== EDIT ====

The SQL INSERT Quiz Table is

CREATE TABLE Quiz ( idQuiz int(11) NOT NULL AUTO_INCREMENT, nameQuiz varchar(45) COLLATE latin1_spanish_ci DEFAULT NULL, descriptionQuiz text COLLATE latin1_spanish_ci, idCategory int(11) DEFAULT NULL, idPortal int(11) DEFAULT NULL, PRIMARY KEY (idQuiz), KEY fk_Quiz_1_idx (idCategory), CONSTRAINT fk_Quiz_1 FOREIGN KEY (idCategory) REFERENCES CategoryQuiz (idCategoryQuiz) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

can you share the sql create statement of table Quiz

Rafik BELDI
– Rafik BELDI

2015-11-24 12:58:58 +00:00
Commented Nov 24, 2015 at 12:58 — Rafik BELDI
– Rafik BELDI, Commented Nov 24, 2015 at 12:58
see the edit question, please

vjsantojaca
– vjsantojaca

2015-11-24 13:43:16 +00:00
Commented Nov 24, 2015 at 13:43 — vjsantojaca
– vjsantojaca, Commented Nov 24, 2015 at 13:43

vjsantojaca · Accepted Answer · 2015-11-24 15:13:52Z

1

I reset all MySQL permissions for this user and it worked correctly.

answered Nov 24, 2015 at 15:13

vjsantojaca

3252 silver badges16 bronze badges

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

1 Comment

Alkanshel Over a year ago

...are you sure the mysql user permissions were the real problem? It makes no sense that that would have anything to do with the error message that you provided.

JATIN AGGARWAL · Accepted Answer · 2020-07-21 05:41:38Z

1

If you are using spring , spring boot , spring data , JPA and Hibernate, try using below property:

spring.jpa.properties.hibernate.globally_quoted_identifiers=true

If you are using hibernate only :

hibernate.globally_quoted_identifiers=true

answered Jul 21, 2020 at 5:41

JATIN AGGARWAL

111 bronze badge

Comments

Nikolaj Hansen · Accepted Answer · 2015-11-24 12:43:47Z

0

Try to set the MySQL dialect as a part of your connection properties.

hibernate.dialect=org.hibernate.dialect.MySQLDialect

answered Nov 24, 2015 at 12:43

Nikolaj Hansen

2531 silver badge7 bronze badges

3 Comments

Nikolaj Hansen Over a year ago

maybe try to obtain the sql to see, what JPA produces: SET GLOBAL general_log_file = '/var/log/mysql/mysql.log'; SET GLOBAL general_log = 'ON'. In mysql as admin. This puts quite a bit of load on the system.

vjsantojaca Over a year ago

/var/log/mysql/mysql.log is empty

vjsantojaca Over a year ago

Sorry this's the mysql.log

select quizentity0_.idQuiz as idQuiz1_4_0_, quizentity0_.idCategory as idCatego5_4_0_, quizentity0_.descriptionQuiz as descript2_4_0_, quizentity0_.idPortal as idPortal3_4_0_, quizentity0_.nameQuiz as nameQuiz4_4_0_, categoryqu1_.idCategoryQuiz as idCatego1_2_1_, categoryqu1_.nameCategory as nameCate2_2_1_  from QuizOwo.Quiz quizentity0_  inner join QuizOwo.CategoryQuiz categoryqu1_  on quizentity0_.idCategory=categoryqu1_.idCategoryQuiz where quizentity0_.idQuiz=2

And if I run this in mysql console, the result is correct.

Collectives™ on Stack Overflow

JPA Exception: org.hibernate.exception.SQLGrammarException

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related