I'm in a java web application project and I'm trying to do MySQL bd queries and show them in a jsp without using java code. I saw this solution in an answer of an old question, but when executing it it does not show me any record. I have the following:
Orders.jsp
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Pedidos</title>
<link href="css/menu.css" rel="stylesheet" type="text/css"/>
<link href="css/bootstrap.css" rel="stylesheet" type="text/css"/>
<link rel="stylesheet" type="text/css" href="https://fonts.googleapis.com/css?family=Patua+One">
</head>
<body>
<nav class="navbar navbar-default">
<ul class="nav navbar-nav">
<li><a href="Actividad.jsp">Actividad</a></li>
<li><a class="active" href="Pedidos.jsp">Pedidos</a></li>
</ul>
</nav>
<table>
<thead>
<tr>
<th>Id</th>
<th>IdRepartidor</th>
<th>IdPedido</th>
<th>Hora Inicio</th>
<th>Hora Fin</th>
</tr>
</thead>
<tbody>
<c:forEach items="${repartos}" var="reparto">
<tr>
<td>${reparto.id}</td>
<td>${reparto.idRepartidor}</td>
<td>${reparto.idPedido}</td>
<td>${reparto.horaInicio}</td>
<td>${reparto.horaFin}</td>
</tr>
</c:forEach>
</tbody>
</table>
</body>
</html>
Inquiry OrdersServlet.java
@WebServlet(name = "ConsultaPedidosServlet", urlPatterns = {"/ConsultaPedidosServlet"})
public class ConsultaPedidosServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Consulta cons;
try {
cons = new Consulta("repartos");
ResultSet rs = cons.recuperar("idRepartidor = 11");
List<Repartos> repartos = new ArrayList<>();
while (rs.next()){
int id = rs.getInt("id");
int idRep = rs.getInt("idRepartidor");
int idPed = rs.getInt("idPedido");
Repartos rep = new Repartos(id, idRep, idPed);
repartos.add(rep);
}
request.setAttribute("repartos", repartos);
request.getRequestDispatcher("Pedidos.jsp").forward(request, response);
} catch (SQLException | ClassNotFoundException ex) {
Logger.getLogger(ConsultaPedidosServlet.class.getName()).log(Level.SEVERE, null, ex);
}
}
Consulta.java
public class Consulta {
private final String tabla;
private final Connection cnx;
public Consulta(String tabla) throws SQLException, ClassNotFoundException{
this.tabla = tabla;
this.cnx = Conexion.obtener();
}
public ResultSet recuperar(String condicion) throws SQLException{
ResultSet rs = null;
try{
PreparedStatement consulta = cnx.prepareStatement("SELECT * FROM " + this.tabla + " WHERE " + condicion);
rs = consulta.executeQuery();
}
catch(SQLException ex){
throw new SQLException(ex);
}
return rs;
}
}
Conexion.java
public class Conexion {
private static Connection cnx = null;
public static Connection obtener() throws SQLException, ClassNotFoundException {
if (cnx == null) {
try {
Class.forName("com.mysql.jdbc.Driver");
cnx = DriverManager.getConnection("jdbc:mysql://localhost/deliverytrackingdb", "root", "root");
} catch (SQLException ex) {
System.out.println(ex.getMessage());
} catch (ClassNotFoundException ex) {
System.out.println(ex.getMessage());
}
}
return cnx;
}
}