java oracle connection, one time close.

[bigbinc]

I dont know what the type of connection is called, but I know it is
the worst kind. Through a servlet, lets say I open a oracle 9.2
connection, run the query and then close the connection, will this
hurt the oracle server in terms of performance. I know this is the
least efficient way to run oracle queries but everybody does it. I
read somewhere that you can open the database connection at the start
of a session and then close the connection at the end of the session.

So the question, how much am I hurting the oracle server by the
one-time-hit-connection, if I have 10-20 users on the servlet hours at
a time. I have been doing it in asp for years.

pseduo-code:

goConnection() {

conn
try {
openconnection()
runquery();
getdata();
conn.close();
} catch() {

conn.close();
} finally() {
conn.close();
}


}

 

[Sudsy]

I dont know what the type of connection is called, but I know it is
the worst kind. Through a servlet, lets say I open a oracle 9.2
connection, run the query and then close the connection, will this
hurt the oracle server in terms of performance. I know this is the
least efficient way to run oracle queries but everybody does it. I
read somewhere that you can open the database connection at the start
of a session and then close the connection at the end of the session.

Most database vendors (Oracle included) supply connection pool
classes. How you integrate them with your servlet container or
app server depends on the facilities provided. Since you didn't
specify your platform, here's an example from my struts-config.xml
file:

<data-sources>
<data-source type="oracle.jdbc.pool.OracleDataSource">
<set-property property="description"
value="SUDS"/>
<set-property property="driverClass"
value="oracle.jdbc.driver.OracleDriver"/>
<set-property property="url"
value="jdbcracle:thin192.168.0.1:1521:INST"/>
<set-property property="URL"
value="jdbcracle:thin192.168.0.1:1521:INST"/>
<set-property property="maxCount" value="10"/>
<set-property property="minCount" value="2"/>
<set-property property="user" value="XXXXX"/>
<set-property property="password" value="XXXXX"/>
</data-source>
</data-sources>

Note that the class is oracle.jdbc.pool.OracleDataSource. This
implement javax.sql.DataSource.

 

[bigbinc]

Most database vendors (Oracle included) supply connection pool
classes. How you integrate them with your servlet container or
app server depends on the facilities provided. Since you didn't
specify your platform, here's an example from my struts-config.xml
file:

<data-sources>
<data-source type="oracle.jdbc.pool.OracleDataSource">
<set-property property="description"
value="SUDS"/>
<set-property property="driverClass"
value="oracle.jdbc.driver.OracleDriver"/>
<set-property property="url"
value="jdbcracle:thin192.168.0.1:1521:INST"/>
<set-property property="URL"
value="jdbcracle:thin192.168.0.1:1521:INST"/>
<set-property property="maxCount" value="10"/>
<set-property property="minCount" value="2"/>
<set-property property="user" value="XXXXX"/>
<set-property property="password" value="XXXXX"/>
</data-source>
</data-sources>

Note that the class is oracle.jdbc.pool.OracleDataSource. This
implement javax.sql.DataSource.

But in general, is the connect - close bad programming, dont do it?

Berlin Brown
http://www.retroevolution.com

 

[Sudsy]

But in general, is the connect - close bad programming, dont do it?

Yes. It's very wasteful to go through that loop for every statement
which you need to execute. Connection pooling is definitely the
way to go. It's rather efficient to allocate existing connections
from the pool and release them back once you're done.

 

[bigbinc]

Yes. It's very wasteful to go through that loop for every statement
which you need to execute. Connection pooling is definitely the
way to go. It's rather efficient to allocate existing connections
from the pool and release them back once you're done.

How is this for bad, making 3 connections within the same instance.
Sigh....