I am currently working on my first Java EE project. I am trying to display data from a database I created, but nothing is showing. So, I wonder if I did something bad in my connection or something else.
I am using mysql-connector-java-8.0.30
I created the DB with wampserver (localhost)
I am using eclipse
Here is my connection to the database with the function I am using.
package com.octest.bdd;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.octest.beans.Utilisateur;
public class Noms {
public List<Utilisateur> recupererUtilisateurs(){
List<Utilisateur> utilisateurs = new ArrayList <Utilisateur>();
//connexion a la bdd
Connection connexion = null;
java.sql.Statement statement = null;
ResultSet resultat = null;
try {
connexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee","root","");
statement = connexion.createStatement();
//execution de la requete
resultat = statement.executeQuery("Select nom, prennom from noms;");
//recuperer les données
while(resultat.next()) {
String nom = resultat.getString("nom");
String prenom = resultat.getString("prenom");
Utilisateur utilisateur = new Utilisateur();
utilisateur.setNom(nom);
utilisateur.setPrenom(prenom);
utilisateurs.add(utilisateur);
}
} catch (SQLException e) {
} finally {
try {
if(resultat != null)
resultat.close();
if(statement != null)
statement.close();
if(connexion != null)
connexion.close();
} catch (SQLException ignore) {
// TODO: handle exception
}
}
return utilisateurs;
}
}
Here is the jsp file where I’m trying to display the data :
<%@page import="com.octest.beans.Utilisateur"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Page bonjour</title>
</head>
<body>
<%@ include file="menu.jsp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:forEach var="utilisateurs" items="${ utilisateurs }">
<li><c:out value="${ utilisateurs.prenom }" /> <c:out value="${ utilisateurs.nom }" /></li>
</c:forEach>
Here is the servlet :
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Noms tableNoms = new Noms();
request.setAttribute("utilisateurs", tableNoms.recupererUtilisateurs());
this.getServletContext().getRequestDispatcher("/WEB-INF/bonjour.jsp").forward(request, response);
}
And here is the class "Utilisateur"
package com.octest.beans;
public class Utilisateur {
private String nom;
private String prenom;
public String getNom() {
return nom;
}
public void setNom(String nom) {
this.nom = nom;
}
public String getPrenom() {
return prenom;
}
public void setPrenom(String prenom) {
this.prenom = prenom;
}
}
Thanks a lot for helping
2
Answers
There is a typo in your SQL
prennom.Also you might use try-with-resources, closing things automatically even on exception or return.
Then you should capture exceptions on a higher level.
I made it a PreparedStatement, though not really necessary. But that takes care of quotes, escaping, and more important prevents SQL injection.
List<Utilisateur> , class "Utilisateur" the web api system in Java only accepts List<Object> where Object is actually java.lang.String
also, this point is critical particularly when passing from one resource URL to another such as passing info on the URL from a Servlet to a JSP page. It would be more sensible to submit a request to the jsp page directly and use step-over jsp scriptlet to control the view.
Too, in the Servlet the "request" object belongs to the servlet, so the redirect probably does not have the info. You can use the ServletContext for both and the Utilisateur class.