Statistics with JSP and MySQL

In this article we will see how to create, with JSP (Java Server Pages) and a MySQL database, a simple system for managing access statistics for a website. So look at some important operations, such as how to access the database directly from the Web container, as it does not work entirely in the context of J2EE (Enterprise Edition) but directly from the Web Server without going through the Entity Bean.

The system we implement will be able to detect users online, the 'last access and create an archive of all visits, where afterwards you can run other reports such as, for example, calculate the number of visitors daily, monthly and annual and much more. The key thing is to keep track of all the important information that characterize a user accessing the pages of the site, once stored the data we can build reports for every need.

MySQL table structure

The first step is obviously to create the MySQL table to store data for access statistics, ie the table that holds all of the historical sightseeing.
For each access, in fact, we're going to store important data that are sent via the HTTP request that the client makes when accessing the site. This information is:

• IP: The IP address of the client that connects to the site
• USER-AGENT: the string that usually describes the browser (IE, Firefox, Chrome, etc..) But can also be a spider or crawler
• REFERER: the url of origin which is zero if the access is direct
• SYSOP: a string that identifies the operating system
• LANGUAGE: A string that identifies the language of origin of clients

In addition to this information (which we extract from the HTTP request) we also store the IP TIMESTAMP together form the primary key of this table (the IP is certainly unique in a given moment, then the pairing given access easily identified). To create the database:

 



 CREATE DATABASE `stat`

 

This is the SQL table which we will call userlog:

 CREATE TABLE `stat`. Userlog `` (



  



 `IP` varchar (19) NOT NULL,



  



 TIME_ACCESS `` varchar (100) NOT NULL default '0000-00-00 00:00:00 ',



  



 SYSOP `` varchar (300) default NULL,



  



 USER_AGENT `` varchar (300) default NULL,



  



 `Language` varchar (300) default NULL,



  



 REFERER `` varchar (300) default NULL,



  



 BTREE USING PRIMARY KEY (`IP`, `` TIME_ACCESS)







 ) ENGINE = InnoDB DEFAULT CHARSET = latin1;

Deploying Servlet / JSP

Now we must prepare our development environment to create the JSP application.
In order to use MySql we have to import the library fits into the project. NetBeans or Eclipse, simply select the right mouse button on the project, then click on Properties - Library - Add Library (Java Build Path for Eclipse) and select the MySQL JDBC Driver. If the library is not present we can download at this page . To import the project must unpack the tar or zip archive and integrate the JAR file with the same procedure but selecting Add JAR / Folder.

• <<
• 1
• 2
• 3
• 4
• 5
• >>