Servlets and JDBC
Servlets and JDBC can be used together to create dynamic web applications that can interact with a database. JDBC (Java Database Connectivity) is a Java API that allows Java programs to interact with a database.
Here's an example of how to use JDBC with Servlets to retrieve data from a database and display it in a web page:
- First, we'll create a database table to store some data. Here's an example of a simple table with three columns (id, name, and email):
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
);
- Next, we'll create a Java class that will handle the database connection and retrieval of the data:
import java.sql.*;
public class UserDAO {
private String jdbcUrl;
private String jdbcUsername;
private String jdbcPassword;
private Connection jdbcConnection;
public UserDAO(String jdbcUrl, String jdbcUsername, String jdbcPassword) {
this.jdbcUrl = jdbcUrl;
this.jdbcUsername = jdbcUsername;
this.jdbcPassword = jdbcPassword;
}
public void connect() throws SQLException {
if (jdbcConnection == null || jdbcConnection.isClosed()) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new SQLException(e);
}
jdbcConnection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
}
}
public void disconnect() throws SQLException {
if (jdbcConnection != null && !jdbcConnection.isClosed()) {
jdbcConnection.close();
}
}
public List<User> getUsers() throws SQLException {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users";
connect();
Statement statement = jdbcConnection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
users.add(new User(id, name, email));
}
resultSet.close();
statement.close();
disconnect();
return users;
}
}
This class represents a Data Access Object (DAO) that handles the database connection and retrieval of data. It has a constructor that takes the JDBC URL, username, and password, as well as methods to connect and disconnect from the database, and to retrieve a list of users from the database.
- Next, we'll create a Servlet that uses the
UserDAOclass to retrieve the list of users and display it in a JSP page:
@WebServlet("/users")
public class UserServlet extends HttpServlet {
private UserDAO userDAO;
public void init() {
String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
String jdbcUsername = "root";
String jdbcPassword = "password";
userDAO = new UserDAO(jdbcUrl, jdbcUsername, jdbcPassword);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<User> users = userDAO.getUsers();
request.setAttribute("users", users);
request.getRequestDispatcher("users.jsp").forward(request, response);
}
}
In this Servlet, we initialize the UserDAO class in the init() method, and use it to retrieve the list of users in the doGet() method. We then set the users attribute in the request and forward it to a JSP page called users.jsp.
- Finally, we'll create the JSP page to display the list of users:
<html>
<head>
<title>Users</title>
</head>
<body>
<h1>Users</h1>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<c:forEach var="user" items="${users}">
<tr>
<td>${user.id}</td>
<td>${user.name}</td>
<td>${user.email}</td>
</tr>
</c:forEach>
</tbody>
</table>
</body>
</html>
This JSP page uses JSTL to iterate over the users list and display each user in a table row.
So when the user accesses the URL http://localhost:8080/myapp/users, the UserServlet will retrieve the list of users from the database using the UserDAO class, set the users attribute in the request, and forward the request to the users.jsp page. The JSP page will then display the list of users in a table.
Note that this is a very simple example and in real-world applications, there would be more complex business logic, security considerations, and performance optimizations involved. But this should give you a good idea of how Servlets and JDBC can be used together to create dynamic web applications.
Leave a Comment