Could not extract ResultSet in hibernate

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<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

) 

Answer 1

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

About the Product class earlier, the name of the join column has been 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.

Interested in SQL? Check out this SQL Certification by Intellipaat.

Answer 2

The problem you have with Hibernate here is that the SQL query generated by Hibernate does not exactly match the column names in your database.

Ensure that column names in the database and entity classes do not match.

The "product0_.ID_CATALOG" column error in "Table" indicates that Hibernate is looking for a column called ID_CATALOG in the product table, but there is a column in the table called catalog_id.

Answer 3

In Hibernate, you would not normally go to the database and fetch a ResultSet directly. Hibernate is an Object-Relational Mapping (ORM) tool, so it abstracts away the need for you to deal with ResultSet objects directly. Instead, you would normally work with Query, Criteria, or HQL (Hibernate Query Language) to interact with the database.

However, when you want to retrieve data in the database directly returning you a ResultSet, you have to use native SQL queries by Hibernate's Session object, which is created via Session.createSQLQuery(). On-the-fly, you can even cast the result of such a method to a ResultSet.

Here is how to work with a native SQL query and access the ResultSet in Hibernate:

Example with createSQLQuery:

Session session = sessionFactory.openSession(); try {
// Construct a native SQL query
String sql = "SELECT * FROM my_table";
SQLQuery query = session.createSQLQuery(sql);

// Specify the result to be returned as a list of objects or map to an specific entity
query.addEntity(MyEntity.class);
// Execute query and get the result list
List<MyEntity> resultList = query.list();
//
// Optionally, access the ResultSet (if needed for custom logic)
ResultSet rs = (ResultSet) query.getHibernateQuery().getQuery().getResultSet();
//
// Now, you can work with ResultSet (but usually it's better to avoid this)
while (rs.next()) {
// Only if strictly needed, Process ResultSet data
}

}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
session.close();
}

ResultSet of SQLQuery:
This would technically work but generally, it is discouraged to use ResultSet directly with Hibernate since it defeats the purpose of using an ORM. Hibernate takes care of mapping the result set for you so it is better to work with the mapped entity objects in general.

Other Options:

Using HQL (Hibernate Query Language): HQL allows you to work directly with Hibernate entities and avoid the ResultSet.
Session session = sessionFactory.openSession();
try
String hql = "FROM MyEntity WHERE status = :status";
Query query = session.createQuery(hql);
query.setParameter("status", "active");

List<MyEntity> result = query.list();
} finally {
session.close();
}

Using Criteria API: Another ORM-native approach is using the Criteria API to retrieve entities.
Session session = sessionFactory.openSession();
try {
Criteria criteria = session.createCriteria(MyEntity.class);
criteria.add(Restrictions.eq("status", "active"));
List<MyEntity> result = criteria.list();
} finally {
session.close();
}