适用于数据量比较大,对数据按年度或者月度纵向分表存储的情况。在查询数据的时候,通常存在跨表查询的情况。
/**
* 根据起始时间查询所有涉及的表
* 如果没有起始时间,则统计所有的air_data_****表,并返回
* @param startedDate
* @param endDate
* @return
*/
private List<String> getTables(String startedDate,String endDate){
String pre = "gk_device_data_" ;
//查询指定数据库中所有的表
String sql = "SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '"+ PropertiesUtil.getConnectedDatabaseName() +"'" ;
List<Object> oList = excuteQuery(sql);
List<String> tlist = new ArrayList<String>();
for (Object obj : oList) {
String tableName = (String) obj ;
if (tableName.contains(pre)) {
tlist.add(tableName);
}
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
List<String> list = new ArrayList<String>();
try {
Date d1 = sdf.parse(startedDate);
Date d2 = sdf.parse(endDate);
Calendar cal1 = Calendar.getInstance();
Calendar cal2 = Calendar.getInstance();
cal1.setTime(d1);
cal1.set(Calendar.DAY_OF_MONTH, 1);
cal2.setTime(d2);
cal2.set(Calendar.DAY_OF_MONTH, 2);
while (cal1.before(cal2)) {
int m = cal1.get(Calendar.MONTH)+1;
String tableName = pre + cal1.get(Calendar.YEAR) + "" + ((m< 10)?("0"+m):m);
list.add(tableName);
cal1.set(Calendar.MONTH, m);
}
} catch (ParseException e) {
e.printStackTrace();
}
return list ;
}