java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found, too many open files

This exception occurs when too many Connections are open at the same time. The following code generates such an SQLException.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
    String url = "jdbc:oracle:thin:@<host>:<port>";
    String driver = "oracle.jdbc.OracleDriver";
    String user = "<userId>";
    String passwd = "<password>";
 
    public Connection getConnection() throws Exception {
        System.out.println("getting Connection");
        Connection conn = null;
     
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, passwd);
     
        return conn;
    }
 
    public static void main(String[] args) {
        Test test = new Test();
        Connection conn = null;
        Statement stm = null;
        String sql = "select distinct NAME from FRUIT";
     
        try {
            conn = test.getConnection();

            //Exception occurs in this loop
            while(true){
                conn = test.getConnection();
                stm = conn.createStatement();
                ResultSet rs = stm.executeQuery(sql);
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
                rs.close();
                stm.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

The while(true) loop above keeps creating Connections and not closing them, therefore, gives such a SQLException.

To fix this problem

Move the "conn = test.getConnection();" out of the while(true) loop and close it after quering database is finished. The code would look like this.

           conn = test.getConnection();
           while(true){              
                stm = conn.createStatement();
                ResultSet rs = stm.executeQuery(sql);
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
                rs.close();
                stm.close();
            }
            conn.close();

If you have to create the Connection inside the loop and are not able to move it out, you, then, have to close it each time when it's job is finished.

            while(true){
                conn = test.getConnection();
                stm = conn.createStatement();
                ResultSet rs = stm.executeQuery(sql);
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
                rs.close();
                stm.close();
                conn.close();
            }

----------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.