Avatar of gbcbr
gbcbrFlag for Cyprus asked on

Connection pool connecting to incoming data

I found very simple solution for organizing connection pool at http://www.javareference.com/jrexamples/viewexample.jsp?id=41 and include it in my code.
Connection pool creates 15 connections and wait for data.
run:
          dbFX data started
=>
          Started at  : Sun Dec 05 16:48:37 EET 2010
=>
Driver   oracle.jdbc.OracleDriver
Checking out...oracle.jdbc.driver.T4CConnection@68814013
Available Connections ... 14
Checking out...oracle.jdbc.driver.T4CConnection@31884174
Available Connections ... 13
Checking out...oracle.jdbc.driver.T4CConnection@679bfb30
Available Connections ... 12
Checking out...oracle.jdbc.driver.T4CConnection@15f48262
Available Connections ... 11
Checking out...oracle.jdbc.driver.T4CConnection@9a68ef9
Available Connections ... 10
Checking out...oracle.jdbc.driver.T4CConnection@2937d090
Available Connections ... 9
Checking out...oracle.jdbc.driver.T4CConnection@5b976011
Available Connections ... 8
Checking out...oracle.jdbc.driver.T4CConnection@33aae94f
Available Connections ... 7
Checking out...oracle.jdbc.driver.T4CConnection@6a5f6303
Available Connections ... 6
Checking out...oracle.jdbc.driver.T4CConnection@7b5a6029
Available Connections ... 5
Checking out...oracle.jdbc.driver.T4CConnection@3d3e58d4
Available Connections ... 4
Checking out...oracle.jdbc.driver.T4CConnection@8888e6c
Available Connections ... 3
Checking out...oracle.jdbc.driver.T4CConnection@39e57e8f
Available Connections ... 2
Checking out...oracle.jdbc.driver.T4CConnection@30e3c624
Available Connections ... 1
Checking out...oracle.jdbc.driver.T4CConnection@39e87719
Available Connections ... 0
Checked in...oracle.jdbc.driver.T4CConnection@68814013
Available Connections ... 1
Checked in...oracle.jdbc.driver.T4CConnection@31884174
Available Connections ... 2
Checked in...oracle.jdbc.driver.T4CConnection@679bfb30
Available Connections ... 3
Checked in...oracle.jdbc.driver.T4CConnection@15f48262
Available Connections ... 4
Checked in...oracle.jdbc.driver.T4CConnection@9a68ef9
Available Connections ... 5
Checked in...oracle.jdbc.driver.T4CConnection@2937d090
Available Connections ... 6
Checked in...oracle.jdbc.driver.T4CConnection@5b976011
Available Connections ... 7
Checked in...oracle.jdbc.driver.T4CConnection@33aae94f
Available Connections ... 8
Checked in...oracle.jdbc.driver.T4CConnection@6a5f6303
Available Connections ... 9
Checked in...oracle.jdbc.driver.T4CConnection@7b5a6029
Available Connections ... 10
Checked in...oracle.jdbc.driver.T4CConnection@3d3e58d4
Available Connections ... 11
Checked in...oracle.jdbc.driver.T4CConnection@8888e6c
Available Connections ... 12
Checked in...oracle.jdbc.driver.T4CConnection@39e57e8f
Available Connections ... 13
Checked in...oracle.jdbc.driver.T4CConnection@30e3c624
Available Connections ... 14
Checked in...oracle.jdbc.driver.T4CConnection@39e87719
Available Connections ... 15
          dbFX data server 
=>
CLEANUP : Available Connections : 11
          dbFX: started logging in
=>
>>> requestTradingSessionStatus = DB_U100D1_jAIAi7bDs9tTJLFRni5qjj7tRaot8LyhJkCqGvgaGDSr2LNx117ZDr-8
=>
          dbFX: done logging in at : Sun Dec 05 16:48:57 EET 2010

Open in new window

But the problem is that into the class Pool_Init I have  
public class Pool_Init {

    private static String driverName = "oracle.jdbc.OracleDriver";

    public static void init (String[] args) {

        try
        {
            Class.forName(driverName).newInstance();
            System.out.println("Driver   " + driverName);
        }
        catch (Exception E)
        {
            System.err.println("Unable to load driver.");
            E.printStackTrace();
        }

        try
        {
            ConnectionPool cp = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:orcl", "", "");

            Connection []connArr = new Connection[15];

            for(int i=0; i<connArr.length;i++)
            {
                connArr[i] = cp.checkout();
                System.out.println("Checking out..." + connArr[i]);
                System.out.println("Available Connections ... " + cp.availableCount());
            }

            for(int i=0; i<connArr.length;i++)
            {
                cp.checkin(connArr[i]);
                System.out.println("Checked in..." + connArr[i]);
                System.out.println("Available Connections ... " + cp.availableCount());
            }
        }
        catch(SQLException sqle)
        {
            sqle.printStackTrace();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

Open in new window

Connection []connArr is an array and when I try to use this connection into ConnectDB class
public class ConnectDB {

    Statement statement;
    Connection conn;
    Pool_Init pi = new Pool_Init();
    Connection[] connArr;

    public void insert(String[] args, String sql) throws SQLException {

        pi.init(args);
        statement = (Statement) connArr.createStatement();
        int st = statement.executeUpdate(sql);

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

        statement.close();
    }
}

Open in new window

I have error
statement = (Statement) connArr.createStatement();
cannot find symbol
symbol: method createStatement()
location: class java.sql. Connection[]
As I understood this java class has not method with array.
Please advice how to add to this class method Connection[] or some other way to solve this question.
Thank you
JavaOracle Database

Avatar of undefined
Last Comment
CEHJ

8/22/2022 - Mon
CEHJ

Normally, you would ask for a connection from a pool, not an array of connections
CEHJ

In that particular case, you need to call pool.checkout and pool.checkin in a finally block
ASKER
gbcbr

Yes, this agree, but how to create statement in this case?
Class ConnectionPool hasn't this method
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
CEHJ

That's a method of Connection, so with a Connection
ASKER
gbcbr

I try this, no error, pool created successfully, debugger show user program running, but no output about sql
public class ConnectDB {

    Statement statement;
    ConnectionPool pool;
    Connection conn;


    public void insert(String sql) throws SQLException {

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

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

        pool.checkin(null);

        statement.close();
    }
}

Open in new window

CEHJ

That can't be the actual full code - the pool is null
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
gbcbr

Pool created well.
But if I initialize it in ConnectDB by
pool = new ConnectionPool(urlString, user, passwd);
I have an error:
url cannot be null
because pool created by Pool_Init class which has reference to property file with all attributes
CEHJ

Can you make doubly sure that you're posting the code that you're actually running?
ASKER
gbcbr

This is last variant:
 
public class Main {

    private static InsertData aInsertData;
    private static String tradingSessionStatusID;
    private static boolean printMarketData = false;
    private static IGateway fxcmGateway;
    private static Pool_Init pi = new Pool_Init();



    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("=>");

        pi.init(args);


        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);
                    

                    }
                }
            }
        });

Open in new window

public class Pool_Init {

    private static String driverName = "oracle.jdbc.OracleDriver";

    public static void init (String[] args) {

        try
        {
            Class.forName(driverName).newInstance();
            System.out.println("Driver   " + driverName);
        }
        catch (Exception E)
        {
            System.err.println("Unable to load driver.");
            E.printStackTrace();
        }

        try
        {
            ConnectionPool cp = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:orcl", "", "");

            Connection []connArr = new Connection[15];

            for(int i=0; i<connArr.length;i++)
            {
                connArr[i] = cp.checkout();
                System.out.println("Checking out..." + connArr[i]);
                System.out.println("Available Connections ... " + cp.availableCount());
            }

            for(int i=0; i<connArr.length;i++)
            {
                cp.checkin(connArr[i]);
                System.out.println("Checked in..." + connArr[i]);
                System.out.println("Available Connections ... " + cp.availableCount());
            }
        }
        catch(SQLException sqle)
        {
            sqle.printStackTrace();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

Open in new window

public class ConnectionPool implements Runnable
{
    // Number of initial connections to make.
    private int m_InitialConnectionCount = 15;
    // 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

public class ConnectDB {

    Statement statement;
    ConnectionPool pool;
    Connection conn;
    String urlString;
    String user;
    String passwd;


    public void insert(String sql) throws SQLException {

        pool = new ConnectionPool(urlString, user, passwd);
        conn = pool.checkout();
        statement = (Statement) conn.createStatement();
        int st = statement.executeUpdate(sql);

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

        pool.checkin(null);

        statement.close();
    }
}

Open in new window

public class InsertData {

    MarketDataSnapshot incomingQuote;
    ConnectDB connectDB;
    String sql;

    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();
                if ("EUR/USD EUR/JPY USD/JPY".indexOf(symbol) > -1) {
                    try {
                        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 = new ConnectDB();
                    connectDB.insert(sql);     

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

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
gbcbr

And last output:
 
run:
          dbFX data started
=>
          Started at  : Sun Dec 05 22:47:54 EET 2010
=>
Driver   oracle.jdbc.OracleDriver
Checking out...oracle.jdbc.driver.T4CConnection@7d95d4fe
CLEANUP : Available Connections : 14
Available Connections ... 14
Checking out...oracle.jdbc.driver.T4CConnection@68814013
Available Connections ... 13
Checking out...oracle.jdbc.driver.T4CConnection@31884174
Available Connections ... 12
Checking out...oracle.jdbc.driver.T4CConnection@679bfb30
Available Connections ... 11
Checking out...oracle.jdbc.driver.T4CConnection@15f48262
Available Connections ... 10
Checking out...oracle.jdbc.driver.T4CConnection@f5db314
Available Connections ... 9
Checking out...oracle.jdbc.driver.T4CConnection@2f242b11
Available Connections ... 8
Checking out...oracle.jdbc.driver.T4CConnection@33aae94f
Available Connections ... 7
Checking out...oracle.jdbc.driver.T4CConnection@6a5f6303
Available Connections ... 6
Checking out...oracle.jdbc.driver.T4CConnection@7b5a6029
Available Connections ... 5
Checking out...oracle.jdbc.driver.T4CConnection@3d3e58d4
Available Connections ... 4
Checking out...oracle.jdbc.driver.T4CConnection@8888e6c
Available Connections ... 3
Checking out...oracle.jdbc.driver.T4CConnection@39e57e8f
Available Connections ... 2
Checking out...oracle.jdbc.driver.T4CConnection@30e3c624
Available Connections ... 1
Checking out...oracle.jdbc.driver.T4CConnection@39e87719
Available Connections ... 0
Checked in...oracle.jdbc.driver.T4CConnection@7d95d4fe
Available Connections ... 1
Checked in...oracle.jdbc.driver.T4CConnection@68814013
Available Connections ... 2
Checked in...oracle.jdbc.driver.T4CConnection@31884174
Available Connections ... 3
Checked in...oracle.jdbc.driver.T4CConnection@679bfb30
Available Connections ... 4
Checked in...oracle.jdbc.driver.T4CConnection@15f48262
Available Connections ... 5
Checked in...oracle.jdbc.driver.T4CConnection@f5db314
Available Connections ... 6
Checked in...oracle.jdbc.driver.T4CConnection@2f242b11
Available Connections ... 7
Checked in...oracle.jdbc.driver.T4CConnection@33aae94f
Available Connections ... 8
Checked in...oracle.jdbc.driver.T4CConnection@6a5f6303
Available Connections ... 9
Checked in...oracle.jdbc.driver.T4CConnection@7b5a6029
Available Connections ... 10
Checked in...oracle.jdbc.driver.T4CConnection@3d3e58d4
Available Connections ... 11
Checked in...oracle.jdbc.driver.T4CConnection@8888e6c
Available Connections ... 12
Checked in...oracle.jdbc.driver.T4CConnection@39e57e8f
Available Connections ... 13
Checked in...oracle.jdbc.driver.T4CConnection@30e3c624
Available Connections ... 14
Checked in...oracle.jdbc.driver.T4CConnection@39e87719
Available Connections ... 15
          dbFX data server 
=>
          dbFX: started logging in
=>
>>> requestTradingSessionStatus = DB_U100D1_49rCJLKP0F9mh7odDT8JCNqiXS2kHr1tOw4MZXMIcaet63wKUvKeCA-9
=>
          dbFX: done logging in at : Sun Dec 05 22:48:10 EET 2010
=>
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/USD','1.33875','1.33935','20101205-20:48:14') : Sun Dec 05 22:48:15 EET 2010

05.12.2010 22:48:15 dbfx_data_201.Main$1 messageArrived
SEVERE: null
java.sql.SQLException: The url cannot be null
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/JPY','110.648','110.725','20101205-20:48:14') : Sun Dec 05 22:48:15 EET 2010

        at java.sql.DriverManager.getConnection(DriverManager.java:554)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at dbfx_data_201.ConnectionPool.getConnection(ConnectionPool.java:54)
        at dbfx_data_201.ConnectionPool.<init>(ConnectionPool.java:44)
        at dbfx_data_201.ConnectDB.insert(ConnectDB.java:19)
        at dbfx_data_201.InsertData.insertData(InsertData.java:53)
        at dbfx_data_201.Main$1.messageArrived(Main.java:54)
        at com.fxcm.internal.transport.FXCMGateway.update(FXCMGateway.java:769)
        at com.fxcm.messaging.util.fix.FIXUserSession$BackToUserQueue.run(FIXUserSession.java:607)
        at java.lang.Thread.run(Thread.java:680)

Open in new window

CEHJ

I can't look in detail at the moment, but it looks like you could be using the pool wrongly. It could be trying eventually to reconnect to the db, having found no more connections available. Unfortunately it looks like the db url is null.
Mick Barry

you need a few changes. See the follwing:

public class ConnectDB {

    private ConnectionPool 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 = (Statement) conn.createStatement();
        int st = statement.executeUpdate(sql);

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

        pool.checkin(null);

        statement.close();
    }
}


public class InsertData {

    MarketDataSnapshot incomingQuote;
    ConnectDB 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();
                if ("EUR/USD EUR/JPY USD/JPY".indexOf(symbol) > -1) {
                    try {
                        String 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);    

                } catch (NotDefinedException ex) {
                    Logger.getLogger(InsertData.class.getName()).log(Level.SEVERE,
                            null, ex);
                }
            }
        }
    }
}
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mick Barry

one other change:


public class ConnectDB {

    private ConnectionPool 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 = (Statement) conn.createStatement();
        int st = statement.executeUpdate(sql);

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

        pool.checkin(conn);

        statement.close();
    }
}
ASKER
gbcbr

All changes are accepted by NetBeans except of
<< private ConnectionPool pool = new ConnectionPool(jdbc:oracle:thin:@10.1.1.7:1521:orcl, "", ""); >>
error message: internal error cannot instantiate dbfx_data_201.ConnectionPool.<init> at dbfx_data_201.ConnectionPool to ()
and suggest to create classes oracle, thin etc
Mick Barry

>     private ConnectionPool pool = new ConnectionPool(jdbc:oracle:thin:@10.1.1.7:1521:orcl, "", "");


sorrytypo, missed the quotes


    private ConnectionPool pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:orcl", "", "");
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
gbcbr

Yes, this changes clean up the code, only I need to split it on 2 parts, because it wants SQLException.
But anyway something wrong generally because it works 10 times slower than before with creating connections for each value. Maybe because we call pool to create each time instead of connect to existing.
I reduce number of connections down to 2 and it start works faster, but anyway very slow.
Mick Barry

that sounds about right, as you're creating a lot more connections than you probably need. your pool is fairly primitive, you'd be better off using something like dbcp
ASKER
gbcbr

I agree that this pool is primitive, this is the reason why I choose it. All other oriented to the work with web application. But mine it's not web, it's just simple bridge between incoming data and DB. At the moment I'm not need to make something complicated. I just need to improve speed of records into DB with pool using. When we speak about dhcp, means that few users work with application and db at the same time. Here we also need few records into db at the same second, but user is only one - server which supplies data.
So, the question is just connect this single user to the pool and let him use already created connections for accessing DB.
I make 15 connections from the beginning because if I open full stream, not only three pairs, it will be 10-20 values per second, so we will need at least 15 connection used at the same time.
I suppose that it's wrong way to initialize connection inside the pool by new ConnectionPool, it has to be something which call
newConnxn = getConnection();
I understand that I have to get existing connection, but my knowledge it's not enough to understand the way in which to do it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mick Barry

understand though that the reason it is slower in your testing is because you are now opening lots more db connections which each consume resources

and the way you previously had it you were creating a new pool every time you insterted a row when you should have only been doing it once
ASKER CERTIFIED SOLUTION
Mick Barry

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gbcbr

I solved problem with SQLEx  like this and change conn to newConnxn:
public class ConnectDB {

    Statement statement;
   private ConnectionPool pool;
   Connection newConnxn;
   
    public void insert(String sql) throws SQLException {

        pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:orcl", "", "");
        newConnxn = pool.checkout();
        statement = (Statement) newConnxn.createStatement();
        int st = statement.executeUpdate(sql);

        pool.checkin(newConnxn);

        statement.close();
    }
}

and I have no error, it works, but too slow.
How to avoid this initialization: pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:orcl", "", "");
So, we will call existing pool but not create new one.
As you can see on this output, all data arrived and marked 8:52:10 but inserting into DB from 8:52:11 up to 8:52:26.
Only on this first data we already have 16 seconds delay.
 
run:
          dbFX data started
=>
          Started at  : Mon Dec 06 10:52:00 EET 2010
=>
Driver   oracle.jdbc.OracleDriver
Checking out...oracle.jdbc.driver.T4CConnection@30e3c624
CLEANUP : Available Connections : 1
Available Connections ... 1
Checking out...oracle.jdbc.driver.T4CConnection@6ef137d
Available Connections ... 0
Checked in...oracle.jdbc.driver.T4CConnection@30e3c624
Available Connections ... 1
Checked in...oracle.jdbc.driver.T4CConnection@6ef137d
Available Connections ... 2
          dbFX data server 
=>
          dbFX: started logging in
=>
>>> requestTradingSessionStatus = DB_U100D1_slb2gzj9FO4uwWKPpKlQeYqUTQvWbV88AIVvINQIyXnDLnGnz3xR16-9
=>
          dbFX: done logging in at : Mon Dec 06 10:52:10 EET 2010
=>
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/CAD','1.33739','1.33789','20101206-08:52:10') : Mon Dec 06 10:52:11 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/SEK','6.8373','6.84048','20101206-08:52:10') : Mon Dec 06 10:52:12 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/DKK','5.59355','5.59403','20101206-08:52:10') : Mon Dec 06 10:52:13 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/GBP','0.84728','0.84748','20101206-08:52:10') : Mon Dec 06 10:52:14 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/USD','1.33214','1.33231','20101206-08:52:10') : Mon Dec 06 10:52:15 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/AUD','1.34553','1.34594','20101206-08:52:10') : Mon Dec 06 10:52:16 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('CAD/CHF','0.9738','0.9743','20101206-08:52:10') : Mon Dec 06 10:52:17 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/CHF','0.9779','0.97811','20101206-08:52:10') : Mon Dec 06 10:52:18 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/NOK','7.9789','7.9824','20101206-08:52:10') : Mon Dec 06 10:52:19 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/JPY','130.21','130.245','20101206-08:52:10') : Mon Dec 06 10:52:20 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/USD','1.57205','1.57227','20101206-08:52:10') : Mon Dec 06 10:52:20 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/CHF','0.968','0.9684','20101206-08:52:10') : Mon Dec 06 10:52:21 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('CHF/JPY','84.685','84.71','20101206-08:52:10') : Mon Dec 06 10:52:22 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/CHF','1.53735','1.5378','20101206-08:52:10') : Mon Dec 06 10:52:23 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/NOK','5.9889','5.9921','20101206-08:52:10') : Mon Dec 06 10:52:24 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/JPY','110.344','110.364','20101206-08:52:10') : Mon Dec 06 10:52:25 EET 2010

CLEANUP : Available Connections : 1
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/CHF','1.30281','1.30306','20101206-08:52:10') : Mon Dec 06 10:52:26 EET 2010

Open in new window

Mick Barry

> I solved problem with SQLEx  like this and change conn to newConnxn:

that doesn't actually make any difference.
its working with the alterations I suggested, but you have only used some of them.

> and I have no error, it works, but too slow.


see my previous comment, it should improve the performance
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
CEHJ

>>
 When we speak about dhcp, means that few users work with application and db at the same time. Here we also need few records into db at the same second, but user is only one - server which supplies data.
>>

Unless you're using a singleton pool for each concurrently-running app (and i'm guessing you're actually using one pool per app) then theoretically, unless the app is multi-threaded (and afaik it isn't) then you'd only need one permanently open connection per app.

Why use a pool then? Two reasons

a. the app will scale better if/when conditions change
b. it makes the application logic of getting a connection simpler.

So:
a. use at most two connections in the pool for now
b. make sure you're only creating the pool *once*
c. just use the checkin/checkout methods when necessary
ASKER
gbcbr

@objects
I put constructors into InsertData and ConnectDB but it doesn't change general situation, it doesn't use all available connections at the same time, it's use and release them recursively, use-release, use-release....
This is the main problem.
@CEHJ
>> a. use at most two connections in the pool for now << This what I do now
>> b. make sure you're only creating the pool *once* << As I see from output it doesn't create new pool
>> c. just use the checkin/checkout methods when necessary << This is my main question, how to use them with appropriate speed 10-20 times per second, not once per second only. Even with previous open-close connection system it was 2-3 times per second. Now with the pool it starts be worst.
If you have any idea, please advice
CEHJ

If the Connection is in constant use (loop) then don't check it in again until the loop is done
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
gbcbr

But the problem is that I can't connect incoming stream to this loop, what I have now doesn't work as a real pool.
CEHJ

>> But the problem is that I can't connect incoming stream to this loop, what I have now doesn't work as a real pool.

Sorry - i don't understand your point there
ASKER
gbcbr

I mean that in this case we don't see properly working pool of connections, because each time, when new data arrive we call new ConnectionPool with arguments, not only method checkout.
So, when we call new ConnectionPool it starts to create new pool and after we call method checkout from new pool.
This how I understand this very slow work.
It has to be some other way to initialize ConnectionPool only once and after just call his method checkout and use opened connections to DB.
Your help has saved me hundreds of hours of internet surfing.
fblack61
CEHJ

>>when new data arrive we call new ConnectionPool with arguments, not only method checkout.

Well that's wrong, as i said above. The ConnectionPool should be initialised once only, at the start of the app
ASKER
gbcbr

Yes, we initialize it into Pool_Init class:

 ConnectionPool cp = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:orcl", "", "");
 Connection []connArr = new Connection[2];

But when I comment
//  pool = new ConnectionPool("jdbc:oracle:thin:@10.1.1.7:1521:orcl", "", ""); into ConnectDB class
it doesn't work and generate NPE
run:
          dbFX data started
=>
          Started at  : Mon Dec 06 16:10:57 EET 2010
=>
Driver   oracle.jdbc.OracleDriver
Checking out...oracle.jdbc.driver.T4CConnection@30e3c624
Available Connections ... 1
CLEANUP : Available Connections : 1
Checking out...oracle.jdbc.driver.T4CConnection@39e87719
Available Connections ... 0
Checked in...oracle.jdbc.driver.T4CConnection@30e3c624
Available Connections ... 1
Checked in...oracle.jdbc.driver.T4CConnection@39e87719
Available Connections ... 2
          dbFX data server 
=>
          dbFX: started logging in
=>
>>> requestTradingSessionStatus = DB_U100D1_l4FJMujz0LO9CFdGPRjj7k8m3Su5mOuzcuwM56z2hvGKa2XPD6snhc-8
=>
          dbFX: done logging in at : Mon Dec 06 16:11:05 EET 2010
=>
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/CAD','1.33688','1.33738','20101206-14:11:05') : Mon Dec 06 16:11:06 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/SEK','6.87724','6.8804','20101206-14:11:05') : Mon Dec 06 16:11:06 EET 2010

06.12.2010 16:11:06 dbfx_data_201.Main$1 messageArrived
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/NZD','1.74745','1.74823','20101206-14:11:05') : Mon Dec 06 16:11:06 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/DKK','5.61535','5.61585','20101206-14:11:05') : Mon Dec 06 16:11:06 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/CAD','1.00735','1.00765','20101206-14:11:05') : Mon Dec 06 16:11:06 EET 2010

SEVERE: null
java.lang.NullPointerException

Open in new window

This is wrong, we have bad loop:
1. If we initialize at ConnectDB new ConnectionPool, we open each time new pool;
2. If not, we have NPE
Where is solution?
CEHJ

Please post current Pool_Init source
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
gbcbr

You can see it above at ID:34278703, I didn't change it
CEHJ

OK. This is what i'd recommend:

a. Replace that class with one called, say, PoolFacade
This will act as a facade to any pool implementation you'll use. I'm not convinced by the current one, but it'll do for the moment
b. Initialise it statically
c. Give it a static init method like the current one (but get rid of that checking-in-and-out business)
public static Connection getConnection

Your application entry point will call PoolFacade.init(). Thereafter, anything needing a connection will call PoolFacade.getConnection
CEHJ

>>
b. Initialise it statically
c. Give it a static init method like the current one (but get rid of that checking-in-and-out business)
public static Connection getConnection
>>

Wasn't quite what i wanted to say:
b. Initialise it statically, giving it a static init method like the current one (but get rid of that checking-in-and-out business)
c. Give it a static method to get a Connection:
public static Connection getConnection

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
gbcbr

But what to put into getConnection method if we already make connection with DB by init method?
CEHJ

You would call pool.getConnection
Mick Barry

> Yes, we initialize it into Pool_Init class:

you don't use the Pool_Init class, and can get rid of it
all you need is the 2 classes I posted above
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
gbcbr

@objects
OK, I'd comment Pool_Init class and made exactly as you said ConnectDB and InsertData classes, but result the same.
This is output:
 
run:
          dbFX data started
=>
          Started at  : Mon Dec 06 23:40:51 EET 2010
=>
          dbFX data server 
=>
          dbFX: started logging in
=>
>>> requestTradingSessionStatus = DB_U100D1_hqsv2syW1uEh5jBif5pCjQFISlSnuQBmUghG8wS3ErgT9ltogFlQr4-8
=>
          dbFX: done logging in at : Mon Dec 06 23:40:59 EET 2010
=>
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('USD/CHF','0.98181','0.98216','20101206-21:41:05') : Mon Dec 06 23:41:06 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/CHF','0.9716','0.9721','20101206-21:41:05') : Mon Dec 06 23:41:07 EET 2010

CLEANUP : Available Connections : 1
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/CHF','1.54255','1.5432','20101206-21:41:05') : Mon Dec 06 23:41:08 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/CHF','1.30591','1.30628','20101206-21:41:05') : Mon Dec 06 23:41:09 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('NZD/JPY','62.92','62.95','20101206-21:41:13') : Mon Dec 06 23:41:15 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('NZD/USD','0.76102','0.76132','20101206-21:41:13') : Mon Dec 06 23:41:15 EET 2010

=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('EUR/NZD','1.74695','1.74785','20101206-21:41:13') : Mon Dec 06 23:41:16 EET 2010

CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('AUD/NZD','1.29975','1.3004','20101206-21:41:13') : Mon Dec 06 23:41:18 EET 2010

CLEANUP : Available Connections : 2
CLEANUP : Available Connections : 2
=>   INSERT INTO STREAM_DATA (SYMBOL, BIDPX, ASKPX, TIMEST) VALUES ('GBP/NZD','2.06391','2.0646','20101206-21:41:13') : Mon Dec 06 23:41:19 EET 2010

Open in new window

as you can see, it still continue recursively inserting with crazy delay.
Mick Barry

that looks like a problem with the code that is calling it
the conection code I posted looks to be working fine.

can you post a new question with the code that is calling insert() and I'll have a look at what the problem is with it
ASKER
gbcbr

@objects
I will do this tomorrow my morning, now I have 00:08
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
gbcbr

We still have some questions
CEHJ

??
ASKER
gbcbr

Because @objects@ provides complete good working code, not only short remarks, and finally found mistake without creating new classes. Just clean and correct existing code.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CEHJ

OK - that's good. Problem solved then