databaseservice.java

jsp+servlet+jdbc结构的在线访问统计系统

package statistic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;
import java.sql.Time;
import java.util.StringTokenizer;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;
import org.jfree.data.time.Day;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;
import org.jfree.data.xy.XYDataset;


/**
 * JDBC访问Service
 *
 */
public class DatabaseService
{	
	/**
	 * 建立到数据库的连接
	 */
	public static Connection getConnection()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/statisticDB","root","root");
			
			return connection;
		}
		catch(Exception e)
		{
			 e.printStackTrace();
			 
			 return null;
		}
	}
	
	/**
	 * 记录客户访问
	 */
	public static void visit(String ip, String os, String browser, String pageName)
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
        
        try {
        	//获取连接
        	connection = DatabaseService.getConnection();
        	pStatement = connection.prepareStatement("insert into access values(?,?,?,?,?,?,?)");

        	//下面分别填充参数
        	pStatement.setInt(1, 0);
        	pStatement.setString(2, ip);
        	pStatement.setString(3, os.toUpperCase());
        	pStatement.setString(4, browser.toUpperCase());
        	pStatement.setString(5, pageName.toUpperCase());
        	pStatement.setDate(6, new Date(System.currentTimeMillis()));
        	pStatement.setTime(7, new Time(System.currentTimeMillis()));
        	
        	//更新数据库
        	pStatement.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
        	try	{
        		//关闭数据库连接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}
	
	/** 
	 * 查询获取操作系统类型统计所需的数据集 DefaultPieDataset 对象
	 * 
	 */
	public static DefaultPieDataset getBrower()
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
	    ResultSet resultset = null;
	    
	    DefaultPieDataset dataset = new DefaultPieDataset();
	    
	    try {
        	//获取连接
        	connection = DatabaseService.getConnection();
        	String sql = "select brower,count(brower) from access group by brower";
        	pStatement = connection.prepareStatement(sql);
        	resultset=pStatement.executeQuery();
        	
        	while(resultset.next())
        	{
        		dataset.setValue(resultset.getString(1), resultset.getInt(2));
        	}
        	
        	return dataset;

	    }catch (Exception e) {
            e.printStackTrace();
            
            return null;
        }
        finally {
        	try	{
        		//关闭数据库连接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}	
	
	/** 
	 * 查询获取页面访问统计所需的数据集 DefaultCategoryDataset 对象
	 * 
	 */
	public static DefaultCategoryDataset getPage()
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
	    ResultSet resultset = null;
	    
	    DefaultCategoryDataset dataset = new DefaultCategoryDataset();
	    
	    try {
        	//获取连接
        	connection = DatabaseService.getConnection();
        	String sql = "select page,count(page) from access group by page ASC";
        	pStatement = connection.prepareStatement(sql);
        	resultset=pStatement.executeQuery();
        	
        	while(resultset.next())
        	{
        		dataset.addValue(resultset.getInt(2),resultset.getString(1), resultset.getString(1));
        	}
        	
        	return dataset;

	    }catch (Exception e) {
            e.printStackTrace();
            
            return null;
        }
        finally {
        	try	{
        		//关闭数据库连接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}
	
	/** 
	 * 查询获取每日流量统计所需的数据集 XYDataset 对象
	 * 
	 */
	public static XYDataset getTime()
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
	    ResultSet resultset = null;
	    
        TimeSeriesCollection dataset = new TimeSeriesCollection();
        //时间序列对象集合，里面可以存放多个TimeSeries对象，
        //TimeSeriesCollection实现了XYDataset接口
        TimeSeries timeseries = new TimeSeries("每日访问", Day.class);
        //时间序列对象，第一个参数表示时间序列的名字，第二个参数是时间类型，这里为天
        //该对象用于保存前count天每天的访问次数
	    
	    try {
        	//获取连接
        	connection = DatabaseService.getConnection();
        	String sql = "select accessDate,count(accessDate) from access group by accessDate ASC";
        	pStatement = connection.prepareStatement(sql);
        	resultset=pStatement.executeQuery();
        	
        	while(resultset.next())
        	{
                StringTokenizer st = new StringTokenizer(resultset.getString(1), "-");
                //从日期字符串str中，获取年、月、日
                int year = Integer.parseInt(st.nextToken());
                int month = Integer.parseInt(st.nextToken());
                int day = Integer.parseInt(st.nextToken());
                timeseries.add(new Day(day, month, year), resultset.getInt(2));
                //将前面第(count - i - 1)天的访问次数保存到时间序列对象中
        	}
        	dataset.addSeries(timeseries);
        	
        	return dataset;

	    }catch (Exception e) {
            e.printStackTrace();
            
            return null;
        }
        finally {
        	try	{
        		//关闭数据库连接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}
	
	public static void main(String[] arg)
	{
		DefaultPieDataset dataset=DatabaseService.getBrower();		
		System.out.println(dataset.getItemCount());
		
		DefaultCategoryDataset dataset1=DatabaseService.getPage();
		System.out.println(dataset1.getColumnCount());
	}
	
	
}