问题

我在使用JDBC更新表列时遇到一些问题。如果我有一个表，例如User（name，address，hobby，...），假设有15个字段。然后，我通过前端从表单获取一个对象，用户可以在其中键入应更改的所有条目。现在我需要将更改保存在数据库中，但并不是所有字段都被更改，因此我的DAO有一些null值。例如，name和address应该被更改，表中的其他条目则不应更改。是否有任何巧妙的方法可以将其放入JDBC的PreparedStatement中？或者您是否知道其他解决方案？我试图避免大量的value != null语句。

提前感谢！（我在后端使用Spring，在前端使用Angular）

答案1

得分: 2

由于您正在使用Spring，您可以使用NamedParameterJdbcTemplate，但真正的技巧是使用COALESCE在给定的值为NULL时使用备用值：

@Autowired
private DataSource dataSource;

public void updateUser(int id, String name, String address, String hobby) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    String sql = "UPDATE User" +
                 " SET Name = COALESCE(:name, Name)" +
                 ", Address = COALESCE(:address, Address)" +
                 ", Hobby = COALESCE(:hobby, Hobby)" +
                 " WHERE Id = :id";
    MapSqlParameterSource paramMap = new MapSqlParameterSource();
    paramMap.addValue("id"     , id     , Types.INTEGER);
    paramMap.addValue("name"   , name   , Types.VARCHAR);
    paramMap.addValue("address", address, Types.VARCHAR);
    paramMap.addValue("hobby"  , hobby  , Types.VARCHAR);
    if (jdbcTemplate.update(sql, paramMap) == 0)
        throw new EmptyResultDataAccessException("User not found: " + id, 1);
}

或者，如果您使用带有用户数据的POJO：

```java
public class User {
    private int id;
    private String name;
    private String address;
    private String hobby;

    // 这里是getter和setter方法
}

public void updateUser(User user) {
NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
String sql = "UPDATE User" +
" SET Name = COALESCE(:name, Name)" +
", Address = COALESCE(:address, Address)" +
", Hobby = COALESCE(:hobby, Hobby)" +
" WHERE Id = :id";
BeanPropertySqlParameterSource paramMap = new BeanPropertySqlParameterSource(user);
if (jdbcTemplate.update(sql, paramMap) == 0)
throw new EmptyResultDataAccessException("User not found: " + id, 1);
}

<details>
<summary>英文:</summary>

Since you&#39;re using Spring, you can use the `NamedParameterJdbcTemplate`, but the real trick is the use of `COALESCE` to use a fall-back value when the value given is `NULL`:

	@Autowired
	private DataSource dataSource;

	public void updateUser(int id, String name, String address, String hobby) {
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
		String sql = &quot;UPDATE User&quot; +
		               &quot; SET Name = COALESCE(:name, Name)&quot; +
		                  &quot;, Address = COALESCE(:address, Address)&quot; +
		                  &quot;, Hobby = COALESCE(:hobby, Hobby)&quot; +
		             &quot; WHERE Id = :id&quot;;
		MapSqlParameterSource paramMap = new MapSqlParameterSource();
		paramMap.addValue(&quot;id&quot;     , id     , Types.INTEGER);
		paramMap.addValue(&quot;name&quot;   , name   , Types.VARCHAR);
		paramMap.addValue(&quot;address&quot;, address, Types.VARCHAR);
		paramMap.addValue(&quot;hobby&quot;  , hobby  , Types.VARCHAR);
		if (jdbcTemplate.update(sql, paramMap) == 0)
			throw new EmptyResultDataAccessException(&quot;User not found: &quot; + id, 1);
	}

Or, if you use a POJO with the user data:

public class User {
private int id;
private String name;
private String address;
private String hobby;

// Getters and setters here

}

	public void updateUser(User user) {
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
		String sql = "UPDATE User" +
		               " SET Name = COALESCE(:name, Name)" +
		                  ", Address = COALESCE(:address, Address)" +
		                  ", Hobby = COALESCE(:hobby, Hobby)" +
		             " WHERE Id = :id";
		BeanPropertySqlParameterSource paramMap = new BeanPropertySqlParameterSource(user);
		if (jdbcTemplate.update(sql, paramMap) == 0)
			throw new EmptyResultDataAccessException("User not found: " + id, 1);
	}

</details>



# 答案2
**得分**: 0

你可以使用SimpleJdbcInsert并添加usingGeneratedKeyColumns("ID")来实现简单的解决方案。

```java
@Autowired
private DataSource dataSource;

@Override
public ResponseEntity<Void> insertEntity(Entity obj) {
    SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate.getDataSource());
    simpleJdbcInsert.withTableName(TABLE_APP_REPO).usingGeneratedKeyColumns("ID");
    BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(obj);

    try {
        simpleJdbcInsert.execute(paramSource);
    } catch (Exception e) {
        return ResponseEntity.badRequest().build();
    }
    return ResponseEntity.ok().build();
}

@Autowired
private DataSource dataSource;
    
@Override
    public ResponseEntity<Void> insertEntity(Entity obj) {
        SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate.getDataSource());
        simpleJdbcInsert.withTableName(TABLE_APP_REPO).usingGeneratedKeyColumns("ID");
        BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(obj);

        try {
            simpleJdbcInsert.execute(paramSource);
        } catch (Exception e) {
            return ResponseEntity.badRequest().build();
        }
        return ResponseEntity.ok().build();
    }