CODEWITHSUNDEEP
javamysqljdbcmysql-error-1064
user3808021
user3808021

Reputation:

Error: Data source rejected establishment of connection, message from server: "Too many connections"

I have a text file that it's size is not so heavy (600KB) and my code should read this file and send whole contains to my database:

    private static void readBigFileAndSendToDB() throws Exception {
    Scanner s = new Scanner(new FileReader("tf1.txt"));
    while (s.hasNextLine()) {
        String eachLine = s.nextLine();   // each Line
        sendBigFileToDB(eachLine);
    }                                    // (end of file).
    System.out.println("Sent big file to DB");
    s.close();
}


    private static void sendBigFileToDB(String line) {
    Connection con = null;
    PreparedStatement ps = null;
    String query = "Insert into BigFile(text) values ('" + line + "')";

    try {
        if (line != null) {
            con = DriverManager.getConnection(...);
            ps = con.prepareStatement(query);
            ps.execute();
        }
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    }
}

But when i run program, This exception has been occur:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

Upvotes: 1

Views: 8780

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109077

When you acquire a connection, you also need to close it. Not closing the connection leads to resource leaks and this might eventually lead to the server refusing connections as the maximum number of connections has been reached.

I'd suggest that you change your code to use try-with-resources so your resources are closed automatically when their scope of use ends:

if (line == null) return;
String query = "Insert into BigFile(text) values (?)";

try (
    Connection con = con = DriverManager.getConnection(...);
    PreparedStatement ps = con.prepareStatement(query);
){
    ps.setString(1, line);
    ps.executeUpdate();
} catch (SQLException sqle) {
    sqle.printStackTrace();
}

Note that I have also replaced your concatenation of line into the query with the proper use of a prepared statement parameter.

Your code could be further improve by moving connection creation and statement preparation out into your readBigFileAndSendToDB so it is only done once and doesn't incur the overhead at each iteration:

String query = "Insert into BigFile(text) values (?)";

try (
    Scanner s = new Scanner(new FileReader("tf1.txt"));
    Connection con = con = DriverManager.getConnection(...);
    PreparedStatement ps = con.prepareStatement(query);
){
    while (s.hasNextLine()) {
        String line = s.nextLine();
        if (line == null) continue;
        ps.setString(1, line);
        ps.addBatch();
    }
    ps.executeBatch();
} catch (SQLException sqle) {
    sqle.printStackTrace();
}

Upvotes: 2

Vikram Jain
Vikram Jain

Reputation: 5588

Please, close statement and connection close, after finished process:

private static void readBigFileAndSendToDB() throws Exception {
    Scanner s = new Scanner(new FileReader("tf1.txt"));
    while (s.hasNextLine()) {
        String eachLine = s.nextLine();   // each Line
        sendBigFileToDB(eachLine);
    }                                    // (end of file).
    System.out.println("Sent big file to DB");
    s.close();
}


    private static void sendBigFileToDB(String line) {

    PreparedStatement ps = null;
    String query = "Insert into BigFile(text) values ('" + line + "')";

    try {
        if (line != null) {
            con = DriverManager.getConnection(...);
            ps = con.prepareStatement(query);
            ps.execute();               
        }
    } catch (SQLException sqle) {
        sqle.printStackTrace();
    }
    finally {
        if (ps != null) try { ps .close(); } catch (SQLException logOrIgnore) {}
        if (con != null) try { con .close(); } catch (SQLException logOrIgnore) {}
        }
     }

Upvotes: 0

Related Questions