How to create a DAO for join tables?

Question

I'm currently on learning on using Dao pattern in my project. I know, one Table is equivalent to one Dao, am I right? just like StudentDao, SubjectDao.

Each Dao performs CRUD operations in their associated tables, but my question is, how am I going to create a DAO for joined tables? lets say I have a query to join student and subject table, then how do I create a DAOfor that?

Should I place it to the StudentDao? or to SubjectDao? or there's a good practice in that kind of situation?

Answer 1

DAO - Data Access Object is Object that should only communicate with database. So if you want to JOIN two tables so you must have in your DTO Object StudentDTO reference on SubjectDTO.

public class StudentDTO {

   private String name;
   private String surname;
   private String age;
   private SubjectDTO subject;

  // getters, setters
}

So, SubjectDTO

public class SubjectDTO {

   private String name;
   private int room;

  // getters, setters
}

And DAO can look like this:

public StudentDAO {

   private final String SELECT_QUERY = "SELECT * FROM Student S JOIN Subject Sb ON (S.id = Sb.id)"

   public ArrayList<StudentDTO> getData() {

      ArrayList<StudentDTO> data = null;
      StudentDTO member = null;
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs = null;

      try {
         con = OracleDAOFactory.getConnection();
         ps = con.prepareStatement(SELECT_QUERY);
         rs = ps.executeQuery();
         while (rs.next()) {
            member = new StudentDTO();
            member.setName(rs.getString(1));
            ...
            data.add(member);
         }
         return data;
      }
      catch (SQLException ex) {
         // body
      }
      finally {
         if (con != null) {
            con.close();
         }
      }
   }
}

I recommend to you check some tutorials.

Regards