?
Solved

Connection pool doesn't close connections

Posted on 2011-09-06
10
Medium Priority
?
217 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

765 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