ResultSet closed issue in postgresql jdbc

Question

What I'm trying to achieve is to store public keys for each employee stored in the database. I fetch the employeeId of each employee and get the public key then simultaneously store the employeeid and the public key into another table in the database. Only the first insertion occurs.

    package com.global.keyutil;

    import java.security.KeyPair;
    import java.security.KeyPairGenerator;
    import java.security.NoSuchAlgorithmException;
    import java.security.PrivateKey;
    import java.security.PublicKey;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Base64;

    public class KeyUtil {

        static private KeyPair generateKeyPair() {
            KeyPair kp = null;
            try {
                KeyPairGenerator kpg = KeyPairGenerator.getInstance("RSA");
                kpg.initialize(2048);
                kp = kpg.generateKeyPair();

            } catch (NoSuchAlgorithmException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return kp;
        }

        static private String keyToString(PublicKey publicKey)
        {
            String b64PublicKey = null;
            try
            {
                System.out.println("converting public key to string : ");
                byte[] encodedPublicKey = publicKey.getEncoded();
                b64PublicKey = Base64.getEncoder().encodeToString(encodedPublicKey);
            }
            catch(Exception e)
            {
                System.out.println(e.getMessage());
            }
            return b64PublicKey;
        }

        static private Connection getConnection()
        {
            Connection connection = null;
            try {
                Class.forName("org.postgresql.Driver");
                connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/empris", "postgres","");
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return connection;
        }

        static private void fetchAndStoreKeys(Connection connection)
        {
            Statement stmt = null;
            try {
                stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery( "select employeeid from employee where name<> 'Reception' and employeestatusid<>3 and employeestatusid<>6 order by name;" );

                int i = 0;

                while ( rs.next() ) {
                    System.out.println("Employee Id : ");
                    int id = rs.getInt("employeeid");
                    System.out.println(++i+" : "+id);

                    KeyPair kp = generateKeyPair();
                    PublicKey pub = kp.getPublic();
                    PrivateKey pvt = kp.getPrivate();
                    System.out.println("public key : "+pub);
                    System.out.println("private key : "+pvt+"\n");


                    String b64PublicKey = keyToString(pub);
                    System.out.println(b64PublicKey+"\n");

                    String query = "insert into keys(employeeid,publickey) values("+id+",'"+b64PublicKey+"')";
                    stmt.executeUpdate(query);
                }
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        public static void main(String[] args)
        {
            Connection connection = getConnection();
            fetchAndStoreKeys(connection);
        }
    }

After the first insertion I get this message even though the result set isn't closed anywhere.

org.postgresql.util.PSQLException: This ResultSet is closed.
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkClosed(AbstractJdbc2ResultSet.java:2859)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.next(AbstractJdbc2ResultSet.java:1897)
    at com.global.keyutil.KeyUtil.generateAndStoreKeys(KeyUtil.java:72)
    at com.global.keyutil.KeyUtil.main(KeyUtil.java:100)

Would greatly appreciate your help and thanks in advance.

Answer 1

From the documentation for Statement:

All execution methods in the Statement interface implicitly close a current ResultSet object of the statement if an open one exists.

To workaround your problem, try doing the insert with a second statement. From inside the loop:

while (rs.next()) {
    // ...
    String query = "insert into keys(employeeid,publickey) values (?, ?);"
    PreparedStatement psInsert = connection.prepareStatement(query);
    psInsert.setId(1, id);
    psInsert.setString(2, b64PublicKey);
    psInsert.executeUpdate();
}

Note that I am using a prepared statement here, which allows us to bind query parameters without having to worry about formatting, escaping, etc. This is the preferred way to use JDBC. With regard to your select query, if you expect it to always be fixed, then there is nothing wrong with an ordinary statement, though consider using a prepared statement if you think it will need parameters at some later point.