java - 使用 Spring NamedParameterJdbcTemplate，如何更新数据库中每个客户的列？

Question

我正在尝试创建一个函数来更新名为customers(lastEmailed 列)的数据库中的每个客户，并且我已尝试使用不同的查询输入和执行方法(更新、批量更新等)多次，但我没有运气。这就是我乱七八糟后的砍伐/注释代码的样子。任何建议将不胜感激，谢谢!此外，输入样式的格式为 2014-12-09 14:20:47(日期时间)。 ** 这是一个 NamedParameterJdbcTemplate。

** 这特别指的是 updateTime 函数，我只是包含了 DAO 的其余部分来提供帮助** 每次调用该函数时，我只想为每个客户更新lastEmailed 的相同值。

@Repository            // creates the bean
public class CustomerDAO {

@Autowired
private NamedParameterJdbcTemplate jdbcTemplate;

private RowMapper<Customer> rowMapper = new RowMapper<Customer>() {
    @Override
    public Customer mapRow(ResultSet resultSet, int index) throws SQLException {
        Customer customer = new Customer();
        customer.setCustomerNumber(resultSet.getString("id"));
        customer.setFirstName(resultSet.getString("fName"));
        customer.setLastName(resultSet.getString("lName"));
        customer.setEmail(resultSet.getString("email"));
        customer.setStreet(resultSet.getString("street"));
        customer.setCity(resultSet.getString("city"));
        customer.setState(resultSet.getString("state"));
        customer.setZip(resultSet.getString("zip"));

        //Map Customer Fields Here

        return customer;
    }
};
public List<Customer> getCustomerList(){
    String sql = "SELECT * FROM CUSTOMERS";
    List<Customer> customerList =this.jdbcTemplate.query(sql, rowMapper);
    return customerList;
}

 // ** in another class?
 //    public String toString(){
//        List<Customer> temp = this.getCustomerList();
 //
 //    }

public void saveCustomer(Customer customer) {
    String sql = "INSERT INTO CUSTOMERS " +
            "(id, lname, fname, email, street,city, state,zip) VALUES (:id, :lname, :fname, :email, " +
            ":street, :city, :state, :zip)";

    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("id", customer.getCustomerNumber());
    parameters.put("lname", customer.getLastName());
    parameters.put("fname", customer.getFirstName());
    parameters.put("email", customer.getEmail());
    parameters.put("street", customer.getStreet());
    parameters.put("city", customer.getCity());
    parameters.put("state", customer.getState());
    parameters.put("zip", customer.getZip());
    this.jdbcTemplate.update(sql, parameters);

}

public void updateTime(String timeSent){
    String query = "UPDATE customers SET lastEmailed ='" + timeSent+"'";
//        String query = "UPDATE customers SET lastEmailed = ?";
//         String query = "INSERT INTO CUSTOMERS "
//        this.jdbcTemplate.
//        this.jdbcTemplate.executeppr
//        this.jdbcTemplate.update

//         jdbcTemplate.update("UPDATE customers SET lastEmailed=?",timeSent);
//        SqlParameterSource [] parameterSource =     SqlParameterSourceUtils.createBatch(this.getCustomerList().toArray());
//        this.jdbcTemplate.batchUpdate("INSERT INTO CUSTOMERS (id,lastEmailed) VALUES     (:customerNumber, " +timeSent + ")", parameterSource);

}
}

Answer 1

我简直不敢相信我怎么忽略了这一点，我在之前的一个函数中几乎已经有了它，但没有意识到我只需要在 map 中放入一个值。感谢所有花时间查看我的代码的人!

public void updateTime(String timeSent){
      String query = "UPDATE customers SET lastEmailed ='" + timeSent+"'";
     Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("lastEmailed", timeSent);
    this.jdbcTemplate.update(query,parameters);
 }