Why Spring JDBC Templates Don't Use Table Defaults

Question

I have a table in MYSQL and I use JDBC Templates to insert into this table.

One of the columns has a default value, and I do not indicate it on the map Map<String, Object> parameters .

I get a Column 'colName' cannot be null exception Column 'colName' cannot be null .

Can someone explain this please?

thanks

* Change: code *

 contactDetailsInsertTemplate = new SimpleJdbcInsert( dataSource ).withTableName("contactdetails").usingGeneratedKeyColumns("contactcode"); Map<String, Object> parameters = new HashMap<String, Object>(); Number newId = contactDetailsInsertTemplate.executeAndReturnKey(parameters); 

Answer 1

You need to restrict the columns specified in SQL Insert.

See SimpleJdbcInsert.usingColumns ()

If you do not, the SQL statement will need values ​​for ALL columns, and the default values ​​cannot be used.

eg. use

 insert into mytable (col1, col2) values (val1, val2); 

instead

 insert into mytable values (val1, val2); 

Answer 2

INSERT INTO mytable (1,null) does not match INSERT INTO mytable (1) . It preliminarily inserts null specifically, later the decision goes to the database (this will be the default value). Spring JDBC does the first.

Answer 3

The problem is elsewhere: when I use SimpleJdbcInsert and pass it a parameter map, I just expect to create a statment insert with only the parameters provided. I have a NOT NULL column in a table with a DEFAULT value and I do not want to specify it. I also don't want to explicitly put all the column names in usingColumns() , as I just do this when creating the parameter map! Why should I do this again? The problem is that SimpleJdbcInsert wants to be smarter and add all the columns to insert an instruction that is not needed. Why not create an operator using columns with map parameters only?

See: http://forum.spring.io/forum/spring-projects/data/54209-simplejdbcinsert-and-columns-with-default-values

Answer 4

You can add columns with a default value as parameters usingGeneratedKeyColumns() .
Column names included as parameters of this method will be omitted from the generated INSERT .

Answer 5

Since the OP delivers the MAP to SimpleJdbcInsert .

This is what I did.

1. I made sure that in the MAP there are only those columns that I want to insert.

2. I extracted the map keys and converted them to String[] as shown below

    Set<String> set = map.keySet(); String[] stringArray = (String[]) map.keySet().toArray(new String[set.size()]);' 

3. When starting SimpleJdbcInsert I passed in the columns that I want to use, as shown below

    SimpleJdbcInsert sji = new SimpleJdbcInsert(dataSource).withTableName("table_name"); sji.usingColumns(columnNames);' 

Hope this helps.