could not extract ResultSet in hibernate

Question

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 lt Product gt  products   new HashSet lt Product gt  0      getter  amp  setter  amp  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  amp  setter  amp  constructor       Repository  SuppressWarnings   unchecked    rawtypes    public class ProductDAOImpl implements ProductDAO        Autowired     private SessionFactory sessionFactory  public List lt Product gt  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              amp  amp  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     lt  -- Scan classpath for annotations  eg   Service   Repository etc  -- gt     lt context component-scan base-package  com shopmvc   gt      lt  -- 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 -- gt     lt bean id  myDataSource  class  org apache commons dbcp BasicDataSource  destroy-method  close  gt       lt property name  driverClassName  value  com mysql jdbc Driver   gt       lt property name  url  value  jdbc mysql   localhost 3306 shoesshopdb autoReconnect true   gt       lt property name  username  value  root   gt       lt property name  password  value  12345   gt       lt property name  validationQuery  value  SELECT 1   gt     lt  bean gt      lt  -- Hibernate Session Factory -- gt     lt bean id  mySessionFactory  class  org springframework orm hibernate4 LocalSessionFactoryBean  gt       lt property name  dataSource  ref  myDataSource   gt       lt property name  packagesToScan  gt         lt array gt           lt value gt com shopmvc pojo lt  value gt         lt  array gt       lt  property gt       lt property name  hibernateProperties  gt         lt value gt          hibernate dialect org hibernate dialect MySQLDialect        lt  value gt       lt  property gt     lt  bean gt      lt  -- Hibernate Transaction Manager -- gt     lt bean id  transactionManager  class  org springframework orm hibernate4 HibernateTransactionManager  gt       lt property name  sessionFactory  ref  mySessionFactory   gt     lt  bean gt      lt  -- Activates annotation based transaction management -- gt     lt tx annotation-driven transaction-manager  transactionManager   gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    lt  --hibernate cfg xml -- gt   lt property name  show sql  gt true lt  property gt    To display what is being queried on the console

User · Answer

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

User · Answer

I was using Spring Data JPA with PostgreSql and during UPDATE call it was showing errors-   could not extract ResultSet  and another one  org springframework dao InvalidDataAccessApiUsageException  Executing an update delete query  nested exception is javax persistence TransactionRequiredException  Executing an update delete query   Showing Transactional required    Actually  I was missing two required Annotations    Transactional and  Modifying  With-  Query vlaue    quot  UPDATE DB TABLE SET Col1    1 WHERE id    2  quot   nativeQuery   true  void updateCol1 String value  long id

User · Answer

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

User · Answer

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

User · Answer

Another solution is add  JsonIgnore     OneToMany mappedBy  catalog   fetch   FetchType LAZY   JsonIgnore private Set lt Product gt  products   new HashSet lt Product gt  0

[java] could not extract ResultSet in hibernate

Examples related to java

Examples related to sql

Examples related to hibernate

Examples related to spring-mvc