Can one JDBC Statement be used at the same time?

Question

I was wondering if it is in theory possible that a single JDBC statement instance can be used at the same time if two users interact with the database simultaneously because when I try to create two result sets in one function and iterate through them at the same time, JDBC throws an exception that the first result set is closed, probably because they both use the same statement instance. That is how my code looks like for executing a query:

private String host;
private String database;
private String username;
private String password;
private int port;

private static Statement state;
private static Connection conn;

private void loadConfig() throws IOException {
    final File file = new File("../MySQL/config.yml");
    if (!file.exists()) {
        final YamlConfiguration yml = YamlConfiguration.loadConfiguration(file);
        yml.set("Host", (Object)"localhost");
        yml.set("Port", (Object)3306);
        yml.set("Database", (Object)"database");
        yml.set("User", (Object)"root");
        yml.set("Password", (Object)"password");
        yml.save(file);
    }
    reloadConfig();
    final YamlConfiguration yml = YamlConfiguration.loadConfiguration(file);
    host = yml.getString("Host");
    port = yml.getInt("Port");
    database = yml.getString("Database");
    username = yml.getString("User");
    password = yml.getString("Password");
}

public void openConnection() throws SQLException {
    if (conn != null && !conn.isClosed()) {
        return;
    }
    conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database + "?autoReconnect=true", username, password);
}

public static ResultSet executeQuery(final String query) {
    try {
        return state.executeQuery(query);
    }
    catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

Can this error occur now also when two users execute a query at the exact same time, ie when another query is started while the last one is still iterating?

This is explicitly mentioned in the JDBC API documentation. Also note that objects like `Connection` or `Statement` should rarely be static (or even an instance field), especially when used concurrently. — Mark Rotteveel, Jul 18 '20 at 14:26

score 1 · Answer 1 · edited Jul 18 '20 at 14:31

A Statement object can have only one result set open at a time.

This is documented in the Statement class description:

All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

As for:

Can this error occur now also when two users execute a query at the exact same time,

You should use different Statement objects for the different users. You wouldn't want one user see the results of the queries of the other user.

Can one JDBC Statement be used at the same time?

1 Answers1