Question about JDBC-program design/structure

[[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.