API tools faq
paste
Guest User

Untitled

a guest
May 28th, 2018
94
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 21.94 KB | None | 0 0
raw download clone embed print report
  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 not false (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. // value can be "true" or "false" or "some string" or "{" ":} (an object)"
  177. if (value.equals("true")) {
  178. attributesPreparedStatement.setString(1, business_id);
  179. attributesPreparedStatement.setString(2, attribute_name);
  180. attributesPreparedStatement.executeUpdate();
  181. }
  182. else if (!(value.equals("true")) && !(value.equals("false")) && !(value[0].equals("{"))) {
  183. String combine_attr = attribute_name + "_" + value
  184. attributesPreparedStatement.setString(1, business_id);
  185. attributesPreparedStatement.setString(2, combine_attr);
  186. attributesPreparedStatement.executeUpdate();
  187. }
  188. else if(value[0].equals("{")) {
  189. JSONObject sub_attributes_jo = attributes_jo.getJSONObject(attribute_name);
  190. ArrayList<String> sub_attribute_names = new ArrayList<String>();
  191. sub_attribute_names.addAll(sub_attributes_jo.keySet());
  192. for (int i = 0; i < sub_attribute_names.size(); ++i) {
  193. String sub_attribute_name = sub_attribute_names.get(i);
  194. String sub_value = sub_attributes_jo.get(sub_attribute_name).toString();
  195. // sub_value can be "true" or "false" or "some string"
  196. if (sub_value.equals("true")) {
  197. String combine_attr = attribute_names + "_" + sub_attribute_name
  198. attributesPreparedStatement.setString(1, business_id);
  199. attributesPreparedStatement.setString(2, combine_attr);
  200. attributesPreparedStatement.executeUpdate();
  201. else if (!(sub_value.equals("false"))) {
  202. String combine_attr = attribute_names + "_" + sub_attribute_name + "_" + sub_value
  203. attributesPreparedStatement.setString(1, business_id);
  204. attributesPreparedStatement.setString(2, combine_attr);
  205. attributesPreparedStatement.executeUpdate();
  206. }
  207. }
  208. }
  209. }
  210.  
  211. // Insert into Business_Category Table (business_id, main_category, sub_category)
  212. // {"categories": ["Mexican", "Restaurants"]} can have 0 or more than 1 main/sub categories
  213. 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"};
  214. ArrayList main_categories_list.addAll(main_business_categories);
  215.  
  216. JSONArray category_array = jsonObject.getJSONArray("categories");
  217. // For the current line, list of main categories and sub categories
  218. ArrayList<String> main_categories = new ArrayList<String>();
  219. ArrayList<String> sub_categories = new ArrayList<String>();
  220.  
  221. for (int i = 0; i < category_array.size(); ++i) {
  222. String category = category_array.get(i);
  223. if (main_categories_list.contains(category)) {
  224. main_categories.add(category);
  225. }
  226. else {
  227. sub_categories.add(category);
  228. }
  229. }
  230.  
  231. // No sub categories, only need to insert main_category
  232. if (sub_categories.size() == 0) {
  233. for (int m = 0; m < main_categories.size(); ++m) {
  234. categoryPreparedStatement.setString(1, business_id);
  235. categoryPreparedStatement.setString(2, main_categories.get(m));
  236. categoryPreparedStatement.executeUpdate();
  237. }
  238. }
  239. // Have sub categories
  240. else {
  241. for (int m = 0; m < main_categories.size(); ++m) {
  242. for (int s = 0; s < sub_categories.size(); ++s) {
  243. categoryPreparedStatement.setString(1, business_id);
  244. categoryPreparedStatement.setString(2, main_categories.get(m));
  245. categoryPreparedStatement.setString(3, sub_categories.get(s));
  246. categoryPreparedStatement.executeUpdate();
  247. }
  248. }
  249. }
  250. main_categories.clear();
  251. sub_categories.clear();
  252. }
  253. catch (ParseException pe) {
  254. System.out.println("Error in parsing business.");
  255. pe.printStackTrace();
  256. }
  257. } //end of while
  258. business_bf.close();
  259. connection.close();
  260. }
  261. catch (JSONException e) {
  262. e.printStackTrace();
  263. }
  264. catch (IOException e) {
  265. e.printStackTrace();
  266. }
  267. catch (SQLException e) {
  268. e.printStackTrace();
  269. }
  270. }
  271.  
  272.  
  273. // Parse yelp_user.json file
  274. public static void parseUserJson(user_json) {
  275. System.out.println("Parsing yelp_user.json");
  276. try {
  277. File user_file = new File(user_json);
  278. FileReader user_fr = new FileReader(user_file);
  279. BufferReader user_bf = new BufferReader(user_fr);
  280.  
  281. JSONObject jsonObject = null;
  282. JSONParser parser = new JSONParser();
  283.  
  284. // preparedStatement
  285. Connection connection = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
  286. PreparedStatement userInsertPreparedStatement = connection.preparedStatement("INSERT INTO User("
  287. + "yelping_since, useful_votes, funny_votes, cool_votes, review_count, user_name, user_id"
  288. + "fans, average_stars, type) "
  289. + "VALUES (?,?,?,?,?,?,?,?,?,?)");
  290.  
  291. // Read each line
  292. while (user_line = user_json.readLine() != null) {
  293. try {
  294. jsonObject = (JSONObject) parser.parse(user_line);
  295.  
  296. // Insert into Review Table
  297. // {"yelping_since": "2012-02"}
  298. String since_str = jsonObject.getString("yelping_since");
  299. DateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
  300. Date since_date = dateFormat.parse(since_str);
  301. Timestamp yelping_since = new Timestamp(since_date.getTime());
  302. // {"votes": {"funny": 0, "useful": 2, "cool": 1}}
  303. JSONObject votes = jsonObject.getJSONObject("votes");
  304. Long useful_votes = votes.getLong("useful");
  305. Long funny_votes = votes.getLong("funny");
  306. Long cool_votes = votes.getLong("cool");
  307. Long review_count = jsonObject.getLong("review_count");
  308. String user_name = jsonObject.getString("name");
  309. String user_id = jsonObject.getString("user_id");
  310. int fans = jsonObject.getInt("fans");
  311. Double average_stars = jsonObject.getDouble("average_stars");
  312. String type = jsonObject.getString("type");
  313.  
  314. reviewInsertPreparedStatement.setTimestamp(1, yelping_since);
  315. reviewInsertPreparedStatement.setLong(2, useful_votes);
  316. reviewInsertPreparedStatement.setLong(3, funny_votes);
  317. reviewInsertPreparedStatement.setLong(4, cool_votes);
  318. reviewInsertPreparedStatement.setLong(5, review_count);
  319. reviewInsertPreparedStatement.setString(6, user_name);
  320. reviewInsertPreparedStatement.setString(7, user_id);
  321. reviewInsertPreparedStatement.setInt(8, fans);
  322. reviewInsertPreparedStatement.setDouble(9, average_stars);
  323. reviewInsertPreparedStatement.setString(10, type);
  324. reviewInsertPreparedStatement.executeUpdate();
  325. }
  326. catch (ParseException pe) {
  327. System.out.println("Error in parsing user.")
  328. e.printStackTrace();
  329. }
  330. }
  331. user_bf.close();
  332. connection.close();
  333. }
  334. catch (JSONException e) {
  335. e.printStackTrace();
  336. }
  337. catch (IOException e) {
  338. e.printStackTrace();
  339. }
  340. catch (SQLException e) {
  341. e.printStackTrace();
  342. }
  343. }
  344.  
  345.  
  346. // Parse yelp_review.json file
  347. public static void parseReviewJson(review_json) {
  348. System.out.println("Parsing yelp_review.json");
  349. try {
  350. File review_file = new File(review_json);
  351. FileReader review_fr = new FileReader(review_file);
  352. BufferReader review_bf = new BufferReader(review_fr);
  353.  
  354. JSONObject jsonObject = null;
  355. JSONParser parser = new JSONParser();
  356.  
  357. // preparedStatement
  358. Connection connection = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
  359. PreparedStatement reviewInsertPreparedStatement = connection.preparedStatement("INSERT INTO Review("
  360. + "useful_votes, funny_votes, cool_votes, user_id, review_id, stars, "
  361. + "review_date, review_text, type, business_id) "
  362. + "VALUES (?,?,?,?,?,?,?,?,?,?)");
  363.  
  364. // Read each line
  365. while (review_line = review_json.readLine() != null) {
  366. try {
  367. jsonObject = (JSONObject) parser.parse(review_line);
  368.  
  369. // Insert into Review Table
  370. // {"votes": {"funny": 0, "useful": 2, "cool": 1}}
  371. JSONObject votes = jsonObject.getJSONObject("votes");
  372. Long useful_votes = votes.getLong("useful");
  373. Long funny_votes = votes.getLong("funny");
  374. Long cool_votes = votes.getLong("cool");
  375. String user_id = jsonObject.getString("user_id");
  376. String review_id = jsonObject.getString("review_id");
  377. int stars = jsonObject.getInt("stars");
  378. // {"date": "2012-01-08"}
  379. String review_date_str = jsonObject.getString("date");
  380. DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
  381. Date review_date_date = dateFormat.parse(review_date_str);
  382. Timestamp review_date = new Timestamp(review_date_date.getTime());
  383. String review_text = jsonObject.getString("text");
  384. String type = jsonObject.getString("type");
  385. String business_id = jsonObject.getString("business_id");
  386.  
  387. reviewInsertPreparedStatement.setLong(1, useful_votes);
  388. reviewInsertPreparedStatement.setLong(2, funny_votes);
  389. reviewInsertPreparedStatement.setLong(3, cool_votes);
  390. reviewInsertPreparedStatement.setString(4, user_id);
  391. reviewInsertPreparedStatement.setString(5, review_id);
  392. reviewInsertPreparedStatement.setInt(6, stars);
  393. reviewInsertPreparedStatement.setTimestamp(7, review_date);
  394. reviewInsertPreparedStatement.setString(8, review_text);
  395. reviewInsertPreparedStatement.setString(9, type);
  396. reviewInsertPreparedStatement.setString(10, business_id);
  397. reviewInsertPreparedStatement.executeUpdate();
  398. }
  399. catch (ParseException pe) {
  400. System.out.println("Error in parsing review.")
  401. e.printStackTrace();
  402. }
  403. }
  404. review_bf.close();
  405. connection.close();
  406. }
  407. catch (JSONException e) {
  408. e.printStackTrace();
  409. }
  410. catch (IOException e) {
  411. e.printStackTrace();
  412. }
  413. catch (SQLException e) {
  414. e.printStackTrace();
  415. }
  416. }
  417.  
  418.  
  419. // Parse yelp_checkin.json file
  420. public static void parseCheckinJson(checkin_json) {
  421. System.out.println("Parsing yelp_checkin.json");
  422. }
  423.  
  424. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!