import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Scanner;

public class BulkInsert {

	private static String URL = "jdbc:oracle:thin:USERNAME/PASSWORD@bd1.priv.di.fct.unl.pt:1521:orcl";
	private static String FILE = "data/usa_accidents_2001_2013.csv";
	private static String NULL = "null";
	
	private static int BATCH = 1000;


	private static Connection getConnection(final String url) {
		Connection connection = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(url);
		} catch (Exception exception) {
			exception.printStackTrace();
		}

		return connection;
	}


	public static void BulkSimpleInsert() {

		String templateInsert = "INSERT INTO ACCIDENTS values (%d, %s, %s, %s, %s, %s, %s, %s, %s)";
		int id = 1;
		Connection conn = getConnection(URL);

		File file = new File(FILE);

		try {
			Scanner scanner = new Scanner(file);
			scanner.nextLine(); //Skip headers

			Statement statement = conn.createStatement();
			long initBulk = System.currentTimeMillis();

			while (scanner.hasNextLine()) {

				String[] splitted = scanner.nextLine().split(";");

				String sqlInsert = String.format(templateInsert, id, splitted[47], splitted[2],splitted[3], splitted[4],
						splitted[5],splitted[53],splitted[54],splitted[41]);

				statement.execute(sqlInsert);
				id++;
			}

			long endBulk = System.currentTimeMillis();
			System.out.println("Time Elapsed Simple: " + (endBulk - initBulk) );

			statement.execute("delete from accidents" );
			statement.close();
			conn.close();
			scanner.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}	

	public static void BulkInsertOneBatch() {

		String templateInsert = "INSERT INTO ACCIDENTS values (%d, %s, %s, %s, %s, %s, %s, %s, %s)";
		int id = 1;
		Connection conn = getConnection(URL);

		File file = new File(FILE);

		try {
			Scanner scanner = new Scanner(file);
			scanner.nextLine(); //Skip headers

			Statement statement = conn.createStatement();
			long initBulk = System.currentTimeMillis();

			while (scanner.hasNextLine()) {

				String[] splitted = scanner.nextLine().split(";");

				String sqlInsert = String.format(templateInsert, id, splitted[47], splitted[2],splitted[3], splitted[4],
						splitted[5],splitted[53],splitted[54],splitted[41]);

				statement.addBatch(sqlInsert);
				id++;
				
			}

			statement.executeBatch();
			
			long endBulk = System.currentTimeMillis();
			System.out.println("Time Elapsed Statement One Batch: " + (endBulk - initBulk) );

			
			statement.execute("delete from accidents" );
			statement.close();
			conn.close();
			scanner.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}	

	public static void BulkInsertOnePrepBatch() {

		String templateInsert = "INSERT INTO ACCIDENTS values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		int id = 1;
		Connection conn = getConnection(URL);

		File file = new File(FILE);

		try {
			Scanner scanner = new Scanner(file);
			scanner.nextLine(); //Skip headers

			PreparedStatement ps = conn.prepareStatement(templateInsert);
			long initBulk = System.currentTimeMillis();

			while (scanner.hasNextLine()) {

				String[] splitted = scanner.nextLine().split(";");

				ps.setInt(1, id);
				
				if(splitted[53].equals(NULL) || splitted[54].equals(NULL)  )
					continue;
				
				ps.setDouble(2, Double.parseDouble(splitted[47]));
				ps.setDouble(3, Double.parseDouble(splitted[2]));
				ps.setDouble(4, Double.parseDouble(splitted[3]));
				ps.setDouble(5, Double.parseDouble(splitted[4]));
				ps.setDouble(6, Double.parseDouble(splitted[5]));
				ps.setDouble(7, Double.parseDouble(splitted[53]));
				ps.setDouble(8, Double.parseDouble(splitted[54]));
				ps.setDouble(9, Double.parseDouble(splitted[41]));

				ps.addBatch();
				id++;
			}

			ps.executeBatch();
			long endBulk = System.currentTimeMillis();
			System.out.println("Time Elapsed Prepared Statement One Batch: " + (endBulk - initBulk) );

			conn.createStatement().execute("delete from accidents" );
			
			ps.close();
			conn.close();
			scanner.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}	


	public static void BulkInsertPrepBatch() {

		String templateInsert = "INSERT INTO ACCIDENTS values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		int id = 1;
		int count = 0;
		Connection conn = getConnection(URL);

		File file = new File(FILE);

		try {
			Scanner scanner = new Scanner(file);
			scanner.nextLine(); //Skip headers

			PreparedStatement ps = conn.prepareStatement(templateInsert);
			long initBulk = System.currentTimeMillis();

			while (scanner.hasNextLine()) {

				String[] splitted = scanner.nextLine().split(";");

				if(splitted[53].equals(NULL) || splitted[54].equals(NULL)  )
					continue;
				
				ps.setInt(1, id);

				ps.setDouble(2, Double.parseDouble(splitted[47]));
				ps.setDouble(3, Double.parseDouble(splitted[2]));
				ps.setDouble(4, Double.parseDouble(splitted[3]));
				ps.setDouble(5, Double.parseDouble(splitted[4]));
				ps.setDouble(6, Double.parseDouble(splitted[5]));
				ps.setDouble(7, Double.parseDouble(splitted[53]));
				ps.setDouble(8, Double.parseDouble(splitted[54]));
				ps.setDouble(9, Double.parseDouble(splitted[41]));
				
				ps.addBatch();
				count ++;
				
				if(count % BATCH == 0) {
					ps.executeBatch();
					count=0;
				}

				id++;
			}

			ps.executeBatch();
			long endBulk = System.currentTimeMillis();
			System.out.println("Time Elapsed Prepared Batch: " + (endBulk - initBulk) );
			
			conn.createStatement().execute("delete from accidents" );
			ps.close();
			conn.close();
			scanner.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}	

	public static void main(String[] args) {

		BulkInsert.BulkSimpleInsert();  // Statement each time
		BulkInsert.BulkInsertOneBatch(); // Statement One Batch
		BulkInsert.BulkInsertOnePrepBatch(); //Prepared Statement One Batch
		BulkInsert.BulkInsertPrepBatch(); //Prepared Several Small Batchs
	}


}