How to create a DAO for join tables?

dao, join

Solution

`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

Problem

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 `DAO`for that? Should I place it to the `StudentDao`? or to `SubjectDao`? or there's a good practice in that kind of situation?

Original source