could not extract ResultSet in hibernate
Solution 1
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
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
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
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
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)
Thanh Duy Ngo
Updated on October 31, 2021Comments
-
Thanh Duy Ngo over 2 years
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 linequery.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 )
-
Harshith Rai over 5 yearsExplain how this would solve the the problem buddy. The answer-seeker would benefit more by understanding the solution.
-
Sameen over 5 yearscould you elaborate?
-
Suraj Patil almost 5 yearsit occurred in my case when i tried creating new sboot -mysql -jpa app. no db table was created. this solved my issue.
-
Paolo about 4 yearsor when the table name doesn't match... like
@Table(name = "catalog", schema = "taretSchemaName")
and the table name istaretSchemaName
. -
Glogo over 3 yearsIf you are using oracle DB and have disabled the auto schema creation you are probably missing the sequence
HIBERNATE_SEQUENCE
too. If this is your case you can try changing the generated value annotation to something else, like:@GeneratedValue(strategy=GenerationType.IDENTITY)
if you are ofc using the identity generation on your columnid NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
-
user2081279 over 3 yearsI guess this is the (sometimes better) alternative to explicitely annotating with @Table(name= "catalog", schema = "targetSchemaName") as suggested above. Better, because when you copy code from an EE application, then you don't have to modify the entity classes.