How to Reuse JDBC connectivity code in other Java programs or classes?

[Remo Reek]

Hi everyone I am new to this forum I need everyone's help to solve my problem, Here's the code I have to use this code at any other program or java class. As I am using this code frequently in all programs can anyone help how to call these code as a function or anything???




Program1:

import java.sql.*;
import java.io.*;
public class DisplayDB {

		// connecting to database

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String url="jdbc:sqlserver://129.4.8.25:1483;Database=Example";
		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con=DriverManager.getConnection(url,"xx","xxxx");
			stmt = con.createStatement();
           }
               catch(Exception e) {  }
              }

For example,

Program2:

import java.io.*;
class sample {
Resultset rs= program1.executeQuery("........");
}

Any help???

Thanks,
Regards,
Remo

[Ram Para]

This is not the right way to use connections.
Use Connection pooling.
Using Connection you can easily write a reusable function which will solve your purpose.

[Remo Reek]

@Ram Para

Can you provide some sample code based on my example, I am just a beginner to JDBC.

Thanks

[Ram Para]

Which App server you are using.

[Remo Reek]

@Ram Para

I am using Apache Tomcat Server and Database server is MSSQL2005.

[Malhar Me]

You can use singlton pattern also.

[binu narayanan]

Why not use hibernate it takes care of connection pooling and lot many things

[Ram Para]

Perfect Configure a datasoure in Server.xml.

provide a JNDI name


you can refer

http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

write a method

static Connection getJNDIConnection(){
String DATASOURCE_CONTEXT = "java:comp/env/jdbc/blah";//jndi from server.xml

Connection result = null;
try {
Context initialContext = new InitialContext();
if ( initialContext == null){
log("JNDI problem. Cannot get InitialContext.");
}
DataSource datasource = (DataSource)initialContext.lookup(DATASOURCE_CONTEXT);
if (datasource != null) {
result = datasource.getConnection();
}
else {
log("Failed to lookup datasource.");
}
}
catch ( NamingException ex ) {
log("Cannot get connection: " + ex);
}
catch(SQLException ex){
log("Cannot get connection: " + ex);
}
return result;
}

[Remo Reek]

Thanks everybody, actually what my master ask me to do is create a class and call them in your program but I am not getting that thing.
I shouldnot have that JDBC connection code in my every program but I have to use them by anymeans in my another java programs.

[Balu Sadhasivam]

In program 1, add a method that returns a connection.

// public Connection getConnection() 
  try {  
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
            con=DriverManager.getConnection(url,"xx","xxxx");  
            stmt = con.createStatement();  
            }  
                catch(Exception e) {  }  
               }  

In program , create a DisplayDB object whenever you need connection object and call getConnection.

But most important thing about Connections are to close them as soon as you finish them so as to avoid holding up resources. So add method to close the connection object

public void closeConnection(){
conn.close // 
}

[binu narayanan]

Create a class called DB do the common connection coding in that class. Like driver manager, connection string etc. Then import this class in the required class to make a connection and do not forget to close all the opened connection.

I am not in a position to send you some sample code.But will post as soon as get hold of one

[Remo Reek]

@Balu Sadhasivam
Thanks everyone for your help and forgive me for not answering for your solutions, I actually tried your code but I need somemore clear explanation and sample, But the thing is really superb, Thanks for everyones help.