使用Hibernate Identity生成器插入数据时出现SQL语法错误

Hey there, let's break down what's causing your Hibernate + Oracle insert error and walk through the fixes step by step. That ORA-00926: missing VALUES keyword error stems from a combination of invalid table name formatting and an incompatible primary key generator for Oracle's ecosystem.

1. Fix the Invalid Table Name (Immediate Syntax Fix) #

Oracle doesn't use "catalogs" like MySQL or SQL Server do. Your Stock.hbm.xml sets catalog="mkyongdb", and your hibernate.cfg.xml defines default_schema=system — this makes Hibernate generate a table reference like mkyongdb.system.STOCK, which Oracle can't parse correctly.

Quick fix:
Update your Stock.hbm.xml to remove the catalog attribute. You can either explicitly set the schema (matching your config) or let the default schema handle it:

<!-- Option 1: Explicit schema -->
<class name="com.mkyong.user.Stock" table="STOCK" schema="system">

<!-- Option 2: Use default schema from hibernate.cfg.xml -->
<class name="com.mkyong.user.Stock" table="STOCK">

2. Fix the Primary Key Generator (Core Compatibility Issue) #

Hibernate's identity generator is built for databases with native auto-increment columns (like MySQL's AUTO_INCREMENT). Oracle handles auto-generated IDs differently depending on its version, so we need to adjust this:

If you're on Oracle 12c or newer (supports Identity Columns)

Oracle 12c introduced native identity columns, but you need to tell Hibernate to use the right dialect to support this:

1. Update your hibernate.cfg.xml dialect to:

   <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</property>
   

2. Ensure your STOCK table's STOCK_ID is defined as an identity column:

   CREATE TABLE STOCK (
       STOCK_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       STOCK_NAME VARCHAR2(10)
   );
   

3. You can keep the <generator class="identity" /> in your mapping — the Oracle12cDialect will handle it correctly now.

If you're on Oracle 11g or older (use Sequences + Triggers)

Older Oracle versions rely on sequences (and optionally triggers) for auto-generated IDs:

1. First, create a sequence for the STOCK table:

   CREATE SEQUENCE STOCK_SEQ 
       START WITH 1 
       INCREMENT BY 1 
       NOCACHE 
       NOCYCLE;
   

2. (Optional) Create a trigger to auto-populate STOCK_ID on insert (so you don't have to manage the sequence in Hibernate):

   CREATE OR REPLACE TRIGGER STOCK_ID_TRG
   BEFORE INSERT ON STOCK
   FOR EACH ROW
   BEGIN
       SELECT STOCK_SEQ.NEXTVAL INTO :NEW.STOCK_ID FROM DUAL;
   END;
   /
   

3. Update your mapping file's generator to something compatible:
   • If you used the trigger, use native (Hibernate auto-detects the setup):

     <id name="stockId" type="java.lang.Integer">
         <column name="STOCK_ID" />
         <generator class="native" />
     </id>
     

   • Or explicitly reference the sequence (no trigger needed):

     <id name="stockId" type="java.lang.Integer">
         <column name="STOCK_ID" />
         <generator class="sequence">
             <param name="sequence">STOCK_SEQ</param>
         </generator>
     </id>
     

Final Quick Checks #

• Double-check that your STOCK table exists in the SYSTEM schema (or adjust the schema name if you're using a different user).
• Confirm your hibernate.cfg.xml has the correct dialect for your Oracle version.

After making these changes, your insert operation should run without the SQL syntax error.

内容的提问来源于stack exchange，提问作者Giridharan