JDBC using adapter (and connection handles)

[Gideonzx]

I wrote the following code on an event server:

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

[Steve11235]

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.