利用java mysql 统计日志

DROP PROCEDURE IF EXISTS test.p_test;
CREATE PROCEDURE test.`p_test`(startDateStr varchar(20), endDateStr varchar(20),frequency int)
begin
declare i int;
declare cnt int;
declare startDate datetime;
declare endDate datetime;
DECLARE intervalSec int;
set i=0;
set cnt=0;
set startDate=str_to_date(startDateStr , '%Y-%m-%d %H:%i:%s');
set endDate=str_to_date(endDateStr, '%Y-%m-%d %H:%i:%s');
set intervalSec=time_to_sec((timediff(endDate,startDate)));
if  intervalSec%(60*frequency)<>0 then
  set cnt=intervalSec DIV (60*frequency)+1;
	
else
	set cnt=intervalSec DIV (60*frequency);
end if;


while i<cnt DO
INSERT INTO result20110828(`daterange`,`result`) 
SELECT 
CONCAT(DATE_FORMAT(date_add(startDate, interval i*frequency minute),'%Y-%m-%d %H:%i:%s'),' - ',DATE_FORMAT(date_add(startDate, interval (i+1)*frequency minute),'%Y-%m-%d %H:%i:%s'))
,count(1) from test20110828 t WHERE 
t.testDate>= date_add(startDate, interval i*frequency minute)
and 
t.testDate< date_add(startDate, interval (i+1)*frequency minute) 
and 
t.testDate<endDate;

set i=i+1;
end while;

end;

package test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;



public class Batch {
	
	private static String  logName="d://catalina.out-20110828";
	private static Connection conn = null;  
	private static PreparedStatement stmt = null;  
	private static int lines=10000;
	private static int batchNum=5000;
    //Batch;;;数据库，专门大量数插;;  
    /*public static void batchInsert(List<String> list){  
        Connection conn = null;  
        PreparedStatement stmt = null;  
        DBHelper db = null;  
       // long start = System.currentTimeMillis();  
        try {  
            if(null==list){
            	System.out.println("日志为空");
            }else{
            	db = new DBHelper();  
                conn = db.getConn();  
                //jdbc默认自动提交;;;insert commit  
                // 下面这句可别创建for  
                conn.setAutoCommit(false);  
//                String sql = "insert into test2011-08-21(testDate) values(str_to_date(?, '%Y-%m-%d %H:%i:%s'))";  
                String sql = "insert into test20110821(testDate) values(?)";  
                
                stmt = conn.prepareStatement(sql);  
            	 for (int i = 0; i < list.size(); i++) {  
                     stmt.setString(1, list.get(i));  
                     stmt.addBatch();  
                     if(i % 100 == 0){  
                         stmt.executeBatch();  
                         conn.commit();  
                         stmt.clearBatch();  
                           
                     }  
                     //stmt.executeUpdate();  
                 }  
                 conn.commit();  
                 //提交方式，改原来自动提交,,一句一提交..  
                 conn.setAutoCommit(true);  
            }
           
        } catch (SQLException e) {  
            try {  
                conn.rollback();  
            } catch (SQLException e1) {  
                e1.printStackTrace();  
            }  
            e.printStackTrace();  
        } finally {  
            db.closeCon(stmt, conn,null);  
        }  
        //long end = System.currentTimeMillis();  
        //ps bach一共花了578毫秒  
        //System.out.println("ps bach一共花了"+(end - start)+"毫秒");  
              
    }  
    */
	
	 
    /**
     * 以行为单位读取文件，常用于读面向行的格式化文件
     * @throws SQLException 
 * @throws AdsDBException 
     */
   /* public static List<String> readFileByLines(String fileName)  {
        File file = new File(fileName);
        BufferedReader reader = null;
        List<String> list=new ArrayList<String>();
        try {
            System.out.println("以行为单位读取文件内容，一次读一整行：");
            
            reader = new BufferedReader(new FileReader(file));
            String tempString = null;
            int line = 1;
            // 一次读入一行，直到读入null为文件结束
            while ((tempString = reader.readLine()) != null) {
            	if(tempString!=null && tempString.length()>=20){
            		if(isDate(tempString.substring(0, 19))){
            			list.add(tempString.substring(0, 19));
            		}
            		
            		if(list.size()%1000==0){
            			batchInsert(list);
            			list.clear();
            		}
            	}
            	
                // 显示行号
                System.out.println("line " + line + ": " + tempString);
                line++;
            }
            reader.close();
            if(list.size()>0){
            	batchInsert(list);
    			list.clear();
            }
           
            
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
        return list;
    }*/
	public static void batchInsert(List<String> list) throws SQLException{  
	            	 for (int i = 0; i < list.size(); i++) {  
	                     stmt.setString(1, list.get(i));  
	                     stmt.addBatch();  
	                     if(i % batchNum == 0){  
	                         stmt.executeBatch();  
	                         conn.commit();  
	                         stmt.clearBatch();  
	                           
	                     }
	                 }  
	            	 if(list.size()%batchNum>0){
                    	 stmt.executeBatch();  
                         conn.commit();  
                         stmt.clearBatch();  
	            	 }
	            	 
	    }  
	 public static List<String> readFileByLines(String fileName) throws SQLException  {
        File file = new File(fileName);
        BufferedReader reader = null;
        List<String> list=new ArrayList<String>();
        try {
            System.out.println("以行为单位读取文件内容，一次读一整行：");
            
            reader = new BufferedReader(new FileReader(file));
            String tempString = null;
            int line = 1;
            // 一次读入一行，直到读入null为文件结束
            while ((tempString = reader.readLine()) != null) {
            	if(tempString!=null && tempString.length()>=20){
            		if(isDate(tempString.substring(0, 19))){
            			list.add(tempString.substring(0, 19));
            		}
            		
            		if(list.size()%lines==0){
            			batchInsert(list);
            			list.clear();
            		}
            	}
            	
                // 显示行号
                System.out.println("line " + line + ": " + tempString);
                line++;
            }
            reader.close();
            if(list.size()>0){
            	batchInsert(list);
    			list.clear();
            }
           
            
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
        return list;
    }
    
    /**
 	 * Checks if is date.
 	 *
 	 * @param dateString the date string
 	 * @return true, if is date
 	 */
 	public static boolean isDate(String dateString) {
		 // validate empty
		 if(dateString == null || dateString.trim().length() == 0){
			 return false;
			 
		 }
		 //validate date format
	        try {
	            SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	            dateformatter.setLenient(false);
	            dateformatter.parse(dateString);
	            return true;
	        } catch (Exception e) {
	            return false;
	        }

	    }
    public static void main(String[] args){
    	
    	
    	try { 
    		
            conn = DBHelper.getConn();  
            //jdbc默认自动提交;;;insert commit  
            // 下面这句可别创建for  
            conn.setAutoCommit(false);  
            String sql = "insert into test20110828(testDate) values(?)";  
            stmt = conn.prepareStatement(sql);  
            readFileByLines(logName);
            conn.commit(); 
            //提交方式，改原来自动提交,,一句一提交..  
            conn.setAutoCommit(true);
    	} catch (SQLException e) {  
            try {  
                conn.rollback();  
            } catch (SQLException e1) {  
                e1.printStackTrace();  
            }  
            e.printStackTrace();  
        } finally {  
            DBHelper.closeCon(stmt, conn,null);  
        }  
    	//readFileByLines(logName);
    	
    	
    }

}

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class DBHelper {
private static String driver ="com.mysql.jdbc.Driver";
private static String url="jdbc:mysql://localhost:3306/test";
private static String user="root";
private static String password="root";

static {
   try {
    Class.forName(driver);
   } catch (Exception e) {
    // TODO: handle exception
    e.printStackTrace();
   }
}
//获取数据连接
@SuppressWarnings("finally")
public static Connection getConn(){
   Connection con=null;
   try {
    con=(Connection) DriverManager.getConnection(url,user,password);
   } catch (Exception e) {
    // TODO: handle exception
    e.printStackTrace();
   }finally{
    return con;
   }
}
//关闭数据库连接
public static void closeCon(Statement st,Connection con,ResultSet rs){
   try {
    if(st!=null)
     st.close();
    if(con!=null)
    	con.close();
    if(rs!=null)
     rs.close();
   } catch (Exception e) {
    // TODO: handle exception
   }
}

public static void main(String[] args){
   System.out.println(getConn());
}

}