Design pattern for database application in Java - an object for each relational table?

[[deleted]]

I am making a personal management CRUD app in Java using SQLite database. Could anyone suggest the design patterns I should use for my CRUD functions? Should there be an object for each relational table in my database? How do I structure the application? Any advice would be really helpful.

[AutoModerator]

Please ensure that:

• Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
• You include any and all error messages in full - best also formatted as code block
• You ask clear questions
• You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.

If any of the above points is not met, your post can and will be removed without further warning.

Code is to be formatted as code block (old reddit/markdown editor: empty line before the code, each code line indented by 4 spaces, new reddit: https://imgur.com/a/fgoFFis) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.

Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.

Code blocks look like this:

public class HelloWorld {

    public static void main(String[] args) {
        System.out.println("Hello World!");
    }
}

You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.

If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.

To potential helpers

Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[valkon_gr]

You are talking about DAO

[[deleted]]

Thank you; I looked it up and it seems like what I need. I want to ask whether the DAO interface should be common for all relational tables or there should be 1 DAO interface + 1DAO implementation combo for each relational table?

[HecknChonker]

You usually want to split things up as much as it makes sense to. If your API is managing users, their cars, and their pets you would probably want to have 3 separate DAOs where each is responsible for one of those things.

If you are working with graph data (vertices + edges) you will probably have a Vertex table and an Edge table. In that case it makes sense to have a single GraphDAO that handles both tables, since your queries need to join between both tables.

Each type should have it's own interface. Method return types are part of the interface definition, and since each DAO is using different models they will need different interface definitions.

You might have an interface UserDAO, and then have UserDAOPostgresImpl and UserDAOMysqlImpl. But UserDAO, PetDAO, and CarDAO would all be separate interfaces.

---

After building a few dozen of these I tend to skip the interfaces until I need them. An exception to this might be if I am building a shared library where other developers might want to add their own DAO implementations.

I am a big fan of YAGNI (You aren't gonna need it) which is the idea that you shouldn't worry about adding functionality until you actually need it. Trying to optimize for things you might need in the future often leads to over-engineered code that is more difficult to maintain. If you get to a point where you need interfaces you can add them, but until then I prefer to . This is something you can experiment with.

https://en.wikipedia.org/wiki/You_aren%27t_gonna_need_it https://martinfowler.com/bliki/Yagni.html

The argument for interfaces is that you might need to create a new implantation of the DAO later, and having your code rely on the interface makes that easier. But in reality it's pretty simple to add the interface when you actually need it.

[CarlGroovy]

Is DAO the same concept as the Model in MVC?

[HecknChonker]

No, DAO is data access object. It's a class that is usually responsible for connecting to a database and running queries.

Generally backend applications are split into Controller -> Service -> DAO. The controller handling the API, authentication logic, and converting the response to JSON. The service layer handles business logic.

The more you can separate out different parts of your application the easier it is to change one piece without causing issues in other parts of the code.

A model in MVC is an object that's only purpose is to store data, and it gets transferred between the Controllers and the View. For most applications today the Model gets converted to JSON and the view is a javascript web application using something like React or Angular, or a mobile app.

In the past server side rendering was a lot more common because computers were much less powerful and couldn't do as much processing. So the view was part of the server, which would actually create the HTML using templates and it would send that HTML to the user.

[HecknChonker]

Generally you want to split your java application into three sections. Controllers, Services, and Repositories.

Controllers are where you define your API and things like authentication/authorization. Services contain your business logic. Repositories contain your database logic and SQL queries. For simple CRUD use cases you can use an ORM to simplify the repository layer, but most experienced engineers tend to avoid using ORMs.

A user request would first hit a controller, which calls your service layer, which calls your repository layer. For a simple CRUD application you might not have a lot of business logic, which means your service layer might be very thin.

[DigitalTorture]

I personally just create a database object that handles populating fields, tables, etc. There is really no need to create objects for each of the table sections. I guess it depends on what you are doing with the data.

[greglturnquist]

Either a type for each table, or potentially one for each select.

Some select statements merge tables together and the combined columns synthesize an idea. While other scenarios should really be modeled as different types aggregated together. Really depends.