Menu


Need MCA Or B.tech College Project Contact Us: +91 9437911966 (Whatsapp) Students Tutorial

How to show total amount month and year wise JSP Java


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>