Development of Java Servlet for Graphing SQL-Data
Ian Gass, May 10, 2002
Introduction
For my final project, I chose to write a servlet that could obtain data from a database and then graphically display the results in a bar chart. This servlet would be used in conjunction with my senior design project to provide more advanced reporting features. My senior design project, entitled Database and Web Integration: An ASP Solution, is a web-based data entry and management system. This servlet is being used to generate a Pareto chart based on data entered into the database.
The Pareto chart categorizes the different types of errors that cause a part to be out of specification and then shows graphically which error occurs more often than others. Personnel in the quality department use this chart to determine what area of the production process is causing the most errors. They can then concentrate their efforts on improving that area to reduce the number of errors it is causing.
Objectives
When designing my graphing servlet, there where several operational requirements that I determined it needed to meet.
· Needed to be secure
· Needed to be able to specify chart criteria from a web form
· Needed to be able to scale bar chart data to fit in a pre-determined window size
Specifications
To complete this project the following items were used:
· Intel Pentium based server
o Pentium III 800 MHz
o 512 MB RAM
o 20 GB free disk space
o 100 Mbps network adapter
· Microsoft Windows 2000 Server Service Pack 2
· Microsoft Internet Information Server
· Microsoft SQL Server 2000
· Jakarta Tomcat Server
· Java 2 SDK
· Java 2 EE
· JCreator Professional
Design Analysis
Since this servlet was being used in conjunction with my senior design project, some portions didn’t need to be created. The database table that I would be using to create the bar chart was already in place and had already been populated with some data. The process of writing data to the database had already been accomplished through the use of ASP. The fields being used to generate the report are Part_Number, Reason, and Quantity. Part_Number is the part number that was produced that contained the errors, Reason is a letter A – H that represents the different types of errors, and Quantity is the number of parts that were bad for the reason. The other fields are not used for any purpose within the context of this servlet. A diagram of the table can be seen in figure 1.
Figure 1 – Table Diagram
My servlet is a stand-alone class in that I didn’t write any other class definitions to be used with it. Others are imported but I only wrote one. A class diagram can be seen below in figure 2.
SQLGraphing.classpublic void doPost()
public void doGet()
public double getScale()
public int maximum()
public Image BarChart()
Figure 2 – Class Diagram
The methods doPost() and doGet() are both inherited methods of the servlet class. doGet() is executed whenever the servlet receives a “GET” request and doPost() is executed whenever the servlet receives a “POST” request. The doGet() method is not used in my servlet since it is receiving POSTED data from a web form. The doPost() method retrieves the values from the form and then runs a query on the database. It then stores the values from the database into an array and passes the array to BarChart() which uses the values to generate the chart.
The method maximum() is used by BarChart() to determine what the largest number is that was passed to it. It then passes the result of maximum() to getScale() to determine how many screen pixels each bar on the chart should be. For example, if a value of 5 is returned from the result set, how many pixels does this translate to onto the screen.
System Implementation
Since a servlet was being used to generate graphs, the application followed a request/response type method between the client and the server. A block diagram of the process can be seen below in figure 3. This figure also shows how ASP communicates with the database and the relationship between IIS, the primary web server in use, and Tomcat, the server being used to serve Java servlets.
Figure 3 – Client/Server Block Diagram
The process of displaying the bar chart works as follows:
A user logs into the website and chooses the link for Pareto Chart (figure 4). The ASP portion of the application controls the site security. The user then chooses what part number they want to run a Pareto chart on, or they can leave the field blank to run a query on all results, as seen in figure 5.
Figure 4 – Main Menu with Report Link
Figure 5 – Pareto Chart Submission Form
Once the user presses “Search” the data is posted to the servlet SQLGraphing. When the data has been posted, the method getPost() is run first. The method starts by initializing the variables required to operate. The method then gets the values of the posted data and stores them in variables. A SQL query, which will obtain information from the database, is then generated using these posted values. The query is executed and the results are then stored in two arrays by means of a while loop and a counter “i” is kept to keep track of how many times the loop executes. Once the while loop has finished executing, two new arrays are created and initialized with the value of “i”. A “for” loop then executes which sets the values of the previously created arrays to the values of the new arrays.
A GifEncoder object is then created. This object was downloaded from www.acme.com and is a free package that can be used for converting drawn images into .gif or .jpg format for use on the web. This object, when instantiated, requires two values, an Image object and an output stream. It then takes the image, converts it to a gif, and sends the byte stream out on the specified output stream.
The image object is created using the method BarChart(). This method requires three variables: a string array for the x-axis labels, an integer array for the values of the bars, and a string for the title of the chart. This method starts by getting the scaled values of the data passed to it. This is done using the method getScale() in conjunction with the method maximum(). The method maximum() returns the largest number in the integer array that was passed to BarChart(). This number is then passed to getScale(), which loops through an array to determine what scaling value to use. Once the scaling value has been determined, all the values in the integer array which was passed to BarChart() are multiplied by it to determine how many pixels tall the bar should be. With the height of the bars determined, a “for” loop is executed which draws the bars and labels onto a frame image. When the loop is finished, the drawn image is returned.
Test Results
The testing results below demonstrate that the project works.
The first screenshot (figure 6) shows the report definition page where a part number is chosen.
Figure 6 – Report Definition Screenshot
The second screenshot (figure 7) shows the results.
Figure 7 – Pareto Chart Screenshot
As can be seen in the above chart, there are currently no errors with the codes A, G, or H.
In figure 8 a new error code was entered for this part number, using the pages that were previously created using ASP. The screenshot shows that an error was entered for part number 57-165 with a quantity of 15 parts bad because of reason “A”
Figure 8 – Adding A New Error Screenshot
After the new error record was added the report was re-ran. As seen in figure 9, the chart now displays the 15 new errors under reason code “A”
Figure 9 – Updated Pareto Chart
Conclusion
This project works well and displays a bar chart as specified. One item that still needs work is that the query isn’t able to search based on a supplied date range. Problems were encountered when trying to convert a Java date value into a SQL smalldatetime value. Much time was spent on this particular problem and I hope to resolve it soon.
This concludes my report and my undergraduate academic career at IPFW. I have learned a great deal in this class about the Java programming language, and while I’m not an expert at it, I have gained a good understanding of the language and hope to continue to use it and expand my knowledge of it.
I would like to thank you for all that you have taught me in the several classes that I have taken with you over the past few years, especially CPET 355. The things that I have learned in that class have helped me immensely over the past two years as I have gotten more involved in computer networking. It helped me get the job I held previously because my employer was impressed that I understood networking that well. That job, in turn, helped me get the one I currently have. Hopefully we can keep in touch and maybe I’ll see you again in a future class.
References
Deitel, Harvey, Deitel, Paul, Nieto, Tem. Java: How to Program. Upper Saddle River, NJ: Prentice Hall, 2002
Hinkle, Jason. Installing Tomcat to Work With IIS. 2000 <http://www.verysimple.com/scripts/suooprt_tc_iis.html> (25 April, 2002)
Hoffman, James. Introduction to Structured Query Language. 2001
< http://w3.one.net/~jhoffman/sqltut.htm> (2 February, 2002).
Hunter, Jason. Java Servlet Programming. Sebastopol, CA: O’Reilly, 2001
O’Neil, Joseph. Teach Yourself Java. Berkeley, CA: Osborne/McGraw-Hill, 1999.
Poskanzer, Jeffrey. ACME Laboratories. 2002 <http://www.acme.com> (6 May, 2002)
Riordan, Rebecca. Microsoft SQL Server 2000 Programming. Redmond, WA: Microsoft Press, 2001.
Appendix A – Activity Log
Activity Log
4/25/02 / Downloaded and installed Jakarta Tomcat server, worked on configuration.4/26/02 / Continued to work on configuration of Tomcat. Had difficulties integrating it with IIS. Wasn’t sure where to start and found many different options on the Internet. Found a work around by just letting Tomcat run on port 8080 and then whenever a servlet is called, just pointing it to port 8080 of the same server address.
5/2/02 / Initial project planning. Researched how to create Servlets and obtain information from my database.
5/3/02 / Worked on retrieving information from the database and displaying the results in tabular format. Learned how to use the Java database classes: Connection, ResultSet, Statement, and ResultSetMetaData.
5/6/02 / Researched methods for creating images within a servlet and converting the images to a known file format, .gif, and then displaying to the user the resulting image. Gif compression class was not written but was downloaded from www.acme.com and was used in this project.
5/7/02 / Created a query and worked on creating a bar chart to display the results of the query. MUCH time spent on formatting of the image and data. Had problems with scaling the data to fit into a certain window area. Had difficulties of working with the data returned since the number of records was not constant, needed a dynamic array and I tried working with Collections, Vectors and Stacks to no avail. Eventually found a work around by creating 2 arrays for each result set.
5/8/02 / Worked on customizing the SQL query by making it possible to post a web form to the servlet and incorporating the posted values into the query. Could not get the data range option to work correctly.
5/9/02 / Continued to work on posting data and in particularly the problemn I was having with the data format being passed to SQL. Also worked on final paper.
5/10/02 / Finished paper and worked on PowerPoint slides
Appendix B – Source Code