What's the "best" approach when needed to get info from multiple DAOs?

[Juan Chris]

I'm developing a software to manage a fictitious Cable TV system. I have the models `Channel` and `Program`:

public class Channel {

	private Long id;
	private String name;
	private int number;
	private ArrayList<Program> programs;

	public Channel(String name, int number, ArrayList<Program> programs) {
		this.name = name;
		this.number = number;
		this.programs = programs;
	}

	public Channel(Long id, String name, int number, ArrayList<Program> programs) {
		this(name, number, programs);
		this.id = id;
	}

	@Override
	public String toString() {
		final StringBuilder sb = new StringBuilder("Channel{");
		sb.append("id='").append(id).append('\'');
		sb.append(", name='").append(name).append('\'');
		sb.append(", number='").append(number).append('\'');
		sb.append(", programs='").append(programs).append('\'');
		sb.append('}');
		return sb.toString();
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getNumber() {
		return number;
	}

	public void setNumber(int number) {
		this.number = number;
	}

	public ArrayList<Program> getPrograms() {
		return programs;
	}

	public void setPrograms(ArrayList<Program> programs) {
		this.programs = programs;
	}
}

public class Program {

	private Long id = null;
	private String name;
	private String summary;

	public Program(String name, String summary) {
		this.name = name;
		this.summary = summary;
	}

	public Program(Long id, String name, String summary) {
		this(name, summary);
		this.id = id;
	}

	@Override
	public String toString() {
		final StringBuilder sb = new StringBuilder("Program{");
		sb.append("id='").append(id).append('\'');
		sb.append(", name='").append(name).append('\'');
		sb.append(", summary='").append(summary).append('\'');
		sb.append('}');
		return sb.toString();
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSummary() {
		return summary;
	}

	public void setSummary(String summary) {
		this.summary = summary;
	}
}

And I have DAO implementations in MySQL for both of them. The problem is that when I call findAll() that returns a ArrayList<Channel> I'm obligated to call ProgramMySQLDAO inside the method because to construct one Channel I need the list of programs, hence I need to get those programs from the `Schedule` DB where I have the relation channel_id, program_id. This is becoming out of control. If I follow this pattern my CustomerMySQLDAO will also need to call others DAO to get subscriptions, etc. How and where can I handle those kind of calls? The only one that doesn't need all those crazy calls is my ProgramMySQLDAO because it doesn't depend on anything, see below the code for it:

public class ProgramMySQLDAO implements ProgramDAO {
	private Connection connection;

	public ProgramMySQLDAO() {
		this.connection = new MySQLConnectionFactory().getConnection();
	}

	@Override
	public ArrayList<Program> findAll() {
		String sql = "SELECT * FROM Program";

		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			ArrayList<Program> programs = new ArrayList<>();
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				Long id = rs.getLong("id");
				String name = rs.getString("name");
				String summary = rs.getString("summary");

				programs.add(new Program(id, name, summary));
			}

			return programs;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public Program findById(Long id) {
		String sql = "SELECT * FROM Program WHERE id=?";

		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			Program program = null;

			stmt.setLong(1, id);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String name = rs.getString("name");
				String summary = rs.getString("summary");

				program = new Program(id, name, summary);
			}

			return program;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public Program findByName(String name) {
		String sql = "SELECT * FROM Program WHERE name=?";

		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			Program program = null;
			stmt.setString(1, name);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				Long id = rs.getLong("id");
				String summary = rs.getString("summary");

				program = new Program(id, name, summary);
			}

			return program;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void insert(Program program) {
		String sql = "INSERT IGNORE INTO Program (name, summary) VALUES (?, ?)";

		try (PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
			stmt.setString(1, program.getName());
			stmt.setString(2, program.getSummary());
			stmt.execute();

			ResultSet rs = stmt.getGeneratedKeys();
			while (rs.next()) {
				Long id = rs.getLong(1);
				program.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void update(Program program) {
		String sql = "UPDATE Program SET name=?, summary=? WHERE id=?";

		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			stmt.setString(1, program.getName());
			stmt.setString(2, program.getSummary());
			stmt.setLong(3, program.getId());
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void delete(Program program) {
		String sql = "DELETE FROM Program WHERE id=?";

		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			stmt.setLong(1, program.getId());
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

[Junilu Lacar]

I'll just point you in a direction for now, then you can come back with specific questions later.  Search for loading a complex object graph. Pay attention to the ones that have references to Hibernate and JPA.

[Juan Chris]

Junilu Lacar wrote:I'll just point you in a direction for now, then you can come back with specific questions later.  Search for loading a complex object graph. Pay attention to the ones that have references to Hibernate and JPA.

I tried doing what you said but I'm not experienced enough to even know what Hibernate and JPA means. I'm just starting with Modeal + DAO. So, I advanced a little bit, I end up with the following method:

	@Override
	public Channel findById(Long id) {
		String sql = "SELECT * FROM Channel WHERE id=?";

		try (PreparedStatement stmt = connection.prepareStatement(sql)) {
			Channel channel = null;
			stmt.setLong(1, id);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String name = rs.getString("name");
				int number = rs.getInt("number");

				sql = "SELECT Schedule.program_id FROM Schedule WHERE channel_id=?";
				PreparedStatement stmt2 = connection.prepareStatement(sql);
				stmt2.setLong(1, id);
				ResultSet rs2 = stmt2.executeQuery();

				ProgramMySQLDAO programMySQLDAO = new ProgramMySQLDAO();
				ArrayList<Program> programs = new ArrayList<>();
				while (rs2.next()) {
					programs.add(programMySQLDAO.findById(rs2.getLong(1)));
				}

				channel = new Channel(id, name, number, programs);
			}

			return channel;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

The problem is that I'm calling ProgramMySQLDAO inside my ChannelMySQLDAO , and I feel that this is a bad practice.

[Junilu Lacar]

Juan Chris wrote:... and I feel that this is a bad practice.

Well, I have to commend you for that. For someone who claims to not have that much experience, you seem to have the sense of "design smells" that only more experienced people have.

In my experience, creating those kinds of dependencies is usually a recipe for pain, which of course can lead to disaster. It's often better to introduce another layer, one that aggregates and routes object data from/to multiple DAOs. When the logic becomes too complex, an ORM framework like Hibernate usually helps by taking care of most of the heavy lifting and plumbing work and you're left to deal with the more abstract and high-level abstract design aspects.  While an ORM framework like Hibernate can introduce its own constraints and challenges, the cost of learning and using it is often outweighed by the benefits it gives you in terms of consistency, flexibility, and often even, performance.

You can learn more about the JPA here: https://docs.oracle.com/javaee/6/tutorial/doc/bnbpz.html

You shouldn't be intimidated by either Hibernate or JPA. Even if you don't know much about them or even intend to use them, you can still get some ideas for some of the approaches that they use to manage the complexities of loading complex object graphs.

If you're not sure what an object graph is, it's just the formal name given to what you have: an object that contains references to other objects that contain references to other objects that contain references ... and so on.  That entire web of related objects you have is what's called an object graph.

[Carey Brown]

I don't see where you are capturing the relationship between Channel and Program. I assume that you have a one-to-many relationship in that a channel can have multiple programs. It's possible it is a many-to-many relationship but I can't tell from what you've told us. With one-to-many you could add a relationship table with a column for channel ID and another column for program ID. If it indeed a one-to-many relationship you could add another column to your program table to hold a channel ID.

[Liutauras Vilda]

Off topic, one tip.

You have your constructors as below:

public Channel(String name, int number, ArrayList<Program> programs) {
    this.name = name;
    this.number = number;
    this.programs = programs;
}
 
public Channel(Long id, String name, int number, ArrayList<Program> programs) {
    this(name, number, programs);
    this.id = id;
}

In such way you're risking to have an 'id' in illegal state in case of first constructor invocation. What you really need to do when you have multiple constructors, is, the longest constructor's parameters initialize explicitly, and use that constructor in other (shorter overloaded) constructors.

i.e.:

public Channel(String name, int number, ArrayList<Program> programs) {
    this(0, name, number, programs);
}
 
public Channel(Long id, String name, int number, ArrayList<Program> programs) {
    this.id = id;
    this.name = name;
    this.number = number;
    this.programs = programs;
}

This way, you'd always have 'id' value in an intended state. Of course this case doesn't demonstrate such risk very well as 'id' would be 0 anyway, but I think you understand what I mean.

[Junilu Lacar]

Also unrelated to the main line of questioning but an important point nonetheless, you should prefer to program to interfaces rather than implementations.

Anywhere you declare a parameter or a variable as an ArrayList<whatever>, you should change it to List<whatever> instead. This gives you maximum flexibility in passing references to these objects around in your program. If you write too much code that's specific to the ArrayList implementation type, you'll have a huge cleanup job later when you finally figure out that it would have been better if you had declared them as List instead.

[Dave Tolls]

Carey Brown wrote:With one-to-many you could add a relationship table with a column for channel ID and another column for program ID.

One to many doesn't need a mapping table.
That's for many to many relationships.
One to many just needs an id on the "many" side.

However, I suspect that many to many is what is needed here...at least based on the number of channels that show CSI on Freeview!

Liutauras Vilda wrote:Of course this case doesn't demonstrate such risk very well as 'id' would be 0 anyway, but I think you understand what I mean.

In this case the id should be null.
The constructor with no id represents an object that hasn't been persisted to the database and so has no id yet.
You'll see it left alone in most models for this reason, since setting it to null is a bit pointless.

[Liutauras Vilda]

Dave Tolls wrote:In this case the id should be null.
The constructor with no id represents an object that hasn't been persisted to the database and so has no id yet.
You'll see it left alone in most models for this reason, since setting it to null is a bit pointless.

And if there were or would be a theoretical need of 3rd constructor, for that would look wonky and not so explicit:

public Channel(String name, int number) {
    this.name = name;
    this.number = number;
}

public Channel(String name, int number, ArrayList<Program> programs) {
    this(name, number);
    this.programs = programs;
}
  
public Channel(Long id, String name, int number, ArrayList<Program> programs) {
    this(name, number, programs);
    this.id = id;
}

That version for me is more readable and tells the intention about the default values in case arguments were not supplied:

public Channel(String name, int number) {
    this(null, name, number, null);
}

public Channel(String name, int number, ArrayList<Program> programs) {
    this(null, name, number, programs);
}
  
public Channel(Long id, String name, int number, ArrayList<Program> programs) {
    this.id = id;
    this.name = name;
    this.number = number;
    this.programs = programs;
}

But yeah, I didn't take context of the problem in this case, well, at least OP can know about the difference of designing constructors in one or another way, that might would matter in other case.

[Dave Tolls]

It's why, for JPA models, you tend to only see no args constructors.
Everything is set via the setters.

[Juan Chris]

Thanks for the tips guys, I'll be reading about it. One thing I see a lot of people using and recommending is the approach: Model + DAO + Service. Inside the Service is where I should be calling more than one DAOs to get information from different places, I believe that makes sense.

[Junilu Lacar]

Juan Chris wrote:Inside the Service is where I should be calling more than one DAOs to get information from different places, I believe that makes sense.

That's the extra layer I was telling you about.  Architecturally, it would look more like  Model <--> Service <--> DAO <--> Persistent Store (DB, etc.)

[Juan Chris]

Junilu Lacar wrote:

Juan Chris wrote:Inside the Service is where I should be calling more than one DAOs to get information from different places, I believe that makes sense.

That's the extra layer I was telling you about.  Architecturally, it would look more like  Model <--> Service <--> DAO <--> Persistent Store (DB, etc.)

First, what do you mean by this "<-->"?

Second, tell me if I'm wrong, but what I have in mind is:

- Model is just a skeleton that we use when we want to do a INSERT into the database or when we SELECT something from into. Of course, after we get our Model instance from the database we may use it as we please (using the methods defined in our Model class like `getName()`, `getBirthdate()`, etc) to do whatever our software needs.
- DAO interface have the methods header that must be implemented, `findAll()`, `findByCustomer(Customer customer)`, `findByWhatever(Whatever whatever)`, `insert(Whatever whatever)`, `update(Whatever whatever)`, `delete(Whatever whatever)`.
- DAO implementation is where we put the code that will talk to our data source, be it SQLite, MySQL, Oracle, XML, file, etc.
- Service is where we will have instances of one or more DAOImpl and use those to have Models to work with and do our business logic, e.g., we may use CustomerDAOImpl to retrieve some Customer and use TvPlanDAOImpl to retrive a TvPlan, do some `getId()` and store it into the table Subscription (two columns, FK customer_id and FK tvplan_id) using SubscriptionDAOImpl.

Is that right?

[Carey Brown]

Juan Chris wrote:First, what do you mean by this "<-->"?

Bi-directional communication.

[Junilu Lacar]

Carey Brown wrote:

Juan Chris wrote:First, what do you mean by this "<-->"?

Bi-directional communication.

More or less. I was thinking more along the lines of dependencies but communication is close enough  

Actually, now that you point that out, I guess it really should be a one way arrow from Service/DAO --> Model

You don't want your model to have a strong dependency on a Service or DAO. Domain objects should be fairly independent of infrastructure layer classes like DAO. Service classes kind of straddle the fence between the domain and infrastructure.