import java.sql.*;
/**
* Provides all necessary methods in order to carry out a transaction with
* database.
*
* @author Sofoklis Stouraitis
*/
class StudentClassB {
private String errorMessages = "";
private Connection con = null;
private PreparedStatement stmt = null, stmt1 = null;
private Statement stmt2 = null;
private ResultSet rs1 = null, rs2 = null;
/*
* Define userQuery query.
*/
private final String insertStudentQuery = "insert into ismstudents2008 (AM, Name, Surname, ip) values (?,?,?,?);";
/*
* Define selectStudentQuery query.
*/
private final String selectStudentQuery = "select * from ismstudents2008 where AM=?;";
/**
* A method to get errors.
*
* @return String, representing the error message.
*/
public String getErrorMessages() {
return errorMessages;
}
/**
* Default Constructor.
*
*/
public StudentClassB() {
}
/**
* Provides a connection with the Database Server. Initializes JDBC driver
* for MySQL. Establishes a connection with the Database Server.
*
* @throws SQLException
* (with the appropriate message) if any driver or connection
* error occured.
*/
public void open() throws SQLException {
try {
// for JDBC driver to connect to mysql, the .newInstance() method
// can be ommited
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e1) {
errorMessages = "MySQL Driver error: <br>" + e1.getMessage();
throw new SQLException(errorMessages);
}
try {
con = DriverManager.getConnection(
"jdbc:mysql://195.251.249.131:3306/eloi_stuff",
"eloi_stuff", "*****");
} catch (Exception e2) {
errorMessages = "Could not establish connection with the Database Server: <br>"
+ e2.getMessage();
con = null;
throw new SQLException(errorMessages);
}
}
/**
* Ends the connection with the database Server. Closes all Statements and
* ResultSets. Finally, closes the connection with the Database Server.
*
* @throws SQLException
* (with the appropriate message) if any error occured.
*/
public void close() throws SQLException {
try {
if (stmt != null)
stmt.close();
if (stmt1 != null)
stmt1.close();
if (rs1 != null)
rs1.close();
if (rs2 != null)
rs2.close();
if (con != null)
con.close();
} catch (Exception e3) {
errorMessages = "Could not close connection with the Database Server: <br>"
+ e3.getMessage();
throw new SQLException(errorMessages);
}
}
/**
* Inserts students personal data into the database.
*
* @param am, Students AM
* @param name, Students name
* @param surname, Students surname
* @param ip, Students ip address
*
* @throws SQLException
* (with the appropriate message) if any error occured.
*/
public void registerStudent(String am, String name, String surname, String ip) throws SQLException {
if (con == null) {
errorMessages = "You must establish a connection first!";
throw new SQLException(errorMessages);
}
try {
stmt = con.prepareStatement(insertStudentQuery);
// replacing the first ? with am, the second ? with
// name, the third ? with surname and the fourth ? with ip.
stmt.setString(1, am);
stmt.setString(2, name);
stmt.setString(3, surname);
stmt.setString(4, ip);
// execute query
stmt.executeUpdate();
stmt.close();
} catch (Exception e4) {
errorMessages = "Error while inserting student to the database: <br>"
+ e4.getMessage();
throw new SQLException(errorMessages);
}
}
/**
* Checks if the specific AM is already registered to the database.
*
* @return boolean, true if student (with the specific AM) is already
* registered or encounter any error, false otherwise(if student is
* not registered).
*/
public boolean isStudentRegistered(String am) {
if (con == null) {
errorMessages = "You must establish a connection first!";
return true;
}
try {
stmt1 = con.prepareStatement(selectStudentQuery);
// replacing the first ? with userName and the second ? with
// userPassword
stmt1.setString(1, am);
// execute query
rs1 = stmt1.executeQuery();
if (rs1.next()) {
stmt1.close();
rs1.close();
errorMessages = "The Student with ΑΜ " + am
+ " is already registered!";
return true;
} else {
stmt1.close();
rs1.close();
return false;
}
} catch (Exception e4) {
errorMessages = "Error while executing selection query: <br>"
+ e4.getMessage();
//throw new SQLException(errorMessages);
return true;
}
}
/**
* Returns the results (all registered students sorted by users critiria) in a ResultSet, or null if
* any error occured.
*
* @return ResultSet
*/
public ResultSet getAllStudents(String input) {
try {
if (con == null) {
errorMessages = "You must establish a connection first!";
return null;
}
String selectAllStudentsQuery = "";
if (input.equals("NO"))
selectAllStudentsQuery = "select * from ismstudents2008;";
else
selectAllStudentsQuery = "select * from ismstudents2008 order by " + input + ";";
stmt2 = con.createStatement();
rs2 = stmt2.executeQuery(selectAllStudentsQuery);
return rs2;
} catch (Exception e5) {
errorMessages = "Error while getting all students from database!<br>"
+ e5.getMessage();
return null;
}
}
}// end of class
|