In this example we discussion about How to show total amount month and year wise JSP Java.
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%
String id = request.getParameter("id");
String driver = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String database = "student";
String userid = "root";
String password = "";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}-
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
%>
<!DOCTYPE html>
<html>
<body>
<table border="1" width="100%">
<tr>
<th>Year</th>
<th>Month</th>
<th>Amount</th>
</tr>
<%
try{
connection = DriverManager.getConnection(connectionUrl+database, userid, password);
statement=connection.createStatement();
String sql ="select year(date) as year, month(date) as month, sum(paid) as total_amount from amount_table group by year(date), month(date)";
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
String month=resultSet.getString("month");
%>
<tr>
<td><%=resultSet.getString("year") %></td>
<td><%=resultSet.getString("month") %></td>
<td><%=resultSet.getString("total_amount") %></td>
</tr>
<%
}
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
%>
</table>
</body>
</html>
