How to properly work with MySQL in Java?

Question

MySQL

Good afternoon everyone!
Essence of the question: I want to understand whether I have correctly built a scheme for working with MySQL and how to close connections correctly?
To communicate with the database with each request, I open a new connection with the method:

private Connection con;
private Statement stmt;
private void openConnection()
{
    try {
        con = DriverManager.getConnection(url, user, password);
        stmt = con.createStatement();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

I then make the required query:

private int executeQuery(String query)
{
    ResultSet rs = null;
    try {
        rs = stmt.executeQuery(query);
        return rs.getInt(1);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        closeConnection(rs);
    }
    return 0;
}

, in which in finally I call the method to close all current connections:

private void closeConnection(ResultSet rs)
{
    if (rs != null) try {rs.close();} catch (Exception e){}
    //
    try { if (stmt != null) stmt.close();}
    catch (Exception e) {}
    finally {stmt = null;}
    //
    try { if (con != null) con.close();}
    catch (Exception e) {}
    finally {con = null;}
}

The first question: please tell me if I built the scheme to work with the base correctly?
The second question: after each request, I have a bunch of Sleep connections in the terminal:

mysql> show processlist;
+-------+------+-----------------+------+---------+------+----------+------------------+
| 23055 | root | localhost:60396 | fmdb | Sleep   |    5 |          | NULL             |
| 23056 | root | localhost:60397 | fmdb | Sleep   |    5 |          | NULL             |
| 23058 | root | localhost:60399 | fmdb | Sleep   |    5 |          | NULL             |
| 23060 | root | localhost:60401 | fmdb | Sleep   |    5 |          | NULL             |

And at some point in Java I get this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

What can be done about it?
Thank you.
(I will say right away that I am new to Java/Back-end/MySQL, I have been working in the front-end without Java and MySQL for a long time.)

Answer 1

The first question: please tell me if I built the scheme to work with the base correctly?

This approach has the right to life and is often found among beginners. But it is not very convenient to use and maintain.

The second question: after each request, I have a bunch of Sleep connections in the terminal

It's hard to tell what's going on based on the information you've provided. Perhaps con.close() threw an exception and did not close the connection, and you have this exception stupidly ignored.
In single-threaded applications, it is common practice to open one connection and work with it for the duration of the application. That is something like this:

package hello.world;

import java.sql.*;

public class Main {

    // один коннект на всё приложение
    private static Connection conn;

    // в данном случае так же можно использовать и единственный Statement
    private static Statement stmt;

    public static void main(String[] args) throws SQLException {
        conn = DriverManager.getConnection("jdbc:mysql://localhost/hello", "user", "password");
        stmt = conn.createStatement();
        doTheJob();
    }

    private static void doTheJob() throws SQLException {

        // здесь используется фишка Java7 под названием try-with-resources,
        // которая освобождает нас от необходимости закрывать ResultSet руками
        // он будет закрыт автоматически в конце try блока
        try ( ResultSet rs = stmt.executeQuery("SHOW TABLES") ) {
            while ( rs.next() ) {
                System.out.println(rs.getString(1));
            }
        }

        // переиспользуем Statement для другого запроса - это допустимо
        try ( ResultSet rs = stmt.executeQuery("SELECT column FROM table") ) {
            while ( rs.next() ) {
                System.out.println(rs.getString(1));
            }
        }

        // вариант без try-with-resources для Java6 и ниже
        ResultSet rs2 = null;
        try {
            rs2 = stmt.executeQuery("SELECT column2 FROM table2");
            // делаем что-то с rs2
        } finally {
            close(rs2);
        }

    }

    private static void close(ResultSet rs) {
        if ( rs != null ) {
            try {
                rs.close();
            } catch ( SQLException e ) {
                e.printStackTrace();
            }
        }
    }

}

That is, closing the connection to the database after each request is completely optional. In some cases, this is even harmful, because opening a new connection takes some time, and with a large number of requests, this overhead becomes noticeable.
For multi-threaded programs, a similar technique is often used, where each thread has its own Connection object with which it works (for example, stores its connection instance in the ThreadLocal field).
But in multi-threaded applications, it is more convenient to use a connection pool. The pool takes responsibility for distributing connections to all those who suffer. At the start, it creates several connections to the database at once and saves it to its internal collection. Further, as needed (calling the getConnection method on the pool), the pool removes the first connection in turn from the internal collection and returns it to the requester. If the collection is empty, a new connection is created and returned immediately. The main trick is that the connections returned by this pool are not actually closed when closed, but are returned back to the pool's internal collection and can be reused by other threads. Thus, higher performance is achieved by reducing the number of open connections to the database and increasing the intensity of their use.
Of course, the description in the previous paragraph is rather superficial and incomplete, but outlines the concept that is common to all pools.
If you are doing multi-threaded programming, and this is a must-have for the backend (especially highload), then you cannot do without a connection pool. Although of course you can do without, but as the project develops, you will cut your own wrapper and eventually implement the simplest connection pool. It's not worth it - it's easier to just take it ready.
I do not recommend immediately taking up the study of any pools. First practice with bare JDBC in single threaded applications. Connection pools are already the second step. They should be started only when you feel confident in JDBC. At the initial stage, they will only interfere.
When you are ready to master some kind of pool, I advise you to pay attention to HikariCP . It has been working for me in highload projects for a long time and has never let me down. Below is a code example using it:

package hello.world;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.*;
import java.util.Properties;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Main {

    // пул, в котором содержатся все соединения
    private static HikariDataSource dbPool;

    // в этом сервисе будем параллельно выполнять запросы
    private static ExecutorService executor = Executors.newFixedThreadPool(5);

    public static void main(String[] args) throws SQLException {

        // конфигурируем пул
        Properties props = new Properties();
        props.setProperty("dataSourceClassName", "com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
        props.setProperty("dataSource.url", "jdbc:mysql://localhost/hello");
        props.setProperty("dataSource.user", "user");
        props.setProperty("dataSource.password", "password");
        props.setProperty("poolName", "MyFirstPool");
        props.setProperty("maximumPoolSize", "5"); // в этом пуле будет максимум 5 соединений
        props.setProperty("minimumIdle", "1"); // как минимум одно активное соединение там будет жить постоянно
        dbPool = new HikariDataSource(new HikariConfig(props));
        doTheJob();
    }

    private static void doTheJob() throws SQLException {

        // сколько запросов будем делать параллельно
        int selects = 5;

        // этот объект позволит нам дождаться выполнения всех запросов,
        // выполняющихся в параллельных потоках чтобы подсчитать общее время
        // выполнения всех запросов
        CountDownLatch waitLatch = new CountDownLatch(selects);

        long startTime = System.nanoTime();
        for ( int i = 0; i < selects; ++i ) {
            executor.submit(new ThreadPoolJob(dbPool, waitLatch));
        }

        try {
            // ждём когда все воркеры закончат
            waitLatch.await();
        } catch ( InterruptedException e ) {
            System.out.println("latch was broken by interruption request");
        }
        long timeElapsed = System.nanoTime() - startTime;
        System.out.println("All queries was executed in: " + (timeElapsed / 1000000000) + " sec");

    }

    // класс-воркер, который будет выполнять запрос
    private static class ThreadPoolJob implements Runnable {

        private final HikariDataSource dbPool;

        private final CountDownLatch waitLatch;

        ThreadPoolJob(HikariDataSource dbPool, CountDownLatch waitLatch) {
            this.dbPool = dbPool;
            this.waitLatch = waitLatch;
        }

        @Override
        public void run() {
            String tName = Thread.currentThread().getName();
            System.out.println(tName + ": Start query");
            try (
                Connection conn = dbPool.getConnection();
                Statement stmt = conn.createStatement();
             ) {
                // здесь мы не получаем и не работаем с ResultSet, поэтому не
                // описываем его в try-with-resources
                stmt.execute("SELECT SLEEP(5)");
                System.out.println(tName + ": Query completed");
            } catch ( SQLException e ) {
                e.printStackTrace();
            } finally {
                waitLatch.countDown();
            }
        }
    }

}

Answer 2

The first question: please tell me if I built the scheme to work with the base correctly?

No. Because:
It is necessary to connect through a connection pool, it must regulate the connections itself. It’s better to take a ready-made pool (you can write it yourself, but it’s not so easy, after all, multi-threaded programming is not the easiest thing).
Then it’s also worth connecting a transaction manager to this pool, which, in principle, also knows how to allocate resources to provide transactions for requests.