troubleshooting Question

Using existing connection of connection pool for inserting data into DB

Avatar of gbcbr
gbcbrFlag for Cyprus asked on
JavaOracle Database
2 Comments1 Solution398 ViewsLast Modified:
This code use connection pool for inserting stream data into Oracle DB.
The question is why all incoming data inserts recursively with interval 1 sec and don't use already opened connections of connection pool  to the DB?
As shown on output all data marked as arrived 07:22:05 and start inserting recursively into db at 07:22:06 and inserts one by one every second during 20 seconds, but all of it has to be inserted during the same second at 07:22:05, because it's doesn't need to open and close connection every time by using connection pool.
Please advice
public class Main {

    private static InsertData aInsertData;
    private static String tradingSessionStatusID;
    private static boolean printMarketData = false;
    private static IGateway fxcmGateway;

    public static void main(String[] args) throws Exception {

        System.out.println("          dbFX data started");
        System.out.println("=>");
        System.out.println("          Started at " + " : " + new java.util.Date());
        System.out.println("=>");

        System.out.println("          dbFX data server ");
        System.out.println("=>");

        fxcmGateway = GatewayFactory.createGateway();

        fxcmGateway.registerGenericMessageListener(new IGenericMessageListener() {

            public void messageArrived(ITransportable message) {

                if (message instanceof MarketDataSnapshot) {

                    try {

                        aInsertData = new InsertData();
                        aInsertData.insertData(message, printMarketData);

                    } catch (Exception ex) {
                        Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        });
public class InsertData {

    MarketDataSnapshot incomingQuote;
    ConnectDB connectDB = null;
    String sql;

    public InsertData() throws SQLException {
       connectDB = new ConnectDB();
   }

    public void insertData(ITransportable message, boolean printMarketData) throws SQLException,
            ClassNotFoundException,
            InstantiationException,
            IllegalAccessException,
            NotDefinedException,
            FieldNotFound,
            Exception {

        if (message instanceof MarketDataSnapshot) {
            incomingQuote = (MarketDataSnapshot) message;

               String symbol = incomingQuote.getInstrument().getSymbol();

                        sql =
                                " INSERT INTO STREAM_DATA "
                                + "(SYMBOL, BIDPX, ASKPX, TIMEST) "
                                + "VALUES "
                                + "('"
                                + incomingQuote.getInstrument().getSymbol()
                                + "','"
                                + incomingQuote.getBidOpen()
                                + "','" + incomingQuote.getAskOpen()
                                + "','"
                                + incomingQuote.getOpenTimestamp()
                                + "')";

                    System.out.println("=>  " + sql + " : " + new java.util.Date());
                    System.out.println();

                    connectDB.insert(sql);     
        }
    }
}
public class ConnectDB {

   Statement statement;
   private ConnectionPool pool = null;

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

    public void insert(String sql) throws SQLException {

        Connection conn = pool.checkout();
        statement = (Statement) conn.createStatement();
        int st = statement.executeUpdate(sql);

        pool.checkin(conn);

        statement.close();
    }
}
public class ConnectionPool implements Runnable
{
    // Number of initial connections to make.
    private int m_InitialConnectionCount = 2;
    // 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();
        }
    }
}
output:
run:
          dbFX data started
=>
          Started at  : Tue Dec 07 09:21:56 EET 2010
=>
          dbFX data server 
=>
          dbFX: started logging in
=>
>>> requestTradingSessionStatus = DB_U100D1_gWfqRkUJRUOxUqmFLJPLv3TxheRDBRF6fsEDkwlX7vYRFkKdJeK1WI-9
=>
          dbFX: done logging in at : Tue Dec 07 09:22:04 EET 2010
=>
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/ZAR','6.889','6.89328','20101207-07:22:05') : Tue Dec 07 09:22:06 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/ZAR','6.8887','6.89328','20101207-07:22:05') : Tue Dec 07 09:22:07 EET 2010

CLEANUP : Available Connections : 1
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/CAD','1.33949','1.33999','20101207-07:22:05') : Tue Dec 07 09:22:08 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/CHF','0.97973','0.97994','20101207-07:22:05') : Tue Dec 07 09:22:09 EET 2010

CLEANUP : Available Connections : 1
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('NZD/JPY','63.01','63.04','20101207-07:22:05') : Tue Dec 07 09:22:10 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/SEK','6.81893','6.822','20101207-07:22:05') : Tue Dec 07 09:22:11 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('NZD/USD','0.7629','0.7632','20101207-07:22:05') : Tue Dec 07 09:22:12 EET 2010

CLEANUP : Available Connections : 1
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/NZD','1.74749','1.74825','20101207-07:22:05') : Tue Dec 07 09:22:13 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/JPY','130.19','130.225','20101207-07:22:05') : Tue Dec 07 09:22:14 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/DKK','5.58825','5.58873','20101207-07:22:05') : Tue Dec 07 09:22:15 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/JPY','81.91','81.945','20101207-07:22:05') : Tue Dec 07 09:22:16 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/JPY','82.589','82.607','20101207-07:22:05') : Tue Dec 07 09:22:17 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/CHF','1.5444','1.54485','20101207-07:22:05') : Tue Dec 07 09:22:18 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/GBP','0.84589','0.84609','20101207-07:22:05') : Tue Dec 07 09:22:19 EET 2010

CLEANUP : Available Connections : 2
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/NZD','2.06569','2.0664','20101207-07:22:05') : Tue Dec 07 09:22:20 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/NZD','1.29966','1.3002','20101207-07:22:05') : Tue Dec 07 09:22:21 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/USD','0.99178','0.992','20101207-07:22:05') : Tue Dec 07 09:22:22 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/USD','1.33356','1.33373','20101207-07:22:05') : Tue Dec 07 09:22:23 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/AUD','1.34434','1.34475','20101207-07:22:05') : Tue Dec 07 09:22:24 EET 2010

CLEANUP : Available Connections : 2
e
          Stopped at Tue Dec 07 09:22:24 EET 2010
=>
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/NOK','5.9789','5.9821','20101207-07:22:05') : Tue Dec 07 09:22:25 EET 2010

CLEANUP : Available Connections : 1
BUILD SUCCESSFUL (total time: 29 seconds)
ASKER CERTIFIED SOLUTION
Mick Barry
Java Developer
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros