org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Question

I have add supporting jars and jdbc drivers to my project but still i am getting below exception

getting could not find resultset exception Error performing load command :getting sqlgrammerexception

my configuration file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">1234</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

        <mapping class="com.util.Product"/>
    </session-factory>
</hibernate-configuration
entity class
@Entity
@Table(name="productdetails")
public class Product implements Serializable {

    @Id
    @Column(name="product-id")
    private int id;
    @Column(name="product-name")
    private String name;
    @Column(name="product-description")
    private String description;
    @Column(name="product-price")
    private float price;
    public Product() {
        super();
    }

    public Product(int id, String name, String description, float price) {
        super();
        this.id = id;
        this.name = name;
        this.description = description;
        this.price = price;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }

}

public class Executingclass {
    public static void  main(String args[]) {
        SessionFactory  sessionFactory=Hibernateutil.getSessionFactory();
        Session session=sessionFactory.openSession();
        Product product=(Product)session.get(Product.class, 1);
        System.out.println(product.getId());
        System.out.println(product.getName());
        System.out.println(product.getDescription());
        System.out.println(product.getPrice());


    }

}

public class Hibernateutil {
    static SessionFactory sessionFactory=null;
    static{
        Configuration configuration=new Configuration();
        configuration.configure();

        ServiceRegistry serviceRegistry=new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
        sessionFactory=configuration.buildSessionFactory(serviceRegistry);
    }

    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
}

Error:

INFO: HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:449)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:202)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:137)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:102)
    at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:186)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4126)
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:503)
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:468)
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:213)
    at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:275)
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:151)
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1106)
    at org.hibernate.internal.SessionImpl.access$2000(SessionImpl.java:176)
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2587)
    at org.hibernate.internal.SessionImpl.get(SessionImpl.java:991)
    at com.util.Executingclass.main(Executingclass.java:10)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'product0_.product' in 'field list'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
    ... 16 more

please help me in addressing this issue I am new to hibernate

Answer 1

I Used the following properties in my application.properties file. We need to tell spring boot our implicit naming strategy. hence used the following properties.

Also checked whether you are using CrudRepository or JPARepository as my solution works on JPA

spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl

and

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

earlier was getting an error

There was an unexpected error (type=Internal Server Error, status=500).
could not extract ResultSet; SQL [n/a]; nested exception is 
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:254)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

Answer 2

In the stacktrace, there is a line that points towards the problem:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'product0_.product' in 'field list'

If you see unknown column, the first thing that comes to my mind is that there is some error in the mapping of the fields in the class.

@Id
@Column(name="product-id")
private int id;
@Column(name="product-name")
private String name;
@Column(name="product-description")
private String description;
@Column(name="product-price")
private float price;

And when you see this together with the output in the stacktrace i mentioned above, you think, well, there is not a product0_.product column name, the closest thing is just product, followed by a hyphen, maybe its the hyphen what it gives some troubles.

After googling a bit, i found this answer from another question, that points that using hyphens is possible, but it has a special requirement, perhaps hibernate is not dealing with this correctly.

So to summarize, try without hyphens in the schema, in both places, the database, and the @Entity.