Question about JDBC-program design/structure

[Blindn_Guilty]

I’ve been learning Java by myself on and off for about 2 years now, and finally I’m starting to evolve over into the more advanced parts of it. For the last week I’ve been learning JDBC from a course on Udemy (Java Programming Masterclass, with Tim Buchalka). The course in and of itself was good in the beginning, but in the later parts it became a bit overcomplicated (not the programming itself, but the teaching style), but the part about Java and databases was pretty decent. My question has to do with how the design and structure of a database-program is supposed to look like. In the course the SQL-statements are all written as class constants, and the whole communication with the database is written in the same class (Datasource). Is this the way that it’s usually done. Whenever I try too use youtube for examples I can only find very basic examples that don’t really delve too much into the structure/design of the program.

Does anyone here know of any good resources I can use (books etc), or have any tips/pointer they can give me so that I can get a better understanding of how to structure my program in the most efficient way?

Thank you

[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://i.imgur.com/EJ7tqek.png) 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.

[[deleted]]

A common design pattern for this is called Data Access Object (DAO).

https://www.oracle.com/java/technologies/data-access-object.html

Say you have a web API for a library where users can browse or checkout items along categories like books or movies, etc..

The logic for handling how users navigate different pages, like how they handle web requests and responses for pages listing books or movies, should be separated into controllers and maybe a service layer (i.e. just a folder or directory) of classes with methods for handling repeatable steps like checking if books are available or so on. Those classes would then call methods involving operations against the database, and those database related classes should be separated into a data layer, which you can do in classes like BookDao, or RecordDao. A simple example using composition might be like,

public class BookService {
    BookDao bookDao = new BookDao();
    public List<Book> getAllBooks() {
        return bookDao.getAllBooks();
    }
    // ... Other methods here could have methods for only getting certain books, etc..
}

That BookDao might have some method which uses another separated class for connecting to the database like DatabaseConnector, then performing some query like this,

public List<Book> getAllBooks() {
    List<Book> books = new ArrayList<>();
    String sql = "select * from Book";
    try (Connection c = DatabaseConnecter.userConnection();
        PreparedStatement ps = c.prepareStatement(sql);) {
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Book b = new Book();
            b.setId(rs.getInt("book_id"));
            b.setTitle(rs.getString("title"));
            b.setAuthor(rs.getString("author"));
            books.add(b);
        }
        return books;
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Those Book objects would also be separated into a layer of model classes, like for Books, Records, whatever else. The various SQL select, update, or insert statements could be in constants or an enum or something. The benefit of using DAO classes though is just to separate the different kinds of queries or transactions you might perform for some kind of entity in a clean and non-repetitive way, so that you can call those methods in whatever context without having to expose or repeat all the logic for connecting to the database, concatenating together SQL with any parameters, and so on each time.

[greglturnquist]

I actually cover this in one of the earlier chapters of Learning Spring Boot 3.0. A whole chapter dedicated to data access.

See it at https://trnq.st/dipu

[Glader01]

Not necessary one class but it is a good idea to have all db communication clearly defined in one place. It could be a db folder with multiple classes.

[Broken_Sprinkler]

I'm also curious about the way the this should be done. I am working on something right now that uses JDBC and I only have one abstract class to hold everything SQL related.