org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Kartik Jajal picture Kartik Jajal · May 22, 2015 · Viewed 50.5k times · Source

I have implemented a simple application in Hibernate-4. This application retrieve the value from a table. But when I try to get the record with this

(Booking) session.get(Booking.class, 3740456);

it gives me exception

INFO: HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet

So, to verify into the database regarding table and column name, I copy the query from the log statement and execute it. It gives me proper output.

I checked several questions & answers related to this question, but could able to derive the solution.

Hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>

        <property name="connection.url">jdbc:oracle:thin:@SHKG9072DB:5030:TMSD10G2</property>
        <property name="connection.username">ICTDEV$EDI_APP</property>
        <property name="connection.password">p2II9JLIaea06</property>
        <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

        <property name="show_sql">true</property>

        <property name="format_sql">true</property>

        <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
        <property name = "hibernate.jdbc.lob.non_contextual_creation">true</property>  


        <property name="connection.pool_size">1</property>
        <property name="current_session_context_class">thread</property>

        <mapping class="com.hibernate.demo.Booking"/> 

    </session-factory>
</hibernate-configuration>

Booking.java

package com.hibernate.demo;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;


@Entity
@Table(name="EDI_IN_BOOKING")
public class Booking {

    /**
    * 
    */
    private static final long serialVersionUID = 1L;

    @Id 
    private int id;

    @Column(name="SITE_AN")
    private String site;


    @Column(name="EVENT_ID")
    private int eventId;

    @Column(name="EVENT_DETAIL_ID")
    private int eventDetailId;

    @Column(name="RECORD_SEQUENCE_ID")
    private int recordSequenceId;

    @Column(name="RECORD_TYPE_N")
    int recordType; 

    @Column(name="EDI_SENDER_AN")
    String sender;

    @Column(name="EDI_RECIPIENT_AN")
    String recipient;

    @Column(name="PARTNER_C")
    String partner;

    @Column(name="SENDER_SEQUENCE_AN")
    String senderSequence;




    public String getSite() {
        return site;
    }

    public void setSite(String site) {
        this.site = (site);
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = (id);
    }

    public int getEventId() {
        return eventId;
    }

    public void setEventId(int eventId) {
        this.eventId = (eventId);
    }

    public int getEventDetailId() {
        return eventDetailId;
    }

    public void setEventDetailId(int eventDetailId) {
        this.eventDetailId = (eventDetailId);
    }

    public int getRecordSequenceId() {
        return recordSequenceId;
    }

    public void setRecordSequenceId(int recordSequenceId) {
        this.recordSequenceId = (recordSequenceId);
    }

    public int getRecordType() {
        return recordType;
    }

    public void setRecordType(int recordType) {
        this.recordType = (recordType);
    }

    public String getSender() {
        return sender;
    }

    public void setSender(String sender) {
        this.sender = (sender);
    }

    public String getRecipient() {
        return recipient;
    }

    public void setRecipient(String recipient) {
        this.recipient = (recipient);
    }

    public String getPartner() {
        return partner;
    }

    public void setPartner(String partner) {
        this.partner = (partner);
    }

    public String getSenderSequence() {
        return senderSequence;
    }

    public void setSenderSequence(String senderSequence) {
        this.senderSequence = (senderSequence);
    }



}

Main.java

package com.hibernate.demo;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;

public class Main {

    public static void main(String[] args) {
        System.out.println("Hello World");
        System.out.println("Trying to create a test connection with the database.");
        Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
        StandardServiceRegistryBuilder serviceRegistryBuilder = new StandardServiceRegistryBuilder();
        serviceRegistryBuilder.applySettings(configuration.getProperties());
        ServiceRegistry serviceRegistry = serviceRegistryBuilder.build();
        SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

        Session session = sessionFactory.getCurrentSession(); 

        session.beginTransaction();
        Booking booking = (Booking) session.get(Booking.class, 3740456);        
        System.out.println(booking.getEventDetailId());
    }

}

Table description

desc EDi_IN_BOOKING
ID                             NOT NULL NUMBER                                                                                                                                                                                        
EVENT_ID                       NOT NULL NUMBER                                                                                                                                                                                        
EVENT_DETAIL_ID                NOT NULL NUMBER                                                                                                                                                                                        
RECORD_SEQUENCE_ID             NOT NULL NUMBER                                                                                                                                                                                        
RECORD_TYPE_N                           NUMBER(1)                                                                                                                                                                                     
SITE_AN                                 VARCHAR2(10)                                                                                                                                                                                  
EDI_SENDER_AN                           VARCHAR2(35)                                                                                                                                                                                  
EDI_RECIPIENT_AN                        VARCHAR2(35)                                                                                                                                                                                  
PARTNER_C                               VARCHAR2(20)                                                                                                                                                                                  
SENDER_SEQUENCE_AN                      VARCHAR2(15)                                                                                                                                                                                  

Query from log (this gives correct output)

select
booking0_.id as id1_0_0_,
booking0_.EVENT_DETAIL_ID as EVENT_DETAIL_ID2_0_0_,
booking0_.EVENT_ID as EVENT_ID3_0_0_,
booking0_.PARTNER_C as PARTNER_C4_0_0_,
booking0_.EDI_RECIPIENT_AN as EDI_RECIPIENT_AN5_0_0_,
booking0_.RECORD_SEQUENCE_ID as RECORD_SEQUENCE_ID6_0_0_,
booking0_.RECORD_TYPE_N as RECORD_TYPE_N7_0_0_,
booking0_.EDI_SENDER_AN as EDI_SENDER_AN8_0_0_,
booking0_.SENDER_SEQUENCE_AN as SENDER_SEQUENCE_AN9_0_0_,
booking0_.site_an as site_an10_0_0_ 
from
EDI_IN_BOOKING booking0_ 
where
booking0_.id=3740456;

exception

INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: 
select
    booking0_.id as id1_0_0_,
    booking0_.EVENT_DETAIL_ID as EVENT_DETAIL_ID2_0_0_,
    booking0_.EVENT_ID as EVENT_ID3_0_0_,
    booking0_.PARTNER_C as PARTNER_C4_0_0_,
    booking0_.EDI_RECIPIENT_AN as EDI_RECIPIENT_AN5_0_0_,
    booking0_.RECORD_SEQUENCE_ID as RECORD_SEQUENCE_ID6_0_0_,
    booking0_.RECORD_TYPE_N as RECORD_TYPE_N7_0_0_,
    booking0_.EDI_SENDER_AN as EDI_SENDER_AN8_0_0_,
    booking0_.SENDER_SEQUENCE_AN as SENDER_SEQUENCE_AN9_0_0_,
    booking0_.site_an as site_an10_0_0_ 
from
    EDI_IN_BOOKING booking0_ 
where
    booking0_.id=?
 May 22, 2015 10:49:43 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
 WARN: SQL Error: 904, SQLState: 42000
 May 22, 2015 10:49:43 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
 ERROR: ORA-00904: "BOOKING0_"."SITE_AN": invalid identifier

 May 22, 2015 10:49:43 AM org.hibernate.event.internal.DefaultLoadEventListener onLoad
 INFO: HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet
 Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:449)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:202)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:137)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:102)
at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:186)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4126)
at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:503)
at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:468)
at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:213)
at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:275)
at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:151)
at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1106)
at org.hibernate.internal.SessionImpl.access$2000(SessionImpl.java:176)
at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2587)
at org.hibernate.internal.SessionImpl.get(SessionImpl.java:991)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:356)
at $Proxy7.get(Unknown Source)
at com.dpworld.demo.Main.main(Main.java:23)
   Caused by: java.sql.SQLException: ORA-00904: "BOOKING0_"."SITE_AN": invalid identifier

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1957)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2555)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2896)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:644)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:570)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
... 22 more

Answer

codedabbler picture codedabbler · May 23, 2015

Is your database configured with case sensitive columns? That could be one reason why Oracle is throwing an "invalid identifier" error Check your table creating script.