What is the best way to make a DAO layer?

Question

Java

Hello!
I'm doing a small web project in java + gwt for the purpose of self-study.
Communication with the database via JDBC. I don't use Hibernate.
Now I am writing a DAO layer and I have questions.
Let's take, for example, three entities: User, Role, Permission. User has 1 Role, Role has 1 or more Permission.
If we transfer everything to the OOP model, then there will be a User class, it will contain a link to Role, and Role will have a Permission list.
Now, suppose we need to get some User'a from the database. It turns out that you will also have to pull everything else (Role and the Permission list). Role is still okay, there is only 1-record you need to get. But Permission is a whole list. At the same time, I gave the simplest example, because the chain can be much longer.
Another option is not to make the Role field in the User class, but just a field that will store the primary key of the corresponding role.
The second option is somehow not beautiful and does not match the model.
And somehow I still can't think of a flexible, beautiful solution.

Answer 1

inner join and also fill nested objects in dao. If it's too hard for you. Then make the getRoles method lazy
Wrap your User in a proxy that will be on getRoles if roles==null call dao method and write to User

Answer 2

The second option is somehow not beautiful and does not match the model.
Your option is quite good, provided that this primary key already contains enough information.
But if there is a need for a model, use the Proxy pattern. Those. create partially initialized objects and make queries to load the rest of the data as needed.

Answer 3

As I understand it, you are using a relational database, which means you can get by with 1 request, apparently, one user will also not have many permissions, which means that there is no problem as such.
If you still don’t want to fetch all the information about the user at once, then you can make an implementation proxy for those entities that you don’t want to drag along with you, this approach will be good if you need to select many users at once and users have more information than the proxy instance will take.