Database helper class and closing

[evergrean100]

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();
    }

    ....
}

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();
       }
   }
....
}

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.

 

[stefanwagner]

Hm.
It schould work for the first call.

On a second call with the same DbWork, the connection is still closed.

Don't close the connection, if you need it again.

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[evergrean100]

Thanks, I did some research and found it is not a good idea to put a Database connection in a Constructor.

I changed it where I combined the classes:

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 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
}

Not sure if this is more efficient or not?

 

[stefanwagner]

I don't see the reason to close the connection in the finally block.


don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[evergrean100]

Where should I close it if not the finally block?

 

[stefanwagner]

Well - it depends on the application.

But if you don't open it in the method, why do you close it?
It might be ok, but if you need to insert more than one city, it might be too expensive to close and open it repeatedly.

A separate method for closing would help, if there is a point, where you know, you don't need it any longer.

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[tom62]

Also a PreparedStatement would only make sense if you need to execuate the same statement multiple times. I you create a new PreparedStatement every time you insert new data in your database the advantages of using a PreparedStatement is lost.

 

[tom62]

Also a PreparedStatement would only make sense if you need to execute the same statement multiple times. I you create a new PreparedStatement every time you insert new data in your database then the advantages of using a PreparedStatement are lost.

 

[evergrean100]

Thanks for the information from both of you!

The program will only insert one city at a time.
I assumed I was closing the connection from cityInserter method which is why I have a closed statement in the dbMethod. If I close directly in the cityInserter method the insert doesnt work.

Also I assumed by using PreparedStatements it would protect me from SQL injections.

 

[stefanwagner]

That's absolutely right.

3 main advantages in prepared statements:
- more speed on repeated execution
- prevents injection attacks
- simplifies masking (name="O'Reilly")

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung