Manage table relationships in Java dynamic web application that accesses a database, without using any framework

Question

I'm developing a web application for academic purposes.
What i have to do is a simple website which keeps track of realties along with their respective owners and the tags they are bound to.

The application takes data from a MySQL database and must include the following features:

• List all realties
• List all realties with a specific tag
• List all realties owned by a specific owner entity
• List all realties owned by a specific owner and with a specific tag

It is required to use JDBC to connect to the database, JSP/Servlet to create the dynamic pages, (optionally) Java Beans to display properties (either using useBean or JSTL/EL) and no frameworks. Everything must be done in pure Java.

It is not a complex application, and it being just for academic purposes also means that i do not have to keep in consideration any scalability issue. I could simply put the code for the database connectivity and queries in the JSP page as a scriptlet and call it a day.

However, what i really want to do is to follow the MVC pattern and learn to make tidy code.

This is the approach i'd like to follow:

• The JSP pages shape the view layer, only containing HTML and JSTL to access the model
• The model consists of the Java Beans. Each JB represents a table with its respective columns
• Servlets make the controller layer. They call methods from Service classes containing database queries and send the resulting Java Beans in the view layer by dispatching the request to a JSP.

This is how i structured the tables:

Realties
-----------------
(pk) id
surface_area
rooms_number
year
price
(fk) owner

Owners
-----------------
(pk) id
name
surname

Tags
-----------------
(pk) id
title  


realties_xref_tags
-----------------
(pk)(fk) realty_id
(pk)(fk) tag_id  

There is a many-to-one relationship between the realties and the owners table and a many-to-many relationship between the realties and the tags table (A realty can have a list of tags)

These are the Beans, one for each table except for realties_xref_tags. (Getters and setters are omitted)

public class Realty implements Serializable {

    private BigInteger id;
    private double surface_area;
    private int rooms_number;
    private int year;
    private BigDecimal price;
    private BigInteger owner_id;
}

public class Owner implements Serializable {

    private BigInteger id;
    private String name;
    private String surname;
}

public class Tag implements Serializable {

    private BigInteger id;
    private String title;
}

Here, i am having troubles trying to find out the best way of managing relationships.

• Should the Realty bean keep track of its owner with a field holding the foreign key, or should it hold a reference to an actual Owner object corresponding to that foreign key? I understand the latter choice may not be optimal with a big number of attributes or entries.

• How to avoid instantiating duplicate Beans for the same Owner entity? Consider a scenario where there are 10 realties and 4 owners. There would be realties which have the same owner, but the application would still end up instantiating 10 Owner beans when trying to fetch all entries
  EDIT: i just had this little idea: is it ok to create a HashMap<BigInteger, Owner> map in the Service class's method for finding entries, so that it calls map.get(ownerId) and then executes the SELECT query ONLY if it returns null?

• Should i just make a List<String> field for tags in the Realties class?

Which approach should i take to handle relationships in DTOs so that it results in good practice in a (small) real-world project scenario?

Answer 1

Answers to this are going to be necessarily opinionated. And in your case, since as you say you don't need to worry about scaling, anything works, really.

My opinion on it: prefer to expose ids in your model instead of objects.

Reasoning:

• any part of your code can call SomeEntity.getById()
• if you have more complex non-CRUD type queries, like an aggregation over a dynamic range of records, it is most efficient in terms of maintainability to code them as a special case
• if you don't "keep it simple", and your application grows, you end up reinventing a broken implementation of an object-relational mapper