java查看mysql日志_利用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

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

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 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 readFileByLines(String fileName) {

File file = new File(fileName);

BufferedReader reader = null;

List list=new ArrayList();

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 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 readFileByLines(String fileName) throws SQLException {

File file = new File(fileName);

BufferedReader reader = null;

List list=new ArrayList();

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());

}

}