ResultSetImpl error when implementing HIkariCP

Question

I am trying to implement the HikariCP in my program. I have a main class:

public class ServerMain {
final static int nPort = 8888;
private HikariDataSource DS = null;

public static void main(String[] args) throws IOException {
    new ServerMain();
}

public ServerMain(){
    DS = dsInitiate();

    try {
        ServerSocket sSocket = new ServerSocket(nPort);
        //Loop that runs server functions
        while(true) {
            //Wait for a client to connect
            Socket socket = sSocket.accept();
            socket.setSoTimeout(30000);

            //Create a new custom thread to handle the connection
            ClientThread cT = new ClientThread(socket, nPort);
            //Start the thread!
            new Thread(cT).start();  
        }
    } 
    catch(IOException ex) {ex.printStackTrace();}
}

private static HikariDataSource dsInitiate(){
    HikariConfig config = new HikariConfig();
    config.setMaximumPoolSize(3);

    config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
    config.addDataSourceProperty("databaseName", "XXX");
    config.addDataSourceProperty("user", "XXX");
    config.addDataSourceProperty("password", "XXX");
    config.addDataSourceProperty("useSSL", "false");

    HikariDataSource ds = new HikariDataSource(config);

    return ds;
}}

This class manages the client connections and opens a new thread for each client. Inside my main class i have the ClientThread class:

class ClientThread implements Runnable{
    public void run(){
        /// some code .....
    }

    private byte[] getFile(LocalDateTime ldt) {
        ldt = ldt.plusSeconds(17); // 17 leap seconds
        ldt = ldt.minusSeconds(3*3600); // 3 hours between UTC and GPS time
        try {
            Connection conn = DS.getConnection();
            CG.Generate(ldt, conn);
        } catch (SQLException ex) {ex.printStackTrace();}

        ///// some code .....
    }}

When invoking the getFile method within the ClientThread class, a connection is borrowed from the DS object. That connection is passed as a variable into the Generate method of the CG object. The CG object is an instance of the next class:

public class Corr_Gen{
//// some variable ....

public void Generate(LocalDateTime LDT, Connection conn){
    MainDB DB = new MainDB();
    DB.setConn(conn);
    DB.createSTMT();    

    for (int sn = 1; sn < 33; sn++){
        String Q = "SELECT IODE FROM Navigation WHERE SV = "+sn+" ORDER BY Date DESC;";
        ResultSet rs = DB.execQuery(Q);
        try {
            if (rs.next()){
                int tmp = rs.getInt(1); /// some code ....
            }
        } catch (SQLException ex) {ex.printStackTrace();}
    }

    DB.closeSTMT();
    DB.closeConn();
}}

Inside the Generate method the connection instance is passed into the MainDB class where a statement is created. Using this statement i am trying to execute a query. As you can see, i am closing the statement and the connection after i end up using it.

The problem is that when i am trying to run more than 1 thread for the ClientThread class, i am getting an error while trying do something with the result set. this is the error: Exception in thread "Thread-0" java.lang.NullPointerException at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:766) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2477) at com.zaxxer.hikari.proxy.HikariResultSetProxy.getInt(HikariResultSetProxy.java) at dummyTest.Corr_Gen.Generate_v2(Corr_Gen.java:37) at Exo_Ntrip_Server.ServerMain$ClientThread.getFile(ServerMain.java:131) at Exo_Ntrip_Server.ServerMain$ClientThread.response(ServerMain.java:247) at Exo_Ntrip_Server.ServerMain$ClientThread.run(ServerMain.java:107) at java.lang.Thread.run(Thread.java:745)

This error happens only when running more than one thread.

What am i doing wrong? Am i using the connection pool wrong? What am i missing? Any help would be highly appreciated.

Answer 1

NPE occurs at this line of MySQL ResultSetImpl class:

        } else if ((columnIndex > this.fields.length)) {

it means that this.fields is null during reading from this ResultSet. It looks like it's closed somewhere else in the code.

Without code of MainDB class it will be hard to reproduce this issue. Exception looks like statement or result set is closed in other thread during reading from ResultSet in execution of loop. Are you sure that connection and statement instances are private, instance members of MainDB class?

However, i suggest to change your code to this:

for (int sn = 1; sn < 33; sn++){
    DB.createSTMT(); // make sure that connection and statement are private only for this instance DB
    String Q = "SELECT IODE FROM Navigation WHERE SV = "+sn+" ORDER BY Date DESC;";
    ResultSet rs = DB.execQuery(Q);
    try {
        if (rs.next()){
            int tmp = rs.getInt(1); /// some code ....
        }
    } catch (SQLException ex) {ex.printStackTrace();}
    finally {
      DB.closeSTMT(); //make sure that this method closes correct statement and / or connection
    }
}

Answer 2

I suspect methods in your MainDB class. particularly execQuery which must return new instance of result every time. also If you can, 1. use try-with-resources everywhere. 2. keep opening and closing of resource (connection, statement, resultset) in same method.