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.