Is there way to set default as null for SQL parameter?

Question

I have a code that creates sql parameters using MapSqlParameterSource. Here is my code:

MapSqlParameterSource parameters = new MapSqlParameterSource()
            .addValue(EVENT_ID, eventId)
            .addValue(TYPE, type.toString())
            .addValue(ACCOUNT_ID, null)
            .addValue(USER_ID, null);
        if (Type.SPOOFER_USER == type) {
            parameters.addValue(USER_ID, account.getUser().getId());
        }
        else {
            parameters.addValue(ACCOUNT_ID, account.getId());
        }

Basically, if account type is spoofer, I have to have user id instead of account id. However, I don't like that I have to set account_id and user_id to null when I instantiate parameters. Is there way to set account_id and user_id as null so I don't have to write this two lines?:

MapSqlParameterSource parameters = new MapSqlParameterSource()
            .addValue(EVENT_ID, eventId)
            .addValue(TYPE, type.toString())
            .addValue(ACCOUNT_ID, null) //////////////////////////THIS ONE
            .addValue(USER_ID, null);   //////////////////////////AND THIS ONE

Here is my sql query:

INSERT INTO database (id, event_id, type, account_id, user_id)
    VALUES (database.nextval, :event_id, :type, :account_id, :user_id)

Update:

Maybe my question was not specific enough. What happens is that when I run

jdbcTemplate.update(insertEventExtra, parameters);

With the given parameters without making them "NULL", I get this exception in my unit test:

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'user_id': No value registered for key 'user_id'

I use hsql to test it. my .sql looks like this:

...
ID NUMBER(38,0) PRIMARY KEY,
EVENT_ID BIGINT NOT NULL,
TYPE VARCHAR2(20 BYTE) NOT NULL,
ACCOUNT_ID NUMBER(38,0),
GROUP_ID NUMBER(38,0),
USER_ID NUMBER(38,0),
...

So my specific question is that my test is giving me exception when I try to run test with parameters without setting them to null.

Just don't set them during the insert. They default to null. — The Impaler, Aug 22 '20 at 12:33
May be I am clueless what's happening here , my question is what is the problem if you just remove the two lines while creating the parameters and add it later as you have shown ? — Sujitmohanty30, Aug 23 '20 at 05:03

score 1 · Answer 1 · answered Aug 21 '20 at 18:24

1

Make your columns nullable and default to null in the database schema. Then, if you don't specify a value for a column when inserting, it should default to null

answered Aug 21 '20 at 18:24

AndreiXwe

723
4
19

score 1 · Answer 2 · answered Aug 21 '20 at 18:40

1

By default all not null columns have default value of NULL unless and until you provide value while inserting or updating the column.

answered Aug 21 '20 at 18:40

Atif

2,011
9
23

score 1 · Accepted Answer · answered Aug 24 '20 at 18:56

You must include the addValue(ACCOUNT_ID, null) and addValue(USER_ID, null) because your INSERT statement includes the two named parameters :account_id, :user_id.

The framework attempts to extract the values for the named parameters from the MapSqlParameterSource object and when it does not find one of them, it throws the exception. It does this to avoid user errors, because if you didn't intend to provide a value for a parameter, you wouldn't include the parameter in the INSERT statement.

oh, so it is designed to thrown an error. I guess I don't have much options if I am sticking with `MapSqlParameterSource` then. Thanks. — Jonathan Hagen, Aug 25 '20 at 01:04

Is there way to set default as null for SQL parameter?

3 Answers3