public void removeNullData() {
System.out.println("start remove null record...");
int len = tables.length;
System.out.println("table number:" + len);
for (int i = 0; i < len; i++) {
System.out.println("deal with " + tables[i] + " " + i + "/" + len + "....");
String sql = "delete from " + tables[i] + " where isnull(latitude) or isnull(longitude)";
conn.execute(sql);
}
System.out.println("remove null data over..");
}
public void removeEmptyData() {
System.out.println("start remove empty record...");
int len = tables.length;
System.out.println("table number:" + len);
for (int i = 0; i < len; i++) {
System.out.println("deal with " + tables[i] + " " + i + "/" + len + "....");
String sql =
"delete from "
+ tables[i]
+ " where latitude='.' or longitude='.' or latitude='' or longitude=''";
conn.execute(sql);
}
System.out.println("remove empty data over..");
}
public void createIndex() {
try {
System.out.println("start create index...");
int len = tables.length;
System.out.println("table number:" + len);
for (int i = 0; i < len; i++) {
System.out.println("deal with " + tables[i] + " " + i + "/" + len + "....");
String sql =
"alter table " + tables[i] + " add index IDX_" + tables[i] + "_date_time(date_time)";
conn.execute(sql);
}
System.out.println("create index over..");
} catch (Exception e) {
e.printStackTrace();
}
}
public void analyDataDis() {
try {
System.out.println("start analy record...");
int len = tables.length;
System.out.println("table number:" + len);
for (int i = 0; i < len; i++) {
System.out.println("deal with " + tables[i] + " " + i + "/" + len + "....");
String sql = "select animal, count(*) as num from " + tables[i] + " group by animal";
ResultSet rs = conn.executeQuery(sql);
while (rs.next()) {
String animal = rs.getString("animal");
int num = rs.getInt("num");
System.out.println(tables[i] + "\t" + animal + "\t" + num);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void mergeTable() {
try {
System.out.println("start mergeTable...");
System.out.println("delete old record...");
conn.execute("delete from gps_total");
int len = tables.length;
System.out.println("table number:" + len);
int idIndex = 0;
for (int i = 0; i < len; i++) {
System.out.println("deal with " + tables[i] + " " + i + "/" + len + "....");
String sql = "select* from " + tables[i];
ResultSet rs = conn.executeQuery(sql);
StringBuffer sb = new StringBuffer();
sb.append("insert into gps_total(id,lat,lon,animal,time,type)values");
int count = 0;
if (rs.next()) {
count++;
idIndex++;
sb.append(
"(" + idIndex + "," + rs.getDouble("latitude") + "," + rs.getDouble("longitude"));
sb.append(
",'"
+ rs.getString("animal")
+ "','"
+ rs.getString("date_time")
+ "','"
+ rs.getString("record_id")
+ "'");
sb.append(")");
}
while (rs.next()) {
count++;
idIndex++;
sb.append(
",(" + idIndex + "," + rs.getDouble("latitude") + "," + rs.getDouble("longitude"));
sb.append(
",'"
+ rs.getString("animal")
+ "','"
+ rs.getString("date_time")
+ "','"
+ rs.getString("record_id")
+ "'");
sb.append(")");
if (idIndex % 2000 == 0) {
conn.execute(sb.toString());
sb = new StringBuffer();
if (rs.next()) {
sb.append("insert into gps_total(id,lat,lon,animal,time,type)values");
count++;
idIndex++;
sb.append(
"(" + idIndex + "," + rs.getDouble("latitude") + "," + rs.getDouble("longitude"));
sb.append(
",'"
+ rs.getString("animal")
+ "','"
+ rs.getString("date_time")
+ "','"
+ rs.getString("record_id")
+ "'");
sb.append(")");
}
}
}
conn.execute(sb.toString());
}
} catch (Exception e) {
e.printStackTrace();
}
}