Oracle ADF对接SQL Server自增主键插入报错问题咨询

Hey Xavier, let’s tackle this primary key auto-increment issue with Oracle ADF and SQL Server— I’ve run into this exact scenario before, so here’s how to fix it step by step:

The root problem is that Oracle ADF tries to assign a value to your primary key field by default, but SQL Server’s IDENTITY (Auto_Increment) column is meant to be populated automatically by the database. This mismatch causes the insertion error.

1. Adjust Entity Object Primary Key Settings #

This is the most critical step— you need to tell ADF to let the database handle the primary key generation:

• Open your target Entity Object (.eo file) and locate the primary key attribute.
• In the Properties panel for the attribute:
  • Navigate to the Update section, set Insert Using to Database Sequence (yes, this works for SQL Server’s IDENTITY columns despite the name).
  • Check the Always Update Attribute box— this ensures ADF fetches the auto-generated primary key value from the database after insertion.
• Switch to the Database tab and confirm:
  • The Column Type reflects SQL Server’s IDENTITY setup.
  • The Primary Key checkbox is selected.

2. Add Custom DML Preparation Logic (Optional but Reliable) #

If you still see issues after the first step, add a small code snippet to force ADF to skip sending a value for the primary key during insertion:

• In your Entity Object’s Java class, override the prepareForDML method:

  protected void prepareForDML(int operation, TransactionEvent e) {
      super.prepareForDML(operation, e);
      // Only apply this logic during insert operations
      if (operation == DML_INSERT) {
          // Replace "YourPrimaryKeyField" with your actual column name
          setAttributeInternal("YourPrimaryKeyField", null);
      }
  }
  

This ensures ADF doesn’t pass any value for the primary key, letting SQL Server do its job.

3. Clean Up View Object and Page Bindings #

• Open your corresponding View Object (.vo file) and make sure the primary key attribute is not marked as Required— users shouldn’t need to input this value.
• On your ADF Faces page, set the primary key’s input component to read-only or hide it entirely (e.g., using rendered="false"). This prevents accidental user input from interfering with the auto-increment logic.

4. Verify SQL Server Table Configuration #

Double-check that your table’s primary key is properly set as an IDENTITY column. Run this SQL query to confirm:

SELECT COLUMNPROPERTY(OBJECT_ID('YourTableName'), 'YourPrimaryKeyField', 'IsIdentity') AS IsIdentity

A return value of 1 confirms the IDENTITY setting is correct.

• For ADF 12c and newer, the Database Sequence setting natively supports SQL Server’s IDENTITY columns— no need to create Oracle-style sequences in SQL Server.
• After setting everything up, test an insertion and verify that the auto-generated primary key is correctly fetched by ADF (you can print the attribute value post-insert to confirm it’s not null).

Hope this gets your CRUD app working smoothly! Let me know if you hit any snags along the way.

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