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

Question

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

You have `prenom` and `prennom`. Trailing `;` not needed. – Joop Eggen Sep 21 '22 at 13:01 — Joop Eggen, Sep 21 '22 at 13:01

score 0 · Answer 1 · answered Sep 21 '22 at 13:12

0

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.

answered Sep 21 '22 at 13:12

Joop Eggen

107,315
7
83
138

Thanks a lot for your time. I fixed the typo in my SQL. Still not working sadly – Dayvin Sep 21 '22 at 13:35
First you could try the SQL in a database tool, and look at the table; maybe the column is named `prénom` or such. Then you look at the SQL exception thrown. – Joop Eggen Sep 21 '22 at 13:45
1

I think everythins is ok here. I spotted the mistake. I can see "No suitable driver found for jdbc:mysql://localhost:3306/javaee" seems like this is a problem with mysql-connector jar. I placed it in WEB-INF/lib as it was recommended – Dayvin Sep 21 '22 at 13:49
There others may help better. – Joop Eggen Sep 21 '22 at 13:52
Is the jdbc driver jar in the application, registered on the classpath or of sufficient modern level to be detected or does it require class.forName loading? – Samuel Marchant Sep 21 '22 at 16:13
@SamuelMarchant Class.forName is still sometimes needed but since years Java SPI, Service Provider Interface, is used to detect the driver, additionally using the jdbc::. With modular Java one should not forget adding the driver jar. – Joop Eggen Sep 21 '22 at 17:07
The last jar version is 8.0.30. Should you use a version _lower_ than 8.0.28: MySQL resolved a (relatively non-infectuous) vulnerability. – Joop Eggen Sep 22 '22 at 09:07

Samuel Marchant · Answer 2 · 2022-09-21T15:39:20.453

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");
    =%>

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

2 Answers2