org.hibernate.exception.SQLGrammarException: could not extract ResultSet
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
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.