I have been trying to display a table in JSP for which I am using this while loop code as given below, the problem is that it is difficult for me to convert my scriptlets into JSTL especially the rs.getInt("id") and I am using JSTL so that I can encode the URL together.
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/login";
String username = "root";
String password = "your-password";
String query = "select * from employeesloginaccount";
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
%>
<tr>
<td><%=rs.getInt("id")%></td>
<td><%=rs.getString("first_name")%></td>
<td><%=rs.getString("last_name")%></td>
<td>
<c:url value ="${pageContext.request.contextPath}/downloadFileServlet" var = "myURL">
<c:param name = "id" value = "<%=rs.getInt(id)%>"/>
</c:url>
<form method="get"
action="<c:import url = "${myURL}"/>">
<input style="text-align: center" type="submit" value="Save">
</form>
</td>
</tr>
<%
}
I think it might be worth considering this answer from BalusC on the question, "How to avoid Java code in JSP files?"
The use of scriptlets (those
<% %>things) in JSP is indeed highly discouraged since the birth of taglibs (like JSTL) and EL (Expression Language, those${}things) over a decade ago.The major disadvantages of scriptlets are:
- Reusability: you can't reuse scriptlets.
- Replaceability: you can't make scriptlets abstract.
- OO-ability: you can't make use of inheritance/composition.
- Debuggability: if scriptlet throws an exception halfway, all you get is a blank page.
- Testability: scriptlets are not unit-testable.
- Maintainability: per saldo more time is needed to maintain mingled/cluttered/duplicated code logic.
Your specific problem here is Maintainability.
What you should be doing is seperating the logic of your application so that you can have a high level of reusability/testability/debuggability/maintainability etc.. Let's start by creating a java class for your database connection, maybe something like this:
public class DBConnection {
private static String url = null;
private static Connection conn = null;
public static Connection getConnection(){
try{
Class.forName("com.mysql.jdbc.Driver");
url = "jdbc:mysql://localhost:3306/login";
conn = DriverManager.getConnection(url,"root","your-password");
} catch (Exception e) {
System.out.println(e);
}
return conn;
}
}
Then you can use this class whenever you want to connect to your database and do something from other classes, in the examples below we will create an object to handle your employee data and then use that to get information from your database:
public class EmployeeObject {
int id;
String firstname;
String lastname;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
}
Now that we have this object class, let's say you have another class called Employees, and in this class you have a method which gets employee info from the employeesloginaccount table:
public class Employees {
public List<EmployeeObject> getEmployeeInfo(){
//this class will return a list of EmployeeObjects from the database.
ArrayList<EmployeeObject> employees = new ArrayList<EmployeeObject>();
//get connection from our DBConneciton class we created earlier
try(Connection conn= DBConnection.getConnection()){
PreparedStatement pst = conn.prepareStatement("select * from employeesloginaccount;");
ResultSet rs = pst.executeQuery();
while (rs.next()) {
//for each result in database, create an EmployeeObject
EmployeeObject employee = new EmployeeObject();
int id = rs.getInt("id");
employee.setId(id);
String fname = rs.getString("first_name");
employee.setFirstname(fname);
String lname = rs.getString("last_name");
employee.setLastname(lname);
employees.add(employee); // add each EmployeeObject to the arrayList of EmployeeObject's
}
} catch (SQLException e) {
e.printStackTrace();
}
return employees; //return all the results
}
}
Then you create a Servlet which will get this information, let's make it easy and put our code in the doGet method of the servlet so that whenever we visit the URL for this Servlet we will call this code (in this case i called my Servlet Test, with url mapping /Test:
@WebServlet("/Test")
public class Test extends HttpServlet {
private static final long serialVersionUID = 1L;
public Test() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Employees e = new Employees(); //instantiate the Employees class
List<EmployeeObject> employees = e.getEmployeeInfo(); //get employee info from database
request.setAttribute("employees", employees); //set this list of employees to the request so we can access it in our jsp
RequestDispatcher rd = request.getRequestDispatcher("example.jsp"); //change to whatever your jsp is that you want to view the information
rd.forward(request, response);
}
}
And finally in our jsp page where we can view this information:
<!DOCTYPE HTML>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<html>
<head>
<title>Example Page</title>
</head>
<body>
<table style="width:100%;">
<thead>
<tr>
<td>id</td>
<td>Firstname</td>
<td>Lastname</td>
</tr>
</thead>
<tbody>
<!-- for each item in our employees list create a variable called "employee" -->
<c:forEach items="${employees}" var="employee">
<tr>
<td>${employee.id}</td>
<td>${employee.firstname}</td>
<td>${employee.lastname}</td>
</tr>
</c:forEach>
</tbody>
</table>
</body>
</html>
Let me know if that helps you or if you have any questions. :)