Question regarding Datasources and Connections

[Former Member]

Hello,

I have an application that establishes a Connection to a backend DB using the DataSource Object:

DataSource ds;

....

Connection c = ds.getConnection();

My question is: After finishing with this Connection do I have to close it or this something that is being done automaticly by the Connection Pool?

Roy

[guru_subramanianb · Accepted solution]

Hi Roy,

I was thinking to jus add clarity to both the answer by providing the entire code snippets for a DB connection.Here it is :-

try

{

// Load the jdbc-odbc driver

Class.forName("oracle.jdbc.driver.OracleDriver");

// Open a connection to the odbc data source entered by the user

Connection connect =DriverManager.getConnection("jdbc:oracle:thin:@10.201.103.78:1521:HCM" ,"<userid>","<passwd>");

System.out.println("Connection Established --->"+connect);

Statement stmt = connect.createStatement();

//Sample Query--->Replace your query here

ResultSet rst = stmt.executeQuery("select text from category where category_id='Index100'");

//Iterate thro' your resultset like this

while(rst.next())

{

resultString = rst.getString("text");

}

//Close your Resultset

rst.close();

}

// If not connected to DB catch your exception to know what was the problem is

catch(Exception e)

{

e.toString();

System.out.println(e.toString());

}

// Close your connection in Finally Block

finally(){

if(connect==null)

{

connect.close();

}

}

Not but not the least copy your DB drivers file in your lib folder of your project structure which sets the classpath to get connected to DB.

Hope my explanation is simple and clear.

Regards,

Guru

[guru_subramanianb]

Hi roy,

pls close the queston.

Regards,

Guru

[guru_subramanianb]

Hi Roy,

I was thinking to jus add clarity to both the answer by providing the entire code snippets for a DB connection.Here it is :-

try

{

// Load the jdbc-odbc driver

Class.forName("oracle.jdbc.driver.OracleDriver");

// Open a connection to the odbc data source entered by the user

Connection connect =DriverManager.getConnection("jdbc:oracle:thin:@10.201.103.78:1521:HCM" ,"<userid>","<passwd>");

System.out.println("Connection Established --->"+connect);

Statement stmt = connect.createStatement();

//Sample Query--->Replace your query here

ResultSet rst = stmt.executeQuery("select text from category where category_id='Index100'");

//Iterate thro' your resultset like this

while(rst.next())

{

resultString = rst.getString("text");

}

//Close your Resultset

rst.close();

}

// If not connected to DB catch your exception to know what was the problem is

catch(Exception e)

{

e.toString();

System.out.println(e.toString());

}

// Close your connection in Finally Block

finally(){

if(connect==null)

{

connect.close();

}

}

Not but not the least copy your DB drivers file in your lib folder of your project structure which sets the classpath to get connected to DB.

Hope my expalnation is simple and clear.

Regards,

Guru

[Former Member]

Hi Guru,

The code what you have given will work fine, but it is not utilizing the connection pool provided by the "Application Server".

By doing this, whenever the application/program connecting to the back-end DB, it has to open the connection it is a costly operation.

Hi Roy,

Get the connection from the datasource and once the database operation is over close the connection in the finally block.

Regards,

Santhosh.C

[Former Member]

I just wanted to add to what Jeff said.. you need to close your connection and you should close your connection in finally{} block.

finally { 
try { 
   if ( con != null )  con.close();
    } catch (Exception e) {}
        	
}

[Jeff-Gebo]

Hi Roy,

you must close your connection(as well as any Statements, and ResultSets)...it will then be returned to the Datasource.

Cheers,

Jeff