Database helper class and closing question

[Joseph Smithern]

I have a JDBC working with Oracle 9i where database is inserted/updated maybe 10 times at most during a week with very little usage in my Tomcat 4.1.3 Container.

The Database classes I have used for the past year are working great but I wonder if I should be closing anything in my database helper class with Prepared statements:

public class DbInsert
{
    private PreparedStatement stat;
    private Connection connection;
  
    public DbInsert(Connection connection)
    {
         this.connection = connection;
    }
 
    public void cityInserter(FormBean city) throws SQLException
    {
        stat = connection.prepareStatement("Insert into City (street, school) values (?,?)");
        stat.setString(1, city.getStreet());
        stat.setString(2, city.getSchool());
       stat.executeUpdate();
    }
 
    //more Methods with preparedstatements here....
}
 
....

public class DbWork
{
 
    private Connection connection = new ConnectionMgr().getConnection();
 
   public dbMethod(FormBean city)
   {
        try 
       {
               new DbInsert(connection).cityInserter(city);
       }
       catch(SQLException ex)
       {
              System.out.println(ex);
       }
       finally
       {
             connection.close();
       }
   }
 
   //more db methods using prepared statements here
 
.....
}

When I experiment and put a close statement in the DbInsert class method then my database insert wont work because it would be closed when it is called in the DbWork class?

 public void cityInserter(FormBean city) throws SQLException
    {
        stat = connection.prepareStatement("Insert into City (street, school) values (?,?)");
        stat.setString(1, city.getStreet());
        stat.setString(2, city.getSchool());
       stat.executeUpdate();
       connection.close();
    }

Please advise.
[ November 08, 2007: Message edited by: Joseph Smithern ]

[Les Hayden]

you say

put a close statement in the DbInsert class method

but in your code you use connection.close();. If you indeed want to close the statement then you should use

 
public void cityInserter(FormBean city) throws SQLException
    {
        stat = connection.prepareStatement("Insert into City (street, school) values (?,?)");
        stat.setString(1, city.getStreet());
        stat.setString(2, city.getSchool());
       stat.executeUpdate();
       [B]stat.close();[/B]
    }

[Joseph Smithern]

Thanks, I was also wondering if the way I am doing it is not recommended because I am keeping the Connection and PreparedStatement as an instance variable.

Is this better or more efficient way where I combine it into one class??

public class DbWork
{
 
    private Connection connection = new ConnectionMgr().getConnection();
    private PreparedStatement stat;
 
     public void cityInserter(FormBean city) throws SQLException  {
        stat = connection.prepareStatement("Insert into City (street, school) values (?,?)");
        stat.setString(1, city.getStreet());
        stat.setString(2, city.getSchool());
        stat.executeUpdate();
        stat.close();
    }
 
    public void cityUpdater(FormBean city) throws SQLException  {
        //update another table in the database using preparedstatements
    }
 
 
   public void dbMethod(FormBean city)
   {
        try 
       {
               cityInserter(city);
               cityUpdater(city);
       }
       catch(SQLException ex)
       {
              System.out.println(ex);
       }
       finally
       {
             connection.close();
       }
 
}

[ November 12, 2007: Message edited by: Joseph Smithern ]