Closing Database issue in new method

[chicago1985]

I have a method that inserts data into my Oracle 9i database with no problems or Database closing issues:

public class MainClass
{

public PreparedStatement preparer;
public Connection connection;

public MainClass()
{
     connection = new DbConnectionClass().getConnection();
}

public int inserter(Beann abc)
{
   int dat = 0;
   try
  {
      preparer = connection.prepareStatement("insert into abTable (one,two) values (?,?)");
      preparer.setString(1, abc.getOne());
      preparer.setString(2, abc.getTwo());
      preparer.executeUpdate();
   }
   catch(Exception e)
  {
      e.printStackTrace();
  }
  return dat;
}

public int matcher(Beann abc)
{
     try
     {
	inserter(abc);
     }
     catch(Exception e)
     {
         e.printStackTrace();
     }
     finally
     { 
        //close the ResultSet ....
        //close the Statement  ....
        //close the Connection  ....
     }
}

Now when I put the method (inserter) in another class called OtherClass, it does insert the data but now I have database closing issues:

public class OtherClass
{
...
public int inserter(Beann abc)
{
   int dat = 0;
   try
  {
      preparer = connection.prepareStatement("insert into abTable (one,two) values (?,?)");
      preparer.setString(1, abc.getOne());
      preparer.setString(2, abc.getTwo());
      preparer.executeUpdate();
   }
   catch(Exception e)
  {
      e.printStackTrace();
  }
  return dat;
}
....

public class MainClass
{

public PreparedStatement preparer;
public Connection connection;

public MainClass()
{
     connection = new DbConnectionClass().getConnection();
}

public int matcher(Beann abc)
{
     try
     {
         new OtherClass().insert(abc);
     }
     catch(Exception e)
     {
         e.printStackTrace();
     }
     finally
     { 
        //close the ResultSet ....
        //close the Statement  ....
        //close the Connection  ....
     }

In Oracle SQL Plus database resource check I see JDBC Thin Client is opened and not closed after each insert with the above attempt.
This didnt happen when I had the method in the same class.

Please advise.

 

[prosper]

I do not know how OtherClass insert data.
The connection come from where?
Could you post more details about connection?

public class OtherClass
{
...
public int inserter(Beann abc)
{
   int dat = 0;
   try
  {
      preparer = connection.prepareStatement("insert into abTable (one,two) values (?,?)");
      preparer.setString(1, abc.getOne());
      preparer.setString(2, abc.getTwo());
      preparer.executeUpdate();
   }
   catch(Exception e)
  {
      e.printStackTrace();
  }
  return dat;
}

 

[chicago1985]

Thanks, its now working with no Oracle database closing issues or leaks.

I had this in the OtherClass as my connection using the no arg constructor. I was told that this is a major blunder and not the way to work with JDBC:

public OtherClass()
{
     //bad JDBC here?
     connection = new DbConnectionClass().getConnection();
}

I added this one arg constructor instead and it worked:

public class OtherClass 
{
   private Connection connection;

   public OtherClass(Connection connection)
   { 
        this.connection = connection; 
   }
... 
}

And I changed MainClass to have the actual database connection:

public class MainClass
{

public PreparedStatement preparer;
public Connection connection;

/* 
I was advised this is a very bad way of connecting to database
public MainClass()
{
     connection = new DbConnectionClass().getConnection();
}
*/

try {
Class.forName("OracleDriverInfoHere");
Connection connection = DriverManager.getConnection("dbURLHere", "usernamehere", "passwordhere");
...

I assume this way gives me just one database connection which is located in MainClass and is the best practice for doing this??

 

[prosper]

public class OtherClass
{
   private Connection connection;

   public OtherClass(Connection connection)
   {
        this.connection = connection;
   }
...
   public void removeReference(){
          connection = null;
   }
}

Please try calling removeReference() after using OtherClass.
I doubt if this works.

 

[prosper]

//In the main class
OtherClass otherObj = new OtherClass(connection);
otherObj.inserter(abc);
// I assume you has finished using otherObj here
otherObj.removeReference(); //first statement
otherObj = null; //second statement

It seems that only second statement is needed. Tell me if this work.