Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteiTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Put SQL inside DAO classes, put library rules and transaction boundaries inside a service class, and let the user interface call only the service. That split keeps each checkout rule in one place and lets the storage details change without touching the workflow.
The design below is an illustrative example, not a verified codebase. The class names, schema, and checkout flow are proposals you can adapt to your own database and Java version.
What the DAO pattern is
A Data Access Object (DAO) gives the rest of your program a simple interface to a data source and hides how that source is reached. Oracle’s “Design Patterns: Data Access Object” page puts the goal this way: “The DAO pattern allows data access mechanisms to change independently of the code that uses them.”
Free tools Windows power users keep installed
One-click scans. No signup required.
In a Java application, the data source is usually a relational database reached through JDBC, the Java API for connecting to data sources, running queries and updates, and reading results. A DAO is the place where those JDBC calls live. Code outside the DAO asks for a book or saves a loan and never sees a PreparedStatement or a ResultSet.
DAO versus service layer
A DAO and a service answer different questions. The DAO answers “how do I read or write this record?” The service answers “is this operation allowed, and which changes must succeed together?” Mixing the two is the most common reason a small project becomes hard to change.
| Layer | Owns | Should not contain |
|---|---|---|
| UI or controller | Reading input, showing results, calling the service | SQL, loan-limit rules, direct DAO calls |
Service (LibraryService) |
Business rules such as member eligibility and copy availability; workflow order; the transaction boundary | SQL strings, ResultSet handling, connection setup |
DAO (BookDao, MemberDao, LoanDao) |
Queries, inserts, updates, and mapping rows to domain objects | Decisions such as “a member may hold three loans” |
A proposed structure for the library system
The following layout is a design recommendation for a system with members, books, copies, and loans. It is not established as the structure of any existing project.
Proposed schema
members: member_id (primary key), name, active flagbooks: book_id (primary key), title, isbnbook_copies: copy_id (primary key), book_id (foreign key), status such as AVAILABLE or CHECKED_OUTloans: loan_id (primary key), copy_id and member_id (foreign keys), checked_out_at, due_date, returned_at
BookDao
Reads book and copy records, finds copies by status, and updates a copy’s status. It accepts a Connection from the caller so it can take part in a larger transaction.
MemberDao
Loads a member by id and checks whether the member is active. It contains no loan-limit logic.
Rank #2
LoanDao
Inserts loan rows, counts a member’s open loans, and marks a loan as returned.
LibraryService
Exposes operations such as checkOut, returnBook, and findAvailableCopies. It is the only class that combines several DAO calls into one business action and decides when to commit or roll back.
UI or controller
Turns user input into service calls and displays the outcome or the exception message. It has no import of JDBC classes.
Walking through a checkout
Checking out a book shows where each layer’s responsibility begins and ends. The steps below describe the proposed flow.
- The controller calls
LibraryService.checkOut(memberId, copyId). - The service opens a connection and turns off auto-commit so the steps below form one unit of work.
- The service asks
MemberDaoto load the member and rejects the request if the member is inactive. - The service asks
LoanDaoto count the member’s open loans and rejects the request if the member is at the limit defined by library policy. - The service asks
BookDaofor the copy’s current status and rejects the request unless it is AVAILABLE. - The service asks
LoanDaoto insert the new loan row. - The service asks
BookDaoto set the copy’s status to CHECKED_OUT. - If every step succeeded, the service commits. If any step threw an exception, it rolls back, so no loan exists without a matching copy status change, or the reverse.
Notice that the DAOs never decide whether a checkout is allowed. They only report what is stored and write what they are told to write.
Where the transaction belongs
The service layer is the right place to open, commit, and roll back a transaction, because only the service knows which steps belong together. A DAO that commits on its own would make the loan insert permanent even if the copy update later fails.
A sketch of the boundary in the service looks like this:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchConnection conn = dataSource.getConnection();
try {
conn.setAutoCommit(false);
// validate member, validate copy, then call the DAOs with conn
loanDao.insert(conn, loan);
bookDao.updateCopyStatus(conn, copyId, "CHECKED_OUT");
conn.commit();
} catch (SQLException | RuntimeException e) {
conn.rollback();
throw e;
} finally {
conn.close();
}The sketch is simplified. A production version would wrap the rollback in its own try block, because a rollback can fail, and it would translate SQL exceptions into domain-level messages before they reach the controller.
Rank #4
The availability race
Checking the copy’s status and then updating it is not safe on its own. Two members could read the same AVAILABLE copy at nearly the same moment. Two common fixes are a conditional update that succeeds only if the copy is still available, and a row lock taken when the copy is read. The conditional form is the simpler option:
UPDATE book_copies SET status = 'CHECKED_OUT'
WHERE copy_id = ? AND status = 'AVAILABLE'If the update reports zero affected rows, the service should throw an “already checked out” error and roll back. Row-locking syntax, such as SELECT ... FOR UPDATE, varies by database, so confirm it against your engine’s documentation.
Writing the DAO methods
Three habits matter in every DAO method: use prepared statements for any user-supplied value, map each row to an object in one place, and close JDBC resources reliably. Oracle’s JDBC tutorial covers prepared statements, exception handling, and transaction use, and those topics apply directly to DAO code. The following method loads a member:
public Optional<Member> findById(Connection conn, long memberId) throws SQLException {
String sql = "SELECT member_id, name, active FROM members WHERE member_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, memberId);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
return Optional.empty();
}
return Optional.of(new Member(
rs.getLong("member_id"),
rs.getString("name"),
rs.getBoolean("active")));
}
}
}The try-with-resources blocks close the statement and result set even when an exception occurs. Confirm the exact syntax and driver behavior against the JDK and JDBC driver you actually use.
Best Value
Two-tier and three-tier JDBC are not your service layer
JDBC supports two-tier and three-tier data access. In the two-tier model, the application talks directly to the data source through a driver. Oracle’s “JDBC Architecture” page describes the three-tier model this way: “In the three-tier model, commands are sent to a ‘middle tier’ of services, which then sends the commands to the data source.”
The service layer in this design is an application-level concept inside your program. It does not decide which JDBC tier model you use. A desktop or single-server library app will usually run a two-tier arrangement with the service class and DAOs in the same process, which is still a valid separation of responsibilities.
Trade-offs and when to simplify
Separating DAOs from services adds classes, interfaces, and method parameters. Whether that cost is worth paying depends on the application’s size and how often its rules change. Four questions help decide:
- Does the UI or controller reach the database directly, or only through the service?
- Is all persistence code isolated behind DAO classes, so a schema change touches one package?
- Do multi-step operations such as checkout share one transaction that the service controls?
- Does the abstraction pay for itself? A project with two tables and a single screen may fold the service into the controller without much risk; a project with checkout, renewal, fines, and reporting rules benefits from the full split.
Passing Connection objects into DAO methods keeps transactions in the service, but it does leak a JDBC type into service-facing code. Teams that want to avoid that leak often introduce a small unit-of-work object. That is a valid alternative, but it adds another moving part.
Versions and sources to check
Oracle’s Java Tutorials state that their examples are JDK 8-era material and may use technology that is no longer available. Use them for concepts such as prepared statements, exceptions, and transactions, and check any version-specific API against current Java and JDBC driver documentation. Oracle’s Core J2EE DAO material and its Spring DAO article date from an older enterprise context, including a Spring 2.0 article from 2006, so treat them as background on the pattern rather than current framework advice.
This article does not assume a particular database, framework, or user interface. If your project uses H2, PostgreSQL, MySQL, or another engine, the DAO and service boundaries stay the same; only the SQL dialect, the driver, and the row-locking syntax change.
The Bottom Line
For a library system, keep JDBC inside DAOs, keep checkout rules and transaction control in the service, and keep the UI away from both SQL and business decisions. Use the full split once the workflow has several rules or multi-step operations; a very small application can fold the service into the controller without losing the core benefit.
Recommended Free Tools
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.
