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

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.

Answer

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.

Advertisement

Answer