英文:
jdbcTemplate - leave field empty
问题
我有一个像这样生成的 H2 SQL 数据库：
CREATE TABLE IF NOT EXISTS dog
(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
...
breed_id BIGINT, -- 引用 breed(id)（见下一行）
FOREIGN KEY (breed_id) REFERENCES breed(id)
);
请注意，`breed_id` 是可选的。
要在此数据库中存储数据，我使用类似以下的代码：
@Override
public Dog save(Dog dog) {
final String sql =
"INSERT INTO " +
TABLE_NAME +
" (name, [...], breed_id)" +
" VALUES (?, [...], ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, dog.getName());
[...]
if (dog.getBreed() != null) {
stmt.setLong(5, dog.getBreed().getId());
} else {
// 将 breed_id 字段保留为空
}
return stmt;
}, keyHolder);
breed.setId(((Number)keyHolder.getKeys().get("id")).longValue());
return breed;
}
我的问题是，如果 `dog.getBreed()` 返回 `null`，该如何将 `breed_id` 字段保留为空？
Java 不允许我设置 `stmt.setLong(5, null);`，我理解这是因为 `long` 类型不支持 `null`。
然而，有什么优雅的方法可以实现这一点呢？
英文:
I have an H2 SQL database generated like this:
CREATE TABLE IF NOT EXISTS dog
(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
...
breed_id BIGINT, -- references to breed(id) (see next line
FOREIGN KEY (breed_id) REFERENCES breed(id)
);
Note that breed_id is optional.
To store data in this database, I use something like
@Override
public Dog save(Dog dog) {
final String sql =
"INSERT INTO " +
TABLE_NAME +
" (name, [...], breed_id)" +
"VALUES (?, [...], ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, dog.getName());
[...]
if (dog.getBreed() != null) {
stmt.setLong(5, dog.getBreed().getId());
} else {
// leave breed_id field empty
}
return stmt;
}, keyHolder);
breed.setId(((Number)keyHolder.getKeys().get("id")).longValue());
return breed;
}
My question is, how to leave the breed_id field empty in case dog.getBreed() returns null?
Java wouldn't let me set stmt.setLong(5, null); which I understand as long doesn't know null.
However, what is an elegant way to do it then?
答案1
得分: 1
if (dog.getBreed() != null) {
stmt.setLong(5, dog.getBreed().getId());
} else {
// leave breed_id field empty
stmt.setNull(5, Types.NULL);
}
英文:
@speendo - Kindly use setNull method of PreparedStatement with using java.sql.Types as below:
if (dog.getBreed() != null) {
stmt.setLong(5, dog.getBreed().getId());
} else {
// leave breed_id field empty
stmt.setNull(5, Types.NULL);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论