Guest User

Untitled

a guest
May 25th, 2018
174
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 20.75 KB | None | 0 0
  1. import java.io.BufferReader;
  2. import java.io.File;
  3. import java.io.FileReader;
  4. import java.io.IOException;
  5. import java.sql.*;
  6. import java.util.*;
  7.  
  8. import org.json.simple.JSONArray;
  9. import org.json.simple.JSONObject;
  10. import org.json.simple.parser.JSONParser;
  11. import org.json.simple.parser.ParseException;
  12.  
  13.  
  14. public class Populate {
  15.  
  16. // Define the connection URL
  17. public static final String HOST = "localhost";
  18. public static final String DBNAME = "ORCL";
  19. public static final String PORT = "1521";
  20. public static final String DBURL = "jdbc:oracle:thin:@" + HOST + ":" + PORT + ":" + DBNAME;
  21. public static final String DBUSERNAME = "";
  22. public static final String DBPASSWORD = "";
  23.  
  24. // @param args: the command line arguments
  25. public static void main(String[] args) throws FileNotFoundException {
  26. // Load the driver
  27. try {
  28. Class.forName("oracle.jdbc.driver.OracleDriver");
  29. Class.forName("com.mysql.jdbc.Driver");
  30. }
  31. catch (Exception E) {
  32. System.out.println("Unable to load the driver.");
  33. E.printStackTrace();
  34. }
  35. // Remove previous data in tables
  36. removePrevData();
  37. // Get four JSON files put in command line
  38. if (args.length != 4) {
  39. System.out.println("Error: Four JSON files are required.")
  40. }
  41. else {
  42. parseBusinessJson(args[0])
  43. ParseUserJson(args[3])
  44. parseReviewJson(args[1])
  45. ParseCheckinJson(args[2])
  46. }
  47. }
  48.  
  49. // Remove previous data in tables
  50. public static void removePrevData() {
  51. try {
  52. Connection connection = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
  53. Statement deleteTableStatement = connection.createStatement();
  54.  
  55. // TO DO: delete from all tables
  56. deleteTableStatement.executeUpdate("DELETE FROM Business");
  57. deleteTableStatement.executeUpdate("DELETE FROM User");
  58. deleteTableStatement.executeUpdate("DELETE FROM ")
  59. }
  60. catch(SQLException e) {
  61. System.out.println("Error in removing previous data.")
  62. e.printStackTrace();
  63. }
  64. }
  65.  
  66. // Parse yelp_business.json file
  67. public static void parseBusinessJson(business_json) {
  68. System.out.println("Parsing yelp_business.json");
  69. try {
  70. File business_file = new File(business_json);
  71. FileReader business_fr = new FileReader(business_file);
  72. BufferReader business_bf = new BufferReader(business_fr);
  73.  
  74. JSONObject jsonObject = null;
  75. JSONParser parser = new JSONParser();
  76.  
  77. // preparedStatement
  78. Connection connection = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
  79. PreparedStatement businessInsertPreparedStatement = connection.preparedStatement("INSERT INTO Business("
  80. + "business_id, full_address, open, city, state, latitude, longitude, "
  81. + "review_count, business_name, stars, type) "
  82. + "VALUES (?,?,?,?,?,?,?,?,?,?,?)");
  83. PreparedStatement hoursInsertPreparedStatement = connection.preparedStatement("INSERT INTO Business_Hours("
  84. + "business_id, business_day, open_time, close_time) "
  85. + "VALUES (?,?,?,?)");
  86. PreparedStatement neighborhoodsPreparedStatement = connection.preparedStatement("INSERT INTO Business_Neighborhoods("
  87. + "business_id, neighborhoods) "
  88. + "VALUES (?,?)");
  89. PreparedStatement attributesPreparedStatement = connection.preparedStatement("INSERT INTO Business_Attributes("
  90. + "business_id, business_attributes) "
  91. + "VALUES (?,?)");
  92. PreparedStatement categoryPreparedStatement = connection.preparedStatement("INSERT INTO Business_Category("
  93. + "business_id, main_category, sub_category) "
  94. + "VALUES (?,?,?)");
  95.  
  96. // Read each line
  97. while (business_line = business_json.readLine() != null) {
  98. try {
  99. jsonObject = (JSONObject) parser.parse(business_line);
  100.  
  101. // Insert into Business Table
  102. String business_id = jsonObject.getString("business_id");
  103. String full_address = jsonObject.getString("full_address");
  104. String open = jsonObject.getBoolean("open").toString();
  105. String city = jsonObject.getString("city");
  106. String state = jsonObject.getString("state");
  107. Double latitude = jsonObject.getDouble("latitude");
  108. Double longitude = jsonObject.getDouble("longitude");
  109. Long review_count = jsonObject.getLong("review_count");
  110. String business_name = jsonObject.getString("review_count");
  111. Double stars = jsonObject.getDouble("stars");
  112. String type = jsonObject.getString("type");
  113.  
  114.  
  115. businessInsertPreparedStatement.setString(1, business_id);
  116. businessInsertPreparedStatement.setString(2, full_address);
  117. businessInsertPreparedStatement.setString(3, open);
  118. businessInsertPreparedStatement.setString(4, city);
  119. businessInsertPreparedStatement.setString(5, state);
  120. businessInsertPreparedStatement.setDouble(6, latitude);
  121. businessInsertPreparedStatement.setDouble(7, longitude);
  122. businessInsertPreparedStatement.setLong(8, review_count);
  123. businessInsertPreparedStatement.setString(9, business_name);
  124. businessInsertPreparedStatement.setDouble(10, stars);
  125. businessInsertPreparedStatement.setString(11, type);
  126. businessInsertPreparedStatement.executeUpdate();
  127.  
  128. // Insert into Business_Hours Table (business_id, business_day, open_time, close_time)
  129. // {"hours": {"Monday": {"close": "22:00", "open": "11:00"}, "Tuesday": {}, ...}}
  130. // hours_jo: {"Monday": {"close": "22:00", "open": "11:00"}, "Tuesday": {}, ...}
  131. JSONObject hours_jo = jsonObject.getJSONObject("hours");
  132. // close_open_time_jo: {"close": "22:00", "open": "11:00"}
  133. JSONObject close_open_time_jo;
  134. // days: ["Sunday", "Monday" ...]
  135. ArrayList<String> days = new ArrayList<String>();
  136. days.addAll(hours_jo.keySet());
  137.  
  138. for (int i = 0; i < hours_jo.size(); ++i) {
  139. // for a particular day
  140. day = days.get(i);
  141. close_open_time_jo = hours_jo.getJSONObject(day);
  142. String close_time = close_open_time_jo.getString("close");
  143. String open_time = close_open_time_jo.getString("open");
  144. DateFormat dateFormat = new SimpleDateFormat("hh:mm");
  145. Date close_date = dateFormat.parse(close_time);
  146. Timestamp close_timestamp = new Timestamp(close_date.getTime());
  147. Date open_date = dateFormat.parse(open_time);
  148. Timestamp open_timestamp = new Timestamp(open_date.getTime());
  149.  
  150. hoursInsertPreparedStatement.setString(1, business_id);
  151. hoursInsertPreparedStatement.setString(2, day);
  152. hoursInsertPreparedStatement.setTimestamp(3, open_timestamp);
  153. hoursInsertPreparedStatement.setTimestamp(4, close_timestamp);
  154. hoursInsertPreparedStatement.executeUpdate();
  155. }
  156.  
  157. // Insert into Business_Neighborhoods Table (business_id, neighborhoods)
  158. // {"neighborhoods": ["Mayfair Park"]}
  159. JSONArray neighborhoods_array = jsonObject.getJSONArray("neighborhoods");
  160. for (int i = 0; i < neighborhoods_array.size(); ++i) {
  161. String neighborhood = neighborhoods_array.get(i);
  162. neighborhoodsPreparedStatement.setString(1, business_id);
  163. neighborhoodsPreparedStatement.setString(2, neighborhood);
  164. neighborhoodsPreparedStatement.executeUpdate();
  165. }
  166.  
  167. // Insert into Business_Attributes Table (business_id, attributes)
  168. // attributes is the attribute that is true (including attributes and subattributes)
  169. // {"attributes": {"Take-out": true, "Good For": {"dessert": false, "breakfast": false}, ...}}
  170. JSONObject attributes_jo = jsonObject.getJSONObject("attributes");
  171. ArrayList<String> attribute_names = new ArrayList<String>();
  172. attribute_names.addAll(attributes_jo.keySet());
  173. for (int i = 0; i < attribute_names.size(); ++i) {
  174. String attribute_name = attribute_names.get(i);
  175. String value = attributes_jo.get(attribute_name).toString();
  176. if (value.equals("true")) {
  177. attributesPreparedStatement.setString(1, business_id);
  178. attributesPreparedStatement.setString(2, attribute_name);
  179. attributesPreparedStatement.executeUpdate();
  180. }
  181. else if(value[0].equals("{")) {
  182. JSONObject sub_attributes_jo = attributes_jo.getJSONObject(attribute_name);
  183. ArrayList<String> sub_attribute_names = new ArrayList<String>();
  184. sub_attribute_names.addAll(sub_attributes_jo.keySet());
  185. for (int i = 0; i < sub_attribute_names.size(); ++i) {
  186. String sub_attribute_name = sub_attribute_names.get(i);
  187. Boolean sub_value = sub_attributes_jo.getBoolean(sub_attribute_name);
  188. if (sub_value) {
  189. attributesPreparedStatement.setString(1, business_id);
  190. attributesPreparedStatement.setString(2, sub_attribute_name);
  191. attributesPreparedStatement.executeUpdate();
  192. }
  193. }
  194. }
  195. }
  196.  
  197. // Insert into Business_Category Table (business_id, main_category, sub_category)
  198. // {"categories": ["Mexican", "Restaurants"]} can have 0 or more than 1 main/sub categories
  199. String[] main_business_categories = {"Active Life", "Arts & Entertainment", "Automotive", "Car Rental", "Cafes", "Beauty & Spas", "Convenience Stores", "Dentists", "Doctors", "Drugstores", "Department Stores", "Education", "Event Planning & Services", "Flowers & Gifts", "Food", "Health & Medical", "Home Services", "Home & Garden", "Hospitals", "Hotels & Travel", "Hardware Stores", "Grocery", "Medical Centers", "Nurseries & Gardening", "Nightlife", "Restaurants", "Shopping", "Transportation"};
  200. ArrayList main_categories_list.addAll(main_business_categories);
  201.  
  202. JSONArray category_array = jsonObject.getJSONArray("categories");
  203. // For the current line, list of main categories and sub categories
  204. ArrayList<String> main_categories = new ArrayList<String>();
  205. ArrayList<String> sub_categories = new ArrayList<String>();
  206.  
  207. for (int i = 0; i < category_array.size(); ++i) {
  208. String category = category_array.get(i);
  209. if (main_categories_list.contains(category)) {
  210. main_categories.add(category);
  211. }
  212. else {
  213. sub_categories.add(category);
  214. }
  215. }
  216.  
  217. // No sub categories, only need to insert main_category
  218. if (sub_categories.size() == 0) {
  219. for (int m = 0; m < main_categories.size(); ++m) {
  220. categoryPreparedStatement.setString(1, business_id);
  221. categoryPreparedStatement.setString(2, main_categories.get(m));
  222. categoryPreparedStatement.executeUpdate();
  223. }
  224. }
  225. // Have sub categories
  226. else {
  227. for (int m = 0; m < main_categories.size(); ++m) {
  228. for (int s = 0; s < sub_categories.size(); ++s) {
  229. categoryPreparedStatement.setString(1, business_id);
  230. categoryPreparedStatement.setString(2, main_categories.get(m));
  231. categoryPreparedStatement.setString(3, sub_categories.get(s));
  232. categoryPreparedStatement.executeUpdate();
  233. }
  234. }
  235. }
  236. main_categories.clear();
  237. sub_categories.clear();
  238. }
  239. catch (ParseException pe) {
  240. System.out.println("Error in parsing business.");
  241. pe.printStackTrace();
  242. }
  243. } //end of while
  244. business_bf.close();
  245. connection.close();
  246. }
  247. catch (JSONException e) {
  248. e.printStackTrace();
  249. }
  250. catch (IOException e) {
  251. e.printStackTrace();
  252. }
  253. catch (SQLException e) {
  254. e.printStackTrace();
  255. }
  256. }
  257.  
  258.  
  259. // Parse yelp_user.json file
  260. public static void parseUserJson(user_json) {
  261. System.out.println("Parsing yelp_user.json");
  262. try {
  263. File user_file = new File(user_json);
  264. FileReader user_fr = new FileReader(user_file);
  265. BufferReader user_bf = new BufferReader(user_fr);
  266.  
  267. JSONObject jsonObject = null;
  268. JSONParser parser = new JSONParser();
  269.  
  270. // preparedStatement
  271. Connection connection = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
  272. PreparedStatement userInsertPreparedStatement = connection.preparedStatement("INSERT INTO User("
  273. + "yelping_since, useful_votes, funny_votes, cool_votes, review_count, user_name, user_id"
  274. + "fans, average_stars, type) "
  275. + "VALUES (?,?,?,?,?,?,?,?,?,?)");
  276.  
  277. // Read each line
  278. while (user_line = user_json.readLine() != null) {
  279. try {
  280. jsonObject = (JSONObject) parser.parse(user_line);
  281.  
  282. // Insert into Review Table
  283. // {"yelping_since": "2012-02"}
  284. String since_str = jsonObject.getString("yelping_since");
  285. DateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
  286. Date since_date = dateFormat.parse(since_str);
  287. Timestamp yelping_since = new Timestamp(since_date.getTime());
  288. // {"votes": {"funny": 0, "useful": 2, "cool": 1}}
  289. JSONObject votes = jsonObject.getJSONObject("votes");
  290. Long useful_votes = votes.getLong("useful");
  291. Long funny_votes = votes.getLong("funny");
  292. Long cool_votes = votes.getLong("cool");
  293. Long review_count = jsonObject.getLong("review_count");
  294. String user_name = jsonObject.getString("name");
  295. String user_id = jsonObject.getString("user_id");
  296. int fans = jsonObject.getInt("fans");
  297. Double average_stars = jsonObject.getDouble("average_stars");
  298. String type = jsonObject.getString("type");
  299.  
  300. reviewInsertPreparedStatement.setTimestamp(1, yelping_since);
  301. reviewInsertPreparedStatement.setLong(2, useful_votes);
  302. reviewInsertPreparedStatement.setLong(3, funny_votes);
  303. reviewInsertPreparedStatement.setLong(4, cool_votes);
  304. reviewInsertPreparedStatement.setLong(5, review_count);
  305. reviewInsertPreparedStatement.setString(6, user_name);
  306. reviewInsertPreparedStatement.setString(7, user_id);
  307. reviewInsertPreparedStatement.setInt(8, fans);
  308. reviewInsertPreparedStatement.setDouble(9, average_stars);
  309. reviewInsertPreparedStatement.setString(10, type);
  310. reviewInsertPreparedStatement.executeUpdate();
  311. }
  312. catch (ParseException pe) {
  313. System.out.println("Error in parsing user.")
  314. e.printStackTrace();
  315. }
  316. }
  317. user_bf.close();
  318. connection.close();
  319. }
  320. catch (JSONException e) {
  321. e.printStackTrace();
  322. }
  323. catch (IOException e) {
  324. e.printStackTrace();
  325. }
  326. catch (SQLException e) {
  327. e.printStackTrace();
  328. }
  329. }
  330.  
  331.  
  332. // Parse yelp_review.json file
  333. public static void parseReviewJson(review_json) {
  334. System.out.println("Parsing yelp_review.json");
  335. try {
  336. File review_file = new File(review_json);
  337. FileReader review_fr = new FileReader(review_file);
  338. BufferReader review_bf = new BufferReader(review_fr);
  339.  
  340. JSONObject jsonObject = null;
  341. JSONParser parser = new JSONParser();
  342.  
  343. // preparedStatement
  344. Connection connection = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
  345. PreparedStatement reviewInsertPreparedStatement = connection.preparedStatement("INSERT INTO Review("
  346. + "useful_votes, funny_votes, cool_votes, user_id, review_id, stars, "
  347. + "review_date, review_text, type, business_id) "
  348. + "VALUES (?,?,?,?,?,?,?,?,?,?)");
  349.  
  350. // Read each line
  351. while (review_line = review_json.readLine() != null) {
  352. try {
  353. jsonObject = (JSONObject) parser.parse(review_line);
  354.  
  355. // Insert into Review Table
  356. // {"votes": {"funny": 0, "useful": 2, "cool": 1}}
  357. JSONObject votes = jsonObject.getJSONObject("votes");
  358. Long useful_votes = votes.getLong("useful");
  359. Long funny_votes = votes.getLong("funny");
  360. Long cool_votes = votes.getLong("cool");
  361. String user_id = jsonObject.getString("user_id");
  362. String review_id = jsonObject.getString("review_id");
  363. int stars = jsonObject.getInt("stars");
  364. // {"date": "2012-01-08"}
  365. String review_date_str = jsonObject.getString("date");
  366. DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
  367. Date review_date_date = dateFormat.parse(review_date_str);
  368. Timestamp review_date = new Timestamp(review_date_date.getTime());
  369. String review_text = jsonObject.getString("text");
  370. String type = jsonObject.getString("type");
  371. String business_id = jsonObject.getString("business_id");
  372.  
  373. reviewInsertPreparedStatement.setLong(1, useful_votes);
  374. reviewInsertPreparedStatement.setLong(2, funny_votes);
  375. reviewInsertPreparedStatement.setLong(3, cool_votes);
  376. reviewInsertPreparedStatement.setString(4, user_id);
  377. reviewInsertPreparedStatement.setString(5, review_id);
  378. reviewInsertPreparedStatement.setInt(6, stars);
  379. reviewInsertPreparedStatement.setTimestamp(7, review_date);
  380. reviewInsertPreparedStatement.setString(8, review_text);
  381. reviewInsertPreparedStatement.setString(9, type);
  382. reviewInsertPreparedStatement.setString(10, business_id);
  383. reviewInsertPreparedStatement.executeUpdate();
  384. }
  385. catch (ParseException pe) {
  386. System.out.println("Error in parsing review.")
  387. e.printStackTrace();
  388. }
  389. }
  390. review_bf.close();
  391. connection.close();
  392. }
  393. catch (JSONException e) {
  394. e.printStackTrace();
  395. }
  396. catch (IOException e) {
  397. e.printStackTrace();
  398. }
  399. catch (SQLException e) {
  400. e.printStackTrace();
  401. }
  402. }
  403.  
  404.  
  405. // Parse yelp_checkin.json file
  406. public static void parseCheckinJson(checkin_json) {
  407. System.out.println("Parsing yelp_checkin.json");
  408. }
  409.  
  410. }
Add Comment
Please, Sign In to add comment