jdbcTemplate – 留空字段

huangapple
go评论96阅读模式
英文:

jdbcTemplate - leave field empty

问题

  1. 我有一个像这样生成的 H2 SQL 数据库：
  3.     CREATE TABLE IF NOT EXISTS dog
  4.     (
  5.       id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  6.       name          VARCHAR(255) NOT NULL,
  7.       ...
  8.       breed_id      BIGINT, -- 引用 breed(id)（见下一行）
  9.       FOREIGN KEY (breed_id) REFERENCES breed(id)
  10.     );
  12. 请注意，`breed_id` 是可选的。
  14. 要在此数据库中存储数据，我使用类似以下的代码：
  16.     @Override
  17.     public Dog save(Dog dog) {
  18.         final String sql =
  19.             "INSERT INTO " +
  20.             TABLE_NAME +
  21.             " (name, [...], breed_id)" +
  22.             " VALUES (?, [...], ?)";
  24.         KeyHolder keyHolder = new GeneratedKeyHolder();
  25.         jdbcTemplate.update(connection -> {
  26.             PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
  27.             stmt.setString(1, dog.getName());
  28.             
  29.             [...]
  31.             if (dog.getBreed() != null) {
  32.                 stmt.setLong(5, dog.getBreed().getId());
  33.             } else {
  34.                 // 将 breed_id 字段保留为空
  35.             }
  36.             return stmt;
  37.         }, keyHolder);
  39.         breed.setId(((Number)keyHolder.getKeys().get("id")).longValue());
  41.         return breed;
  42.     }
  44. 我的问题是，如果 `dog.getBreed()` 返回 `null`，该如何将 `breed_id` 字段保留为空？
  46. Java 不允许我设置 `stmt.setLong(5, null);`，我理解这是因为 `long` 类型不支持 `null`。
  48. 然而，有什么优雅的方法可以实现这一点呢？
英文:

I have an H2 SQL database generated like this:

  1. CREATE TABLE IF NOT EXISTS dog
  2. (
  3.   id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  4.   name          VARCHAR(255) NOT NULL,
  5.   ...
  6.   breed_id      BIGINT, -- references to breed(id) (see next line
  7.   FOREIGN KEY (breed_id) REFERENCES breed(id)
  8. );

Note that breed_id is optional.

To store data in this database, I use something like

  1. @Override
  2. public Dog save(Dog dog) {
  3.     final String sql =
  4.         "INSERT INTO " +
  5.         TABLE_NAME +
  6.         " (name, [...], breed_id)" +
  7.         "VALUES (?, [...], ?)";
  9.     KeyHolder keyHolder = new GeneratedKeyHolder();
  10.     jdbcTemplate.update(connection -> {
  11.         PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
  12.         stmt.setString(1, dog.getName());
  13.         
  14.         [...]
  15.         
  16.         if (dog.getBreed() != null) {
  17.             stmt.setLong(5, dog.getBreed().getId());
  18.         } else {
  19.             // leave breed_id field empty
  20.         }
  21.         return stmt;
  22.     }, keyHolder);
  24.     breed.setId(((Number)keyHolder.getKeys().get("id")).longValue());
  26.     return breed;
  27. }

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

  1. if (dog.getBreed() != null) {
  2.     stmt.setLong(5, dog.getBreed().getId());
  3. } else {
  4.     // leave breed_id field empty
  5.     stmt.setNull(5, Types.NULL);
  6. }
英文:

@speendo - Kindly use setNull method of PreparedStatement with using java.sql.Types as below:

  1. if (dog.getBreed() != null) {
  2.             stmt.setLong(5, dog.getBreed().getId());
  3. } else {
  4.             // leave breed_id field empty
  5.             stmt.setNull(5, Types.NULL);
  6. }

huangapple
  • 本文由 发表于 2020年10月12日 04:47:50
  • 转载请务必保留本文链接：https://go.coder-hub.com/64308850.html
  • java
  • jdbc
  • jdbctemplate
  • null
  • spring-boot
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定