Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

Could not extract ResultSet in hibernate

Asked by Yash Jaiteley Jul 1, 2021 2.4K views 1 answer
Share

About this question

I am having a problem with the Hibernate. I am also trying to parse to List but It results with 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 products = new HashSet(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 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) -->
  
  <!-- 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 -->
  
    
    
    
    
    
  
  <!-- Hibernate Session Factory -->
  
    
    
      
        com.shopmvc.pojo
      
    
    
      
        hibernate.dialect=org.hibernate.dialect.MySQLDialect
      
    
  
  <!-- Hibernate Transaction Manager -->
  
    
  
  <!-- Activates annotation based transaction management -->
  

 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
)

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.