Hello friends, Today I am going to show you how to display data from the MySQL database using JSP and servlet. In this example, we will display data from the MySQL database using JSP pages and Servlet Controller.
Previous Post: How to insert data in MySQL database using JSP and Servlet | jsp (codesnipeet.com)
Previous Post: How to connect MySQL Database Using JSP and Servlet | (codesnipeet.com)
Previous Post: How to install xampp in windows 10 | PHP (codesnipeet.com)
To show the data from the MySQL database, we are using your previous project. If you are not following the previous post, please follow the previous post also.
Step-1: Create a JSP file in the expense folder named as all_expense.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<%@page import="com.model.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Expenses</title>
<style>
.data-table {
width: 50%;
margin: 100px auto;
}
table {
border: outset;
line-height: 28px;
text-align: center;
}
tr:nth-child(even) {
background-color: #D6EEEE;
}
h2 {
text-align: center;
}
</style>
</head>
<body>
<div class="data-table">
<div style="clear: both">
<h2 style="float: left">
<a href="<%=request.getContextPath()%>/expense?add">Add Expense</a>
</h2>
<h2>Expense List</h2>
</div>
<table width="700">
<tr>
<td width="119"><b>ID</b></td>
<td width="168"><b>Expense Name</b></td>
<td width="168"><b>Category</b></td>
<td width="168"><b>Amount</b></td>
<td width="168"><b>Description</b></td>
<td width="168"><b>Date</b></td>
</tr>
<%
List<Expense> data = (List<Expense>) request.getAttribute("data");
for (Expense item : data) {
%>
<tr>
<td width="119"><%=item.getId()%></td>
<td width="168"><%=item.getExpense_name()%></td>
<td width="168"><%=item.getCategory()%></td>
<td width="168"><%=item.getAmount()%></td>
<td width="168"><%=item.getExpense_description()%></td>
<td width="168"><%=item.getDate()%></td>
</tr>
<%
}
%>
</table>
</div>
</body>
</html>Step-2: Add another else if block within the doGet() method in the ExpenseManagerController.java class
else if (request.getParameter("view") != null) {
List<Expense> data = null;
try {
data = jdbc.getAllExpense();
} catch (SQLException e) {
e.printStackTrace();
}
request.setAttribute("data", data);
request.getRequestDispatcher("expense/all_expense.jsp").forward(request,
response);
}The ExpenseManagerController.java class should be like
package com.controller;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.model.Expense;
import com.jdbc.JDBCDao;
@WebServlet("/expense")
public class ExpenseManagerController extends HttpServlet {
private static final long serialVersionUID = 1L;
private JDBCDao jdbc;
public ExpenseManagerController() {
jdbc = new JDBCDao();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
if (request.getParameter("add") != null) {
request.getRequestDispatcher("expense/add_expense.jsp").forward(request,
response);
}else if (request.getParameter("view") != null) {
List<Expense> data = null;
try {
data = jdbc.getAllExpense();
} catch (SQLException e) {
e.printStackTrace();
}
request.setAttribute("data", data);
request.getRequestDispatcher("expense/all_expense.jsp").forward(request,
response);
}else {
request.getRequestDispatcher("index.jsp").forward(request,
response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
if (request.getParameter("add_expense") != null) {
String name = request.getParameter("expense_name");
String category = request.getParameter("category");
String amount = request.getParameter("amount");
String date = request.getParameter("date");
String description = request.getParameter("description");
if (name.length() != 0 && category.length() != 0 && amount.length() != 0 && date.length() != 0
&& description.length() != 0) {
Expense expense = new Expense();
expense.setExpense_name(name);
expense.setCategory(category);
expense.setAmount(Double.parseDouble(amount));
expense.setDate(date);
expense.setExpense_description(description);
Boolean status = jdbc.insertExpenseData(expense);
request.setAttribute("status", status);
} else {
request.setAttribute("status", false);
}
request.getRequestDispatcher("expense/add_expense.jsp").forward(request, response);
}
}
}
Step-3: Add a new method in the JDBCDao.java class for fetching all the from the Database.
public List<Expense> getAllExpense() throws SQLException {
Connection con = getDatabaseConnection();
List<Expense> list = new ArrayList<>();
String sql = "SELECT * FROM expenses ORDER BY date DESC";
ResultSet result = null;
try {
PreparedStatement ps = con.prepareStatement(sql);
result = ps.executeQuery();
while (result.next()) {
Long id = Long.parseLong(result.getString("id"));
String name = result.getString("expense_name");
String description = result.getString("expense_description");
String category = result.getString("category");
Double amount = Double.parseDouble(result.getString("amount"));
String date = result.getString("date");
Expense record = new Expense(id, name, description, amount, category, date);
list.add(record);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
result.close();
con.close();
}
return list;
}The updated JDBCDao.java class should be:
package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.model.Expense;
public class JDBCDao {
static final String DB_URL = "jdbc:mysql://localhost:3306/expense_management_system";
static final String USER = "root";
static final String PASS = "";
static final String JDBCDRIVER = "com.mysql.jdbc.Driver";
public void loadDriver(String driver) {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getDatabaseConnection() {
loadDriver(JDBCDRIVER);
Connection conn = null;
try {
System.out.println("Connecting to the database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected!");
} catch (SQLException se) {
se.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
JDBCDao dao = new JDBCDao();
dao.getDatabaseConnection();
}
public boolean insertExpenseData(Expense expense) {
Connection con = getDatabaseConnection();
String sql = "";
sql = "INSERT INTO expenses (expense_name,expense_description,category,amount,date) VALUES (?,?,?,?,?)";
try {
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, expense.getExpense_name());
ps.setString(2, expense.getExpense_description());
ps.setString(3, expense.getCategory());
ps.setDouble(4, expense.getAmount());
ps.setString(5, expense.getDate());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
public List<Expense> getAllExpense() throws SQLException {
Connection con = getDatabaseConnection();
List<Expense> list = new ArrayList<>();
String sql = "SELECT * FROM expenses ORDER BY date DESC";
ResultSet result = null;
try {
PreparedStatement ps = con.prepareStatement(sql);
result = ps.executeQuery();
while (result.next()) {
Long id = Long.parseLong(result.getString("id"));
String name = result.getString("expense_name");
String description = result.getString("expense_description");
String category = result.getString("category");
Double amount = Double.parseDouble(result.getString("amount"));
String date = result.getString("date");
Expense record = new Expense(id, name, description, amount, category, date);
list.add(record);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
result.close();
con.close();
}
return list;
}
}
Now it’s time to show the data in the JSP page
So , Go to this url: localhost:8080/MyJSPServletProject/expense?view
Hope it works, if have any issues please fill free to comment.
