英文:
How to make delete and select in a single database call?
问题
我正在开发一个待办事项应用程序。我的删除映射如下所示：
@DeleteMapping("/remove-todo")
public ResponseEntity<?> removeTodo(@RequestParam("todo_id") String id, Authentication authentication) {
SecurityUser user = (SecurityUser) authentication.getPrincipal();
todoDAO.deleteTodo(id, user.getUserId());
List<Todo> todos = todoDAO.getTodosByUserId(user.getUserId());
return todos;
}
deleteTodo 和 getTodosByUserId 方法如下：
@Override
public Todo deleteTodo(String id, String userId) {
String sql = "delete from todo where id = ? and userId = ? returning *";
try {
return jdbcTemplate.queryForObject(sql, new Object[]{id, userId}, new TodoMapper());
} catch (EmptyResultDataAccessException e) {
System.out.println("NO ResultSet Found");
return null;
}
}
@Override
public List<Todo> getTodosByUserId(String userId) {
return jdbcTemplate.query("select * from todo where userId = ?",
new TodoMapper(), userId);
}
我正在进行两次数据库调用，首先删除待办事项，然后选择所有用户的待办事项。如何将这两个方法合并为单个查询？我是否可以使用 batchUpdate 完成这个任务？
英文:
I am developing todo application. My delete mapping looks as below:
@DeleteMapping("/remove-todo")
public ResponseEntity<?> removeTodo(@RequestParam("todo_id") String id, Authentication authentication) {
SecurityUser user = (SecurityUser) authentication.getPrincipal();
todoDAO.deleteTodo(id, user.getUserId())
List<Todo> todos = todoDAO.getTodosByUserId(user.getUserId());
return todos;
}
deleteTodo and getTodosByUserId
@Override
public Todo deleteTodo(String id, String userId) {
String sql = "delete from todo where id = ? and userId = ? returning *";
try {
return jdbcTemplate.queryForObject(sql, new Object[]{id, userId}, new TodoMapper());
}catch (EmptyResultDataAccessException e){
System.out.println("NO ResultSet Found");
return null;
}
}
@Override
public List<Todo> getTodosByUserId(String userId) {
return jdbcTemplate.query("select * from todo where userId = ?",
new TodoMapper(), userId);
}
I am making two database calls first for deleting todo and then selecting all user todos.
How can I combine these two methods into single query? Can I do it with batchUpdate?
答案1
得分: 1
你可以将delete和list方法合并成一个单一的方法。建议使用jdbcTemplate.update方法来执行INSERT, UPDATE和DELETE操作。
public List<Todo> deleteTodo(String id, String userId) {
String sql = "delete from todo where id = ? and userId = ?";
try {
if (jdbcTemplate.update(sql, new Object[]{id, userId}, new TodoMapper()) == 1) {
return jdbcTemplate.query("select * from todo where userId = ?",
new TodoMapper(), userId);
}
} catch (EmptyResultDataAccessException e){
System.out.println("没有找到结果集");
return new ArrayList<>();
}
}
英文:
You can combine delete and list method into a single method. jdbcTemplate.update method is recommended for INSERT, UPDATE & DELETE.
public List<Todo> deleteTodo(String id, String userId) {
String sql = "delete from todo where id = ? and userId = ?";
try {
if (jdbcTemplate.update(sql, new Object[]{id, userId}, new TodoMapper()) == 1) {
return jdbcTemplate.query("select * from todo where userId = ?",
new TodoMapper(), userId);
}
} catch (EmptyResultDataAccessException e){
System.out.println("NO ResultSet Found");
return new ArrayList<>();
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论