title: jdbc performance
body: <p>There are thee tables inside my database.  One is <code>employee</code>, the second is <code>employee_Project</code>, and the third is <code>employee_Reporting</code>.  Each table has a common <code>employee_Number</code> as its primary key, and there is a one to many relationship among them such that an employee has many projects and reporting dates.</p>
  
  <p>I have run <code>select * from employee</code>, <code>select * from employee_project</code>, <code>select * from employee_reporting</code> in three data holder classes which have methods <code>fillResultSet(Result set)</code> and <code>List&lt;T&gt; getData()</code>. This is based on a <code>SqlDbEngine</code> class with a  <code>runQuery(PreparedStatement,DataHolder)</code> method, and the implementation has been completed. </p>
  
  <p>Now I have to design a <code>getAllEmployee()</code> method along with project and reporting detail with optimal code in java using JDBC.  I have used an iterator but this solution is not acceptable; now I have to use a foreach loop.</p>
  
  <p>This is what I have done:</p>
  
  <pre><code>public List&lt;Employee&gt; getAllEmployees() {
      EmployeeDataHolderImpl empdataholder = new EmployeeDataHolderImpl();
      List&lt;Employee&gt; list_Employee_Add = null;
  
      try {
          Connection connection = mySqlDbConnection.getConnection();
          PreparedStatement preparedStatement = connection
                  .prepareStatement(GET_ALL_EMPLOYEE_DETAILS);
          mySqlDBEngineImpl.runQuery(preparedStatement, empdataholder);
      } catch (SQLException e) {
  
          e.printStackTrace();
      }
      for (Employee employee : empdataholder.getData()) {
          new EmployeeDAOImpl().getProject(employee);
                      new EmployeeDAOImpl.getReport(employee);
      }
      list_Employee_Add = empdataholder.getData();
      return list_Employee_Add;
  
  }
  </code></pre>
  
  <p>and make another method</p>
  
  <pre><code>    public void getProject(Employee emp) {
      EmployeeProjectDataHolderImpl employeeProjectHolder = new EmployeeProjectDataHolderImpl();
      try {
          Connection connection = mySqlDbConnection.getConnection();
          PreparedStatement preparedStatement = connection
                  .prepareStatement(GET_ALL_PROJECT_DETAILS);
          mySqlDBEngineImpl
                  .runQuery(preparedStatement, employeeProjectHolder);
      } catch (SQLException e) {
          e.printStackTrace();
      }
      for (EmployeeProject employee_Project : employeeProjectHolder.getData()) {
          if (employee_Project.getEmployeeNumber() == emp.getEmpNumber()) {
              emp.getProjects().add(employee_Project);
          }
      }
  }
  
      public void getReport(Employee emp) {
      EmployeeReportDataHolderImpl employeeReportHolder = new EmployeeReportDataHolderImpl();
      try {
          Connection connection = mySqlDbConnection.getConnection();
          PreparedStatement preparedStatement = connection
                  .prepareStatement(GET_ALL_REPORT_DETAILS);
          mySqlDBEngineImpl
                  .runQuery(preparedStatement, employeeReportHolder);
      } catch (SQLException e) {
          e.printStackTrace();
      }
      for (EmployeeReport employee_Report : employeeReportHolder.getData()) {
          if (employee_Report.getEmployeeNumber() == emp.getEmpNumber()) {
              emp.getProjects().add(employee_Project);
          }
      }
  }
  }
  </code></pre>
  
  <p>and same for Employee Reporting but doing, this performance is going to decrease.no body worry about closing connection i will do it  </p>
  
  <p>Please tell me how I could improve my solution..</p>