A servlet application should obtain database connections from a DataSource, execute parameterized JDBC statements in a service or repository, and close every result, statement, and connection promptly. The servlet translates HTTP input and output; it should not become the owner of connection credentials or SQL scattered across request branches.
This lesson assumes SQL and servlet basics. After it, you can use a container or application-managed connection pool, bind values safely, define a transaction boundary, map database failures to a controlled response, and avoid sharing JDBC objects across concurrent requests.
Servlets commonly interact with databases using JDBC (Java Database Connectivity). The typical pattern is: Servlet receives request -> calls DAO (Data Access Object) -> DAO uses JDBC -> returns data -> Servlet forwards to JSP view.
package com.example.dao;
import com.example.model.User;
import java.sql.*;
import java.util.*;
public class UserDAO {
// CREATE
public boolean createUser(User user) throws SQLException {
String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, user.getUsername());
ps.setString(2, user.getEmail());
ps.setString(3, user.getPassword()); // Hash in real app!
return ps.executeUpdate() > 0;
}
}
// READ - Get by ID
public User getUserById(int id) throws SQLException {
String sql = "SELECT * FROM users WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return mapRow(rs);
}
}
}
return null;
}
// READ - Get all
public List<User> getAllUsers() throws SQLException {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users ORDER BY username";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
users.add(mapRow(rs));
}
}
return users;
}
// UPDATE
public boolean updateUser(User user) throws SQLException {
String sql = "UPDATE users SET email = ? WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, user.getEmail());
ps.setInt(2, user.getId());
return ps.executeUpdate() > 0;
}
}
// DELETE
public boolean deleteUser(int id) throws SQLException {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
return ps.executeUpdate() > 0;
}
}
private User mapRow(ResultSet rs) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setEmail(rs.getString("email"));
return u;
}
}package com.example.dao;
import java.sql.*;
public class DBUtil {
private static final String URL = "jdbc:mysql://localhost:3306/mydb?useSSL=false";
private static final String USERNAME = "root";
private static final String PASSWORD = "password";
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException("MySQL Driver not found", e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
}@WebServlet("/users")
public class UserServlet extends HttpServlet {
private UserDAO userDAO = new UserDAO();
// GET /users - List all users
// GET /users?id=1 - Get specific user
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
try {
String idParam = req.getParameter("id");
if (idParam != null) {
User user = userDAO.getUserById(Integer.parseInt(idParam));
req.setAttribute("user", user);
req.getRequestDispatcher("/WEB-INF/views/user-detail.jsp")
.forward(req, resp);
} else {
List<User> users = userDAO.getAllUsers();
req.setAttribute("users", users);
req.getRequestDispatcher("/WEB-INF/views/user-list.jsp")
.forward(req, resp);
}
} catch (SQLException e) {
throw new ServletException("Database error", e);
}
}
// POST /users - Create new user
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
User user = new User();
user.setUsername(req.getParameter("username"));
user.setEmail(req.getParameter("email"));
user.setPassword(req.getParameter("password"));
try {
userDAO.createUser(user);
resp.sendRedirect(req.getContextPath() + "/users");
} catch (SQLException e) {
throw new ServletException("Database error", e);
}
}
// DELETE /users?id=1 - Delete user
@Override
protected void doDelete(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
int id = Integer.parseInt(req.getParameter("id"));
try {
userDAO.deleteUser(id);
resp.setStatus(HttpServletResponse.SC_NO_CONTENT); // 204
} catch (SQLException e) {
throw new ServletException("Database error", e);
}
}
}For production applications, use a connection pool instead of creating a new connection per request. Configure a JNDI DataSource in Tomcat's context.xml:
<!-- META-INF/context.xml -->
<Context>
<Resource name="jdbc/mydb"
auth="Container"
type="javax.sql.DataSource"
maxTotal="20"
maxIdle="10"
maxWaitMillis="10000"
username="root"
password="password"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mydb?useSSL=false"/>
</Context>// Using JNDI DataSource in DAO
import javax.naming.*;
import javax.sql.DataSource;
public class DBUtil {
private static DataSource dataSource;
static {
try {
Context ctx = new InitialContext();
dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/mydb");
} catch (NamingException e) {
throw new RuntimeException("JNDI lookup failed", e);
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); // From pool
}
}Configure a pooled DataSource once for the application and inject or look it up at the repository boundary. Calling getConnection borrows a connection; close returns it to the pool. Do not open one physical connection at application startup and reuse it for every request, because JDBC Connection, PreparedStatement, and ResultSet objects are not request-safe shared state.
Use try-with-resources in the narrowest practical scope. Close the ResultSet and statement before returning the connection. Set pool size and acquisition timeout from measured workload and database capacity; a very large pool can overload the database rather than improve throughput.
Use PreparedStatement placeholders for every value. Placeholders prevent data from becoming SQL syntax and also handle quoting and type conversion. Table names, column names, and sort directions cannot normally be bound as values, so choose those from a fixed allowlist instead of concatenating request text.
When several writes form one business operation, disable auto-commit, execute them on the same connection, commit only after all succeed, and roll back on failure. Restore or discard connection state according to the pool contract. A transaction should be short and must not wait for browser input or a remote API call.
Do not show raw SQLException messages, SQL text, or credentials to the browser. Log a correlation ID and the safe diagnostic details on the server, then return a stable application error. Distinguish invalid input from a conflict such as a unique constraint and from an unavailable database; they lead to different HTTP responses and retry decisions.
Test repositories against the database engine used in production for important syntax and constraint behavior. A mock can verify call shape, but it cannot prove that a query, index, transaction isolation assumption, or migration works.
Try this next
0 of 2 completed
Explore 500+ free tutorials across 20+ languages and frameworks.