jdbc中想同时使用两个sql查询语句，必须创建两套statement和resultSet
来源：互联网 发布：建筑信息模型软件培训 编辑：程序博客网 时间：2024/05/17 06:12
/**
* @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// TODOpublic 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冲掉了（自动关闭）。
- jdbc中想同时使用两个sql查询语句，必须创建两套statement和resultSet
- JDBC的Statement 和 ResultSet
- JDBC中Connection,Statement,ResultSet的关系
- Java jdbc中Statement,ResultSet,PreparedStatement
- JDBC之使用Statement,PreparedStatement,ResultSet
- jdbc preparedStatement和statement，resultset等区分
- jdbc preparedStatement和statement，resultset等区分
- JDBC（二）Statement，PrepareStatement和ResultSet
- 在Eclipse中测试MySQL-JDBC（9）statement 批处理（同时执行多条sql语句）
- Java中使用Statement接口SQL语句
- 连接（Connection）、SQL语句（Statement）和结果集（ResultSet）
- 转个：JDBC中，如果ResultSet 和 Statement 不关闭的话，会有什么影响
- JDBC中Connection、Statement、ResultSet的关闭方法
- JDBC中Statement接口、ResultSet接口中的方法
- JDBC Connection Statement ResultSet DatabaseMetaData
- JDBC—Connection、Statement、ResultSet
- [JSP&JDBC]Statement/ResultSet/PreparedStatement
- JDBC : 使用 ResultSet 执行查询操作
- tar命令的详细解释
- C# IDisposable接口与using关键字
- touchGFX 初探
- 安卓简单登录与注册
- 全面解析JS中的this机制
- jdbc中想同时使用两个sql查询语句，必须创建两套statement和resultSet
- Ubuntu 10.10安装SSH
- 通信加密的一些理解
- 练习 18：日志：/var/log，rsyslog，logger
- unity & php上传文件
- Deep Learning Exercise 1-3
- 神奇的Timer
- 一个时代的终结微软高层认Windows手机业务不再是重点
- 基于force布局的map
