Java Project JSP JDBC Java Program Core Java Demo MORE

Show Transaction history between two date range JSP Java


In this example we discussion about how to show transction history between two data range in JSP Java.

SQL table query

CREATE TABLE `transaction` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `amount` float(10,2) NOT NULL,
  `date_time` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here we using 2 files for show transaction history:

  • index.jsp
  • imultiple-checkbox.jsp:for process the incoming data from index.jsp

index.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%
String name=request.getParameter("name");
String driver = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String database = "test";
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 PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Show Transction</title>
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-1.10.2.js"></script>
    <script src="https://code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
<script>
$( function() {
$( "#datepicker" ).datepicker({ changeMonth: true, changeYear: true });
$( "#datepicker" ).datepicker( "option", "dateFormat", "yy-mm-dd" );
} );
</script>
<script>
$( function() {
$( "#datepicker1" ).datepicker({ changeMonth: true, changeYear: true });
$( "#datepicker1" ).datepicker( "option", "dateFormat", "yy-mm-dd" );
} );
</script>
</head>
<body>
<form method="post" action="show-transaction.jsp">
<p> From Date: <input type="text" name="form_date" id="datepicker"></p>
<p> To Date: <input type="text" name="to_date" id="datepicker1"></p>
<%
try{
connection = DriverManager.getConnection(connectionUrl+database, userid, password);
statement=connection.createStatement();
String sql ="select * from transaction";
resultSet = statement.executeQuery(sql);
%>
Name : <select  name="name">
<option value="" disabled selected>Choose name</option>
 <% 
while(resultSet.next()) {
%>	
<option value=<%=resultSet.getString("name")%>> <%=resultSet.getString("name")%></option>
<% 
}
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
%>
</select>
<br>
<br>
<input type="submit" value="Submit"><br>
</form>
</body>
</html>

show-transaction.jsp

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%
String name=request.getParameter("name");
String form_date=request.getParameter("form_date");
String to_date=request.getParameter("to_date");
String driver = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String database = "test";
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>
<h1>Show Transction</h1>
<table border="1">
<tr>
<td>Id</td>
<td>Name</td>
<td>Amount</td>
<td>Date_Time</td>
</tr>
<%
try{
connection = DriverManager.getConnection(connectionUrl+database, userid, password);
statement=connection.createStatement();
String sql ="select * from transaction where date_time between '"+form_date+"' and '"+to_date+"' and name='"+name+"'";
resultSet = statement.executeQuery(sql);
while(resultSet.next())
{
%>
<tr>
<td><%=resultSet.getString("id") %></td>
<td><%=resultSet.getString("name") %></td>
<td><%=resultSet.getString("amount") %></td>
<td><%=resultSet.getString("date_time") %></td>
</tr>
<%
}
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
%>
</table>
</body>
</html>