JDBC在一个请求中选择和更新

Question

ID varchar2() primary key, 
STATUS varchar2(),
....

我需要选择状态为'Delayed'的行，同时设置状态为'Progress'。

有没有使用JDBC的方法？

Answer 1

由于您不能直接在JDBC中完成此操作，因此可以使用以下代码片段分两步完成此操作，希望对您有帮助：

这里我们应该使用jdbc：oracle作为驱动程序，使用update..set..where语法。

首先，您必须调用updateTable（）将所有延迟行更改为PROGRESS，然后调用getAllDelayedObjects（）将所有延迟行作为列表获取。

public class DataAccess {
    private Connection connection;
    private Statement statement;

    public boolean updateTable() throws SQLException {
        openConnection();
        try (PreparedStatement statement=
                connection.prepareStatement("update yourTableName set STATUS=? where STATUS=?")
        ) {
            statement.setString(1, "DELAYED");
            statement.setString(2, "PROGRESS");
            
            if (statement.executeUpdate()>0) {
                closeConnection();
                return true;
            }
            else {
                closeConnection();
                return false;
            }
        }

        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }


    public List<YourEntityClass> getAllDelayedObjects() throws SQLException {
        openConnection();
        try (
                PreparedStatement statement = connection.prepareStatement("select * from users where STATUS=?")
        ) {
            
            statement.setString(2, "PROGRESS");
            ResultSet resultSet = statement.executeQuery();

            List<YourEntityClass> entityObjects = new ArrayList<>();

            while (resultSet.next()) {
                YourDaType object = new YourDaType();
                entityObject.setId(resultSet.getInt("ID"));
                entityObject.setStatus(resultSet.getString("STATUS"));
                // and whatever more properties you have in the table

                entityObjects.add(object);
            }
            closeConnection();
            return entityObjects;
        }
        catch (Exception e) {
            closeConnection();
            e.printStackTrace();
            return null;
        }
    }

    public void openConnection() throws SQLException {
        String username = "yourUserNameForDB";
        String pass = "yourPasswordForDB";
        connection = DriverManager.getConnection("jdbc:oracle://yourDBdomain:yourDBport/yourDataBaseName", username, pass);
    }

    public void closeConnection() throws SQLException {
        this.connection.close();
    }
}