import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.lang.Math;
public class SunServlet2 extends HttpServlet {
// The database connection
Connection con;
//The statement
Statement stmt;
//The queryString
String queryString = null;
public void init (ServletConfig conf) throws ServletException {
super.init(conf);
// ----confugure this for the site
String username = "nobody";
String password = null;
// the URL that will connect to TECFA's MySQL server
// Syntax: jdbc:TYPE:machine:port/DB_NAME
// String url = "jdbc:mysql://localhost:3306/staf2x
String url = "jdbc:mysql://tecfa2.unige.ch:3306/staf2x";
//----configure END
try {
Class.forName ("org.gjt.mm.mysql.Driver");
//Establish Connection to the database at URL with username and password
con = DriverManager.getConnection(url, username, password);
System.out.println("Ok, the connection to the database is working.");
}
catch (Exception e) // (ClassNotFoudException and SQLException)
{
throw (new UnavailableException(this, "Sorry! The database didn't load!"));
}
}
/*
* service() mehtod to handle user interaction
*/
public void service (HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
try {
String login = req.getParameter ("log");
String pwd = req.getParameter ("pword");
String queryString ="SELECT Reading, Speaking, Writing, Translation FROM grades WHERE (Login='"+login+"') AND (Password='"+pwd+"')";
Statement stmt = con.createStatement();
ResultSet rs=stmt.executeQuery(queryString);
ResultSetMetaData rsMeta = rs.getMetaData();
if ((!login.equals("")) && (!pwd.equals("")))
{
if (rs.next())
{
String title = "Final Grades of English Courses";
out.println ("" + title + "");
out.println ("" + title + "
");
out.println("
");
out.println("
Hello, " + login + ", your final scores of the English examinations are:
");
out.println("");
//get the number of Cols in the ResultSet
int noCols=rsMeta.getColumnCount();
out.println("");
for (int c=1; c<=noCols; c++){
String el=rsMeta.getColumnLabel(c);
out.println("| "+el+" | ");
}
out.println("
");
out.println("");
int total = 0;
int final_grade = 0;
for (int c=1; c<=noCols; c++) {
int el=rs.getInt(c);
out.println("| "+el+" | ");
total=total+el;
}
out.println("
");
out.println("
");
final_grade=Math.round(total/4);
out.println("
");
out.println("Your average score of the English examinations is: " +final_grade);
if (final_grade>=85){
out.println("
You are excellent in learning the language");
}
else if (final_grade>=70){
out.println("
You've got a normal score for the course. Proceed!");
}
else if (final_grade>=60){
out.println("
You neraly failed the course. Study hard!");
}
else {
out.println("
Sorry, you failed the course. Please pick it up again next term!");
}
}else {
out.println("Sorry, there must be a mistake either in the login name or in the password! Please try again.");
}
} else {
out.println("Sorry, you are required to put in both the login name and the password! Please try again.");
}
}catch (SQLException ex) {
out.println("
");
while (ex !=null){
out.println("Message: " + ex.getMessage());
out.println("SQLState: " + ex.getSQLState());
out.println("ErrorCode: " + ex.getErrorCode());
ex = ex.getNextException();
out.println("");
}
out.println ("");
}
out.println("
");
out.println("SUN June 2000 ");
out.println("Source Code");
out.println("");
try { con.close();}
catch(Exception e) {}
return;
}
}