Problem
Hibernate is causing me problems. When I try to parse to List, I get the following error: HTTP Status 500 – Could not extract ResultSet. It fails at line query when I debug. list()…
Here’s a sample of my code.
@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
)
Asked by Thanh Duy Ngo
Solution #1
The @JoinColumn annotation gives the name of the column on the targeted item that is being utilised as a foreign key.
The name of the join column on the Product class above is 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 update either the table column name or the name you’re using in the @JoinColumn to make sure they’re both the same. See http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html/entity.html#entity-mapping-association for more information on entity mapping associations.
Answered by Will Keeling
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.
You’ll need to add the schema name to your entity entry in this case:
@Table(name= "catalog", schema = "targetSchemaName")
Answered by sweetfa
Solution #3
When I tried to edit a row, I ran into the same problem:
@Query(value = "UPDATE data SET value = 'asdf'", nativeQuery = true)
void setValue();
My mistake was forgetting to add the @Modifying annotation:
@Modifying
@Query(value = "UPDATE data SET value = 'asdf'", nativeQuery = true)
void setValue();
Answered by Manuel Schmitzberger
Solution #4
In your query, try utilizing an inner join.
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>
On the console, to show what is being queried.
Answered by Lakshmi
Solution #5
I Used the following properties in my application. property file, and the problem was solved
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
I was getting an error before.
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)
Answered by Saurabh Verma
Post is based on https://stackoverflow.com/questions/20089031/could-not-extract-resultset-in-hibernate