Thread: jsp mysql

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    1
    Rep Power
    0

    jsp mysql


    I have a problem retrieving information from a mysql database using a jsp bean.
    I have compiled the class and it works fine.
    I have tested the jsp page calling the class and I get no error message.
    My problem is that the page shows no results from the database.

    Could someone help me please? I really need to solve the problem....

    (The class is in the right directory and the jdbc driver works fine)

    I have displayed the source files in this message and you can also find the sources files in the attach file.

    --Here is the code for the class Client.class--


    package deust;

    public class Client
    {
    public int refClient;
    public String nom;
    public String adresse;
    public String codePostal;
    public String ville;
    }


    --Here is the code for the class ListesDesClients.class--


    package deust;

    /**
    * Masque complétement l'accès à la base ce qui permet par exemple
    * d'intaller un pool de connexions ou un cache mémoire sans modifier
    * les pages qui utilisent ce bean.<br/>
    * Attention : aucune gestion de la concurrence... NON THREAD SAFE
    */
    public class ListeDesClients
    {
    public ListeDesClients() throws Exception
    {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    java.sql.Connection cnx = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/java_01","root", "");
    java.sql.Statement requete = cnx.createStatement();
    rs = requete.executeQuery("SELECT * FROM client");
    }
    /**
    * Positionne le client courant sur une nouvelle ligne.
    */
    public Client getNextClient() throws Exception
    {
    if (rs.next())
    {
    client = new Client();
    client.refClient = rs.getInt("refClient");
    client.nom = rs.getString("nom");
    client.adresse = rs.getString("adresse");
    client.codePostal = rs.getString("codePostal");
    client.ville = rs.getString("ville");
    }
    else
    {
    client = null;
    }
    return client;
    }

    public boolean next() throws Exception
    {
    return getNextClient() == client;
    }

    public int getRefClient()
    {
    return client.refClient;
    }

    public String getNom()
    {
    return client.nom;
    }

    public String getAdresse()
    {
    return client.adresse;
    }

    public String getCodePostal()
    {
    return client.codePostal;
    }

    public String getVille()
    {
    return client.ville;
    }
    public java.sql.ResultSet getRs() {
    return rs;
    }

    /**
    * Modifie la ville du client courant

    public void setVille(String inVille) throws java.sql.SQLException
    {
    java.sql.Connection cnx = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/Papeterie","root", "");
    java.sql.Statement requete = cnx.createStatement();
    requete.executeUpdate("UPDATE Client SET Ville='" + inVille+ "' WHERE refClient=" + client.refClient);
    }
    */

    java.sql.ResultSet rs;
    Client client;
    }


    --Here is the code for the jsp page--


    <%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
    <html>
    <head><title>Liste des clients</title></head>
    <body>
    <% deust.ListeDesClients maListe = new deust.ListeDesClients(); %>

    <table border='1'>
    <tr>
    <th>Nom</th><th>Ville</th>
    </tr>

    <%
    while (maListe.next())
    {
    %>
    <tr>
    <td><% out.print(maListe.getNom()); %></td>
    <td><% out.print(maListe.getVille()); %></td>
    </tr>
    <%
    }
    %>
    </table>
    </body>
    </html>

    Attached Files
  2. #2
  3. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    Well, I must say you have some interesting code. I have never seen someone iterate through a resultset in this way before. It is doable, but you have a couple of problems. First, as far as I can tell the next method should always return true because the client variable is being set in the getNextClient method before the comparison is taking place. Does your jsp run in an endless loop? You need to rethink how you do this.

    Also, you never close your resultset or connection object. This is going to be a major problem for you.

    My suggestion is to change the way you are doing this. You could either just iterate through the resultset in your jsp, or if you would rather have a layer of abstraction, you could make your ListeDesClients contain a collection of Clients. The constructor would make the database call and populate the collection of Clients then close the resultset and database connection. Then you could have a next method in the ListeDesClients class that iterates through the collection instead of the resultset. This would have the added benefit of allowing you to keep a list of clients in memory for an extended period of time without taking up database connection resources.

IMN logo majestic logo threadwatch logo seochat tools logo