title: How to create a mysql persistent connection from a java application?
body: <p>Hi I'm writing a java application which needs to create a persistent connection with a mysql database. I don't have access to the database server so I can't change anything on the database server so that it does not close connection.</p>
  
  <p>I am getting following error:</p>
  
  <pre><code>Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  
  The last packet successfully received from the server was 368,140 milliseconds ago.  The last packet sent successfully to the server was 366,869 milliseconds 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:409)
      at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:101)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:110)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:110)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:110)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:110)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:110)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:110)
      at SearchAndUpdate.recursiveUpdate(SearchAndUpdate.java:110)
      at SearchAndUpdate.recursiveTraversal(SearchAndUpdate.java:55)
      at SearchAndUpdate.main(SearchAndUpdate.java:40)
  Caused by: java.net.SocketException: Connection reset
      at java.net.SocketInputStream.read(Unknown Source)
      at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
      at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
      at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
      at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2499)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2952)
      ... 17 more
  </code></pre>
  
  <p>I am assuming that this message is telling me that the connection is being close automatically. I'm not sure create a persistent connection...I would really appreciate it if someone gives me some instructions in java code. Thanks so much in advance.</p>
  
  <p>following is my code which is creating a connnection:</p>
  
  <pre><code>import java.sql.*;
  
     public class Connect
     {
         public static void main (String[] args)
         {
             Connection conn = null;
  
             try
             {
                 String userName = "sdfasdfsf";
                 String password = "asdfasdfsd";
                 String url = "jdbc:mysql://nameOfcomputer:3306/databasename";
                 Class.forName ("com.mysql.jdbc.Driver").newInstance();
                 conn = DriverManager.getConnection (url, userName, password);
                 System.out.println ("Database connection established");
             }
             catch (Exception e)
             {
                 System.err.println ("Cannot connect to database server");
                 System.out.println(e.getMessage());
             }
  
         }
     }
  </code></pre>
  
  <p>I just found the following example online:</p>
  
  <p><a href="http://www.java2s.com/Tutorial/Java/0340__Database/KeeptheConnectionAliveforMySQL.htm" rel="nofollow">http://www.java2s.com/Tutorial/Java/0340__Database/KeeptheConnectionAliveforMySQL.htm</a></p>
  
  <p>would this be an example of a persistent connection?</p>
  
  <p>well my code is getting the connection by using the same code I posted and runs for over 4 hours updating a table in mysql. the application basically does a recursive search of a directory finding all the files and and getting the file's info and then enters the information into the database. the directory is searches contains more thanks 100,000 files. The connecton sometimes closes because while sometimes  the program takes a while to 
  recursively find a file, following is my code:</p>
  
  <pre><code>public class Search{
  
      public static void main(String[] args) throws SQLException 
      {
          File fileObject = new File("scanLocation");
          Connection conn = getConnection();
  
          update(fileObject, conn);           
          conn.close();
      }
  
      public static void update(File fileObject, Connection conn)throws SQLException 
      {   
          if (fileObject.isDirectory()) 
          {
              File allFiles[] = fileObject.listFiles();
              if (allFiles != null) 
              {
                  for (File aFile : allFiles) 
                  {               
                      if (aFile.isFile()) 
                      {
                          String filename=aFile.aFile.getName();
                          String query = "INSERT INTO table(" 
                                         + "File_Name" +")"
                                         +  " Values (?)";
                          try 
                          {   PreparedStatement psmt=conn.prepareStatement(query);
                                  psmt.setString(1, fileName);
  psmt.executeUpdate();
  
                          } 
                          catch (SQLException e) 
                          {
                              System.out.println("SQLExceptoin - " + e.getMessage());
                              System.out.println("SQLState: " + e.getSQLState());
                              System.out.println("VendorError: " + e.getErrorCode());
                              e.printStackTrace();
                          }
  
                      }
                      else
                      {
                          recursiveUpdate(aFile, conn);
                      }
                  }
              }
          }
      }
  
      public static Connection getConnection()
      {
          String DATABASE_USER = "user";
          String DATABASE_PASSWORD = "password";
          String MYSQL_AUTO_RECONNECT = "autoReconnect";
          String MYSQL_MAX_RECONNECTS = "maxReconnects";
  
          Connection conn = null;
          try 
          {
              String userName = "username";
              String password = "password";
              String url = "jdbc:mysql://compName:3306/database";
  
              Class.forName("com.mysql.jdbc.Driver").newInstance();
              conn = DriverManager.getConnection(url, userName, password);
  
              java.util.Properties connProperties = new java.util.Properties();
              connProperties.put(DATABASE_USER, userName);
              connProperties.put(DATABASE_PASSWORD, password);
  
              connProperties.put(MYSQL_AUTO_RECONNECT, "true");
  
              connProperties.put(MYSQL_MAX_RECONNECTS, "4");
              return DriverManager.getConnection(url, connProperties);
          } 
          catch (Exception e) 
          {
              System.err.println("Cannot connect to database server");
              System.out.println(e.getMessage());
              e.printStackTrace();
          }
          return conn;
      }
  
  
  }
  </code></pre>
  
  <p>The program sometimes completes and sometimes the database server force closes the connection? Since I do not have any access to the database server, what should I do so that the database server does not close the connection?</p>
  
  <p>Thanks so much</p>