Re: Database helper class with PreparedStatements

teser3@hotmail.com wrote:


Now the problem is that this variable 'connection' has not been declared.
Remember, a variable loses scope with the closing brace. That means that the
local variable 'connection' from method cityInserter() is not available any more.

The good new is that you can put the finally block in cityInserter() and get
rid of dbMethod().

Try it like this (some class names changed to better reflect their purpose):

  public class CityDb
  {
   private static final String INSERT_SQL =
     "INSERT INTO city (street, school) VALUES (?,?)";

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

   /** Insert the city into the DB.
    * @param city <code>City</code> to insert.
    * @return boolean <code>true</code> iff insert succeeded.
    */
   public City insert( City city )
   {
     List<City> batch = new ArrayList<City>();
     batch.add( city );
     return insert( batch );
   }

   /** Insert a batch of cities into the DB.
    * Uses one <code>PreparedStatement</code> for the whole batch.
    * @param city <code>City</code> to insert.
    * @return boolean <code>true</code> iff all inserts succeeded.
    */
   public boolean insert( Iterable <City> batch )
   {
     if ( batch == null )
     {
       throw new IllegalArgumentException( "Batch cannot be null" );
     }
     Connection connection = ConnectionMgr().getConnection();
     if ( connection == null )
     {
       return false;
     }
     try
     {
       return insert( batch, connection );
     }
     finally
     {
       try
       {
         connection.close();
       }
       catch ( SQLException exc )
       {
         logger.error( "Cannot close. "+ exc.getMessage();
         logger.debug( exc );
       }
     }
   }

   /** Insert a batch of cities into the DB.
    * Assumes a live <code>Connection</code> for the whole batch.
    * @param city <code>City</code> to insert.
    * @param connection <code>Connection</code> through which to insert.
    * @return boolean <code>true</code> iff all inserts succeeded.
    */
   boolean insert( Iterable <City> batch, Connection connection )
   {
     try
     {
       connection.setAutoCommit( false );

       PreparedStatement stat = connection.prepareStatement( INSERT_SQL );
       for ( City city : batch )
       {
         if ( city == null )
         {
           throw new IllegalArgumentException( "City cannot be null" );
         }
         insert( city, stat );
       }
       connection.commit();
       return true;
     }
     catch ( SQLException ex )
     {
       logger.error( "Whoops! "+ ex.getMessage();
       logger.debug( ex );
       try
       {
         connection.rollback();
       }
       catch ( SQLException exc )
       {
         logger.error( "Cannot rollback. "+ exc.getMessage();
         logger.debug( exc );
       }
       return false;
     }
   }

   private void insert( City city, PreparedStatement stat )
     throws SQLException
   {
     stat.setString( 1, city.getStreet() );
     stat.setString( 2, city.getSchool() );
     stat.executeUpdate();
   }

  }

--
Lew