Springboot with JPA. Cannot Save to Auto Increment Field field in SQL Server. Null Error

Question

I'm trying to learn some API development with Spring Boot with JPA and I have this table in the database with id column specified as primary key with auto increment. When I submit data I keep getting this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'Id', table

In my Class I have this for id. You might say why @Column(name='Id'). I started adding this as I'm troubleshooting. Still before and now after a few changes code works fine when I read information from the table.

package com.FUT.track.web.FUTtrackapplication.squads;

import javax.persistence.*;

@Entity
@Table(name="Squads")
public class Squad {

@Id
@GeneratedValue( strategy = GenerationType.IDENTITY )
@Column(name ="Id", nullable = false, unique = true)
public int id;
public String squadName;
public String squadDescription;
public String primaryFormation;


public Squad() {

}


public Squad(int id, String squadName, String squadDescription, String 

primaryFormation) {
    super();
    this.id = id;
    this.squadName = squadName;
    this.squadDescription = squadDescription;
    this.primaryFormation = primaryFormation;

}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getSquadName() {
    return squadName;
}

public void setSquadName(String squadName) {
    this.squadName = squadName;
}

public String getSquadDescription() {
    return squadDescription;
}

public void setSquadDescription(String squadDescription) {
    this.squadDescription = squadDescription;
}

public String getPrimaryFormation() {
    return primaryFormation;
}

public void setPrimaryFormation(String primaryFormation) {
    this.primaryFormation = primaryFormation;
}

}

Not sure ie @GeneratedValue( strategy = GenerationType.IDENTITY) is needed but with or without it I still keep getting the same error. I'm submitting data for all fields except for id. All other fields can store nulls so even if nothing was submitted and I would think id should be generated for the new column. But for some reason it is not working.

Also in my application properties file:

spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Any advice?

Solution

Try doing something like this:

import javax.persistence.*;

@Entity
@Table(name = "Squads")
public class Squad{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id", nullable = false, unique = true)
    public int id;

    @Column(name = "SquadName", nullable = false)
    private String squadName;

    @Column(name = "SquadDescription", nullable = false)
    private String squadDescription;

    @Column(name = "PrimaryFormation", nullable = false)
    private String primaryFormation;

    public Squad(){

    }

    public Squad(final String squadName, final String squadDescription, final String primaryFormation){

        this.squadName = squadName;
        this.squadDescription = squadDescription;
        this.primaryFormation = primaryFormation;
    }

    public int getId(){

        return id;
    }

    public void setId(final int id){

        this.id = id;
    }

    public String getSquadName(){

        return squadName;
    }

    public void setSquadName(final String squadName){

        this.squadName = squadName;
    }

    public String getSquadDescription(){

        return squadDescription;
    }

    public void setSquadDescription(final String squadDescription){

        this.squadDescription = squadDescription;
    }

    public String getPrimaryFormation(){

        return primaryFormation;
    }

    public void setPrimaryFormation(final String primaryFormation){

        this.primaryFormation = primaryFormation;
    }
}

Notice how I do not include the "id" in the constructor. Also, I've added column names for each field. I assumed that you or your DBA will want to use camel case names instead of undersored names, so in your "application.properties" file (or yml ... whatever), set the physical naming strategy like so:

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Most database developers and administrators that work with Microsoft SQL Server prefer camel case for table and column names, as opposed to using the underscores approach that JPA and Spring assume to be the default. For example, most of the time in Java/Spring you expect a table name of "MY_TABLE", but in SQL Server you will probably have a table name of "MyTable". Since this is non-standard in the spring ecosystem, you need to specify a different naming strategy.

Comments

[Admin]

IDENTITY means your database column is of type IDENTITY (any JPA doc would tell you this), and will be assigned on INSERT. When you persist a record post what is the SQL that is issued. This is the first step to understanding why you get that. aka debugging

[sayayin]

What does NoArgsConstructor AllArgsConstructor annotations do as I already have both constructors in the code? As for naming conventions this is just a app I'm working on for learning purposes but still I don't see where you are seeing the underscores.

[Pytry]

I was not seeing underscores. Most database developers and administrators that work with Microsoft SQL Server prefer camel case for table and column names, as opposed to using the underscores approach that JPA and Spring assume to be the default. For example, most of the time in Java/Spring you expect a tabkle name of "MY_TABLE", but in SQL Server you will probably have a table name of "MyTable". Since this is non-standard in the spring ecosystem, you need to specify a different naming strategy.

[Pytry]

As for the annotations, those are from Lombok, which will generate the boiler plate code for you (constructors, getters and setters etc), so you don't have to. I can see how that is clouding the issue though, so I'll remove that for clarification in a bit.

[sayayin]

ohh ok thank you. I removed Id from the constructor and also added Column annotation for each field but still not working. Only thing I have not changed according to our suggestion is the strategy in the properties file. Mine work fine to fetch data from the table so I believe it is fine and not a matter of filed mapping. I went to the database and inserted records in the table and it does not expect a value for Id as it is automatically generated so it is really mind boggling why is this happening. I restarted my SQL server just in case and still no luck.

[sayayin]

By the way not sure why I am not able to add your username with @. It disappears when I save, just in case you are wondering. :-)

[Pytry]

If your tables and columns are named similar to how I put them, then you need to change the naming-strategy. Also, I have never noticed my name disappearing before.

[Pytry]

FYI: My real name is @

[sayayin]

ok so names are different and I do have that setting along with another one in there. I had issues with that initially and finally found those two settings that worked for me. So still not working and not sure what to do.

[Pytry]

I'm stump. If you can create a GiHub example, I can try to work through it during lunch. (I have my own SQL Server instance).

[sayayin]

You don't need to do this during your lunch as this is my side project mainly to learn api development with spring boot. I really appreciate your help so far. I'll make and example on github or on bitbucket and let you know.

[sayayin]

You will not believe this as I was cleaning up the project to upload to github I caught the bug. It was actually in the front end of the application. Form was submitting to a different API and ofcourse a different table. One of those stupid mistakes. Sorry to put you through this. You had some good suggestions above and I still learnt things. So thank you!

[Pytry]

That's hilarious and completely normal:) I'm glad I could "help".