英文:
Is there way to set default as null for SQL parameter?
问题
MapSqlParameterSource parameters = new MapSqlParameterSource()
.addValue(EVENT_ID, eventId)
.addValue(TYPE, type.toString());
if (Type.SPOOFER_USER == type) {
parameters.addValue(ACCOUNT_ID, null)
.addValue(USER_ID, account.getUser().getId());
}
else {
parameters.addValue(ACCOUNT_ID, account.getId())
.addValue(USER_ID, null);
}
INSERT INTO database (id, event_id, type, account_id, user_id)
VALUES (database.nextval, :event_id, :type, :account_id, :user_id)
Update:
When you execute the line:
jdbcTemplate.update(insertEventExtra, parameters);
with the given parameters without explicitly setting them to null, you might encounter the exception:
org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'user_id': No value registered for key 'user_id'
This occurs because the parameters that you are passing to the SQL query are expected to have values for all the keys. However, in cases where you don't want to provide a value for a certain key, you should explicitly set it to null. This aligns with the structure of your SQL query and the table's schema.
Your specific question pertains to the test giving you an exception when you try to run it with parameters without setting them to null. The reason for this is that the test setup seems to require all the parameter keys to have associated values, and not setting them explicitly to null results in the mentioned exception.
英文:
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.
答案1
得分: 1
将数据库架构中的列设置为可为空，并默认为null。然后，如果在插入时未指定列的值，它应默认为null。
英文:
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
答案2
得分: 1
默认情况下，除非您在插入或更新列时提供值，否则所有非空列的默认值为NULL。
英文:
By default all not null columns have default value of NULL unless and until you provide value while inserting or updating the column.
答案3
得分: 1
你必须包含addValue（ACCOUNT_ID，null）和addValue（USER_ID，null），因为您的INSERT语句包括两个命名参数“：account_id”，“：user_id”。
该框架尝试从MapSqlParameterSource对象中提取命名参数的值，当它找不到其中之一时，就会抛出异常。它这样做是为了避免用户错误，因为如果您不打算为参数提供值，您就不会在INSERT语句中包含该参数。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论