Java Servlet

Eclipse Helios/GlashFish/SQLite

HTML Report

System Requirements:

Download Eclipse Helios

http://www.eclipse.org/helios/

Download GlassFish Server 3.1

http://glassfish.java.net/downloads/3.1-final.html


Download SQLite

http://www.sqlite.org/download.html


Download SQLiteJDBC jar file

http://code.google.com/p/sqlite-jdbc/


Introduction:

Build a connection to SQLite database located on C Drive using a Java Servlet class and a database connector class. Once connection is established, grab data from SQLite and fill HTML report based on JavaServer Pages Application, serviced by GlassFish Web Server; user is diverted to login page if session is invalid.


Disclaimer: Information contained in the following is presented as is. This tutorial assumes you have basic programming knowledge. All tutorials are based on an Eclipse/Eclipse-based software. Should you need to familiarize yourself with a certain Eclipse environment, prior to continuing this tutorial, please stop now and see our Tutorials page...


Write Java Servlet classs to build HTML Report

At this point we assume Eclipse Helios, SQLite database and JDBC jar file, GlassFish Web Server are downloaded/installed and a Java Servlet class has been created. You are going to use a database connector class with Java Servlet to grab data from SQLite database and fill HTML page. Copy and paste below code to your environment for each code samples.


SearchUserDataServlet.java


/**

* Copyright 2012 Dököll Solutions, Inc.

Licensed under the Apache License, Version 2.0 (the "License");

you may not use this file except in compliance with the License.

You may obtain a copy of the License at


http://www.apache.org/licenses/LICENSE-2.0


Unless required by applicable law or agreed to in writing, software

distributed under the License is distributed on an "AS IS" BASIS,

WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

See the License for the specific language governing permissions and

limitations under the License.

*

*

* @AppName: login

* Program: SearchUserDataServlet.java

* Created from Copy: 2010.09.05.11.01.AM

*

* Modified: 2012.04.07.5.36.AM

* Added SQLite connection

*/

package com.dokoll.solutions.inc.developement.Servlets;


/**

* @author Dököll Solutions, Inc.

* @version 2010.09.05.11.01.AM

*

*/


import java.io.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.sql.*;

import java.util.*;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.dokoll.solutions.inc.developement.Utils.DBConnector;


/**

* Servlet implementation class for Servlet: SearchUserDataServlet

*

*/


public class SearchUserDataServlet extends HttpServlet{

private static final long serialVersionUID = 1L;


public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException,IOException{

response.setContentType("text/html");

PrintWriter out = response.getWriter();


System.out.println("SQLite Connectting...");

Connection conn = null;


int count = 0;

// Enter Try Catch

try {

//bring in connection from DBConnector class

conn = DBConnector.getConnection();

System.out.println("Connected to the database...");

//set up array

ArrayList myArrayList=null;

ArrayList myUserList =new ArrayList();

//set up query string (as a PreparedStatement)

PreparedStatement pst = conn.prepareStatement("Select AllegationCode,Office, InvCode, DateOpen, Age from USERDETAILS where InvCode = ? AND Office =? AND InvCode NOT NULL ");


//fill variables with request

String Office = request.getParameter("Office");

String InvCode = request.getParameter("InvCode");

//...

pst.setString(1,Office);

pst.setString(2,InvCode);

System.out.println("Office Recorded..." + Office);

System.out.println("Records Returned...");

//run Query string

ResultSet rs = pst.executeQuery();


//loop through results

while(rs.next()){

//load array with results

myArrayList = new ArrayList();

//...

myArrayList.add(rs.getString("AllegationCode"));

myArrayList.add(rs.getString("Office"));

myArrayList.add(rs.getString("Age"));

myArrayList.add(rs.getString("DateOpen"));


myUserList.add(myArrayList);

count++;


}


request.setAttribute("userData",myUserList);

System.out.println("Records " + count);


//fill HTML/JSP page with reports data

String nextJSP = "/UserSearchResults.jsp";

RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(nextJSP);

dispatcher.forward(request,response);

//close resultSet and connection

rs.close();

conn.close();

System.out.println("Connected stopped...");

} catch (Exception e) {

e.printStackTrace();

}

}

}



Build a search form to retrieve data for the report

Let's perform the step of searching for data to fill in a new HTML form. Copy and Paste code below, run it in your browser


SearchComplaints.jsp


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<%@page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@ page import="java.text.*,java.util.*" session="true"%>

<%@page import="java.sql.Date.*"%>


<html>

<head>


<%

String name = request.getParameter( "UserID" );

session.setAttribute( "theName", name );

%>


<%

if (session.getAttribute("theName" ) == null) {

response.sendRedirect("Login.jsp");

return;

}


%>



<title>Search User Complaints</title>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

</head>



<body bgcolor="#deefef">

<b>Logged in As:</b> <%= session.getAttribute( "theName" ) %>

<form method="post" name="frm" action="SearchUserDataServlet">


<table border="0" width="330" align="left" bgcolor="lightgrey">

<tr><td colspan=2 style="font-size:12pt;color:#00000;" align="left">

<h3><font color="red">User Complaints - <b><a href="SearchUserByAge.jsp">Search By Age Range</a></b></font></h3></td></tr>


<tr><td align="right">Office</td>

<td><input type="text" name="Office" id="Office">

</td></tr>


<tr><td align="right"> UserID</td>

<td><input type="text" name="InvCode" id="InvCode">

</td></tr>


<tr><td align="right"> </td>

<td><input type="submit" name="submit" value="Run Report"></td></tr>


</table>




</form>



<table border="0" width="420" align="right" bgcolor="#deefef">

<tr><td colspan=2 style="font-size:12pt;color:#00000;" align="left">

<font color="blue"><b>TIP:</b> Results for this report will be rendered in HTML format</font></td></tr>

<tr><td><font color="blue">We suggest consulting our report manual before you begin</font></td>

<tr><td><font color="blue">Click "Run report" button only once for faster results</font></td>

<tr><td><b></b><font color="blue"><font color="blue">Visit our Frequently Asked Questions page for added support</font></font></td>

<tr><td><b></b><font color="blue"><font color="blue">Send us a email for technical assistance: <a href="UserComments.jsp">Technical Support</font></font></td>



</table>

<br><br><br><br><br><br><br><br>

<HR>



<table align="center" cellpadding="10" cellspacing="1" width="98%" bgcolor="white"

border="0" height="570">

<tr>

<td align="right" bgcolor="#bac6c7" width="23%">Complaints By Users</td>

<td bgcolor="#bac6c7" colspan="3" width="200">

<center><font size="-1"> <a

href="SearchComplaints.jsp" target="colours">Search User Complaints</a></font></center>

</td>

<td bgcolor="#bac6c7" align="right" width="23%">Complaints By Age</td>

<td bgcolor="#bac6c7">

<center><font size="-1"> <a

href="SearchUserByAge.jsp" target="colours">Search User By Age</a></font></center>

</td>

</tr>



</table></body></html>





Load this data to HTML report

Our last critical step is to load the retrieved data to an HTML report. Copy and Paste code below, run it in your browser


UserSearchResults.jsp


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<%@page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@ page import="java.text.*,java.util.*" session="true"%>

<%@page import="java.sql.Date.*"%>

<html>

<head>

<%

String name = request.getParameter( "UserID" );

session.setAttribute( "theName", name );

%>


<%

if (session.getAttribute("theName" ) == null) {

response.sendRedirect("Login.jsp");

return;

}


%>

<title>User Search Results</title>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

</head>

<body>

<b>Logged in As:</b> <%= session.getAttribute( "theName" ) %>

<br>

<table align=center border="0" width="820">

<tr>

<th><h2 align=left> Dököll Solutions, Inc.<br> Complaint Report</h2></th>

<th><h2 align=right>Users for Better Software<br><script type="text/javascript">

var d=new Date()

var weekday=new Array("Sunday","Monday","Tuesday","Wednesday","Thursday",

"Friday","Saturday")

var monthname=new Array("January","February","March","April","May","June","July","August",

"September","October","November","December")

document.write(weekday[d.getDay()] + " ")

document.write(d.getDate() + ", ")

document.write(monthname[d.getMonth()] + ", ")

document.write(d.getFullYear())

</script></h2></th>

</tr>


</table>


<center><a href="javascript:void(0);"

onclick="document.execCommand('SaveAs',true,'ComplaintsByUser.html');">

<h2><font color="blue">User Complaints</font><br><font color="red"></font></h2></a></center>


<table border="0" cellspacing="0" cellpadding="2" width="800" align="center" style="border:0px solid #000000;">


<tr style="background-color:efefef;">


<HR>

<td><center><b><a class="print_page" href="javascript:window.print();" onclick="javascript:urchinTracker('outgoing/printlink/en');">Allegation</a></b></center></td>


<td><center><b><a class="print_page" href="javascript:window.print();" onclick="javascript:urchinTracker('outgoing/printlink/en');">Office</a></b></center></td>


<td><center><b><a class="print_page" href="javascript:window.print();" onclick="javascript:urchinTracker('outgoing/printlink/en');">Age</a></b></center></td>

<td><center><b><a class="print_page" href="javascript:window.print();" onclick="javascript:urchinTracker('outgoing/printlink/en');">Open</a></b></center></td>

</tr>

<%

int RecordCounts=0;

int count=0;

String color = "#F9EBB3";



if(request.getAttribute("userData")!=null)

{

ArrayList myArrayList = (ArrayList)request.getAttribute("userData");

Iterator itr = myArrayList.iterator();



while(itr.hasNext()){


if((count%2)==0){

color = "#eeffee";


System.out.println("Records " + RecordCounts);

}

else{

color = "#F9EBB3";

}

count++;

RecordCounts++;

ArrayList userData = (ArrayList)itr.next();

%>


<tr style="background-color:<%=color%>;">


<td><center><%=userData.get(0)%></center></td>

<td><center><%=userData.get(1)%></center></td>

<td><center><%=userData.get(2)%></center></td>

<td><center><%=userData.get(3)%></center></td>

</tr>

<%

}

}

%>

<%

if(count!=0) {

%>


<tr>

<td colspan=8 align="center" style="background-color:eeffee"><b><font color="blue"><br><br>Record(s) Found: </font></b><%= RecordCounts %></td>

</tr>

<%

}

%>


<%

if(count==0) {

%>


<tr>

<td colspan=8 align="center" style="background-color:eeffee"><b><font color="red">No Record(s) Found</font></b></td>

</tr>

<%

}

%>

</table><br>


<center><i>The information contained in this document is strictly confidential and may be used only for the purpose of

conducting the business of Dököll Solutions, Inc.</i></center>


</html>


Conclusion:

Our HTML report contains a hidden print feature and an save mechanism. See cheat sheet below for clues. Please stay tuned to additional tutorials on the subject...


Hint: This item can be linked to an existing login project, making a connection to SQLite database...


Cheat sheet:

Save the report using the web page's own Save feature. Click on the name of the report to save (must have JavaScript enabled)...


<a href="javascript:void(0);"

onclick="document.execCommand('SaveAs',true,'ComplaintsByUser.html');">

<h2><font color="blue">...</...


Further, Print this report using the web page's own Print feature. Click on the the column/label area to print...


<a class="print_page" href="javascript:window.print();" onclick="javascript:urchinTracker('outgoing/printlink/en');">...</a>


Questions, comments, please post a brief message on our Contact form on the main site. Thank you for coming...