a little confused!

[joew weakers]

I have set up a connection to my database with no hiccups and everything is executing correctly. However I was hoping somebody could offer me some sound advice on the following. My program executes various SQL commands called in different classes. Rather than requesting a NEW connection to my database in each class every time I want to process a simple database request, is it possible to connect just the ONCE and just call
Statement stmt = conn.createStatement(); and
OracleResultSet ors = (OracleResultSet) stmt.executeQuery(query);
each time I want to process an SQL query. What I mean is have one simple method that connects to my database only the once and just create statements in different classes or what is the best approach! My code currently looks messy with all my calls to connect to my database. Surely there must be a neater way to achieve my goal...Cheers Joe.
P.S. how does one close a connection if executing statements like in my proposed way

[Gregg Bolinger]

What I have done in the past is created my own custom connection class with a getInstance static method. In this getInstance method I either return the already created connection OR I create the connection (usually only on the first request) and return that one. Basically a singleton pattern for a connection.
Then you only have to do a
MyConnection con = MyConnection.getInstance();
and you have your connection.

[Winston Smith]

Try looking into Connection Pooling. Otherwise, as Gregg was stating, you can use a class to encapsulate all your connection functionality, and then use this class to establish connections when the need arises. Just be sure you have a function to close the connection.

[Gayathri Prasad]

Hi,
Preferably we initialise the connection to the database in the constructor and when ever there is a need we pull the data from the DB. This is very efficient and doesn't consume resources. Closing the connection after the work is done is very important.
Cheers,
Gaya3

[joew weakers]

Cheers guys for responding. Just one or two subsequent queries. If I proceed as Greg suggests and call
MyConnection con = MyConnection.getInstance(); in the various classes where I need to access info in my database, where do I close the connection. Do I close it after every time I make a request to my database or what? Basically in my program the connection will stay open from the moment a user logs into my system until he actually quits altogether, i.e. the user has the potential to make database requests during this period. So will I simply close the connection once the user quits the application or what way should it happen? Cheers Joe

[Nagendra Prasad]

From what has been discussed in this thread, that would seem to be the logical place to close the connection.
i.e
- when user logs in.. creation connection retain it.
- get an instance of it whenever u need to perform any db activity
- close it when the user exits.

this has its dangers though...
- what happens to the connection if the user is idle for a long time?
- What if user abnormally exits the applications?
Not sure if these are valid for your situaiton but might need considerations. I know these are potential problems.. but not entirely sure of the best solution.. so someone will need to discuss this further,

[joew weakers]

Cheers again for the responses. Can anybody tell me why I may be getting this particular error message with the following code. Excuse my naivity with regard to my programming knowledge:
Error: java.lang.NullPointerException
Here are the relevant code snippets from the two classes interacting with each other:
public class Connection //this establishes the connection which works
{
public static OracleConnection conn = null;
public void getInstance(){
try
{
String database = "jdbc racle:thin:@" + m_host + ":" + m_port + ":" + m_sid;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = (OracleConnection)DriverManager.getConnection(database, schemaName, schemaPassword);
}
catch(Exception e){}
AND from the class requesting an instance of Connection in order to process the query below:
String query = "Select geom from interstates";
Statement stmt = (Connection.conn).createStatement(); //this line gives the error
thanks ....Joe

[sumana ar]

--
String query = "Select geom from interstates";
Statement stmt = (Connection.conn).createStatement(); //this line gives the error
--
Are both the classes in the same package?
try out this way and check for the results..
OracleConnection cont = Connection.conn;
Statement stmt = cont.createStatement();
ResultSet rs = stmt.executeQuery(query);

sumana

[joew weakers]

Hi Sumana. Yes both classes are in the same package. I tried what you suggested and I am getting the exact same error this time around. I really dont know where to go from here! Joe

[sumana ar]

hai..
i tried it the same way ..and that was working fine. this is the code that i used to instantiate the connection...
code.....

public ResultSet execute_prepared(String query) {
newdatasheet connect = new newdatasheet();
connect.connectdb();
try{
Connection cont = connect.connt;
pstmt = cont.prepareStatement(query);
pstmt.setString(1,p_date_from);
pstmt.setString(2,p_date_to);
rs = pstmt.executeQuery();
}
catch(SQLException e) {
System.out.println("sqlexception error..." + e);
}
return rs;
}

i've set the connt (Connection variable) as public in the connect class...and this the whole thing works fine..
--sumana

[joew weakers]

Cheers once again Sumana. I managed to sort out my problem through a process of trial and error but I am not too sure if it is a good way of achieving it. Below are pieces of code from the two classes involved. Would you mind telling me if this is a valid way of doing it and if it is good programming practice:
public class Connection //the class which establishes the connection
{
private static String m_host = "localhost";
private static String m_port = "1521";
private static String m_sid = "weakers";
protected static String schemaName = "system";
protected static String schemaPassword = "hello";

public static OracleConnection conn = null;

public Connection()
{
try
{
String database = "jdbc racle:thin:@" + m_host + ":" + m_port + ":" + m_sid;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = (OracleConnection)DriverManager.getConnection(database, schemaName, schemaPassword);
}
catch(Exception e){}
}
}
AND code from the second class which calls an instance of Connection in order to execute the SQL:
Connection cont = new Connection();
String query = "Select geom from interstates";
Statement stmt = cont.conn.createStatement();
OracleResultSet ors = (OracleResultSet) stmt.executeQuery(query);
cheers Joe