我正在尝试创建一个函数来更新数据库中的每个客户,即customers(lastEmailed列),并且我已经尝试使用不同的查询输入和执行方法(update,batchupdate等)多次,但我没有运气。这是我经过修改后的切碎/注释代码的样子。任何建议将不胜感激,谢谢!此外,输入样式的格式为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);
}
}
最佳答案
我简直不敢相信我会忽略它,我在以前的功能之一中就已经拥有了它,但是却没有意识到我只需要在地图上放置一个值即可。感谢所有花时间查看我的代码的人!
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);
}