如何在单个数据库调用中执行删除和选择操作?

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

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(&quot;/remove-todo&quot;)
    public ResponseEntity&lt;?&gt; removeTodo(@RequestParam(&quot;todo_id&quot;) String id, Authentication authentication) {
        SecurityUser user = (SecurityUser) authentication.getPrincipal();
        todoDAO.deleteTodo(id, user.getUserId())
        List&lt;Todo&gt; todos = todoDAO.getTodosByUserId(user.getUserId());
        return todos;
    } 

deleteTodo and getTodosByUserId

    @Override
    public Todo deleteTodo(String id, String userId) {
        String sql = &quot;delete from todo where id = ? and userId = ? returning *&quot;;
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{id, userId}, new TodoMapper());
        }catch (EmptyResultDataAccessException e){
            System.out.println(&quot;NO ResultSet Found&quot;);
            return null;
        }
    }
    @Override
    public List&lt;Todo&gt; getTodosByUserId(String userId) {
        return jdbcTemplate.query(&quot;select * from todo where userId = ?&quot;,
                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

你可以将deletelist方法合并成一个单一的方法。建议使用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 &amp; DELETE.

public List&lt;Todo&gt; deleteTodo(String id, String userId) {
    String sql = &quot;delete from todo where id = ? and userId = ?&quot;;
    try {
        if (jdbcTemplate.update(sql, new Object[]{id, userId}, new TodoMapper()) == 1) {
            return jdbcTemplate.query(&quot;select * from todo where userId = ?&quot;,
                new TodoMapper(), userId);
        }
    } catch (EmptyResultDataAccessException e){
        System.out.println(&quot;NO ResultSet Found&quot;);
        return new ArrayList&lt;&gt;();
    }
}

huangapple
  • 本文由 发表于 2020年7月21日 19:13:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/63013260.html
匿名

发表评论

匿名网友

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

确定