Java Mysql Exception

Question

I'm getting an exception in my query for inserting data into my database, i am inserting a lot of data into my database around 15k lines. this is the exception message:

The driver was unable to create a connection due to an inability to  establish the client portion of a socket.This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable.

my function for the insert query:

public static void insertObject(int modelId,float x,float y,float z,float rx,float ry,float rz){

     try
        {
         con = DriverManager.getConnection(url, user, password);
          String query = " insert into wastobjects (modelid,x,y,z,rx,ry,rz)"
            + " values (?, ?, ?, ?, ?, ?, ?)";
          PreparedStatement preparedStmt = con.prepareStatement(query);
          preparedStmt.setInt (1, modelId);
          preparedStmt.setFloat (2, x);
          preparedStmt.setFloat (3, y);
          preparedStmt.setFloat (4, z);
          preparedStmt.setFloat (5, rx);
          preparedStmt.setFloat (6, ry);
          preparedStmt.setFloat (7, rz);

          preparedStmt.execute();
          con.close();
        }
        catch (Exception e)
        {
          System.err.println("Got an exception!");
          System.err.println(e.getMessage());
        }
}

You're running out of socket connections. You're creating a new connection with every row. You should either reuse your connection, or you can open up more sockets, with is an operating system command. Look up max socket connections. You should be aware that each socket connection creates a small file, so there are resource constraints, but you can create a lot of connections if you increase the amount. — CargoMeister
– CargoMeister, Commented Sep 29, 2015 at 23:40
i actually tough creating a new connection each time was a bad idea, is there anyway i can reuse the same connection? — Lorenzo
– Lorenzo, Commented Sep 29, 2015 at 23:42

Tiago Mussi · Accepted Answer · 2015-09-30 17:42:39Z

1

You can reuse your connection instead of creating a new one each time you need to insert a new row.

And try to close the prepared statement and the connection in a finally block.

For example:

Connection con = null;
PreparedStatement ps = null;
try {
    // your code here.
    ps.execute();
    con.commit();
} catch (Exception e){

} finally {
    closeResource(ps)
    closeResource(con);
}

Your new method:

private void closeResource(AutoCloseable resource) {
    if (resource != null) {
        try {
            resource.close();
        } catch (Exception e) {
        }
    }
}

That's why when you get an exception the resources won't close and you will reach the limit of sockets.

edited Sep 30, 2015 at 17:42

answered Sep 29, 2015 at 23:50

Tiago Mussi

8053 gold badges13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guilherme Santos Over a year ago

public void closeResources(AutoCloseable resource) { if (resource == null) { return; } try { resource.close(); } catch (Exception e) { System.err.println(e.getMessage()); } }

user207421 · Accepted Answer · 2015-09-29 23:57:42Z

0

Close the connection in a finally block.
Don't use a new connection per row. Use the same connection.
In fact you can use the same PreparedStatement if you use batches.

answered Sep 29, 2015 at 23:57

user207421

312k45 gold badges324 silver badges493 bronze badges

Collectives™ on Stack Overflow

Java Mysql Exception

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related