Solved

Connection pool doesn't close connections

Posted on 2011-09-06
10
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
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…
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…
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…

749 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