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
)
This question is related to
java
sql
hibernate
spring-mvc
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
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")
I was using Spring Data JPA with PostgreSql and during UPDATE call it was showing errors-
Actually, I was missing two required Annotations.
With-
@Query(vlaue = " UPDATE DB.TABLE SET Col1 = ?1 WHERE id = ?2 ", nativeQuery = true)
void updateCol1(String value, long id);
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")
Another solution is add @JsonIgnore :
@OneToMany(mappedBy="catalog", fetch = FetchType.LAZY)
@JsonIgnore
private Set<Product> products = new HashSet<Product>(0);
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
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)
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.
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();
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;
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.
Source: Stackoverflow.com