Error while insertion of data using identity in hibernate

Question

I was facing an issue of inserting data in database where STOCK_ID is identity and STOCK_NAME is string. While inserting the data in database it shows an exception which has mentioned below. Kindly guide me to rectify the issue.

Hibernate: insert into mkyongdb.system.STOCK (STOCK_NAME) values (?)
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not insert: [com.mkyong.user.Stock]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:64)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2345)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2852)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
    at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
    at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
    at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:713)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:701)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:697)
    at com.mkyong.common.App.main(App.java:45)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00926: missing VALUES keyword

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:57)
    ... 16 more

Stock.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.mkyong.user.Stock" table="STOCK" catalog="mkyongdb">
        <id name="stockId" type="java.lang.Integer">
            <column name="STOCK_ID" />
            <generator class="identity" />
        </id>
        <property name="stockName" type="string">
            <column name="STOCK_NAME" length="10"  />
        </property>

        <!-- <one-to-one name="stockDetail" class="com.mkyong.user.StockDetail"
            cascade="save-update"></one-to-one> -->
    </class>
</hibernate-mapping>

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="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="hibernate.connection.url">jdbc:oracle:thin:@127.0.0.1:1521/xe</property>
        <property name="hibernate.connection.username">system</property>
        <property name="hibernate.connection.password">admin</property>
        <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

        <property name="show_sql">true</property>
        <property name="hibernate.default_schema">system</property> 
        <property name="hibernate.hbm2ddl.auto">validate</property>
        <mapping resource="com/mkyong/user/DBUser.hbm.xml"></mapping>
        <mapping resource="com/mkyong/user/Stock.hbm.xml" />
    <mapping resource="com/mkyong/user/StockDetails.hbm.xml" />
    </session-factory>
</hibernate-configuration>

Stock.java

package com.mkyong.user;

public class Stock implements java.io.Serializable {

    private Integer stockId;
    private String stockName;
    //private StockDetail stockDetail;

    /*public StockDetail getStockDetail() {
        return stockDetail;
    }
    public void setStockDetail(StockDetail stockDetail) {
        this.stockDetail = stockDetail;
    }*/

    public Stock() {
        //super();
        // TODO Auto-generated constructor stub
    }
    public Stock(Integer stockId, String stockName) {
        super();
        this.stockId = stockId;
        this.stockName = stockName;
    }
    public Integer getStockId() {
        return stockId;
    }

    public void setStockId(Integer stockId) {
        this.stockId = stockId;
    }
    public String getStockName() {
        return stockName;
    }
    public void setStockName(String stockName) {
        this.stockName = stockName;
    }






}

Main.java

Session session = HibernateUtil.getSessionFactory().openSession();

        session.beginTransaction();

        Stock stock = new Stock();

        stock.setStockName("sam");


        session.save(stock);
        session.getTransaction().commit();

Create Query:

CREATE TABLE STOCK
(
  STOCK_ID numeric(10) not null,
  STOCK_NAME varchar2(50) not null,
  CONSTRAINT STOCK_PK PRIMARY KEY (STOCK_ID)
);

Did you choose to do so? There are better way to do it nowadays. — NiVeR, May 31 '18 at 20:18
You say that STOCK_ID is identity, but you don't seem to have created a sequence/trigger or used the `AS IDENTITY` keywords in the table definition. — kfinity, May 31 '18 at 20:27
@kfinity: could you help me on how it could be handled using sequence.Stock_id should be autoincremented — Giridharan, May 31 '18 at 20:30
See the link in my comment on Barbaros' answer. It depends on your Oracle version. — kfinity, May 31 '18 at 20:33

Barbaros Özhan · Answer 1 · 2018-05-31T20:40:53.723

1

I think mkyongdb is your schema which has table named STOCK. So, use mkyongdb.STOCK instead of mkyongdb.system.STOCK in the insert statement.
STOCK_ID is a mandatory field and missing in the insert statement.

( By the way, If you use Oracle12c this field may not be included in the insert statement provided that defined as STOCK_ID numeric(10) GENERATED by default on null as IDENTITY in the create table DDL statement directly without need of any other mechanism like trigger. If you have version prior to 12c, a before insert trigger may be created which includes :new.STOCK_ID := seq_stock_id.nextval; statement, where seq_stock_id is a sequence )

edited May 31 '18 at 20:40

answered May 31 '18 at 20:22

Barbaros Özhan

59,113
10
31
55

Alternately, if you're on 11g, you need a trigger/sequence to create an identity/auto-increment column. https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – kfinity May 31 '18 at 20:28

Error while insertion of data using identity in hibernate

1 Answers1