Java - MySQL concurrency solution needed

Question

I am developing a java application that uses MySQL to write and read some data. After a while (very long time) my jdbc connection closes, i was reading on many forums and i still cannot make it last forever.

What i want to do is the next ting: After 5,6,24 hours (still don't know how many hours) i will close the connection and connect again. The problem is that if another thread tries to use that connection to write something it will fail on exception. So i want to do is the next thing if the jdbc connection is restarting all the threads that need to use the jdbc wait until the reconnect is finished. Also i don't want to block the thread if another thread uses that connection, only if is restarting. Also i am afraid of deadlocks :).

I was reading about java concurrency and how to manage it from many forums and still don't know what to use and how to use it.

Useful forums:

http://www.vogella.com/tutorials/JavaConcurrency/article.html

http://en.wikipedia.org/wiki/Java_concurrency

http://tutorials.jenkov.com/java-concurrency/index.html

Edit:

To make it more clear what i want to do

Java Database Class:

    public class Database {

        //Locks
        final Object readLock = new Object();
        final Object writeLock = new Object();
        final Object readWriteLock = new Object();
        boolean enableReadLock = false;
        boolean enableWriteLock = false;
        //Database parametars
        String user; //DB username /корисник на базата
        String password; //DB password /лозинка за базата
        String dbname; //DB name / име на базата
        String ip; //DB Server IP address / адреса на серверот каде е базата
        Connection connection; //mysql connection /


        public Database(String user, String password, String dbname, String ip) throws ClassNotFoundException {
            Class.forName("com.mysql.jdbc.Driver");
            this.user = user;
            this.password = password;
            this.dbname = dbname;
            this.ip = ip;
        }


        public Database(String user, String password, String dbname) throws ClassNotFoundException {
            Class.forName("com.mysql.jdbc.Driver");
            this.user = user;
            this.password = password;
            this.dbname = dbname;
            this.ip = "localhost";
        }


        public void setReadLock(boolean enable) {
            enableReadLock = enable;
        }

        /**
         * Enable or disable write lock
         *
         * @param enable true -> enable , false -> disable
         */
        public void setWriteLock(boolean enable) {
            enableWriteLock = enable;
        }

        /**
         * Enable or disable read-write lock
         *
         * @param enable true -> enable , false -> disable
         */
        public void setReadWriteLock(boolean enable) {
            enableReadLock = enable;
            enableWriteLock = enable;
        }

        public void reconnect() throws SQLException {
             connection.close();
             connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbname, user, password);
    }

        public void connect() throws SQLException {
            connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbname, user, password);
        }


        public void disconnect() throws SQLException {
            connection.close();
        }



        /**
         * Test connection with mysql server
         *
         * @return (boolean) true -> OK, false -> NOK
         */
        public boolean testConnection() {
            String SQL = "SELECT 1";
            try {
                PreparedStatement statement = connection.prepareStatement(SQL);
                ResultSet rs = statement.executeQuery();
                while (rs.next()) {
                }
            } catch (SQLException ex) {
                return false;
            } catch (NullPointerException ex){
                return false;
            }
            return true;
        }


        public ArrayList<HashMap<String, String>> executeSelect(String SQL) throws SQLException {
            if (enableReadLock && enableWriteLock) { //Доколку има read-write lock
                synchronized (readWriteLock) {
                    return select(SQL);
                }
            } else {
                if (enableReadLock) { //Доколку има read-lock
                    synchronized (readLock) {
                        return select(SQL);
                    }
                } else {
                    return select(SQL);
                }
            }
        }


        private ArrayList<HashMap<String, String>> select(String SQL) throws SQLException {
            ArrayList<HashMap<String, String>> results = new ArrayList<>();
            HashMap<String, String> row;
            PreparedStatement statement = connection.prepareStatement(SQL);
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int no_columns = rsmd.getColumnCount();
            while (rs.next()) {
                row = new HashMap<>();
                for (int i = 1; i <= no_columns; i++) {
                    row.put(rsmd.getColumnName(i), rs.getString(i));
                }
                results.add(row);
            }
            statement.close();
            rs.close();
            return results;
        }


        public long executeInsert(String SQL) throws SQLException {
            if (enableReadLock && enableWriteLock) {
                synchronized (readWriteLock) {
                    return insert(SQL);
                }
            } else {
                if (enableWriteLock) {
                    synchronized (writeLock) {
                        return insert(SQL);
                    }
                } else {
                    return insert(SQL);
                }
            }
        }


        private long insert(String SQL) throws SQLException {
            long ID = -1;
            PreparedStatement statement = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate();
            ResultSet rs = statement.getGeneratedKeys();
            if (rs.next()) {
                ID = rs.getLong(1);
            }
            statement.close();
            return ID;
        }


        public int executeUpdate(String SQL) throws SQLException {
            if (enableReadLock && enableWriteLock) {
                synchronized (readWriteLock) {
                    return update(SQL);
                }
            } else {
                if (enableWriteLock) {
                    synchronized (writeLock) {
                        return update(SQL);
                    }
                } else {
                    return update(SQL);
                }
            }
        }


        private int update(String SQL) throws SQLException {
            PreparedStatement statement = connection.prepareStatement(SQL);
            int rez = statement.executeUpdate(SQL);
            statement.close();
            return rez;
        }


        public int executeDelete(String SQL) throws SQLException {
            if (enableReadLock || enableWriteLock) {
                synchronized (readWriteLock) {
                    synchronized (readLock) {
                        synchronized (writeLock) {
                            return delete(SQL);
                        }
                    }
                }
            } else {
                return delete(SQL);
            }
        }


        private int delete(String SQL) throws SQLException {
            PreparedStatement statement = connection.prepareStatement(SQL);
            int rez = statement.executeUpdate(SQL);
            statement.close();
            return rez;
        }
    }

In reconnect method i will need some lock or something that will make everyone who call select, update, insert or delete methods to wait (block) until the reconnect is finished.

Answer 1

I think the easiest solution is to mark all your methods using the resource "connection" with the synchronized flag. http://docs.oracle.com/javase/tutorial/essential/concurrency/syncmeth.html

For example:

Class YourClass
{
  public synchronized boolean reconect(...) { ...}
  public synchronized String getData (...) {...}
}

This locks this object for the flagged methods so you should to encapsulate your connection in a small class like this or to just lock your connection object: http://docs.oracle.com/javase/tutorial/essential/concurrency/locksync.html

Class YourClass
{
  public boolean reconect(...) {
   synchronized(con) 
   {
     ...
   }
  }

  public String getData (...) {
    synchronized(con) 
    {
     ...
    }
  }
  private Connection con;
}

These synchronized zones will never run at the same time.

EDIT: Considering you want protect reconnect against other db operations, you should consider the use of a semaphore: http://docs.oracle.com/javase/7/docs/api/java/util/concurrent/Semaphore.html

Class YourClass
{
  YourClass() {
    sem = new Semaphore(1);
  }

  public boolean reconect(...) {
     sem.acquire();
     ...
     sem.release();
  }

  public String getData (...) {
    synchronized(sem) 
    {
      if(sem.availablePermits()>0) sem.reducePermits(1);
    }
    ...
    ...
    synchronized(sem) 
    {
      sem.release();
    }
  }
  private Connection con;
  Semaphore sem;
}

Answer 2

Try this oracle jdbc multithreading tutorial. Or simply user Apache connection pool

Multi-threading will improve your performance but there are a couple of things you need to know:

Each thread needs its own JDBC connection. Connections can't be shared between threads because each connection is also a transaction. Upload the data in chunks and commit once in a while to avoid accumulating huge rollback/undo tables. Cut tasks into several work units where each unit does one job. To elaborate the last point: Currently, you have a task that reads a file, parses it, opens a JDBC connection, does some calculations, sends the data to the database, etc.

What you should do:

One (!) thread to read the file and create "jobs" out of it. Each job should contains a small, but not too small "unit of work". Push those into a queue The next thread(s) wait(s) for jobs in the queue and do the calculations. This can happen while the threads in step #1 wait for the slow hard disk to return the new lines of data. The result of this conversion step goes into the next queue One or more threads to upload the data via JDBC. The first and the last threads are pretty slow because they are I/O bound (hard disks are slow and network connections are even worse). Plus inserting data in a database is a very complex task (allocating space, updating indexes, checking foreign keys)

Using different worker threads gives you lots of advantages:

It's easy to test each thread separately. Since they don't share data, you need no synchronization. The queues will do that for you You can quickly change the number of threads for each step to tweak performance