Why does my function to collect data from database is not working-CodePudding

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

CodePudding user response：

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.

public List<Utilisateur> recupererUtilisateurs() throws SQLException {
    try (Connection connexion = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/javaee", "root", "");
            PreparedStatement statement = connexion.prepareStatement(
                "Select nom, prenom from noms where prenom <> ?")) {

        statement.setString(1, "Pierre");

        try (ResultSet resultat = statement.executeQuery()) {
        
            List<Utilisateur> utilisateurs = new ArrayList <Utilisateur>(); 
            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);
            }
            return utilisateurs;
        } // Ferme resultat.
        
    } // Ferme statement et connexion.
}

I made it a PreparedStatement, though not really necessary. But that takes care of quotes, escaping, and more important prevents SQL injection.

CodePudding user response：

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.

// List<Utilisateur> utilisateursReference = new List<Utilisateur>;
    ServletContext   sxz = ((ServletConfig)this.getServletConfig()).getServletContext();
    sxz.setAttribute("utilisateurs",((Object)utilisateursReference)); 
    
    // in the JSP page 
    
    <%=
    ServletContext   servcxt = ((ServletConfig)this.getServletConfig()).getServletContext();
    
    Utilisateur utilisateurs = (List<Utilisateur>)servcxt.getAttribute("utilisateurs");
    =%>