title: How can I improve my DAO? - Java EE
body: <p>I want to access my applicant database that's why I created a DAO class for it.</p>
  
  <p>I think I have lots of code smells because I keep on repeating some code. So what can I do to make my code simpler in order to achieve less code smells? What rules am I violating? How can i improve my code? Thank you.</p>
  
  <p>My code is as follows:</p>
  
  <pre><code>public class ApplicantDAO {
  
  private static ApplicantDAO me = null;
  
  private ApplicantDAO(){};
  
  public static synchronized ApplicantDAO getInstance() {
      if(me == null) {
          me = new ApplicantDAO();
      }
      return me;
  }
  
  public Applicant getApplicant(int applicantNumber) throws SQLException {
      Applicant applicant = null;
  
      Connection conn = null; 
      Statement statement= null;
      String query = null;
      ResultSet rs = null;
  
      try {
          conn = ConnectionManager.getConnection();
          statement = conn.createStatement();
          query = "SELECT * FROM applicant WHERE applicant_no = '" + applicantNumber +"'";    //check applicant_number
          rs = statement.executeQuery(query);
  
          while(rs.next()){
              applicant = new Applicant();
  
              applicant.setApplicantNumber(rs.getInt("applicant_no"));
              applicant.setApplicationDate(rs.getString("applicant_date")); 
              applicant.setfName(rs.getString("first_name"));
              applicant.setlName(rs.getString("last_name"));
              applicant.setmName(rs.getString("middle_name"));
              applicant.setAge(rs.getInt("age"));
              applicant.setGender(rs.getString("gender"));
              applicant.setEmail(rs.getString("email_address"));
              applicant.setContactNumber(rs.getString("contact_no"));
              applicant.setCity(rs.getString("city"));
              applicant.setSchool(rs.getString("school"));
              applicant.setCourse(rs.getString("course"));
              applicant.setYearGraduated(rs.getInt("year_graduated"));
              applicant.setYearWorkExp(rs.getInt("year_work_exp"));
              applicant.setSourceChannel(rs.getString("source_channel"));
              applicant.setStatus_id(rs.getInt("status_id"));
  
          }
  
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }finally {
          if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
          if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
          if (conn!= null) try { conn.close(); } catch (SQLException logOrIgnore) {}
      }
  
  
      return applicant;
  }
  
  public ArrayList&lt;Applicant&gt; getApplicants() throws SQLException{
      ArrayList&lt;Applicant&gt; applicantList = null;
      Applicant applicant = null;
  
      Connection conn = null; 
      Statement statement= null;
      String query = null;
      ResultSet rs = null;
  
      try {
          conn = ConnectionManager.getConnection();
          statement = conn.createStatement();
          query = "select * from applicant";
          rs = statement.executeQuery(query);
  
          while(rs.next()){
              if(applicantList == null){
                  applicantList = new ArrayList&lt;Applicant&gt;();
              }
              applicant = new Applicant();
  
              applicant.setApplicantNumber(rs.getInt("applicant_no"));
              applicant.setApplicationDate(rs.getString("applicant_date")); 
              applicant.setfName(rs.getString("first_name"));
              applicant.setlName(rs.getString("last_name"));
              applicant.setmName(rs.getString("middle_name"));
              applicant.setAge(rs.getInt("age"));
              applicant.setGender(rs.getString("gender"));
              applicant.setEmail(rs.getString("email_address"));
              applicant.setContactNumber(rs.getString("contact_no"));
              applicant.setCity(rs.getString("city"));
              applicant.setSchool(rs.getString("school"));
              applicant.setCourse(rs.getString("course"));
              applicant.setYearGraduated(rs.getInt("year_graduated"));
              applicant.setYearWorkExp(rs.getInt("year_work_exp"));
              applicant.setSourceChannel(rs.getString("source_channel"));
              applicant.setStatus_id(rs.getInt("status_id"));
  
              applicantList.add(applicant);
          }
  
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }finally{
           if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
           if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
           if (conn!= null) try { conn.close(); } catch (SQLException logOrIgnore) {}
      }
      return applicantList;
  }
  </code></pre>