0

I used this same code on older mysql5 database engine and I did not have this issue there (well I see sleeping connections on mysql5 too, but it does not causes "Too many connections" problem). But now I moved java servlet to mariadb 10.1 server, I have this code and I think that connection to mysql (mariadb) database is open until it reaches mariadb timeout limit - so this is not good. I have no idea why it does not close connection on conn.close(); in public void closeConnection(). Any idea please?

package com.example.java_servlet.server.data_holders;

import com.mysql.jdbc.PreparedStatement;

import com.example.java_servlet.server.singleton.DBobject;
import com.example.java_servlet.server.singleton.DBresult;

import java.sql.*;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Map.Entry;

public class Database {
    private Connection conn;
    private final DBobject dBobject;


    public Database(DBobject dBobject) {
        this.dBobject = dBobject;
    }


    public static Database getInstance(DBobject dBobject) {
        Database db = new Database(dBobject);
        return db;
    }

public DBobject getDBobj(){
        return dBobject;
    }

    private Connection getConnection() throws SQLTimeoutException, ClassNotFoundException, SQLException {

        if (conn == null || (conn.isValid(31536) || conn.isClosed())) {
            conn = DriverManager.getConnection("jdbc:mysql://" + dBobject.urlDB//
                    + ":" + dBobject.port //
                    + "/" + dBobject.databaseName + //
                    "?autoReconnect=true&"//
                    + "failOverReadOnly=false&"//
                    + "maxReconnects=10&"//
                    + "useUnicode=true&"//
                    + "wait_timeout=31536000&"//
                    + "interactive_timeout=31536000&"//
                    + "characterEncoding=utf-8"//
            , dBobject.user, dBobject.pass);
        }

        return conn;
    }

    public void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
        }
        // destroyDrivers();
    }

    public boolean execute(String command) throws SQLTimeoutException, ClassNotFoundException, SQLException {
        boolean result = false;

        Statement statement = getConnection().createStatement();
        result = statement.executeUpdate(command) > 0;
        statement.close();

        return result;

    }

    public boolean executeNewProcedure(String command) throws SQLTimeoutException, ClassNotFoundException, SQLException {
        boolean result = false;

        Statement statement = getConnection().createStatement();
        result = statement.execute(command);
        statement.close();

        return result;

    }

    public DBresult getPrimaryKeys(String tableName) throws Exception {
        Exception err;
        DBresult result;
        ResultSet set = null;
        DatabaseMetaData meta = null;
        try {
            meta = getConnection().getMetaData();
            set = meta.getPrimaryKeys(null, null, tableName);
            result = DBresult.buildOnlyPK(set);
            err = null;

        } catch (Exception e) {
            err = e;
            e.printStackTrace();
            result = null;
        } finally {
            try {
                set.close();
            } catch (Exception e2) {
            }
        }
        if (err != null) {
            throw new RuntimeException(err);
        }
        return result;

    }

    public DBresult query(String query) throws Exception {
        Exception err;
        DBresult result;
        ResultSet set = null;
        Statement statement = null;
        try {
            statement = getConnection().createStatement();
            set = statement.executeQuery(query);
            result = DBresult.build(set);
            err = null;

        } catch (Exception e) {
            err = e;
            e.printStackTrace();
            result = null;
        } finally {
            try {
                set.close();
            } catch (Exception e2) {
            }
            try {
                statement.close();
            } catch (Exception e2) {
            }
        }
        if (err != null) {
            throw new RuntimeException(err);
        }
        return result;

    }

    public static void destroyDrivers() {
        try {
            Enumeration<Driver> drivers = DriverManager.getDrivers();
            while (drivers.hasMoreElements()) {
                Driver driver = drivers.nextElement();
                try {
                    // System.out.println(driver.toString());
                    DriverManager.deregisterDriver(driver);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
        }
    }

    public static class BathItem {
        public String command;
        public HashMap<Integer, String> values;

        public static BathItem newInstance(String command) {
            BathItem item = new BathItem();
            item.command = command;
            item.values = new HashMap<Integer, String>();
            return item;
        }

        public void addValue(int key, double value) {
            values.put(key, String.valueOf(value));
        }

        public void addValue(int key, long value) {
            values.put(key, String.valueOf(value));
        }

        public void addValue(int key, int value) {
            values.put(key, String.valueOf(value));
        }

        public void addValue(int key, String value) {
            values.put(key, value);
        }

    }

    public void applyBath(ArrayList<BathItem> bath) throws SQLException, ClassNotFoundException {
        // ArrayList<String> bathList = new ArrayList<String>();
        final int batchSize = 100;
        String commandBase = null;
        PreparedStatement ps = null;
        int count = 0;

        for (BathItem bathItem : bath) {
            if (commandBase == null) {
                commandBase = bathItem.command;
                ps = (PreparedStatement) getConnection().prepareStatement(commandBase);
            } else if (!commandBase.equals(bathItem.command) || count > batchSize) {

                // if(count > batchSize){
                ps.executeBatch();
                count = 0;
                // }
                if (!commandBase.equals(bathItem.command)) {
                    commandBase = bathItem.command;
                    ps.close();
                    ps = (PreparedStatement) getConnection().prepareStatement(commandBase);
                }

            }
            for (Entry<Integer, String> set : bathItem.values.entrySet()) {
                ps.setString(set.getKey(), set.getValue());
            }
            ps.addBatch();
            count += 1;

        }
        if (count > 0) {
            ps.executeBatch();
        }
        ps.close();

    }
}
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
peter
  • 4,289
  • 12
  • 44
  • 67
  • Possible duplicate of [Closing Database Connections in Java](https://stackoverflow.com/questions/2225221/closing-database-connections-in-java) – Austin Schaefer Jul 27 '17 at 14:55
  • I can not see any call to `closeConnection()` method. Who do you think to close the connection? – Balázs Nemes Jul 27 '17 at 15:09
  • I am calling closeConnection in another file but it seems that connection always sleeps. – peter Jul 27 '17 at 15:24
  • btw, no idea why when I check it with show processlist; it shows me sleeping process and his time is counting up - so this connection is not closed. – peter Jul 27 '17 at 15:40
  • Why are you using a 'poor man's connection pool'? Just use a data source with a proper connection pool, and obtain + close connections for units of work. You probably also have hard to find bugs due to race conditions if you use this from a servlet. – Mark Rotteveel Jul 29 '17 at 15:58

0 Answers0