Solved

Connection pool doesn't close connections

Posted on 2011-09-06
10
204 Views
Last Modified: 2012-05-12
I have three connection pools with exactly the same ConnectionPool class and different, but similar structure of ConnectDB class.
Two of them work perfect and keep permanent QTY of sessions open:
public class ConnectDB {

    private ConnectionPool pool = null;

    public ConnectDB() throws SQLException {
        pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:xxx", "xxx", "xxx");
    }

    public List<Data> select(String sql) {
        List<Data> data = new ArrayList<Data>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = pool.checkout();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String s = rs.getString("symbol");
                Timestamp ts = rs.getTimestamp("TIMEST");
                double bpx = rs.getDouble("BIDPX");
                double apx = rs.getDouble("ASKPX");
                data.add(new Data(s, bpx, apx, ts));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                pool.checkin(conn);
            }
        }

        return data;
    }

    public void insert(double[] outY, Timestamp[] timest) throws SQLException {

        Connection conn = pool.checkout();
        PreparedStatement pstat = conn.prepareStatement("INSERT INTO RBF_DATA_60 (OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, OUTY10, OUTY11, OUTY12, OUTY13, OUTY14, OUTY15, OUTY16, OUTY17, OUTY18, OUTY19, OUTY20, OUTY21, OUTY22, OUTY23, OUTY24, TIMEST) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        for (int i = 0; i <= 24; i++) {
            pstat.setFloat(i + 1, (float) outY[i]);
        }
        pstat.setTimestamp(26, timest[0]);
        ResultSet rset = pstat.executeQuery();
        pstat.close();
        pool.checkin(conn);

    }
}

Open in new window

it select and insert data at the same time.
Another one has also select and insert data from these DB, but as soon I start app it starts permanently increase number of sessions until complete depleting. I have 300 sessions available and it starts from permanent 47 opened before by previous apps and increase up to 296 in 15 minutes.
public class ConnectDB {

    private ConnectionPool pool = null;

    public ConnectDB() throws SQLException {
        pool =
new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:xxx", "xxx",
                   "xxx");
    }

    Connection conn;
    ResultSet rs;
    PreparedStatement ps;
    PreparedStatement[] pst = new PreparedStatement[50];
    String[] sell_px;
    String[] buy_px;
    double outY[] = new double[25];
    ArrayConverter aConverter;

    public ResultSet select(String sql) throws ClassNotFoundException,
                                               InstantiationException,
                                               IllegalAccessException,
                                               SQLException, Exception {
        aConverter = new ArrayConverter();
        conn = null;
        ps = null;
        rs = null;
        try {
            conn = pool.checkout();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                outY[0] = rs.getDouble("OUTY0");
                outY[1] = rs.getDouble("OUTY1");
                outY[2] = rs.getDouble("OUTY2");
                outY[3] = rs.getDouble("OUTY3");
                outY[4] = rs.getDouble("OUTY4");
                outY[5] = rs.getDouble("OUTY5");
                outY[6] = rs.getDouble("OUTY6");
                outY[7] = rs.getDouble("OUTY7");
                outY[8] = rs.getDouble("OUTY8");
                outY[9] = rs.getDouble("OUTY9");
                outY[10] = rs.getDouble("OUTY10");
                outY[11] = rs.getDouble("OUTY11");
                outY[12] = rs.getDouble("OUTY12");
                outY[13] = rs.getDouble("OUTY13");
                outY[14] = rs.getDouble("OUTY14");
                outY[15] = rs.getDouble("OUTY15");
                outY[16] = rs.getDouble("OUTY16");
                outY[17] = rs.getDouble("OUTY17");
                outY[18] = rs.getDouble("OUTY18");
                outY[19] = rs.getDouble("OUTY19");
                outY[20] = rs.getDouble("OUTY20");
                outY[21] = rs.getDouble("OUTY21");
                outY[22] = rs.getDouble("OUTY22");
                outY[23] = rs.getDouble("OUTY23");
                outY[24] = rs.getDouble("OUTY24");

                equals(rs);

                aConverter.arrayConverter(outY);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                pool.checkin(conn);
            }
        }

        return rs;
    }


    public void insertBotResult(String[] sell_px, String[] buy_px,
                                java.util.Date ts) throws SQLException,
                                                          ClassNotFoundException,
                                                          InstantiationException,
                                                          IllegalAccessException {

        String[] sql = new String[50];
        conn = pool.checkout();

                System.out.println("Current thread is " +
                                   Thread.currentThread().getName());
                System.out.println("Current array is " + sell_px);
     
        if (sell_px[0] != null) {

            sql[0] =
                    ("INSERT INTO EURUSD_BOT_SELL(SELL_PX, TS) VALUES (?, ?)");
            pst[0] = conn.prepareStatement(sql[0]);
            pst[0].setString(1, sell_px[0]);
            pst[0].setTimestamp(2, new Timestamp(ts.getTime()));
            pst[0].executeUpdate();
            
            pst[0].clearParameters();
            pst[0].close();

        } else {
        }

        if (buy_px[0] != null) {

            sql[1] = ("INSERT INTO EURUSD_BOT_BUY(BUY_PX, TS) VALUES (?, ?)");
            pst[1] = conn.prepareStatement(sql[1]);
            pst[1].setString(1, buy_px[0]);
            pst[1].setTimestamp(2, new Timestamp(ts.getTime()));
            pst[1].executeUpdate();
            
            pst[1].clearParameters();
            pst[1].close();

        } else {
        }

.....................................

        

        if (buy_px[24] != null) {

            sql[49] = ("INSERT INTO GBPNZD_BOT_BUY(BUY_PX, TS) VALUES (?, ?)");
            pst[49] = conn.prepareStatement(sql[49]);
            pst[49].setString(1, buy_px[24]);
            pst[49].setTimestamp(2, new Timestamp(ts.getTime()));
            pst[49].executeUpdate();
            //                System.out.println("buy_px[9] CHFJPY  " + buy_px[9]);
            //                System.out.println("new Timestamp(ts.getTime()   " +
            //                                   new Timestamp(ts.getTime()));
            System.out.println();
            pst[49].clearParameters();
            pst[49].close();

        } else {
        }

        Arrays.fill(sell_px, null);
        Arrays.fill(buy_px, null);
        pool.checkin(conn);
    }
}

Open in new window

Please advice what is wrong, why pool does not work properly
0
Comment
Question by:gbcbr
  • 5
  • 5
10 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 36489030
Make sure, as in your select(String) method, that all connections are checked in in a finally block

There is no logging in your code. Why? Any program more than a few lines long should use proper logging.

See if you can turn on the pool's logging, which it should have of its own. Look at the checks out and in of the pool and make sure they are OK
0
 

Author Comment

by:gbcbr
ID: 36489368
>>Make sure, as in your select(String) method, that all connections are checked in in a finally block
Sorry, but I'm especially published my code for the help to find this mistake.
If I can find it myself, what is the reason to ask this question.
I realize, that something wrong with connections closing, but I don't understand why and where.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36494377
>> but I don't understand why and where.

That's why i specifically told you HOW to understand why and where
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:gbcbr
ID: 36501093
I don't know how to
>> See if you can turn on the pool's logging, which it should have of its own.
This is Connection pool class which I use in both cases.
 
public class ConnectionPool implements Runnable {
    // Number of initial connections to make.
    private int m_InitialConnectionCount = 10;
    // A list of available connections for use.
    private Vector m_AvailableConnections = new Vector();
    // A list of connections being used currently.
    private Vector m_UsedConnections = new Vector();
    // The URL string used to connect to the database
    private String m_URLString = null;
    // The username used to connect to the database
    private String m_UserName = null;
    // The password used to connect to the database
    private String m_Password = null;
    // The cleanup thread
    private Thread m_CleanupThread = null;


    //Constructor

    public ConnectionPool(String urlString, String user,
                          String passwd) throws SQLException {
        // Initialize the required parameters
        m_URLString = urlString;
        m_UserName = user;
        m_Password = passwd;

        for (int cnt = 0; cnt < m_InitialConnectionCount; cnt++) {
            // Add a new connection to the available list.
            m_AvailableConnections.addElement(getConnection());
        }

        // Create the cleanup thread
        m_CleanupThread = new Thread(this);
        m_CleanupThread.start();
    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(m_URLString, m_UserName,
                                           m_Password);
    }

    public synchronized Connection checkout() throws SQLException {
        Connection newConnxn = null;

        if (m_AvailableConnections.isEmpty()) {
            // Im out of connections. Create one more.
            newConnxn = getConnection();
            // Add this connection to the "Used" list.
            m_UsedConnections.addElement(newConnxn);
            // We dont have to do anything else since this is
            // a new connection.
        } else {
            // Connections exist !
            // Get a connection object
            newConnxn = (Connection)m_AvailableConnections.lastElement();
            // Remove it from the available list.
            m_AvailableConnections.removeElement(newConnxn);
            // Add it to the used list.
            m_UsedConnections.addElement(newConnxn);
        }

        // Either way, we should have a connection object now.
        return newConnxn;
    }


    public synchronized void checkin(Connection c) {
        if (c != null) {
            // Remove from used list.
            m_UsedConnections.removeElement(c);
            // Add to the available list
            m_AvailableConnections.addElement(c);
        }
    }

    public int availableCount() {
        return m_AvailableConnections.size();
    }

    public void run() {
        try {
            while (true) {
                synchronized (this) {
                    while (m_AvailableConnections.size() >
                           m_InitialConnectionCount) {
                        // Clean up extra available connections.
                        Connection c =
                            (Connection)m_AvailableConnections.lastElement();
                        m_AvailableConnections.removeElement(c);

                        // Close the connection to the database.
                        c.close();
                    }

                    // Clean up is done
                }

                System.out.println("CLEANUP : Available Connections : " +
                                   availableCount());

                // Now sleep for 1 minute
                Thread.sleep(60000 * 1);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Open in new window

But in one case it close all connections and in second not.
Please advice how to monitor opening and closing connections.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36501099
Why are you reinventing (not easy btw) the wheel of writing a connection pool? Use an existing one.

a. it will (should) implement logging already
b. it will work
0
 

Author Comment

by:gbcbr
ID: 36501112
Sorry, I don't understand what you mean about reinventing?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36501145
Writing your own connection pool
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 36501188
http://www.source-code.biz/miniconnectionpoolmanager/

http://www.h2database.com/javadoc/org/h2/jdbcx/JdbcConnectionPool.html

are examples of standalone CPs. Generally speaking, a CP will be server-side - for one thing, a client-side DB can't be secured easily. That's why examples of CPs you see will be generally in connection with a web app

MySql has a pooling datasource which could be adapted for client-side use too
0
 

Author Comment

by:gbcbr
ID: 36501214
Thank you I'll study it and try to use. If I have questions, I'll open new one.
0
 

Author Closing Comment

by:gbcbr
ID: 37114323
not finished solution
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
numbers ascending pyramid 101 213
java jdbc batch example 8 41
check java version using powershell 13 177
American Express @Work site and Java 4 28
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This video teaches viewers about errors in exception handling.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question