Semi-Multi threaded JDBC connection

Question

I have the following Callable instance where the SQLException is thrown here:

public long[] call() throws Exception {
    long[] stats = new long[6];
    try {
        executer.acquire();
        PreparedStatement statement =
            connection
                .prepareStatement("SELECT `War`.`EndTime` FROM `WarSim`.`War` WHERE `War`.`WarName` = ?");
        statement.setString(1, warName);
        ResultSet res = statement.executeQuery(); //<--------------SQLEXCEPTION HERE
        if (res.first()) {
        Timestamp ts = res.getTimestamp("EndTime");
        if (ts != null)
            stats[0] = 1;
        statement =
            connection
                .prepareStatement("SELECT COUNT(`ID`) FROM `Missile` WHERE `WarName` = ?");
        statement.setString(1, warName);
        res = statement.executeQuery();
        stats[1] = res.getInt(1);
        statement =
            connection
                .prepareStatement("SELECT COUNT(`ID`) FROM `Missile` WHERE `WarName` =  ?  AND `Intercepted` = '1'");
        statement.setString(1, warName);
        res = statement.executeQuery();
        stats[2] = res.getInt(1);
        stats[3] = stats[1] - stats[2];
        statement =
            connection
                .prepareStatement("SELECT COUNT(`ID`) FROM `EnemyLauncher` WHERE `WarName` = ? AND `Intercepted` = '1'");
        statement.setString(1, warName);
        res = statement.executeQuery();
        stats[4] = res.getInt(1);
        statement =
            connection
                .prepareStatement("SELECT SUM(`Damage`) FROM `Missile` WHERE `WarName` =  ? AND `Intercepted` = '0'");
        statement.setString(1, warName);
        res = statement.executeQuery();
        stats[5] = res.getInt(1);
        }
    } catch (SQLException e) {
        System.out.println(warName + " is problematic");
        while (e != null) {
        System.out.println("\tmsg: " + e.getMessage()+
                       "\n\tstate: " + e.getSQLState());
        e = e.getNextException();
        }
    } catch (InterruptedException e) {
        e.printStackTrace();
    } finally {
        executer.release();
    }
    return stats;
    }

executer is a single-permit, fair semaphore I use.

When I debug the code, everything works perfectly (no exceptions), yet when I run the program "normally" I get the "start of result set" SQLException thrown along with SQLState S1000.

How come I get exceptions even though I used a semaphore to acquire the mutex to query?

Help please :)

EDIT: here's the stack trace.

java.sql.SQLException: Before start of result set

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
    at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2672)
    at db.jdbc.GetWarStatsTask.call(GetWarStatsTask.java:37)
    at db.jdbc.GetWarStatsTask.call(GetWarStatsTask.java:1)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

The class handling the Connection to the database:

public class JDBCConnection implements DBConnection {

    private Connection connection;
    private String dbUrl;
    private Semaphore executer;
    private ExecutorService es;
    private static JDBCConnection instance;

    public static JDBCConnection getInstance() {
    if (instance == null) {
        instance = new JDBCConnection();
    }
    return instance;
    }

    private JDBCConnection() {
    dbUrl = "jdbc:mysql://---------/WarSim";
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        connection =
            DriverManager.getConnection(dbUrl, "------", "-------");
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        while (e != null) {
        System.out.println(e.getMessage());
        e = e.getNextException();
        }
    }
    es = Executors.newCachedThreadPool();
    executer = new Semaphore(1, true);
    }

    public Future<long[]> getWarStats(String warName) {
    return es.submit(new GetWarStatsTask(executer, connection, warName));
    }

    public void closeDB() {
    try {
     if (connection != null) {
        connection.close();
     }
    } catch (Exception e) {
        System.out.println("Could not close the current connection.");
        e.printStackTrace();
    }
}

Answer 1

Basically you are positioning the cursor before the first row and then requesting data. You need to move the cursor to the first row.

So first call result.next();

Answer 2

By the use of the semaphore, you understand the JDBC rule that a connection should only be in use by a single thread at a time. However, there are other threads that you need to be aware of, namely the garbage collector thread.

In this case, you need to close all the statement objects that are created before releasing the semaphore. If you don't close the statement objects, they will be closed by the garbage collector thread at an indeterminate time, causing unusual behavior on the connection.

So, before you prepare a new statement object, you need to close the old one.

---

statement.close()

statement = connection .prepareStatement("SELECT COUNT(ID) FROM Missile WHERE WarName = ?");

---

At the end you need to close the statement.

---

stats[5] = res.getInt(1);

statement.close()

---

You may want to get in the practice of running "findbugs" on your code. I think that may have spotted the problem of not closing the Statement object.