Usual solution to that is a connection pool.  Abstract the connection
responsibility one layer away from the application logic and it simplifies the
engineering.

Which is worse, to take a while to construct a connection, or to run out of
connections because your objects never let them go?

There's efficiency, and there's safety.

Basically good.  Written this way, you push the question of pooling an so
forth into the driver.

The safety aspect is to check for return values and exceptions.  If you get a
null connection back, or an exception, from its construction, you will have
trouble.  If you get an exception later, you risk not closing the connection,
hastening the saturation of your connection limit.

By relaying the SQLException you're adding to the burden of Sports client
classes to deal with the exception at that low level.  You should either eat
the Exception, turning it into a return code, or rethrow as an
ApplicationException (your custom derivative).

'objref' is not a very useful name for a variable.

Here's an example of safety-first connection use, leaving pooling to the
driver, much like yours except for the extra checks and the use of
DriverManager instead of the custom class:

 package foo.sports;
 import org.apache.log4j.Logger;
 public class Sportster
 {
  private static final String LOCATION =
    "jdbc:postgresql://localhost:5432/sportster";
  private static final String USER = "sportster";
  private static final String PWD  = "sportster";

  private static final String INSERT =
    "INSERT INTO franchise ( team, manager ) VALUES (?, ?)";

  private static final String UPDATE =
    "UPDATE franchise SET manager = ? WHERE team ?";

  private final Logger logger = Logger.getLogger( getClass() );

  public void insert( Sports entity )
      throws AppException
  {
    Connection connection;
    try
    {
      connection = DriverManager.getConnection( LOCATION, USER, PWD );
    }
    catch ( SQLException ex )
    {
      logger.error( "Connection failure: "+ ex.getMessage(), ex );
      throw new AppException( ex );
    }

    PreparedStatement stat = null;
    try
    {
      connection.setAutoCommit( false );

      stat = connection.prepareStatement( INSERT );
      stat.setString( 1, entity.getTeam() );
      stat.setString( 2, entity.getManager() );

      int rows = stat.executeUpdate();
      connection.commit();
      logger.debug( "Inserted "+ rows +" rows." );
    }
    catch ( SQLException ex )
    {
      logger.error( "Insert failure: "+ ex.getMessage(), ex );
      try
      {
        connection.rollback();
      }
      catch ( SQLException exc )
      {
        logger.error( "Rollback failure: "+ ex.getMessage(), exc );
        throw new AppException( ex );
      }
      throw new AppException( ex );
    }
    finally
    {
      if ( stat != null )
      {
        stat.close();
      }
      try
      {
        connection.close();
      }
      catch ( SQLException exc )
      {
        logger.error( "Close failure: "+ ex.getMessage(), exc );
        throw new AppException( ex );
      }
    }
  }  // insert()

// similarly for update(), delete(), query(), find(), etc.
 }   // Sportster

This example might cry out to you for refactoring.

Hi Francan00,

Your code doesn't generate the same enthousiasm with me as it does with
Lew. Two points I don't think Lew brought up:

- how many times do you open a connection when databasesUpdIns() is
executed? How many of these connections are actually used?
- in my view the use of the 'LIKE' operator is still a serious problem:
you are sending a statement to the database that is *not* what you want
it to be (or you don't know how to ask what you want to ask). How is
your database going to return a proper and timely answer when the
request is incorrect? This may seem quite harsh, but do you know what
each and every optimizer will do with the 'LIKE' operator?

Regards,

Ruud de Koter.