import java.sql.*;
/**
* Provides all necessary methods in order to carry out a transaction with
* database.
*
* @author Sofoklis Stouraitis
*/
class EloiStaff {
private String errorMessages = "";
private Connection con = null;
private PreparedStatement stmt = null;
private Statement stmt1 = null;
private ResultSet rs = null, rs1 = null;
/*
* Define userQuery query.
*/
private final String userQuery = "select username from users where username =? and password =?";
/*
* Define staffQuery query.
*/
private final String staffQuery = "select * from staff";
/**
* A method to get errors.
*
* @return String, representing the error message.
*/
public String getErrorMessages() {
return errorMessages;
}
/**
* The Constructor.
*
*/
public EloiStaff() {
}
/**
* 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 (rs != null)
rs.close();
if (rs1 != null)
rs1.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);
}
}
/**
* Checks if the username and password are valide.
*
* @return boolean, true if user is valide, false otherwise.
*/
public boolean isUserValid(String username, String password) {
if (con == null) {
errorMessages = "You must establish a connection first!";
return false;
}
try {
stmt = con.prepareStatement(userQuery);
// replacing the first ? with userName and the second ? with
// userPassword
stmt.setString(1, username);
stmt.setString(2, password);
// execute query
rs = stmt.executeQuery();
int counter = 0;
while (rs.next())
counter++;
if (counter == 1) {
stmt.close();
rs.close();
return true;
} else {
errorMessages = "Error Login: <br>Invalide username or password!";
stmt.close();
rs.close();
return false;
}
} catch (Exception e4) {
errorMessages = "Error while executing authentication query: <br>"
+ e4.getMessage();
return false;
}
}
/**
* Executes staffQuery and returns the results in a ResultSet, or null if
* any error occured.
*
* @return ResultSet
*/
public ResultSet getEloiStaff() {
try {
if (con == null) {
errorMessages = "You must establish a connection first!";
return null;
}
stmt1 = con.createStatement();
rs1 = stmt1.executeQuery(staffQuery);
return rs1;
} catch (Exception e5) {
errorMessages = "Error while executing selection query!<br>"
+ e5.getMessage();
return null;
}
}
}// end of class
|