could not extract ResultSet in hibernate

Java Problem Overview

I have a problem with Hibernate. I try to parse to List but It throws an exception: HTTP Status 500 - could not extract ResultSet. When I debug, It fault at line query.list()...

My sample code here

@Entity
@Table(name = "catalog")
public class Catalog implements Serializable {

@Id
@Column(name="ID_CATALOG")
@GeneratedValue	
private Integer idCatalog;

@Column(name="Catalog_Name")
private String catalogName;

@OneToMany(mappedBy="catalog", fetch = FetchType.LAZY)
private Set<Product> products = new HashSet<Product>(0);

//getter & setter & constructor
//...
}


@Entity
@Table(name = "product")
public class Product implements Serializable {

@Id
@Column(name="id_product")
@GeneratedValue	
private Integer idProduct;

@ManyToOne
@JoinColumn(name="ID_CATALOG")
private Catalog catalog;

@Column(name="product_name")
private String productName;

@Column(name="date")
private Date date;

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

@Column(name="price")
private Integer price;

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

//getter & setter & constructor
}



@Repository
@SuppressWarnings({"unchecked", "rawtypes"})
public class ProductDAOImpl implements ProductDAO {
	@Autowired
	private SessionFactory sessionFactory;
public List<Product> searchProductByCatalog(String catalogid, String keyword) {
	String sql = "select p from Product p where 1 = 1";
	Session session = sessionFactory.getCurrentSession();

	if (keyword.trim().equals("") == false) {
		sql += " and p.productName like '%" + keyword + "%'";
	}
	if (catalogid.trim().equals("-1") == false
			&& catalogid.trim().equals("") == false) {
		sql += " and p.catalog.idCatalog = " + Integer.parseInt(catalogid);
	}
	Query query = session.createQuery(sql);
	List listProduct = query.list();
	return listProduct;
}

}

My beans

  <!-- Scan classpath for annotations (eg: @Service, @Repository etc) -->
  <context:component-scan base-package="com.shopmvc"/>
   
  <!-- JDBC Data Source. It is assumed you have MySQL running on localhost port 3306 with 
       username root and blank password. Change below if it's not the case -->
  <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/shoesshopdb?autoReconnect=true"/>
    <property name="username" value="root"/>
    <property name="password" value="12345"/>
    <property name="validationQuery" value="SELECT 1"/>
  </bean>
   
  <!-- Hibernate Session Factory -->
  <bean id="mySessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="myDataSource"/>
    <property name="packagesToScan">
      <array>
        <value>com.shopmvc.pojo</value>
      </array>
    </property>
    <property name="hibernateProperties">
      <value>
        hibernate.dialect=org.hibernate.dialect.MySQLDialect
      </value>
    </property>
  </bean>
   
  <!-- Hibernate Transaction Manager -->
  <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="mySessionFactory"/>
  </bean>
   
  <!-- Activates annotation based transaction management -->
  <tx:annotation-driven transaction-manager="transactionManager"/>

Exception:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:948)
	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

root cause 

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
	org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
	org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
	org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
	org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61)
	org.hibernate.loader.Loader.getResultSet(Loader.java:2036)

root cause 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'product0_.ID_CATALOG' in 'field list'
	sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	java.lang.reflect.Constructor.newInstance(Unknown Source)
	com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	com.mysql.jdbc.Util.getInstance(Util.java:386)
	com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
	com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
	com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
	com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
	com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
	com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
	com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
	org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
	org.hibernate.loader.Loader.getResultSet(Loader.java:2036)
	org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836)
	org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1815)
	org.hibernate.loader.Loader.doQuery(Loader.java:899)
	org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
	org.hibernate.loader.Loader.doList(Loader.java:2522)
	org.hibernate.loader.Loader.doList(Loader.java:2508)
	org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2338)
	org.hibernate.loader.Loader.list(Loader.java:2333)
	org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)

My Database:

CREATE TABLE `catalog` (
  `ID_CATALOG` int(11) NOT NULL AUTO_INCREMENT,
  `Catalog_Name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID_CATALOG`)
)

CREATE TABLE `product` (
  `id_product` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(45) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `author` varchar(45) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `catalog_id` int(11) DEFAULT NULL,
  `linkimage` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id_product`),
  KEY `FK_Product_idx` (`catalog_id`),
  CONSTRAINT `FK_Product` FOREIGN KEY (`catalog_id`) REFERENCES `catalog` (`ID_CATALOG`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

Java Solutions

Solution 1 - Java

The @JoinColumn annotation specifies the name of the column being used as the foreign key on the targeted entity.

On the Product class above, the name of the join column is set to ID_CATALOG.

@ManyToOne
@JoinColumn(name="ID_CATALOG")
private Catalog catalog;

However, the foreign key on the Product table is called catalog_id

`catalog_id` int(11) DEFAULT NULL,

You'll need to change either the column name on the table or the name you're using in the @JoinColumn so that they match. See http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html/entity.html#entity-mapping-association

Solution 2 - Java

Another potential cause, for other people coming across the same error message is that this error will occur if you are accessing a table in a different schema from the one you have authenticated with.

In this case you would need to add the schema name to your entity entry:

@Table(name= "catalog", schema = "targetSchemaName")

Solution 3 - Java

I had the same issue, when I tried to update a row:

@Query(value = "UPDATE data SET value = 'asdf'", nativeQuery = true)
void setValue();

My Problem was that I forgot to add the @Modifying annotation:

@Modifying    
@Query(value = "UPDATE data SET value = 'asdf'", nativeQuery = true)
void setValue();

Solution 4 - Java

If you don't have 'HIBERNATE_SEQUENCE' sequence created in database (if use oracle or any sequence based database), you shall get same type of error;

Ensure the sequence is present there;

Solution 5 - Java

I Used the following properties in my application.properties file and the issue got resolved

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)

Solution 6 - Java

Try using inner join in your Query

    Query query=session.createQuery("from Product as p INNER JOIN p.catalog as c 
    WHERE c.idCatalog= :id and p.productName like :XXX");
    query.setParameter("id", 7);
    query.setParameter("xxx", "%"+abc+"%");
    List list = query.list();

also in the hibernate config file have

<!--hibernate.cfg.xml -->
<property name="show_sql">true</property>

To display what is being queried on the console.

Solution 7 - Java

I had similar issue. Try use the HQL editor. It will display you the SQL (as you have a SQL grammar exception). Copy your SQL and execute it separately. In my case the problem was in schema definition. I defined the schema, but I should leave it empty. This raised the same exception as you got. And the error description reflected the actual state, as the schema name was included in SQL statement.

Solution 8 - Java

I faced the same problem after migrating a database from online server to localhost. The schema changed so I had to define the schema manually for each table:

@Entity
@Table(name = "ESBCORE_DOMAIN", schema = "SYS")

Solution 9 - Java

I was using Spring Data JPA with PostgreSql and during UPDATE call it was showing errors-

'could not extract ResultSet' and another one.
org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query. (Showing Transactional required.)

Actually, I was missing two required Annotations.

@Transactional and
@Modifying

With-

@Query(vlaue = " UPDATE DB.TABLE SET Col1 = ?1 WHERE id = ?2 ", nativeQuery = true)
void updateCol1(String value, long id);

Solution 10 - Java

For MySql take in mind that it's not a good idea to write camelcase. For example if the schema is like that:

CREATE TABLE IF NOT EXISTS `task`(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `teaching_hours` DECIMAL(5,2) DEFAULT NULL,
    `isActive` BOOLEAN DEFAULT FALSE,
    `is_validated` BOOLEAN DEFAULT FALSE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You must be very careful cause isActive column will translate to isactive. So in your Entity class is should be like this:

 @Basic
 @Column(name = "isactive", nullable = true)
 public boolean isActive() {
     return isActive;
 }
 public void setActive(boolean active) {
     isActive = active;
 }

That was my problem at least that got me your error

This has nothing to do with MySql which is case insensitive, but rather is a naming strategy that spring will use to translate your tables. For more refer to this post

Solution 11 - Java

Another solution is add @JsonIgnore :

@OneToMany(mappedBy="catalog", fetch = FetchType.LAZY)
@JsonIgnore
private Set<Product> products = new HashSet<Product>(0);

Solution 12 - Java

This message also appears if you by mistake use a reserved keyword for your table (https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-C). When doing a join on a table which has the name the sama as the reserved mysql keyword you would get the same message, although everything else is correct. Hopefully this will spare some time and pain for other people.

Solution 13 - Java

I've reproduced similar issue:

Caused by: 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:279)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)

...

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'setting0_.advance_payment_first_text' in 'field list'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:975)

And in my specific use case it was because of the conflict between new dump which was used for database and liquibase configuration files data.xml with logic like:

<changeSet  author="liquibase-docs"  id="sqlFile-data">
        <sqlFile  dbms="!h2, oracle, mysql"
                  encoding="UTF-8"
                  path="..\..\..\sql\data.sql"
                  relativeToChangelogFile="true"
                  splitStatements="true"
                  stripComments="true"/>
</changeSet>

and structure.xml:

<changeSet  author="liquibase-docs"  id="sqlFile-structure">
        <sqlFile  dbms="!h2, oracle, mysql"
                  encoding="UTF-8"
                  path="..\..\..\sql\structure.sql"
                  relativeToChangelogFile="true"
                  splitStatements="true"
                  stripComments="true"/>
</changeSet>

for old scripts of the project.

After some investigations by checking the correspondence of tables with columns in the code I understood these old scripts overwrote after running the project new dump.

So after removing old scripts data.sql and structure.sql with configuration files data.xml and structure.xml from the project this problem was solved.

Content Type	Original Author	Original Content on Stackoverflow
Question	Thanh Duy Ngo	View Question on Stackoverflow
Solution 1 - Java	Will Keeling	View Answer on Stackoverflow
Solution 2 - Java	sweetfa	View Answer on Stackoverflow
Solution 3 - Java	Manuel Schmitzberger	View Answer on Stackoverflow
Solution 4 - Java	Mohammad Badiuzzaman	View Answer on Stackoverflow
Solution 5 - Java	Saurabh Verma	View Answer on Stackoverflow
Solution 6 - Java	Lakshmi	View Answer on Stackoverflow
Solution 7 - Java	Gico	View Answer on Stackoverflow
Solution 8 - Java	thadian	View Answer on Stackoverflow
Solution 9 - Java	iamfnizami	View Answer on Stackoverflow
Solution 10 - Java	Panagiss	View Answer on Stackoverflow
Solution 11 - Java	thadian	View Answer on Stackoverflow
Solution 12 - Java	Liviu Florin Ilie	View Answer on Stackoverflow
Solution 13 - Java	invzbl3	View Answer on Stackoverflow