Results 1 to 2 of 2
  1. #1
    Gideonzx is offline Member
    Join Date
    Jul 2009
    Posts
    7
    Rep Power
    0

    Default JDBC using adapter (and connection handles)

    I wrote the following code on an event server:

    Java Code:
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:thin:scott/tiger@10.14.1.52:1521/orcl");
    Connection conn = ods.getConnection(); 
    // Check for the existence of a previous customerID
    String selectStmt     = "SELECT * from STEPS " +
    	"WHERE (streamID='" + 
    	this.customerId +
    	"') and (assetid='" + 
    	this.eventName +
    	"')";    
    Statement stmt  = conn.createStatement();
    //System.out.println("-- calling SELECT");
    //System.out.println(selectStmt);
    ResultSet rs    = stmt.executeQuery(selectStmt);
    This works great, except it is exceedingling slow. I am sending about 20,000 messages to this code.

    I think the problem is that I open the connection everytime I perform a select (and later an insert statement).

    The server instance goes away after the message has been processed, and I can not save the connection.

    Would using an adapter in config.xml prevent this openig a new connection every time a message invokes this code.

    If so, I have never done JDBC with an adapter and I dont understand how to use an adapter to use a WHERE clause to select on variables, since in the example I have seen so far, it places "SELECT 1 from DUAL" into the config.xml and does not explain how to get mix have the SELECT statement interact with code variables or better yet how to get your code to get a connection handle.

    Could someone please post an exaqmple of how to use an adapter with JDBC, and possibly how to get your code to access a connection handle from the config.xml ?

    Thanks

    This is part of a server app that is instantiated everytime a message comes in so I can not
    Last edited by Gideonzx; 07-27-2009 at 11:26 PM.

  2. #2
    Steve11235's Avatar
    Steve11235 is offline Senior Member
    Join Date
    Dec 2008
    Posts
    1,046
    Rep Power
    7

    Default

    What you want is a connection pool. The latest JDBC drivers seem to provide this capability in a very simple manner. In addition, you should be using a prepared statement, since creating a statement takes a long time as well, although opening a connection is about the worst. The new JDBC also supports pooling prepared statements on the pooled connection. Do this, and you application will run about 10,000 times faster, no joke. Depending on the database, opening a connection can take over a second, and creating a statement more than 1/10 of a second.

Similar Threads

  1. jdbc connection problem
    By Assaf A in forum JDBC
    Replies: 0
    Last Post: 12-01-2008, 05:08 PM
  2. JDBC connection
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 11-10-2007, 07:39 PM
  3. SQL Server 2005 Jdbc connection
    By mgt83 in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 07-27-2007, 04:52 AM
  4. How to close JDBC Connection
    By Heather in forum JDBC
    Replies: 2
    Last Post: 07-15-2007, 01:07 PM
  5. JDBC driver connection problem
    By creativehacker in forum JDBC
    Replies: 3
    Last Post: 07-10-2007, 09:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •