/**
* @author 王涵
* @version 创建时间：2017年10月9日 下午1:11:03
* 类说明
*/
package com.jl.datang.excel.table;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import com.jl.datang.excel.common.MyVariable1;
import blog.csdn.net.chart.AbnormalFlowEvent;
import blog.csdn.net.chart.GridAbnormalFlow;
/**
* @author 王涵
* @version 创建时间：2017年10月9日 下午1:11:03
* 类说明
*/
/**
* @author 王涵
*
*/
public class GridSql {
//TODO
/**
* 这个函数意义是，求出曲线在每一个点的变化率，变化率以时间点前后均值来计算，这样可以排除个别天异常波动情况，
* 对于过滤平稳曲线很有用，比如想求平稳的高流量小区，或者想求平稳的低流量小区。对于波动较大的情况还需再进行深入研究
*
* @author 王涵
* @version 创建时间：2017年9月14日 下午3:26:48
* @param begin
* @param end
* @return
* @throws Exception
*/
public static void createGridDesc(Date begin, Date end) throws Exception {
if ( ! begin.before(end) ) {
throw new Exception("输入日期不对，结束时间早于（或等于）开始时间");
}
// 计算两个时间的毫秒时间差异，再除以1000为秒，再除以3600为小时，再除以24为天
int interval = (int) ( ( end.getTime() - begin.getTime() ) / 1000 / 3600 / 24 );
if (interval < 6) {
throw new Exception("数据库中数据不足6天，或结束时间与起始时间相差不足6天，不足以计算均值");
}
// 数据库至少要有六天数据，对比平均值才有意义。6-5=1，表示6天做一次循环，对比一次
int cnt = interval - 5 ;
Calendar dateAdd = Calendar.getInstance();
dateAdd.setTime(begin);
dateAdd.add(Calendar.DATE, 3);
DateFormat kPIDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Connection connection = null;
Statement statement = null;
//ResultSet rs = null;
//String cellName = new String();
// 不要设为null，因为后续都是追加字符到这个对象，null会一直保留，不会别刷新掉
//List<String> names = new ArrayList<String>();
String dateCursor = "2017-08-01 00:00:00";
// load the mysql-JDBC driver using the current class loader
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
statement.executeUpdate("drop table if exists gridflowdesc_table");
statement.executeUpdate("create table if not exists gridflowdesc_table (grid_id int, avg1 float, avg2 float, flowratio float, avgcpe1 float, avgcpe2 float, cperatio float, begin datetime, primary key (grid_id, begin))");
//
for (int i = 0; i < cnt; i++) {
dateCursor = kPIDateFormat.format(dateAdd.getTime());
dateAdd.add(Calendar.DATE, 1);
statement.executeUpdate("drop view if exists gridfirsthalf_view");
/* 求平均用avg()函数看似合理，但avg函数只能对存在的数据求平均，
* 大唐数据库来自基站的数据有时候无法上报，
* 导致某天数据为空，干脆select不到这天那行数据，
* 如果用avg算则这些本该记录为0的数据不会被算到分母中，
* 因此avg得到的平均值比实际的平均值要高许多
* 这里必须用sum(flow/天数)的算法来算均值
* 将avg(flow) avg1 改为sum(flow/(i+3)) avg1;
* avg(flow) avg2 改为sum( flow/( interval-(i+3) ) ) avg2;
*/
statement.executeUpdate("create view gridfirsthalf_view as select grid_id, ( avg(dflow) + avg(uflow) ) avg1 from gridflow_table where date1 < '" + dateCursor + "' group by grid_id");
statement.executeUpdate("drop view if exists gridsecondhalf_view");
// interval-(i+3)是后半时段的天数
statement.executeUpdate("create view gridsecondhalf_view as select grid_id, ( avg(dflow) + avg(uflow) ) avg2 from gridflow_table where date1 >= '" + dateCursor + "' group by grid_id");
statement.executeUpdate("insert into gridflowdesc_table (grid_id, avg1, avg2, flowratio, begin) select a.grid_id grid_id, avg1, avg2, (avg1/avg2) flowratio, '" + dateCursor + "' begin from gridfirsthalf_view a, gridsecondhalf_view b where a.grid_id = b.grid_id");
}
/*
rs = statement.executeQuery("select name, max(flowratio) flowratio from enbflowdesc_table where avg1 > 5000 group by name order by flowratio desc");
while(rs.next()) {
cellName = rs.getString("name");
// read the result set cpe流量前后之比 "\t 日期 = \t" + rs.getString("begin") +
System.out.println( "前半段流量比后半段流量 = \t" + rs.getString("flowratio") + "\t小区名 = \t" + rs.getString("name") );
if ( ! names.contains(cellName) ) {
names.add(cellName);
}
}
*/
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
//if (rs != null)
//rs.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch(SQLException e) {
// connection close failed.
System.err.println(e);
}
}
//return names;
}
// TODO
// TODO
public static List<GridAbnormalFlow> selectGridDesc() {
Connection connection = null;
Statement statement = null;
Statement statement2 = null;
ResultSet rs = null;
ResultSet rs2 = null;
ArrayList<GridAbnormalFlow> returnNames = new ArrayList<GridAbnormalFlow>();
ArrayList<String> names = new ArrayList<String>();
SimpleDateFormat dformat1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
// 因为内嵌查询可能不按照order by的顺序排列，所以只能在最外层的select用order by
rs = statement.executeQuery("select distinct(a.grid_id), flowratio, (southeast_longitude + northwest_longitude)/2 Longitude, (northwest_latitude + southeast_latitude)/2 Latitude, x_inmap, y_inmap, date1, avg1 from (select grid_id, max(flowratio) flowratio, avg1, begin date1 from gridflowdesc_table where avg1 > 50000 group by grid_id) a inner join gridinfo_table b on a.grid_id = b.grid_id order by flowratio desc");
while ( rs.next() ) {
String name = rs.getString("grid_id");
float flow = rs.getFloat("flowratio");
double longitude = rs.getDouble("Longitude");
double latitude = rs.getDouble("Latitude");
int x = rs.getInt("x_inmap");
int y = rs.getInt("y_inmap");
Date d = rs.getDate("date1");
float avg1 = rs.getFloat("avg1");
rs2 = statement.executeQuery("select grid_id, max(dflow + uflow) flow from gridflow_table where date1 >= '" + dformat1.format(d) + "' and grid_id = " + name);
while ( rs2.next() ) {
float f = rs2.getFloat("flow");
if (f < avg1 * MyVariable1.descRate) {
if ( ! names.contains(name) ) {
names.add(name);
GridAbnormalFlow topEnbs = new GridAbnormalFlow(name, flow, longitude, latitude, x, y, d);
returnNames.add(topEnbs);
}
}
}
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (rs2 != null)
rs2.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch(SQLException e) {
// connection close failed.
System.err.println(e);
}
}
return returnNames ;
}
public static void main(String[] args) throws ParseException, Exception {
DateFormat kPIDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
createGridDesc(kPIDateFormat.parse("2017-07-10 00:00:00"), kPIDateFormat.parse("2017-09-04 00:00:00"));
}
}
/**
* @author 王涵
* @version 创建时间：2017年10月9日 下午1:11:03
* 类说明
*/
package com.jl.datang.excel.table;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import com.jl.datang.excel.common.MyVariable1;
import blog.csdn.net.chart.AbnormalFlowEvent;
import blog.csdn.net.chart.GridAbnormalFlow;
/**
* @author 王涵
* @version 创建时间：2017年10月9日 下午1:11:03
* 类说明
*/
/**
* @author 王涵
*
*/
public class GridSql {
//TODO
/**
* 这个函数意义是，求出曲线在每一个点的变化率，变化率以时间点前后均值来计算，这样可以排除个别天异常波动情况，
* 对于过滤平稳曲线很有用，比如想求平稳的高流量小区，或者想求平稳的低流量小区。对于波动较大的情况还需再进行深入研究
*
* @author 王涵
* @version 创建时间：2017年9月14日 下午3:26:48
* @param begin
* @param end
* @return
* @throws Exception
*/
public static void createGridDesc(Date begin, Date end) throws Exception {
if ( ! begin.before(end) ) {
throw new Exception("输入日期不对，结束时间早于（或等于）开始时间");
}
// 计算两个时间的毫秒时间差异，再除以1000为秒，再除以3600为小时，再除以24为天
int interval = (int) ( ( end.getTime() - begin.getTime() ) / 1000 / 3600 / 24 );
if (interval < 6) {
throw new Exception("数据库中数据不足6天，或结束时间与起始时间相差不足6天，不足以计算均值");
}
// 数据库至少要有六天数据，对比平均值才有意义。6-5=1，表示6天做一次循环，对比一次
int cnt = interval - 5 ;
Calendar dateAdd = Calendar.getInstance();
dateAdd.setTime(begin);
dateAdd.add(Calendar.DATE, 3);
DateFormat kPIDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Connection connection = null;
Statement statement = null;
//ResultSet rs = null;
//String cellName = new String();
// 不要设为null，因为后续都是追加字符到这个对象，null会一直保留，不会别刷新掉
//List<String> names = new ArrayList<String>();
String dateCursor = "2017-08-01 00:00:00";
// load the mysql-JDBC driver using the current class loader
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
statement.executeUpdate("drop table if exists gridflowdesc_table");
statement.executeUpdate("create table if not exists gridflowdesc_table (grid_id int, avg1 float, avg2 float, flowratio float, avgcpe1 float, avgcpe2 float, cperatio float, begin datetime, primary key (grid_id, begin))");
//
for (int i = 0; i < cnt; i++) {
dateCursor = kPIDateFormat.format(dateAdd.getTime());
dateAdd.add(Calendar.DATE, 1);
statement.executeUpdate("drop view if exists gridfirsthalf_view");
/* 求平均用avg()函数看似合理，但avg函数只能对存在的数据求平均，
* 大唐数据库来自基站的数据有时候无法上报，
* 导致某天数据为空，干脆select不到这天那行数据，
* 如果用avg算则这些本该记录为0的数据不会被算到分母中，
* 因此avg得到的平均值比实际的平均值要高许多
* 这里必须用sum(flow/天数)的算法来算均值
* 将avg(flow) avg1 改为sum(flow/(i+3)) avg1;
* avg(flow) avg2 改为sum( flow/( interval-(i+3) ) ) avg2;
*/
statement.executeUpdate("create view gridfirsthalf_view as select grid_id, ( avg(dflow) + avg(uflow) ) avg1 from gridflow_table where date1 < '" + dateCursor + "' group by grid_id");
statement.executeUpdate("drop view if exists gridsecondhalf_view");
// interval-(i+3)是后半时段的天数
statement.executeUpdate("create view gridsecondhalf_view as select grid_id, ( avg(dflow) + avg(uflow) ) avg2 from gridflow_table where date1 >= '" + dateCursor + "' group by grid_id");
statement.executeUpdate("insert into gridflowdesc_table (grid_id, avg1, avg2, flowratio, begin) select a.grid_id grid_id, avg1, avg2, (avg1/avg2) flowratio, '" + dateCursor + "' begin from gridfirsthalf_view a, gridsecondhalf_view b where a.grid_id = b.grid_id");
}
/*
rs = statement.executeQuery("select name, max(flowratio) flowratio from enbflowdesc_table where avg1 > 5000 group by name order by flowratio desc");
while(rs.next()) {
cellName = rs.getString("name");
// read the result set cpe流量前后之比 "\t 日期 = \t" + rs.getString("begin") +
System.out.println( "前半段流量比后半段流量 = \t" + rs.getString("flowratio") + "\t小区名 = \t" + rs.getString("name") );
if ( ! names.contains(cellName) ) {
names.add(cellName);
}
}
*/
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
//if (rs != null)
//rs.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch(SQLException e) {
// connection close failed.
System.err.println(e);
}
}
//return names;
}
// TODO
// TODO
public static List<GridAbnormalFlow> selectGridDesc() {
Connection connection = null;
Statement statement = null;
Statement statement2 = null;
ResultSet rs = null;
ResultSet rs2 = null;
ArrayList<GridAbnormalFlow> returnNames = new ArrayList<GridAbnormalFlow>();
ArrayList<String> names = new ArrayList<String>();
SimpleDateFormat dformat1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
// 因为内嵌查询可能不按照order by的顺序排列，所以只能在最外层的select用order by
rs = statement.executeQuery("select distinct(a.grid_id), flowratio, (southeast_longitude + northwest_longitude)/2 Longitude, (northwest_latitude + southeast_latitude)/2 Latitude, x_inmap, y_inmap, date1, avg1 from (select grid_id, max(flowratio) flowratio, avg1, begin date1 from gridflowdesc_table where avg1 > 50000 group by grid_id) a inner join gridinfo_table b on a.grid_id = b.grid_id order by flowratio desc");
while ( rs.next() ) {
String name = rs.getString("grid_id");
float flow = rs.getFloat("flowratio");
double longitude = rs.getDouble("Longitude");
double latitude = rs.getDouble("Latitude");
int x = rs.getInt("x_inmap");
int y = rs.getInt("y_inmap");
Date d = rs.getDate("date1");
float avg1 = rs.getFloat("avg1");
rs2 = statement.executeQuery("select grid_id, max(dflow + uflow) flow from gridflow_table where date1 >= '" + dformat1.format(d) + "' and grid_id = " + name);
while ( rs2.next() ) {
float f = rs2.getFloat("flow");
if (f < avg1 * MyVariable1.descRate) {
if ( ! names.contains(name) ) {
names.add(name);
GridAbnormalFlow topEnbs = new GridAbnormalFlow(name, flow, longitude, latitude, x, y, d);
returnNames.add(topEnbs);
}
}
}
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (rs2 != null)
rs2.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch(SQLException e) {
// connection close failed.
System.err.println(e);
}
}
return returnNames ;
}
public static void main(String[] args) throws ParseException, Exception {
DateFormat kPIDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
createGridDesc(kPIDateFormat.parse("2017-07-10 00:00:00"), kPIDateFormat.parse("2017-09-04 00:00:00"));
}
}
报错：
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6320)
at com.jl.datang.excel.table.GridSql.selectGridDesc(GridSql.java:218)
at com.jl.datang.excel.table.WriteExcel.writeDescFlowGrid(WriteExcel.java:338)
at com.jl.datang.excel.table.WriteExcel.main(WriteExcel.java:440)
原因：
第二个resultSet用了第一个resultSet的statement，结果把第一个resultSet冲掉了（自动关闭）。
135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
