Put SQL in data access objects (DAOs), put library rules and multi-step workflows in a service class, and let the user interface call only the service. Wrap each workflow that changes more than one table in a single database transaction that the service controls. The layout below is a proposed design for a small Java library system built on JDBC. It is not a description of a finished codebase, and the class names, schema, and database choices are examples you can adapt.
What a DAO does and what it does not
A data access object is a class whose job is to read and write one kind of stored data. Oracle’s “Design Patterns: Data Access Object” page puts the core idea this way: “The DAO pattern allows data access mechanisms to change independently of the code that uses them.” In practice, that means code outside the DAO asks for a book or a loan by meaning, and the DAO decides how to fetch it.
As an Amazon Associate I earn from qualifying purchases.
A DAO should answer questions like “find a member by ID,” “insert a loan,” or “mark a copy as on loan.” It should not decide whether a member is allowed to borrow, how many days a loan lasts, or whether a copy can be checked out. Those decisions belong to a higher layer, and keeping them out of the DAO is what lets you change the storage mechanism later without rewriting the library rules.
Where JDBC fits
JDBC is the Java API for connecting to a data source, sending queries and updates, and processing the results. Your DAOs are the code that uses JDBC directly. Everything else in the application should see plain Java objects and method calls, not ResultSet objects or SQL strings.
Oracle’s JDBC architecture material describes two models. In a two-tier model, the client talks to the database through a driver. In a three-tier model, Oracle’s wording is: “In the three-tier model, commands are sent to a ‘middle tier’ of services, which then sends the commands to the data source.” A layered library application is a small version of the three-tier idea: the service layer is the middle tier that sits between the interface and the persistence code.
The proposed layers
| Layer | Example classes | Owns | Must not |
|---|---|---|---|
| User interface or controller | MainMenu, CheckoutController |
Input parsing, screens, and displaying messages | Write SQL, open connections, or decide whether a checkout is allowed |
| Service | LibraryService |
Library rules, workflow order, transaction boundaries, and translating failures into library errors | Contain SQL text or ResultSet handling |
| DAO | BookDao, MemberDao, LoanDao |
Parameterized SQL for one table or aggregate, row-to-object mapping, and resource cleanup | Enforce loan limits or decide business outcomes |
| Domain objects | Book, Member, Loan |
Plain data and simple invariants | Know about JDBC |
| Database | Tables described below | Storage, constraints, and keys | Be the only place a rule is enforced |
Keep the controller and service apart as well. A controller that calls a single service method per user action is easy to test, and it means a future web or command-line front end can reuse the same service without copying rules.
A proposed schema
The schema below is an example for a relational database with a JDBC driver. Column types and auto-increment syntax differ between databases, so adjust them to the one you choose. An in-memory database is a common choice for automated tests, but nothing in this design requires a particular product.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Rank #2
CREATE TABLE members (
member_id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
active BOOLEAN NOT NULL
);
CREATE TABLE books (
book_id BIGINT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
isbn VARCHAR(20)
);
CREATE TABLE book_copies (
copy_id BIGINT PRIMARY KEY,
book_id BIGINT NOT NULL REFERENCES books(book_id),
status VARCHAR(20) NOT NULL CHECK (status IN ('AVAILABLE', 'ON_LOAN'))
);
CREATE TABLE loans (
loan_id BIGINT PRIMARY KEY,
member_id BIGINT NOT NULL REFERENCES members(member_id),
copy_id BIGINT NOT NULL REFERENCES book_copies(copy_id),
due_date DATE NOT NULL,
returned_on DATE
);Tracking availability at the copy level, rather than as a count on the book row, makes it possible to say which physical copy is out and to block a second checkout of that same copy.
Walking through a checkout
Checkout is the workflow that exposes the layering most clearly, because it reads and writes several tables and must not leave partial results behind. Here is the sequence, with the layer responsible for each step.
- Controller: reads the member ID and copy ID from the user and calls
LibraryService.checkOut(memberId, copyId). - Service: opens a connection, turns off auto-commit, and starts the transaction.
- Service, through
MemberDao: confirms the member exists and is active. - Service, through
LoanDao: counts the member’s open loans and rejects the request if the limit is reached. - DAO, through
BookCopyDao: attempts to claim the copy with a conditional update. The service checks whether exactly one row changed. - Service, through
LoanDao: inserts the loan row with a due date. - Service: commits if every step succeeded, or rolls back if any step failed.
Step 5 deserves attention. A common mistake is to read the copy’s status, see that it is AVAILABLE, and then update it. Two checkouts running at the same time can both pass that read. Making the update conditional on the current status closes the gap:
UPDATE book_copies
SET status = 'ON_LOAN'
WHERE copy_id = ? AND status = 'AVAILABLE';If the update reports zero changed rows, the copy was already taken and the service should reject the checkout. Some databases also offer row locking through SELECT ... FOR UPDATE, but its syntax and behavior vary by product, so check your database’s documentation before relying on it.
Free tools Windows power users keep installed
One-click scans. No signup required.
Where the transaction belongs
The transaction boundary belongs in the service method, not in the DAO. A single DAO method usually performs one statement, so it cannot know whether the other steps in the workflow will succeed. The service knows the whole workflow, so it is the layer that should commit or roll back.
To make this work, DAO methods accept a Connection argument instead of opening their own. The service owns the connection, and every DAO call in the workflow uses that same connection. The sketch below shows the shape; it is not a complete application.
Rank #4
public Loan checkOut(long memberId, long copyId) throws LibraryException {
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false);
try {
if (!memberDao.isActive(conn, memberId)) {
throw new LibraryException("Member is not active");
}
if (loanDao.countOpenLoans(conn, memberId) >= MAX_OPEN_LOANS) {
throw new LibraryException("Loan limit reached");
}
if (!copyDao.markOnLoanIfAvailable(conn, copyId)) {
throw new LibraryException("Copy is not available");
}
Loan loan = loanDao.insert(conn, memberId, copyId,
LocalDate.now().plusDays(LOAN_DAYS));
conn.commit();
return loan;
} catch (LibraryException e) {
rollbackQuietly(conn);
throw e;
} catch (SQLException e) {
rollbackQuietly(conn);
throw new LibraryException("Checkout failed", e);
}
} catch (SQLException e) {
throw new LibraryException("Could not obtain a database connection", e);
}
}
private void rollbackQuietly(Connection conn) {
try {
conn.rollback();
} catch (SQLException ignored) {
// Logged in a real application; the original failure is more useful to the caller.
}
}Two details matter here. First, the connection is closed by try-with-resources on every path, so a failed checkout does not leak it. Second, the rollback happens before the exception leaves the service, so the caller never sees a half-finished loan.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.DAO implementation habits
Every DAO method that takes a value from the user should use a PreparedStatement with ? placeholders. Concatenating user input into SQL text invites injection and makes the code harder to read. Oracle’s JDBC tutorial covers prepared statements, exception handling, and transaction use, and these are the three areas where DAO code most often goes wrong.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Map each row to a domain object inside the DAO, so the service receives a Loan rather than a ResultSet. Close statements and result sets with try-with-resources. A minimal insert looks like this:
Best Value
public Loan insert(Connection conn, long memberId, long copyId, LocalDate due)
throws SQLException {
String sql = "INSERT INTO loans (member_id, copy_id, due_date) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
ps.setLong(1, memberId);
ps.setLong(2, copyId);
ps.setDate(3, java.sql.Date.valueOf(due));
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
keys.next();
return new Loan(keys.getLong(1), memberId, copyId, due, null);
}
}
}Oracle’s JDBC examples are written in JDK 8-era material and warn that some technology they use may no longer be available. Use them for the concepts, and check the current JDK release and your JDBC driver’s documentation before copying any version-specific detail.
Trade-offs of this structure
The layers add classes and indirection. For a classroom project or a single-screen tool, that overhead may outweigh its benefit. The table compares the two extremes along the axes that matter most for a library system.
| Axis | Interface calls data access directly | Service layer mediates access |
|---|---|---|
| Where SQL lives | Often mixed into screen or menu code | Only in DAOs |
| Where rules live | Scattered across screens | Centralized in the service |
| Multi-step transactions | Hard to keep consistent when steps span screens | One transaction per workflow, owned by the service |
| Changing the database | Touches many files | Usually limited to DAO implementations |
| Upfront cost | Lower: fewer classes and interfaces | Higher: more classes, more wiring, more tests to write |
A reasonable rule: if the application has one or two screens and no workflow that touches more than one table, a single data access class may be enough. Once checkout, return, renewal, and fines all share the same rules and tables, the service layer usually pays for itself.
Troubleshooting the design
- Two members check out the same copy. The availability check and the update are separate steps. Move the check into the conditional
UPDATEand test the row count. - A loan row exists but the copy still shows available. The insert and the status update are not in the same transaction, or the service committed before the second step. Confirm one connection and one commit per workflow.
- Connections run out under load. A DAO is opening its own connection or a service method is missing a
closepath. Use try-with-resources at the service level only. - The controller contains SQL. The screen is doing persistence work. Move the query into a DAO method and expose a service method that returns the result.
- Changing the database requires editing the service. SQL or driver-specific types have leaked upward. Keep
ConnectionandResultSetout of the service’s public methods, except for the transaction boundary.
The structure above is a starting point rather than a finished library system. Its value is that each layer has one job, and each rule has one place to live.
Oracle’s Java Tutorials, the Oracle “JDBC Architecture” page, and the “Design Patterns: Data Access Object” page are the primary references for the concepts discussed here.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
