import java.io.*;
import javax.servlet.*;
import java.sql.*;
import javax.servlet.http.*;
public class LhairesSqlServlet extends HttpServlet {
Connection con; //connection with database
Statement stat; //statement returned by the database
String querystr = null;
public void init(ServletConfig conf) throws ServletException {
super.init(conf);
//strings
String username = "nobody";
String password = null;
String url ="jdbc:mysql://tecfa2.unige.ch:3306/staf2x";
try{
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection(url, username, password);
}
catch (Exception e)
{
throw(new UnavailableException(this, "Sorry, database problem!"));
}
} // init
protected String replaceApos (String str){
String newstr= str;
try{
newstr = newstr.replace('\'', ' ');
newstr.trim();
return newstr;
}
catch (Exception e){
}
return newstr;
}
protected void doPost (HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
ResultSet rs;
int updateResult;
try {
out.println("Résultat");
out.println("Résultat
");
String listquery = req.getParameter("listcompl");
String deletequery = req.getParameter("delete");
String deleteId = req.getParameter("delid");
String addquery = req.getParameter("ajout");
String searchquery = req.getParameter("search");
String loginsrc = replaceApos(req.getParameter("loginsearch"));
String updatequery = req.getParameter("update");
String logadd = replaceApos(req.getParameter("loginadd"));
String pass = replaceApos(req.getParameter("passwd"));
String studname = replaceApos(req.getParameter("stname"));
String studfirst = replaceApos(req.getParameter("stfirst"));
String studbirth = req.getParameter("stbirth");
String studyear = req.getParameter("styear");
String studem = req.getParameter("stem");
String idupd = req.getParameter("idup");
String logupd = replaceApos(req.getParameter("logup"));
String passupd = replaceApos(req.getParameter("passup"));
String nameupd = replaceApos(req.getParameter("nameup"));
String firstupd = replaceApos(req.getParameter("firstup"));
String birthupd = req.getParameter("birthup");
String studupd = req.getParameter("studup");
String emupd = req.getParameter("emup");
if (deletequery != null){
if (deleteId != null){
querystr = "delete from lhairestest where id=" + deleteId;
stat = con.createStatement();
updateResult = stat.executeUpdate(querystr);
}
}
if (addquery != null){
querystr = "insert into lhairestest values (null,'" + logadd + "','" + pass + "','" +
studname + "','" + studfirst + "','" + studbirth + "'," + studyear + ",'" + studem + "')";
stat = con.createStatement();
updateResult = stat.executeUpdate(querystr);
}
if (updatequery != null){
querystr = "update lhairestest set login='" + logupd + "', password='" + passupd + "', name='" +
nameupd + "', firstname='" + firstupd + "', birthyear='" + birthupd + "', studyyear=" +
studupd + ", email='" + emupd + "' where id=" + idupd;
stat = con.createStatement();
updateResult = stat.executeUpdate(querystr);
}
if (searchquery != null){
if (loginsrc != null){
querystr = "select * from lhairestest where login = '" + loginsrc + "'";
stat = con.createStatement();
rs = stat.executeQuery(querystr);
out.println("");
out.println("| Id | Login | Password | ");
out.println("Name | First name | Birth date | ");
out.println("Study year | Email |
|---|
");
out.println("
");
out.println("");
out.println("
");
}
}
if (listquery != null || deletequery != null || addquery != null || updatequery != null){
querystr = "select * from lhairestest order by login";
//! case sensitivity!
out.println("Liste complète
");
out.println("");
out.println("| Id | Login | Password | ");
out.println("Name | First name | Birth date | ");
out.println("Study year | Email |
|---|
");
out.println("
");
out.println("");
out.println("
");
out.println("Ajoutez une ligne
");
out.println("
");
out.println("Rechercher ou modifier les entrées par login:
");
}
out.println("
Retour à la page ");
out.println("de départ
Page ");
out.println("travaux
");
out.println("");
} // try
catch (SQLException ex){
out.println("Exception(s):
");
while (ex != null){
out.println("Message : " + ex.getMessage() + "
");
out.println("SQLState : " + ex.getSQLState() + "
");
out.println("Error code : " + ex.getErrorCode() + "
");
ex = ex.getNextException();
}
}
} // doPost
} // servlet