Spring JdbcTemplate / NamedParameterJdbcTemplate passing null value as a parameter value

Question

I have an issue passing a null value to NamedParameterJdbcTemplate using MapSqlParameterSource of the spring framework. Anyone knows how to do this?

Currently my code is :

String sql = "update person set project = :project where id = :id;";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("project ", null);
params.addValue("id ", 1);
int count = newNamedParameterJDBCTemplate().update(sql, params);

This is where I get a NullPointerException.

Are you set the `dataSource` for `NamedParameterJDBCTemplate` that returning from `newNamedParameterJDBCTemplate()`? — Saar, Jul 26 '12 at 23:44
xSNRG, can you share what was the problem? Answer by Titi is same as your code. — bschandramohan, Aug 19 '15 at 10:22

score 16 · Accepted Answer · edited Sep 12 '19 at 15:45

16

This is my code on Spring 3.1

String sql = "update user set name = :name where id = :id";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("name", null);
params.addValue("id", 1);
namedParameterJdbcTemplate.update(sql, params);

works fine. Maybe a stack trace might help?

edited Sep 12 '19 at 15:45

djm.im

3,295
4
30
45

answered Feb 15 '12 at 07:14

Titi Wangsa Bin Damhore

858
7
11

score 12 · Answer 2 · answered Feb 15 '12 at 04:10

12

In pure jdbc its PreparedStatement.setNull(int,java.sql.Types.NULL);
From MapSqlParameterSource api there is

addValue(String paramName, Object value,int sqlType)

try providing java.sql.Types.NULL as sqlType.

May be this helps.

answered Feb 15 '12 at 04:10

baba.kabira

3,111
2
26
37

score 9 · Answer 3 · answered Jun 19 '17 at 21:16

9

There is an extra space after parameter name:

params.addValue("project ", null);
                        ↑   
params.addValue("id ", 1);
                   ↑

answered Jun 19 '17 at 21:16

cnstntn

91
2
3

epox · Answer 4 · 2022-01-18T23:43:48.433

2

Map.of() doesn't support null

String sql = "update person set project = :project where id = :id;";

// ISSUE: Map.of doesn't support null values, but HashMap does:
Map<String, Object> params = new HashMap<>();
params.put("project", null);
params.put("id", 1);
int count = newNamedParameterJDBCTemplate().update(sql, params);

edited Jan 18 '22 at 23:43

answered Oct 04 '19 at 15:21

epox

9,236
1
55
38

1

thanks, helped for me ) – diana-pure Jan 18 '22 at 02:12

score -1 · Answer 5 · answered Dec 30 '13 at 01:08

-1

Please make sure if datasource is set for your jdbcTemplate like below as an example namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

answered Dec 30 '13 at 01:08

Kran

69
1
3

score -1 · Answer 6 · edited Nov 02 '17 at 18:53

-1

I think semicolon is extra in code below:

String sql = "update person set project = :project where id = :id;";

Remove semicolon after id. It should be like:

String sql = "update person set project = :project where id = :id";

edited Nov 02 '17 at 18:53

Dalton Cézane

3,672
2
35
60

answered Nov 02 '17 at 18:00

Tanya verma

11
3

Spring JdbcTemplate / NamedParameterJdbcTemplate passing null value as a parameter value

6 Answers6

Map.of() doesn't support null