JDBC : Unable to connect second time

Question

Hi All I am trying to connect a data base. below i have given the code. I am able to connect to data base but unable to connect 2nd time if difference between 1st time and 2nd time is greater then "wait time out" of mysql data base. I am using mysql data base. I am getting "Communications link failure" problem wait_timeout is 10 sec (for convenience) it is 8 hour for my DB I am giving you code

public class DatabaseManager {
 private static Connection jConn;
 private static DatabaseManager manager;

public Boolean getUser(){
  Statement jStmt = null;
  ResultSet rs = null;
  try {

   String query = "SELECT * FROM  USER  ";
    System.out.println(query);
   jStmt = jConn.createStatement();
   rs = jStmt.executeQuery(query);
   while (rs.next()) {

   }
  }catch (Exception e) {
   e.printStackTrace();
   return false;
  }
  return true;
 }

    public static synchronized DatabaseManager getInstance() {
  if (manager == null) {
   try {
    manager = initialize();
   } catch (DatabaseInitializationException e) {
    // e.printStackTrace();
    // throw new RuntimeException("Database Initialization
    // Exception");
   }
  }
  return manager;
 }
        private static DatabaseManager initialize()
   throws DatabaseInitializationException {

  try {
   Class.forName(serverSrv.readPropertyValue("Srver_Path",
     "DBdriver"));
  } catch (ClassNotFoundException e) {
   throw new DatabaseInitializationException(e.getMessage());
  }
  try {
   jConn = DriverManager.getConnection("DB_URL","user","password");
   System.out.println("Excecuted initialize() for Database ");
  } catch (SQLException e) {
   throw new DatabaseInitializationException(e.getMessage());
  }
  return new DatabaseManager();
 }
}

AND I am testing like

 public static void main(String[] args) {
  long st = System.currentTimeMillis();
  long end ;
  DatabaseManager dm = DatabaseManager.getInstance();

  if( dm.getUser() )
   System.out.println("success");
  else
   System.out.println("failed..");

  do{
   end = System.currentTimeMillis();
  }while( (end-st)<12*1000 );

  if( dm.getUser() )
   System.out.println("success");
  else
   System.out.println("failed..");
 }

and it is giving exception like

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
 at java.lang.reflect.Constructor.newInstance(Unknown Source)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
 at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2873)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2763)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3299)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2466)
 at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383)
 at mypackage.db.DatabaseManager.getUser(DatabaseManager.java:127)
 at mypackage.test.Test.main(Test.java:23)
Caused by: java.net.SocketException: Software caused connection abort: recv failed
 at java.net.SocketInputStream.socketRead0(Native Method)
 at java.net.SocketInputStream.read(Unknown Source)
 at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
 at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
 at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
 at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2329)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2774)

"Srver_Path" -> is that a typo? – darioo Dec 06 '10 at 08:28 — darioo, Dec 06 '10 at 08:28

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

Perhaps you should close the ResultSet as well as Statement.

This link has some tips on doing this correctly.

edited May 23 '17 at 10:33

Community

1
1

answered Dec 06 '10 at 08:36

Raghuram

51,854
11
110
122

Srver_Path is not a typo problem it is locating the mysql server – user531260 Dec 06 '10 at 09:14

score 1 · Answer 2 · answered Dec 06 '10 at 08:45

1

Your problem is that you are trying to reuse jConn which closes automatically after a timeout. So when you try and use it again an exception is thrown. Depending on what you are trying to do i would suggest (for simplicity) ..

Closing jConn by typeing jConn.close();
Then When you need to use the db again reconnect using jConn = DriverManager.getConnection("DB_URL","user","password");

answered Dec 06 '10 at 08:45

Paul

4,812
3
27
38

I'd try closing the connection and re-opening. If that works, then your connection is being automatically closed on you. If it doesn't work, then ... we move on to the next level. – Jay Dec 06 '10 at 19:48
@user531260 - considering you replied below, i will answer your question here. If performance is a concern you will need to look at database pooling, for example http://commons.apache.org/dbcp/ or http://sourceforge.net/projects/c3p0 – Paul Dec 06 '10 at 19:58

score 0 · Answer 3 · answered Dec 06 '10 at 08:32

0

Check your connection time out settings.

answered Dec 06 '10 at 08:32

lalit

1,485
2
17
32

JDBC : Unable to connect second time

3 Answers3