I am try to search my database using jstl tags but it is not working. So far what I manage is to make it display a null message. I am very new to this side of java, so please if you have any suggestion try to explain in details. Thanks
My codes are as follows:
user-list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ page isELIgnored="false"%>
<html>
<head>
<title>User Management Application</title>
<link rel="stylesheet"
href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
crossorigin="anonymous">
</head>
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dbname2?allowPublicKeyRetrieval=true&useSSL=false"
user="root" password="root"/>
<body>
<header>
<nav class="navbar navbar-expand-md navbar-dark"
style="background-color: tomato">
<div>
<a href="" class="navbar-brand"> User
Management App </a>
</div>
<ul class="navbar-nav">
<li><a href="<%=request.getContextPath()%>/list"
class="nav-link">Users</a></li>
</ul>
</nav>
</header>
<br>
<div class="row">
<!-- <div class="alert alert-success" *ngIf='message'>{{message}}</div> -->
<div class="container">
<h3 class="text-center">List of Users</h3>
<hr>
<div class="container text-left">
<a href="<%=request.getContextPath()%>/new" class="btn btn-success">Add
New User</a>
</div>
<form name="userForm" action="user-list.jsp" method="post">
<div class="container text-center">
<input type="text" class="form-group" name="search" value="<%=request.getParameter("id")%>" placeholder="Name" style="width: 400px;">
<INPUT TYPE ="submit">
</div>
</form>
<br>
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Date</th>
<th>Time</th>
<th>Email</th>
<th>Country</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<sql:query var="user" dataSource="${snapshot}">
select *from users where
id like '%<%=request.getParameter("id")%>%'
</sql:query>
<c:forEach var="user" items="${listUser}">
<tr>
<td><c:out value="${user.id}" /></td>
<td><c:out value="${user.name}" /></td>
<td><c:out value="${user.date}" /></td>
<td><c:out value="${user.time}" /></td>
<td><c:out value="${user.email}" /></td>
<td><c:out value="${user.country}" /></td>
<td><a href="edit?id=<c:out value='${user.id}' />">Edit</a>
<a
href="delete?id=<c:out value='${user.id}' />">Delete</a></td>
</tr>
</c:forEach>
<!-- } -->
</tbody>
</table>
</div>
</div>
UserDAO class
package com.project.usermanagement.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;
import com.project.usermanagement.model.User;
public class UserDAO {
public static Date getSQLDate(LocalDate date) {
return java.sql.Date.valueOf(date);
}
public static LocalDate getUtilDate(Date sqlDate) {
return sqlDate.toLocalDate();
}
public static Time getSQLTime(LocalTime time) {
return java.sql.Time.valueOf(time);
}
public static LocalTime getUtilTime(Time sqlTime) {
return sqlTime.toLocalTime();
}
private String jdbcURL = "jdbc:mysql://localhost:3306/dbname2?allowPublicKeyRetrieval=true&useSSL=false";
private String jdbcUsername = "root";
private String jdbcPassword = "root";
private static final String INSERT_USERS_SQL = "INSERT INTO users" + " (name, date ,time ,email , country) VALUES " +
" (?, ?, ?, ?, ?);";
private static final String SELECT_USER_BY_ID = "select id,name, date, time, email,country from users where id =?";
private static final String SELECT_ALL_USERS = "select * from users";
private static final String DELETE_USERS_SQL = "delete from users where id = ?;";
private static final String UPDATE_USERS_SQL = "update users set name = ?, date=?, time=?, email= ?, country =? where id = ?;";
public UserDAO() {}
protected Connection getConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
public void insertUser(User user) throws SQLException {
System.out.println(INSERT_USERS_SQL);
// try-with-resource statement will auto close the connection.
try (Connection connection = getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
preparedStatement.setString(1, user.getName());
preparedStatement.setDate(2, getSQLDate(user.getDate()));
preparedStatement.setTime(3, getSQLTime(user.getTime()));
preparedStatement.setString(4, user.getEmail());
preparedStatement.setString(5, user.getCountry());
System.out.println(preparedStatement);
preparedStatement.executeUpdate();
} catch (SQLException e) {
printSQLException(e);
}
}
public User selectUser(int id) {
User user = null;
// Step 1: Establishing a Connection
try (Connection connection = getConnection();
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(SELECT_USER_BY_ID);) {
preparedStatement.setInt(1, id);
System.out.println(preparedStatement);
// Step 3: Execute the query or update query
ResultSet rs = preparedStatement.executeQuery();
// Step 4: Process the ResultSet object.
while (rs.next()) {
String name = rs.getString("name");
LocalDate date = rs.getDate("date").toLocalDate();
LocalTime time = rs.getTime("time").toLocalTime();
String email = rs.getString("email");
String country = rs.getString("country");
user = new User(id, name, date, time , email, country);
}
} catch (SQLException e) {
printSQLException(e);
}
return user;
}
public List < User > selectAllUsers() {
// using try-with-resources to avoid closing resources (boiler plate code)
List < User > users = new ArrayList < > ();
// Step 1: Establishing a Connection
try (Connection connection = getConnection();
// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_USERS);) {
System.out.println(preparedStatement);
// Step 3: Execute the query or update query
ResultSet rs = preparedStatement.executeQuery();
// Step 4: Process the ResultSet object.
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
LocalDate date = rs.getDate("date").toLocalDate();
LocalTime time = rs.getTime("time").toLocalTime();
String email = rs.getString("email");
String country = rs.getString("country");
users.add(new User(id, name, date, time, email, country));
}
} catch (SQLException e) {
printSQLException(e);
}
return users;
}
public boolean deleteUser(int id) throws SQLException {
boolean rowDeleted;
try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement(DELETE_USERS_SQL);) {
statement.setInt(1, id);
rowDeleted = statement.executeUpdate() > 0;
}
return rowDeleted;
}
public boolean updateUser(User user) throws SQLException {
boolean rowUpdated;
try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement(UPDATE_USERS_SQL);) {
statement.setString(1, user.getName());
statement.setDate(2, getSQLDate(user.getDate()));
statement.setTime(3, getSQLTime(user.getTime()));
statement.setString(4, user.getEmail());
statement.setString(5, user.getCountry());
statement.setInt(6, user.getId());
rowUpdated = statement.executeUpdate() > 0;
}
return rowUpdated;
}
private void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
UserServlet class
package com.project.usermanagement.web;
import java.io.IOException;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.project.usermanagement.dao.UserDAO;
import com.project.usermanagement.model.User;
@WebServlet("/")
public class UserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private UserDAO userDAO;
public void init() {
userDAO = new UserDAO();
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String action = request.getServletPath();
try {
switch (action) {
case "/new":
showNewForm(request, response);
break;
case "/insert":
insertUser(request, response);
break;
case "/delete":
deleteUser(request, response);
break;
case "/edit":
showEditForm(request, response);
break;
case "/update":
updateUser(request, response);
break;
default:
listUser(request, response);
break;
}
} catch (SQLException ex) {
throw new ServletException(ex);
}
}
private void listUser(HttpServletRequest request, HttpServletResponse response)
throws SQLException, IOException, ServletException {
List < User > listUser = userDAO.selectAllUsers();
request.setAttribute("listUser", listUser);
RequestDispatcher dispatcher = request.getRequestDispatcher("user-list.jsp");
dispatcher.forward(request, response);
}
private void showNewForm(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
dispatcher.forward(request, response);
}
private void showEditForm(HttpServletRequest request, HttpServletResponse response)
throws SQLException, ServletException, IOException {
int id = Integer.parseInt(request.getParameter("id"));
User existingUser = userDAO.selectUser(id);
RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
request.setAttribute("user", existingUser);
dispatcher.forward(request, response);
}
private void insertUser(HttpServletRequest request, HttpServletResponse response)
throws SQLException, IOException {
String name = request.getParameter("name");
String email = request.getParameter("email");
String country = request.getParameter("country");
User newUser = new User(name, LocalDate.now(), LocalTime.now(), email, country);
userDAO.insertUser(newUser);
response.sendRedirect("list");
}
private void updateUser(HttpServletRequest request, HttpServletResponse response)
throws SQLException, IOException {
int id = Integer.parseInt(request.getParameter("id"));
String name = request.getParameter("name");
LocalDate date = LocalDate.parse(request.getParameter("date"));
LocalTime time = LocalTime.parse(request.getParameter("time"));
String email = request.getParameter("email");
String country = request.getParameter("country");
User book = new User(id, name, date, time, email, country);
userDAO.updateUser(book);
response.sendRedirect("list");
}
private void deleteUser(HttpServletRequest request, HttpServletResponse response)
throws SQLException, IOException {
int id = Integer.parseInt(request.getParameter("id"));
userDAO.deleteUser(id);
response.sendRedirect("list");
}
}
You can send value to be search using form .Then , get that value using ${param.yourinputname} and pass same to your query .Currently , you have incorrect input name i.e : name="search" but you are getting same using id which will return null as you can see in your added output as well .So, your form like below :
<form name="userForm" action="user-list.jsp" method="post">
<!--change value-->
<input type="text" class="form-group" name="search" value="${param.search}" placeholder="Name" style="width: 400px;">
<input type ="submit">
</form>
Now , when form will get submitted get that the value which is typed by user and pass same to your query . i.e :
<sql:query var="user" dataSource="${snapshot}">
select *from users where id LIKE '%' ? '%'
<sql:param value="${param.search}" />
</sql:query>
And get result from query using below code :
<c:forEach var="listUser" items="${user.rows}">
<tr>
<td><c:out value="${listUser.id}" /></td>
<td><c:out value="${listUser.name}" /></td>
<!--same for other fields as well-->
</tr>
</c:forEach>