jsfnetbeans

JSF CRUD Netbeans Tutotial - Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

I am following this tutorial where you can create a JSF CRUD application directly from a database (Generate Entity Classes from database and then Generate JSF pages from Entity Classes).

When I try to apply this to my MSSQL Database, I get a problem after the generation of the JSF pages when I try to insert a record in a table where the PK is set to "Is Identity" and "Identity Increment" set to 1.

This is my JSF create page:

enter image description here

The idActivityRiskType is my Auto Increment PK. So when I try and save a record with no idActivityRiskType value I get a Validation error 'The IdActivityRiskType field is required. ' When I try to set a value for the PK lets say 10 I get the following error:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.1.v20150605-31e8258): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'activityrisktype' when IDENTITY_INSERT is set to OFF.
Error Code: 544
Call: INSERT INTO activityrisktype (idActivityRiskType, name, riskValue, idCustomerType) VALUES (?, ?, ?, ?)
    bind => [4 parameters bound]
Query: InsertObjectQuery(com.cmsss.mavenproject1.jpa.entities.Activityrisktype[ idActivityRiskType=10 ])
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)

Searching through for a solution I tried to set the IDENTITY_INSERT of the table to ON but this didn't work:

SET IDENTITY_INSERT xxx.[xxx].[activityrisktype] ON

Is there way to be able to generate JSF pages from my Entity classes that take into consideration this issue. If not, how could I go about and fix this problem?

Solution

  • Ok, it seems that I found a work around it and works pretty well...

    Step 1: Modify the annotations in the Entity Class:

    @Id
@Basic (optional = false)
@NotNull
@GeneratedValue(strategy = javax.persistence.GenerationType.IDENTITY)
@Column (name = "idActivityRiskType")
private Integer idActivityRiskType;

    Step 2: Modify the Create.xhtml to add a CSS class to the id field (to be able to hide it when page loads)

    <h:inputText class="autoInsert" id="idActivityRiskType" value="#{activityrisktypeController.selected.idActivityRiskType}" title="#{bundle.CreateActivityrisktypeTitle_idActivityRiskType}" required="true" requiredMessage="#{bundle.CreateActivityrisktypeRequiredMessage_idActivityRiskType}"/>

    Step 3: On load set the value of the field to some integer value (so that the validation will not trigger) and hide it from the user

    $( document ).ready(function() {
    $('.autoInsert').val(1);
    $('.autoInsert').hide(); 
});

    Works pretty well now