public static void main2(String[] args) {
AdjacentLocListGenerator generator = new AdjacentLocListGenerator();
Connection conn;
try {
conn = DataLoader.getBeijingConn();
AdjacentLocList list = generator.getListFromDB(conn);
conn.setAutoCommit(false);
PreparedStatement stmt =
conn.prepareStatement(
"INSERT INTO AdjacentLocation_Clustered"
+ "(SiteId1, SiteId2, UserCount, TotalCount)"
+ "VALUES (?, ?, ?, ?)");
for (Site loc : list.getSites().values()) {
for (AdjacentLocPair pair : loc.getNextSites()) {
int siteId1 = pair.getSite1().getSiteId();
int siteId2 = pair.getSite2().getSiteId();
if (pair.getUsersCount() == 0) continue;
stmt.setInt(1, siteId1);
stmt.setInt(2, siteId2);
stmt.setInt(3, pair.getUsersCount());
stmt.setInt(4, pair.getTotalCount());
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
System.out.println(loc.getSiteId() + " succeed.");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public AdjacentLocList getListFromDB(Connection conn) throws SQLException {
AdjacentLocList list = new AdjacentLocList();
Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT IMSI,SiteId, Longitude,Latitude,ConnectTime FROM FilteredByCT_Data2"
+ " ORDER BY IMSI ASC, ConnectTime ASC");
rs.next();
LocationRecord record = new LocationRecord(rs);
LocationRecord nextRecord;
HashMap<AdjacentLocPair, int[]> pairs = new HashMap<AdjacentLocPair, int[]>();
long count = 0;
while (rs.next()) {
if (count % 1000000 == 0) {
System.out.println(count);
}
count++;
nextRecord = new LocationRecord(rs);
if (nextRecord.getSiteId() == record.getSiteId()) continue;
if (!nextRecord.getImsi().equals(record.getImsi())) {
record = nextRecord;
storePairs(pairs);
pairs = new HashMap<AdjacentLocPair, int[]>();
continue;
}
Site site1 = list.getSite(record.getSiteId());
if (site1 == null) {
site1 = list.addSite(record.getSiteId(), record.getLongitude(), record.getLatitude());
}
Site site2 = list.getSite(nextRecord.getSiteId());
if (site2 == null) {
site2 =
list.addSite(
nextRecord.getSiteId(), nextRecord.getLongitude(), nextRecord.getLatitude());
}
int hour = record.getHour();
AdjacentLocPair pair = list.getAdjacentLocPair(site1, site2);
if (!pairs.containsKey(pair)) {
int[] arr = new int[24];
arr[hour] = 1;
pairs.put(pair, arr);
} else {
pairs.get(pair)[hour] += 1;
}
record = nextRecord;
}
storePairs(pairs);
return list;
}