I have an exercise which requires to create:
- a JSP - to display the data forwarded by the servlet
- a servlet - to get data from the DAO class and forward to JSP
- a DAO class - to connect and retrieve data from mariaDB database
The problem is I cannot open the connection to the database in the servlet. When I debug servlet file, it broke at the line creating new DAO object (I marked the line within **...** below), in the DAO file, it cannot open the connection with the message "No suitable driver found for jdbc:mysql://localhost:3306/a1605354"; even though I did add jdbc driver for mariaDB in the build path.
I included the picture of my project explorer at the end of the post. I am so confused and do not understand the problem clearly to fix it. Really need your help!
I have a file named "Connection Parameters" with all the information needed to connect to mariaDB:
ConnectionParameter.java
public class ConnectionParameters {
public static final String username = "a1605354";
public static final String password = "jaZEMs85o";
private static final String databaseName = username;
public static final String databaseURL = "jdbc:mysql://localhost:3306/" + databaseName;
public static final String jdbcDriver = "org.mariadb.jdbc.Driver";
// PK violation: error code in MariaDB is 1062
public static final int PK_VIOLATION_ERROR = 1062;
}
StudentDAO.java
public class StudentDAO {
private final String username;
private final String password;
private final String databaseURL;
public StudentDAO() throws Exception {
username = ConnectionParameters.username;
password = ConnectionParameters.password;
databaseURL = ConnectionParameters.databaseURL;
try {
Class.forName(ConnectionParameters.jdbcDriver);
} catch (Exception ex) {
System.out.print(ex.getMessage());
}
}
private Connection openConnection() throws SQLException {
Connection dbConnection = DriverManager.getConnection(databaseURL, username, password);
return dbConnection;
}
private void closeConnection(Connection dbConnection) throws SQLException {
if (dbConnection != null) {
dbConnection.close();
}
}
public ArrayList<Student> getAllStudents() throws SQLException {
ArrayList<Student> studentList = new ArrayList<Student>();
Connection dbConnection = null;
try {
dbConnection = openConnection();
String sqlText = "SELECT id, firstname, lastname, streetaddress, postcode, postoffice FROM Student ORDER BY lastname";
Statement statement = dbConnection.createStatement();
ResultSet resultSet = statement.executeQuery(sqlText);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String firstname = resultSet.getString("firstname");
String lastname = resultSet.getString("lastname");
String streetaddress = resultSet.getString("streetaddress");
String postcode = resultSet.getString("postcode");
String postoffice = resultSet.getString("postoffice");
studentList.add(new Student(id, firstname, lastname, streetaddress, postcode, postoffice));
}
return studentList;
} catch (SQLException sqle) {
throw sqle;
} finally {
closeConnection(dbConnection);
}
}
StudentListServlet.java
@WebServlet("/StudentListServlet")
public class StudentListServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
**StudentDAO studentDAO = new StudentDAO();**
ArrayList<Student> studentList = studentDAO.getAllStudents();
request.setAttribute("studentList", studentList);
request.getRequestDispatcher("StudentList.jsp").forward(request, response);
} catch (Exception ex) {
ex.getMessage();
}
}
}
StudentList.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %>
<body>
<h2>List of Students</h2>
<br/>
<form action="StudentListServlet" method="GET"></form>
<table>
<thead>
<tr>
<th>Student ID</th><th>Last Name</th><th>First Name</th><th>Street</th><th>Postcode</th><th>Post Office</th>
</tr>
</thead>
<tbody>
<c:forEach items="${ studentList }" var="studentListObject">
<tr>
<td><c:out value="${studentListObject.id }" /></td>
<td><c:out value="${studentListObject.lastname }" /></td>
<td><c:out value="${studentListObject.firstname }" /></td>
<td><c:out value="${studentListObject.streetaddress }" /></td>
<td><c:out value="${studentListObject.postcode }" /></td>
<td><c:out value="${studentListObject.postoffice }" /></td>
</tr>
</c:forEach>
</tbody>
</table>
</body>
Project explorer view: