Solved

Connection pool doesn't close connections

Posted on 2011-09-06
10
195 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now