Correction applications traitement des instructions SQL avec JDBC
Correction applications traitement des instructions SQL avec JDBC
-
Objectifs
- Établir une connexion à la base de données.
- Exécuter une requête Update avec un Statement
- Obtenir les résultats dans un ResultSet
- Examiner quelques méthodes du ResultSet.
- Application 01
-
Énoncé
- Vous pouvez visualiser l’énoncé de l’exercice
-
Solution : Base de données
-
Solution : Page listeApprenant.jsp
-
Solution : Page afficherApprenant.jsp
-
Solution : Page InsertApprenant.jsp
-
Solution : Page ModifierApprenant.jsp
-- -- Base de données : `gestion-apprenants` -- DROP DATABASE IF EXISTS `gestion-apprenants`; CREATE DATABASE IF NOT EXISTS `gestion-apprenants` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `gestion-apprenants`; -- -------------------------------------------------------- -- -- Structure de la table `apprenants` -- DROP TABLE IF EXISTS `apprenants`; CREATE TABLE IF NOT EXISTS `apprenants` ( `cdeApp` int(11) NOT NULL, `nom` varchar(100) DEFAULT NULL, `prenom` varchar(100) DEFAULT NULL, `date_naissance` date DEFAULT NULL, `genre` tinyint(2) NOT NULL, `email` varchar(255) DEFAULT NULL, `cdeGouv` int(4) DEFAULT NULL, `code_postal` varchar(5) DEFAULT NULL, PRIMARY KEY (`cdeApp`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Déchargement des données de la table `apprenants` -- INSERT INTO `apprenants` (`cdeApp`, `nom`, `prenom`, `date_naissance`, `genre`, `email`, `cdeGouv`, `code_postal`) VALUES (12325678, 'Elina', 'MOTAFA', '2000-02-10', 2, 'elina@gmail.com', 111, '3541'), (12345678, 'Mohamed', 'SALHI', '2000-02-17', 1, 'mohamed.salhi@yahoo.fr', 100, '4100'), (12345679, 'Tarek', 'HARZI', '2001-12-17', 1, 'tarek.harzi@gmail.fr', 110, '4100'), (12396387, 'Salah', 'MEJBRI', '2000-12-01', 1, 'mejbri@gmail.com', 100, '5412'), (14735236, 'Mohamed Salah', 'HAJJI', '2021-02-18', 2, 'hajjriadh@gmail.com', 100, '4587'), (14783236, 'Mohamed Salah', 'HAJJI', '2021-03-04', 2, 'hajjriadh@gmail.com', 101, '4587'), (14785216, 'Mohamed Salah', 'HAJJI', '2021-03-04', 1, 'hajjriadh@gmail.com', 101, '4587'), (14785236, 'Riadh', 'HAJJI', '2021-03-03', 1, 'hajjriadh@gmail.com', 100, '4587'), (21548754, 'ali', 'Tanezefti', '2001-11-02', 1, 'ali@yahoo.fr', 102, '8745'), (22345678, 'Rafika', 'MENII', '2001-03-30', 2, 'rafika147@yahoo.fr', 110, '3100'), (62396387, 'Wahida', 'MOTREJ', '2001-11-21', 2, 'mejbri@gmail.com', 102, '5412'), (64785236, 'Riadh', 'HAJJI', '2021-02-10', 2, 'hajjriadh@gmail.com', 100, '4587'); -- -- Base de données : `motdepasse` -- DROP DATABASE IF EXISTS `motdepasse`; CREATE DATABASE IF NOT EXISTS `motdepasse` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `motdepasse`; -- -------------------------------------------------------- -- -- Structure de la table `recup_mdp` -- DROP TABLE IF EXISTS `recup_mdp`; CREATE TABLE IF NOT EXISTS `recup_mdp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` char(32) COLLATE utf8_unicode_ci NOT NULL, `mail` varchar(50) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Déchargement des données de la table `recup_mdp` -- INSERT INTO `recup_mdp` (`id`, `code`, `mail`) VALUES (1, '100', 'hajjriadh@gmail.com'); -- -------------------------------------------------------- -- -- Structure de la table `users` -- DROP TABLE IF EXISTS `users`; CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `pwd` varchar(255) NOT NULL, `email` varchar(100) NOT NULL, `date_demande_recuperation_pwd` datetime DEFAULT NULL, `pwd_recuperation_token` varchar(200) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1; -- -- Déchargement des données de la table `users` -- INSERT INTO `users` (`id`, `username`, `pwd`, `email`, `date_demande_recuperation_pwd`, `pwd_recuperation_token`) VALUES (1, 'johndoe1', '$2y$10$W1u/5k25VPATxcJNOwFzKunINlguThOoVdN8dGF9MWndjbClXjqlK', 'hajjriadh@gmail.com', '2021-02-13 13:00:00', 'o3HUTlAJngPzK7mpO9iR'), (2, 'johndoe3', 'Password hashé 3', 'johndoe3@monsite.tld', '2019-02-01 00:00:00', 'gfvbfd0D-5Fvjg'), (15, 'Ali', '$2y$10$cktY60i7fOkdP/Vd2t0CYuG.M2LPbbRomdcXA6Qs0u8LauTpzLehK', 'hajjriadhh@gmail.com', NULL, ''); COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<HTML>
<HEAD>
<TITLE>insérer des données à l'aide d'une instruction préparée </TITLE>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
<style>
.div{padding-left:15px;
box-shadow: 0 1px 6px rgba(0, 0, 0, 0.12), 0 1px 4px rgba(0, 0, 0, 0.24);
height: 40px;
border-radius: 2px;
font-family: arial;
font-size: 15px;
}
</style>
</HEAD>
<BODY bgcolor="#ffffcc">
<%
Class.forName("com.mysql.cj.jdbc.Driver");
java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/"
+ "gestion-apprenants","root","");
java.sql.Statement stmt = conn.createStatement();
java.sql.ResultSet RS = stmt.executeQuery("SELECT * from apprenants");
%>
<div class="container">
<div class="row pt-4">
<div class="col-md-8">
<h2 class="pull-center">Liste des apprenants</h2>
</div>
<div class=" col-4 pb-1 d-grid gap-2 d-md-flex justify-content-md-end">
<a href="./insertApprenant.jsp" class="btn btn-primary" role="button" data-bs-toggle="button">Ajouter un apprenant</a>
</div>
</div>
</br>
<table class="table table-bordered">
<tr>
<th>CIN</th>
<th>Nom</th>
<th>Prénom</th>
<th>Date de naissance</th>
<th>Genre</th>
<th>Email</th>
<th>Gouvernorat</th>
<th>Code postal</th>
<th>Actions</th>
</tr>
<%
while(RS.next()) {
%>
<tr>
<td><%=RS.getString(1) %></td>
<td><%=RS.getString(2) %></td>
<td><%=RS.getString(3) %></td>
<td><%=RS.getString(4) %></td>
<td><%=RS.getString(5) %></td>
<td><%=RS.getString(6)%></td>
<td><%=RS.getString(7)%></td>
<td><%=RS.getString(8) %></td>
<td align="center">
<a href="./afficherApprenant.jsp?id=<%=RS.getString(1) %>" class="btn btn-primary btn-sm" role="button" data-bs-toggle="button">Afficher</a>
<a href="./modifierApprenant.jsp?id=<%=RS.getString(1) %>" class="btn btn-success btn-sm" role="button" data-bs-toggle="button">Editer</a>
<a href="/edit?id=<%=RS.getString(1) %>" class="btn btn-warning btn-sm" role="button" data-bs-toggle="button">Supprimer</a>
</td>
</tr>
<% }
RS.close();
stmt.close();
conn.close();
%>
</table>
</body>
</html><%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<HTML>
<HEAD>
<TITLE>insert data using prepared statement </TITLE>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
<style>
.div{padding-left:15px;
box-shadow: 0 1px 6px rgba(0, 0, 0, 0.12), 0 1px 4px rgba(0, 0, 0, 0.24);
height: 40px;
border-radius: 2px;
font-family: arial;
font-size: 15px;
}
</style>
</HEAD>
<BODY bgcolor="#ffffcc">
<%
Class.forName("com.mysql.cj.jdbc.Driver");
String connectionURL = "jdbc:mysql://localhost:3306/gestion-apprenants";
Connection connection = DriverManager.getConnection
(connectionURL, "root", "");
String queryString ="select * from apprenants where cdeApp=?";
PreparedStatement stmt = connection.prepareStatement(queryString);
String cdeApp = request.getParameter("id");
stmt.setString(1, cdeApp);
ResultSet resultSet = stmt.executeQuery();
%>
<div class="container pt-4">
</br>
<%
if (resultSet.next()) {
%>
<div class="card border-info mb-3" style="margin-left: 25%; margin-right: 25%;width: auto;">
<div class="card-header">
<div class="row">
<div class="col-6">Fiche Apprenant</div>
<div class="col-6">
<ul class="nav nav-pills card-header-pills justify-content-md-end">
<li class="nav-item">
<a href="./listeApprenant.jsp" class="btn btn-primary btn-sm" role="button" data-bs-toggle="button">Retour</a>
</li>
</ul></div>
</div>
</div>
<div class="card-body">
<h5 class="card-title"><%=resultSet.getString(2) %></h5>
<p class="card-text">
<p><%=resultSet.getString(1) %></p>
<p><strong>Nom : </strong><%=resultSet.getString(2) %></p>
<p><strong>Prénom : </strong><%=resultSet.getString(3) %></p>
<p><strong>Date de naissance : </strong><%=resultSet.getString(4) %></p>
<p><strong>Genre : </strong><%=resultSet.getString(5) %></p>
<p><strong>Email : </strong><%=resultSet.getString(6)%></p>
<p><strong>Gouvernorat : </strong><%=resultSet.getString(7)%></p>
<p><strong>Code postal : </strong><%=resultSet.getString(8) %></p>
</div>
<% }
resultSet.close();
stmt.close();
connection.close();
%>
</body>
</html><%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<HTML>
<HEAD>
<TITLE>insert data using prepared statement </TITLE>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
<style>
.div{padding-left:15px;
box-shadow: 0 1px 6px rgba(0, 0, 0, 0.12), 0 1px 4px rgba(0, 0, 0, 0.24);
height: 40px;
border-radius: 2px;
font-family: arial;
font-size: 15px;
}
</style>
</HEAD>
<BODY bgcolor="#ffffcc">
<div class="container">
<div class="row pt-4">
<div class="col-md-8">
<h2 class="pull-center">Ajout d'un nouveau apprenant</h2>
</div>
<div class=" col-4 pb-1 d-grid gap-2 d-md-flex justify-content-md-end">
<a href="./listeApprenant.jsp" class="btn btn-primary" role="button" data-bs-toggle="button">Retour</a>
</div>
</div>
<%
String cdeApp = request.getParameter("cdeApp");
String nom = request.getParameter("nom");
String prenom = request.getParameter("prenom");
String date_naissance = request.getParameter("date_naissance");
String genre = request.getParameter("genre");
String email = request.getParameter("email");
String cdeGouv = request.getParameter("cdeGouv");
String code_postal = request.getParameter("code_postal");
/*out.println(cdeApp+"cin");
out.println(nom+"nom"+"\n");
out.println(prenom+"prenom"+"\n");
out.println(date_naissance+"date_naissance"+"\n");
out.println(genre+"genre\r\n");
out.println(email+"email\r\n");
out.println(cdeGouv+"cdeGouv\r\n");
out.println(code_postal+"code_postal");*/
String connectionURL = "jdbc:mysql://localhost:3306/gestion-apprenants";
// declare a connection by using Connection interface
Connection connection = null;
// declare object of Statement interface that uses for
PreparedStatement pstatement = null;
// Load JBBC driver "com.mysql.jdbc.Driver"
Class.forName("com.mysql.cj.jdbc.Driver");
int updateQuery = 0;
// check if the text box is empty
if(cdeApp!=null && nom!=null && prenom!=null && date_naissance!=null
&& genre!=null && email!=null && cdeGouv!=null && code_postal!=null){
try {
connection = DriverManager.getConnection
(connectionURL, "root", "");
// sql query to insert values in the secified table.
String queryString ="INSERT INTO apprenants (cdeApp, nom, prenom,"
+ " date_naissance, genre, email, cdeGouv, code_postal) "+
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
/* createStatement() is used for create statement
object that is used for sending sql statements to the specified database. */
pstatement = connection.prepareStatement(queryString);
pstatement.setString(1, cdeApp);
pstatement.setString(2, nom);
pstatement.setString(3, prenom);
pstatement.setString(4, date_naissance);
pstatement.setString(5, genre);
pstatement.setString(6, email);
pstatement.setString(7, cdeGouv);
pstatement.setString(8, code_postal);
updateQuery = pstatement.executeUpdate();
if (updateQuery != 0) { %>
<div class="div" style="background: #d5efc2;" >
Les données sont insérées avec succès
dans la base de données.
</div>
<%
}
}
catch (Exception ex) {
%>
<div class="div" style="background: #ffc2a3;">
Impossible de se connecter à la batabase.<% out.println(ex.getMessage());%>
</div>
<%
}
finally {
// close all the connections.
pstatement.close();
connection.close();
}
}
%>
</br>
<div style=" border-radius: 2px;border: 1px solid blue;box-shadow: 0 1px 6px rgba(0, 0, 0, 0.12), 0 1px 4px rgba(0, 0, 0, 0.24);" >
<form class="row g-3 p-4" action="#" method="post">
<div class="row">
<div class="col-md-3">
<label for="first-name" class="form-label">CIN</label>
<input type="text" class="form-control" name="cdeApp" placeholder="Saisir votre cin" required>
</div>
<div class="col-md-3">
<label for="last-name" class="form-label">Nom</label>
<input type="text" class="form-control" name="nom" placeholder="Saisir votre nom" required>
</div>
<div class="col-md-3">
<label for="last-name" class="form-label">Prénom</label>
<input type="text" class="form-control" name="prenom" placeholder="Saisir votre prénom" required>
</div>
<div class="col-md-3">
<label for="last-name" class="form-label">Date de naissance</label>
<input type="date" class="form-control" name="date_naissance" required>
</div>
</div>
<div class="row">
<div class="col-md-3">
<label for="region" class="form-label">Genre</label>
<select class="form-select" name="genre" required>
<option selected disabled value="">Choisir</option>
<option value="1">Homme</option>
<option value="2">Femme</option>
</select>
</div>
<div class="col-md-3">
<label for="username" class="form-label">Email</label>
<div class="input-group">
<span class="input-group-text">@</span>
<input type="text" class="form-control" name="email" required>
</div>
</div>
<div class="col-md-3">
<label for="region" class="form-label">Gouvernorat</label>
<select class="form-select" name="cdeGouv" required>
<option selected disabled value="">Choisir</option>
<option value="100">Tunis</option>
<option value="101">Gafsa</option>
</select>
</div>
<div class="col-md-3">
<label for="username" class="form-label">Code postal</label>
<div class="input-group">
<input type="text" class="form-control" name="code_postal" required>
</div>
</div>
</div>
<div class="row pt-4">
<div class="d-grid gap-2">
<button class="btn btn-primary btn-block" type="submit">Enregistrer</button>
</div>
</div>
</form>
</div>
</div>
</body>
</html><%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<HTML>
<HEAD>
<TITLE>insert data using prepared statement </TITLE>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
<style>
.div{padding-left:15px;
box-shadow: 0 1px 6px rgba(0, 0, 0, 0.12), 0 1px 4px rgba(0, 0, 0, 0.24);
height: 40px;
border-radius: 2px;
font-family: arial;
font-size: 15px;
}
</style>
</HEAD>
<BODY bgcolor="#ffffcc">
<div class="container">
<div class="row pt-4">
<div class="col-md-8">
<h2 class="pull-center">Ajout d'un nouveau apprenant</h2>
</div>
<div class=" col-4 pb-1 d-grid gap-2 d-md-flex justify-content-md-end">
<a href="./listeApprenant.jsp" class="btn btn-primary" role="button" data-bs-toggle="button">Retour</a>
</div>
</div>
<%
String cdeApp = request.getParameter("cdeApp");
String nom = request.getParameter("nom");
String prenom = request.getParameter("prenom");
String date_naissance = request.getParameter("date_naissance");
String genre = request.getParameter("genre");
String email = request.getParameter("email");
String cdeGouv = request.getParameter("cdeGouv");
String code_postal = request.getParameter("code_postal");
String connectionURL = "jdbc:mysql://localhost:3306/gestion-apprenants";
Connection connection = null;
PreparedStatement pstatement = null;
PreparedStatement smtp = null;
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection
(connectionURL, "root", "");
String req ="select * from apprenants where cdeApp=?";
smtp = connection.prepareStatement(req);
String code = request.getParameter("id");
smtp.setString(1, code);
ResultSet resultSet = smtp.executeQuery();
//out.println(code+"cin");
resultSet.next();
int updateQuery = 0;
// check if the text box is empty
if(cdeApp!=null && nom!=null && prenom!=null && date_naissance!=null
&& genre!=null && email!=null && cdeGouv!=null && code_postal!=null){
try {
connection = DriverManager.getConnection
(connectionURL, "root", "");
// sql query to insert values in the secified table.
String queryString ="update apprenants set cdeApp=?, nom=?, prenom=?,"
+ " date_naissance=?, genre=?, email=?, cdeGouv=?, code_postal=? where cdeApp=? ";
/* createStatement() is used for create statement
object that is used for sending sql statements to the specified database. */
pstatement = connection.prepareStatement(queryString);
pstatement.setString(1, cdeApp);
pstatement.setString(2, nom);
pstatement.setString(3, prenom);
pstatement.setString(4, date_naissance);
pstatement.setString(5, genre);
pstatement.setString(6, email);
pstatement.setString(7, cdeGouv);
pstatement.setString(8, code_postal);
pstatement.setString(9, cdeApp);
updateQuery = pstatement.executeUpdate();
if (updateQuery != 0) { %>
<div class="div" style="background: #d5efc2;" >
Les données sont insérées avec succès
dans la base de données.
</div>
<%
}
}
catch (Exception ex) {
%>
<div class="div" style="background: #ffc2a3;">
Impossible de se connecter à la database.<% out.println(ex.getMessage());%>
</div>
<%
}
finally {
// close all the connections.
pstatement.close();
connection.close();
}
}
%>
</br>
<div style=" border-radius: 2px;border: 1px solid blue;box-shadow: 0 1px 6px rgba(0, 0, 0, 0.12), 0 1px 4px rgba(0, 0, 0, 0.24);" >
<form class="row g-3 p-4" action="#" method="post">
<div class="row">
<div class="col-md-3">
<label for="first-name" class="form-label">CIN</label>
<input type="text" class="form-control" name="cdeApp" value="<%=resultSet.getString(1) %>" required>
</div>
<div class="col-md-3">
<label for="last-name" class="form-label">Nom</label>
<input type="text" class="form-control" name="nom" value="<%=resultSet.getString(2) %>" required>
</div>
<div class="col-md-3">
<label for="last-name" class="form-label">Prénom</label>
<input type="text" class="form-control" name="prenom" value="<%=resultSet.getString(3) %>" required>
</div>
<div class="col-md-3">
<label for="last-name" class="form-label">Date de naissance</label>
<input type="date" class="form-control" value="<%=resultSet.getString(4) %>"name="date_naissance" required>
</div>
</div>
<div class="row">
<div class="col-md-3">
<label for="region" class="form-label">Genre</label>
<select class="form-select" name="genre" required>
<option selected disabled value="<%=resultSet.getString(5) %>">Choisir</option>
<option value="1">Homme</option>
<option value="2">Femme</option>
</select>
</div>
<div class="col-md-3">
<label for="username" class="form-label">Email</label>
<div class="input-group">
<span class="input-group-text">@</span>
<input type="text" class="form-control" value="<%=resultSet.getString(6) %>" name="email" required>
</div>
</div>
<div class="col-md-3">
<label for="region" class="form-label">Gouvernorat</label>
<select class="form-select" name="cdeGouv" value="<%=resultSet.getString(7) %>"required>
<option selected disabled value="">Choisir</option>
<option value="100">Tunis</option>
<option value="101">Gafsa</option>
</select>
</div>
<div class="col-md-3">
<label for="username" class="form-label">Code postal</label>
<div class="input-group">
<input type="text" class="form-control" value="<%=resultSet.getString(8) %>"name="code_postal" required>
</div>
</div>
</div>
<div class="row pt-4">
<div class="d-grid gap-2">
<button class="btn btn-primary btn-block" type="submit">Enregistrer</button>
</div>
</div>
</form>
</div>
</div>
</body>
</html>