title: Performing CRUD operations using Servlet/JSP
body: <p>I'm performing CRUD operations using Servlet and JSP. The following class is used to retrieve a connection from a connection pool maintained by a server (Tomcat).</p>
  
  <pre><code>public final class DatabaseConnection {
  
      private static final DataSource dataSource;
  
      static {
          try {
              Context initContext = new InitialContext();
              Context context = (Context) initContext.lookup("java:/comp/env");
              dataSource = (DataSource) context.lookup("jdbc/assignment_db");
          } catch (NamingException e) {
              Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, e);
              throw new ExceptionInInitializerError("DataSource not initialized.");
          }
      }
  
      public static Connection getConnection() throws SQLException {
          return dataSource.getConnection();
      }
  }
  </code></pre>
  
  <p>And methods in the following class (DAO) perform CRUD operations.</p>
  
  <pre><code>public final class CountryDao {
  
      public Long getCurrentRow(Long id) throws SQLException {
          Connection connection = null;
          PreparedStatement preparedStatement = null;
          ResultSet resultSet = null;
  
          try {
              connection = DatabaseConnection.getConnection();
              preparedStatement = connection.prepareStatement("select rownum from (select @rownum:=@rownum+1 as rownum, tbl.country_id from country_tbl tbl, (select @rownum:=0)t order by tbl.country_id desc)t where country_id=?");
              preparedStatement.setLong(1, id);
              resultSet = preparedStatement.executeQuery();
              return resultSet.next() ? resultSet.getLong("rownum") : 1;
          } finally {
              if (connection != null) {connection.close();}
              if (resultSet != null) {resultSet.close();}
              if (preparedStatement != null) {preparedStatement.close();}
          }
      }
  
      public Long rowCount() throws SQLException {
          Connection connection = null;
          PreparedStatement preparedStatement = null;
          ResultSet resultSet = null;
  
          try {
              connection = DatabaseConnection.getConnection();
              preparedStatement = connection.prepareStatement("select count(*) as cnt from country_tbl");
              resultSet = preparedStatement.executeQuery();
              resultSet.next();
              return resultSet.getLong("cnt");
          } finally {
              if (connection != null) {connection.close();}
              if (resultSet != null) {resultSet.close();}
              if (preparedStatement != null) {preparedStatement.close();}
          }
      }
  
      public List&lt;CountryBean&gt; getData(Long currentPage, Long pageSize) throws SQLException {
          Connection connection = null;
          PreparedStatement preparedStatement = null;
          ResultSet resultSet = null;
          List&lt;CountryBean&gt; countryBeans = new ArrayList&lt;CountryBean&gt;();
  
          try {
              connection = DatabaseConnection.getConnection();
              preparedStatement = connection.prepareStatement("select * from country_tbl order by country_id desc limit ?,?");
  
              //preparedStatement.setMaxRows(pageSize);
              preparedStatement.setLong(1, currentPage);
              preparedStatement.setLong(2, pageSize);
              resultSet = preparedStatement.executeQuery();
  
              while (resultSet.next()) {
                  CountryBean countryBean = new CountryBean();
                  countryBean.setCountryId(resultSet.getLong("country_id"));
                  countryBean.setCountryName(resultSet.getString("country_name"));
                  countryBean.setCountryCode(resultSet.getString("country_code"));
                  countryBeans.add(countryBean);
              }
          } finally {
              if (connection != null) {connection.close();}
              if (resultSet != null) {resultSet.close();}
              if (preparedStatement != null) {preparedStatement.close();}
          }
  
          return countryBeans;
      }
  
      public boolean delete(Long id) throws SQLException {
          boolean status = false;
          Connection connection = null;
          PreparedStatement preparedStatement = null;
  
          try {
              connection = DatabaseConnection.getConnection();
              preparedStatement = connection.prepareStatement("delete from country_tbl where country_id=?");
              preparedStatement.setLong(1, id);
  
              if (preparedStatement.executeUpdate() == 1) {
                  status = true;
              }
          } finally {
              if (connection != null) {connection.close();}
              if (preparedStatement != null) {preparedStatement.close();}
          }
          return status;
      }
  
      public boolean delete(Long[] ids) throws SQLException {
          boolean status = false;
          Connection connection = null;
          PreparedStatement preparedStatement = null;
  
          try {
              connection = DatabaseConnection.getConnection();
              connection.setAutoCommit(false);
              preparedStatement = connection.prepareStatement("delete from country_tbl where country_id=?");
              int len = ids.length;
  
              for (int i = 0; i &lt; len; i++) {
                  preparedStatement.setLong(1, ids[i]);
                  preparedStatement.addBatch();
              }
  
              preparedStatement.executeBatch();
              connection.commit();
              status = true;
          } finally {
              if (connection != null) {connection.close();}
              if (preparedStatement != null) {preparedStatement.close();}
          }
          return status;
      }
  
      public boolean insert(String countryName, String countryCode) throws SQLException {
          boolean status = false;
          Connection connection = null;
          PreparedStatement preparedStatement = null;
  
          try {
              connection = DatabaseConnection.getConnection();
              preparedStatement = connection.prepareStatement("insert into country_tbl(country_name, country_code)values(?,?)");
              preparedStatement.setString(1, countryName);
              preparedStatement.setString(2, countryCode);
              preparedStatement.executeUpdate();
              status = true;
          } finally {
              if (connection != null) {connection.close();}
              if (preparedStatement != null) {preparedStatement.close();}
          }
          return status;
      }
  
      public boolean update(Long countryId, String countryName, String countryCode) throws SQLException {
          boolean status = false;
          Connection connection = null;
          PreparedStatement preparedStatement = null;
  
          try {
              connection = DatabaseConnection.getConnection();
              preparedStatement = connection.prepareStatement("update country_tbl set country_name=?, country_code=? where country_id=?");
              preparedStatement.setString(1, countryName);
              preparedStatement.setString(2, countryCode);
              preparedStatement.setLong(3, countryId);
              preparedStatement.executeUpdate();
              status = true;
          } finally {
              if (connection != null) {connection.close();}
              if (preparedStatement != null) {preparedStatement.close();}
          }
  
          return status;
      }
  }
  </code></pre>
  
  <p>These methods are called appropriately from a Servlet after validation is performed. The Servlet in turn, interacts with a JSP (along with JSTL/EL).</p>
  
  <p>There is only one question. Curretly, <code>connection</code>, <code>preparedStatement</code> and <code>resultSet</code> are all <strong>local variables to specific methods</strong>.</p>
  
  <p>Can I declare them only at one place as class members (instance variables)? Can doing so maintain a consistent state precisely?</p>
  
  <p><sub>There is no need concentrate much on the core logic. Please don't just say, <em>It is better to use an MVC framework :)</em></sub></p>