Can't insert into Sql Server using Spring Framework SimpleJdbcInsert

Question

I'm trying to insert a row in Sql Server 2008 using JDBC Spring framework:

Here is the code:

BdClass bdclass= new BdClass(BdClass.BdConfig.ConnectionString_MYHOME);
Person personaNueva = new Person();
personaNueva.setIdPerson(5);
personaNueva.setUsuario("JavaUser");
personaNueva.setClave("JavaPassword");
personaNueva.setNombre("JavaNombreUsuario");
personaNueva.setActivo("S");
personaNueva.AddPerson(bdclass); 


//Person class

public void AddPerson(BdClass bdclass)
{
    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put(Constantes.idperson, this.getIdperson());
    parameters.put(Constantes.usuario,this.getUsuario());
    parameters.put(Constantes.clave,this.getClave());
    parameters.put(Constantes.nombre,this.getNombre());
    parameters.put(Constantes.activo, this.getActivo());
    bdclass.Insert(parameters,Constantes.Table_Name);
}

   //BdClass Insert

In this step I am enabling IDENTITY INSERT

public void Insert(Map<String, Object> parameters, String TableName)
   {
      this.jdbcTemplate.execute("SET IDENTITY_INSERT " + TableName + " ON");
      JdbcInsert = new SimpleJdbcInsert(this.jdbcTemplate).withTableName(TableName);        
        JdbcInsert.execute(parameters); 

    }

When running the execute statement I am getting the following exception:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'Persons' when IDENTITY_INSERT is set to OFF

I have already tried the command SET IDENTITY_INSERT PERSONS ON in sql management studio with no luck.

EDIT: idperson field is identity, but it doesn't matter if I pass the parameter and value or not... I'm getting the same error

EDIT 2:

Ok, I fixed it by using the usingColumns Property, So I can specify every column:

public void Insert(Map<String, Object> parameters, String TableName)
{       
  this.jdbcTemplate.execute("SET IDENTITY_INSERT " + TableName + " OFF");
  JdbcInsert = new SimpleJdbcInsert(this.jdbcTemplate).withTableName(TableName)
    .usingColumns(Person.Constantes.usuario,Person.Constantes.clave,
    Person.Constantes.nombre,Person.Constantes.activo);

  JdbcInsert.execute(parameters);
}

Marlon Bernardes · Accepted Answer · 2013-05-09 12:12:14Z

2

SQL Server's SET IDENTITY is session scoped. That means you must execute the command SET IDENTITY_INSERT " + TableName + " ON" under the same transaction you are using to insert your data.

Spring JDBC Template opens a new connection for each statement, and thats why you are not being able to set it under the same session.

//connection opened --> statement executed --> connection closed
this.jdbcTemplate.execute("SET IDENTITY_INSERT " + TableName + " ON");

I think the best approach is not to the IDENTITY_INSERT ON and do the following:

public void Insert(Map<String, Object> parameters, String TableName)
{       
   JdbcInsert = new SimpleJdbcInsert(this.jdbcTemplate).withTableName(TableName);
   //This column won't be included on the insert statement (it will be autogenerated by SQL Server)
   JdbcInsert.setGeneratedKeyName("id");
   JdbcInsert.execute(parameters);
}

Another alternative is to execute both statements under the same transaction, but I don't think you could use SimpleJdbcInsert for that.

answered May 9, 2013 at 12:12

Marlon Bernardes

13.9k7 gold badges39 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Carlos Landeras Over a year ago

Ok. Though I have my problem fixed I've tried your code and yeah, It's working. Thank you @Marlon Bernardes

Marlon Bernardes Over a year ago

@Carlos Landeras Great! The only problem with your approach is that if you include a new column, you would have to add it both to the map and as a parameter to usingColumns. Good to know it's working, though.

Carlos Landeras Over a year ago

Yeah it was a temporary fix until I found your answer

Collectives™ on Stack Overflow

Can't insert into Sql Server using Spring Framework SimpleJdbcInsert

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related