[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Connection pool doesn't close connections

Posted on 2011-09-06
10
Medium Priority
?
229 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month18 days, 6 hours left to enroll

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