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; }