Advertisement
Guest User

SQL

a guest
Feb 16th, 2017
92
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.09 KB | None | 0 0
  1. import java.io.*;
  2. import java.sql.Connection;
  3.  
  4. import java.sql.DriverManager;
  5. import java.sql.PreparedStatement;
  6. import java.sql.SQLException;
  7.  
  8. public class Main {
  9.  
  10. static Connection conn;
  11. static PreparedStatement sql;
  12.  
  13. public static void main(String[] args){
  14. conn = getConnection();
  15. query("DROP DATABASE IF EXISTS AdAgency");
  16. query("CREATE DATABASE IF NOT EXISTS AdAgency");
  17. query("USE AdAgency");
  18. query("CREATE TABLE Click(clickdate DATETIME, id BIGINT, cost DOUBLE)");
  19. query("CREATE TABLE Impression(impdate DATETIME, id BIGINT, gender varchar(6), age varchar(20), income ENUM('Low', 'Medium', 'High'), category varchar(20), impressioncost DOUBLE);");
  20. query("CREATE TABLE Server(entrydate DATETIME, id BIGINT, exitdate DATE, pagesvisit INT, conversion BOOLEAN)");
  21.  
  22. long start = System.currentTimeMillis();
  23. loadData("Click", new File("logfiles/click_log.csv"));
  24. loadData("Impression", new File("logfiles/impression_log.csv"));
  25. loadData("Server", new File("logfiles/server_log.csv"));
  26. System.out.println((System.currentTimeMillis() - start) / 1000);
  27.  
  28. }
  29.  
  30. private static void loadData(String table, File file){
  31.  
  32. BufferedReader br = null;
  33. String line = "";
  34. String clickdate, impdate, entrydate, exitdate, category, gender, age, income, initialQuery;
  35. int pagesvisit;
  36. long id;
  37. boolean conversion;
  38. double cost, impressioncost;
  39. String[] split;
  40. String query = "";
  41. int insertNum = 0;
  42.  
  43. try {
  44. br = new BufferedReader(new FileReader(file));
  45. } catch (FileNotFoundException e) {
  46. e.printStackTrace();
  47. }
  48.  
  49. //read first line of file - i.e. field headers
  50. try {
  51. br.readLine();
  52. } catch (IOException e) {
  53. e.printStackTrace();
  54. }
  55.  
  56. if(table.equals("Click")){
  57. query = "INSERT INTO " + table + " (clickdate, id, cost) VALUES ";
  58. }else if(table.equals("Impression")){
  59. query = "INSERT INTO " + table + " (impdate, id, gender, age, income, category, impressioncost) VALUES ";
  60. }else if(table.equals("Server")){
  61. query = "INSERT INTO " + table + " (entrydate, id, exitdate, pagesvisit, conversion) VALUES ";
  62. }
  63.  
  64. initialQuery = query;
  65. int multipleInserts = 0;
  66.  
  67. try {
  68. while((line = br.readLine()) != null){
  69. split = line.split(",");
  70. if(table.equals("Click")){
  71. clickdate = split[0];
  72. id = Long.parseLong(split[1]);
  73. cost = Double.parseDouble(split[2]);
  74. query = query + "('" + clickdate + "', " + id + ", " + cost + "),";
  75. }else if(table.equals("Impression")){
  76. impdate = split[0];
  77. id = Long.parseLong(split[1]);
  78. gender = split[2];
  79. age = split[3];
  80. income = split[4];
  81. category = split[5];
  82. impressioncost = Double.parseDouble(split[6]);
  83. query = query + "('" + impdate + "', " + id + ", '" + gender + "', '" + age + "', '" + income + "', '" + category + "', " + impressioncost + "),";
  84. }else if(table.equals("Server")){
  85. entrydate = split[0];
  86. id = Long.parseLong(split[1]);
  87. exitdate = split[2];
  88. if(exitdate.equals("n/a")) exitdate = "0000-00-00 00:00:00";
  89. pagesvisit = Integer.parseInt(split[3]);
  90. conversion = split[4].equals("Yes");
  91. query = query + "('" + entrydate + "', " + id + ", '" + exitdate + "', " + pagesvisit + ", " + conversion + "),";
  92. }
  93.  
  94. multipleInserts++;
  95. //inserts to database at 200 rows at a time
  96. if(multipleInserts == 200){
  97. multipleInserts = 0;
  98. query(query.substring(0, query.length()-1));
  99. insertNum++;
  100. //System.out.println(insertNum + " of " + 10000000 / 200);
  101. query = initialQuery;
  102. }
  103. }
  104. query(query.substring(0, query.length()-1));
  105. } catch (IOException e) {
  106. e.printStackTrace();
  107. }
  108. }
  109.  
  110. private static void query(String query){
  111. try {
  112. sql = conn.prepareStatement(query);
  113. sql.executeUpdate();
  114. } catch (SQLException e) {
  115. System.out.println(query);
  116. e.printStackTrace();
  117. }
  118. }
  119.  
  120. private static Connection getConnection(){
  121. String url = "jdbc:mysql://localhost:3306";
  122. String user = "root";
  123. String password = "";
  124.  
  125. try {
  126. conn = DriverManager.getConnection(url, user, password);
  127. return conn;
  128. } catch (SQLException e) {
  129. e.printStackTrace();
  130. }
  131.  
  132. return null;
  133.  
  134. }
  135. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement