Unable to get a Connection Object

[Rajat Bhatnagar]

I have a particular scenario where i have five methods in a class.
All of the methods require to connect to database to perform some operation

So what i ideally want to do is have five operation specific methods
and one DBMethod where i establish the connection and return back the connection object . i.e the signature would be like

public Connection DBObject(){
//Establishing connection with a type 4 driver

}

[Paul Sturrock]

OK, that all sounds good. What is your question?

[Rajat Bhatnagar]

Sorry for the incomplete post.

then in the other five operation specific methods i just say

Connection connection = DBObject();

However this approach is not working and i am getting a null pointer exception as the DBObject() method is not able to return a connection object.

i cannot understand as to why this is happening.
Also is this the right approach from the design perspective.
If not please let me know what's the right approach.

Thanks in advance

[Rajat Bhatnagar]

I just need to get the visibility of connection type outside of the try block.

How to do it is not what i am able to find out>>

 
package databaseAccessPrograms;
import java.sql.*;
 
import oracle.jdbc.*;
import oracle.jdbc.driver.*;
public class DBConnect {
Connection connection = null;
String dateQuery = "select sysdate from dual";
public Connection DBConnection(){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@kaikeyee:1521:enr9i","e51628","e51628");
/*
 * The visibility of connection type is only in this try block.
 * How do i get it out from here
 */
}catch(SQLException se){
se.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
return connection;
}

public void method1(){
try{
Connection connection = DBConnection();
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

ResultSet rset = stmt.executeQuery(dateQuery);
if(rset.first()==true){
System.out.println("I got a record in the ResultSet");

/***************************************************************************
 * Conditional block to check if their is a return of value from database
 ****************************************************************************/

while(rset.first()){
while(rset.next()){
String firstRecord = rset.getString(1);
}

}}}catch(SQLException se){
se.printStackTrace();
}
}
  }
   

[vedha vas]

Hi Rajat Bhatnagar,

In the DBConnect class you already have a field for Connection connection.
Then in the DBConnection method why again you are creating a connection variable.
Just use the above.

Code to be modified:

    public class DBConnect {
    
String dateQuery = "select sysdate from dual";
public Connection DBConnection(){
Connection connection = null ;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@kaikeyee:1521:enr9i","e51628","e51628");
/*
 * The visibility of connection type is only in this try block.
 * How do i get it out from here
 */
}catch(SQLException se){
se.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
return connection;
}

public void method1(){
try{
Connection connection = DBConnection();
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

ResultSet rset = stmt.executeQuery(dateQuery);
if(rset.first()==true){
System.out.println("I got a record in the ResultSet");

/***************************************************************************
 * Conditional block to check if their is a return of value from database
 ****************************************************************************/

while(rset.first()){
while(rset.next()){
String firstRecord = rset.getString(1);
}

}}}catch(SQLException se){
se.printStackTrace();
}
}
  }

[Added code tags - Dave]
[ July 24, 2008: Message edited by: David O'Meara ]

[David O'Meara]

Hopefully you meant something like this...

 
public Connection dbConnection() throws SQLException
{
Connection conn = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@kaikeyee:1521:enr9i","e51628","e51628");
}
catch(ClassNotFoundException e)
{
throw new RuntimeException("Unable to load Driver",e);
}
return connection;
} 

In theory the connection can still be null, but in practice either an SQLException or RuntimeException will be thrown.

Question: How to get the Connection outside the try scope?
Answer: Place the Connection outside the try scope.