Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.io.*;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.SQLException;
- public class Main {
- static Connection conn;
- static PreparedStatement sql;
- public static void main(String[] args){
- conn = getConnection();
- query("DROP DATABASE IF EXISTS AdAgency");
- query("CREATE DATABASE IF NOT EXISTS AdAgency");
- query("USE AdAgency");
- query("CREATE TABLE Click(clickdate DATETIME, id BIGINT, cost DOUBLE)");
- query("CREATE TABLE Impression(impdate DATETIME, id BIGINT, gender varchar(6), age varchar(20), income ENUM('Low', 'Medium', 'High'), category varchar(20), impressioncost DOUBLE);");
- query("CREATE TABLE Server(entrydate DATETIME, id BIGINT, exitdate DATE, pagesvisit INT, conversion BOOLEAN)");
- long start = System.currentTimeMillis();
- loadData("Click", new File("logfiles/click_log.csv"));
- loadData("Impression", new File("logfiles/impression_log.csv"));
- loadData("Server", new File("logfiles/server_log.csv"));
- System.out.println((System.currentTimeMillis() - start) / 1000);
- }
- private static void loadData(String table, File file){
- BufferedReader br = null;
- String line = "";
- String clickdate, impdate, entrydate, exitdate, category, gender, age, income, initialQuery;
- int pagesvisit;
- long id;
- boolean conversion;
- double cost, impressioncost;
- String[] split;
- String query = "";
- int insertNum = 0;
- try {
- br = new BufferedReader(new FileReader(file));
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- }
- //read first line of file - i.e. field headers
- try {
- br.readLine();
- } catch (IOException e) {
- e.printStackTrace();
- }
- if(table.equals("Click")){
- query = "INSERT INTO " + table + " (clickdate, id, cost) VALUES ";
- }else if(table.equals("Impression")){
- query = "INSERT INTO " + table + " (impdate, id, gender, age, income, category, impressioncost) VALUES ";
- }else if(table.equals("Server")){
- query = "INSERT INTO " + table + " (entrydate, id, exitdate, pagesvisit, conversion) VALUES ";
- }
- initialQuery = query;
- int multipleInserts = 0;
- try {
- while((line = br.readLine()) != null){
- split = line.split(",");
- if(table.equals("Click")){
- clickdate = split[0];
- id = Long.parseLong(split[1]);
- cost = Double.parseDouble(split[2]);
- query = query + "('" + clickdate + "', " + id + ", " + cost + "),";
- }else if(table.equals("Impression")){
- impdate = split[0];
- id = Long.parseLong(split[1]);
- gender = split[2];
- age = split[3];
- income = split[4];
- category = split[5];
- impressioncost = Double.parseDouble(split[6]);
- query = query + "('" + impdate + "', " + id + ", '" + gender + "', '" + age + "', '" + income + "', '" + category + "', " + impressioncost + "),";
- }else if(table.equals("Server")){
- entrydate = split[0];
- id = Long.parseLong(split[1]);
- exitdate = split[2];
- if(exitdate.equals("n/a")) exitdate = "0000-00-00 00:00:00";
- pagesvisit = Integer.parseInt(split[3]);
- conversion = split[4].equals("Yes");
- query = query + "('" + entrydate + "', " + id + ", '" + exitdate + "', " + pagesvisit + ", " + conversion + "),";
- }
- multipleInserts++;
- //inserts to database at 200 rows at a time
- if(multipleInserts == 200){
- multipleInserts = 0;
- query(query.substring(0, query.length()-1));
- insertNum++;
- //System.out.println(insertNum + " of " + 10000000 / 200);
- query = initialQuery;
- }
- }
- query(query.substring(0, query.length()-1));
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- private static void query(String query){
- try {
- sql = conn.prepareStatement(query);
- sql.executeUpdate();
- } catch (SQLException e) {
- System.out.println(query);
- e.printStackTrace();
- }
- }
- private static Connection getConnection(){
- String url = "jdbc:mysql://localhost:3306";
- String user = "root";
- String password = "";
- try {
- conn = DriverManager.getConnection(url, user, password);
- return conn;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return null;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement