时间戳

首页 > 解决方案 > 两个查询并行关闭 Statement 和 ResultSet 分别导致竞争条件

java - 两个查询并行关闭 Statement 和 ResultSet 分别导致竞争条件

问题描述

我有两个并行运行的查询从我的远程数据库中获取数据

public CompletableFuture<ObservableList<Appointment>> getAppointments(){
        return CompletableFuture.supplyAsync(() -> {
            return queryAppointments();
        });
    }

    public CompletableFuture<ObservableList<Customer>> getCustomers(){
        return CompletableFuture.supplyAsync(() -> {
           return queryCustomers();
        });
    }

但是，这会导致竞争条件，因为在每个查询结束时，我都会关闭ResultSet，Statement所以有时，并非总是如此，一个查询没有完成，并且ResultSet在第二个查询完成之前关闭，并给我一个异常：

java.sql.SQLException：ResultSet 关闭后不允许操作

第一个查询：

public ObservableList<Appointment> queryAppointments() {  
        ObservableList<Appointment> listAppointments = FXCollections.observableArrayList();
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            statement = ConnectionManager.getConnection().createStatement();
            resultSet = statement.executeQuery(countriesQuery);
            while (resultSet.next()) {
                int appointmentId = resultSet.getInt("Appointment_ID");
                String title = resultSet.getString("Title");
                String description = resultSet.getString("Description");
                
                //Omitted Code for creating an appointment object here...

                listAppointments.add(appointment);
            }
            return listAppointments;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            ConnectionManager.closeConnection();
            ConnectionManager.closeResultSetAndStatement(resultSet, statement);
        }
    }

第二个查询：

public ObservableList<Customer> queryCustomers() {
        ObservableList<Customer> listCustomers = FXCollections.observableArrayList();
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            statement = ConnectionManager.getConnection().createStatement();
            resultSet = statement.executeQuery(customersQuery);
            while (resultSet.next()) {
                int customerId = resultSet.getInt("Customer_Id");
                String name = resultSet.getString("Customer_Name");
                
                //Omitted code for creating customer object here

                listCustomers.add(customer);
            }

            return listCustomers;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            ConnectionManager.closeConnection();
            ConnectionManager.closeResultSetAndStatement(resultSet, statement);
        }

    }

public class ConnectionManager {

    private static Connection connection;

    public static Connection getConnection() {
        try {
            connection = DriverManager.getConnection(Constants.CONNECTION_URL);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeConnection(){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeResultSetAndStatement(ResultSet resultSet, Statement statement){
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException sqlEx) { }
            resultSet = null;
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException sqlEx) { }
            statement = null;
        }
    }

我应该如何处理比赛条件？我尝试过考虑一种解决方案，该解决方案将一个接一个地执行一个查询，而不是并行运行它。

public static void main(String[] args) {
        mainRepository.getAppointments().thenAccept(retrievedAppointments -> {
            
             mainRepository.getCustomers().thenAccept(customers -> {
            
             });
        });

       
}

但是在第二次查询期间，ResultSet 也不能​​被切断吗？在做我想要完成的事情时有更好的解决方案吗？

标签： javajdbcmysql-connectorcompletable-future

解决方案

你用flag怎么样？ResultSet并且Statement仅当标志为0时才关闭？

例如：

AtomicInteger flag = new AtomicInteger(0);

public ObservableList<Customer> queryCustomers() {
        flag.incrementAndGet(); // Flag is 2/1 now..
        ...
        // In your finally part
        int currFlag = flag.decrementAndGet();
        if(currFlag == 0){
            //close everything
        }
    }

public ObservableList<Appointment> queryAppointments() {  
        flag.incrementAndGet(); // Flag is 2/1 now..
        ...
        // In your finally part
        int currFlag = flag.decrementAndGet();
        if(currFlag == 0){
            //close everything
        }
    }

但是，@Holger 的评论似乎更重要......

推荐阅读