Database helper class

[oaklander]

I have a JDBC working with Oracle 9i 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.

[r035198x]

Sorry I don't quite get the question but if you close the connection then you most certainly won't be able to perform another insert after that without creating a new connection.

[oaklander]

Thanks in my example I have a Database connection in my Constructor.
Is it not a good idea to put a Database connection in a Constructor?

Is the below way more efficient???

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();
    }
   //more db methods to insert and update tables
 
   public void dbMethod(FormBean city)
   {
        try 
       {
               cityInserter(city);
       }
       catch(SQLException ex)
       {
              System.out.println(ex);
       }
       finally
       {
             connection.close();
       }
   }
   //more db methods that call my insert and update methods
}

[heat84]

Thats more like it. You can execute more queries as long as the connection is not closed.