import static java.lang.System.out;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Random;

public class driver3 {

   // create database database1
   // CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
   // GRANT ALL ON *.* TO 'user1'@'localhost';

   public static String tablename = "table4";

   public static String log(String s) {
      try {
         return s;
      } finally {
         // out.println(s);
      }
   }

   // byte[] key byte ordering is BigInteger compatible
   public static void test(int keySpaceSize, final boolean index, final int numberOfBytes, final boolean asIndividualINTs, final int selectCount,
         final int insertCount) throws Throwable {
      if (numberOfBytes < 1) {
         throw new UnsupportedOperationException();
      }
      if (numberOfBytes % 4 != 0 && numberOfBytes > 0) {
         throw new UnsupportedOperationException();
      }
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/database1", "user1", "pass1");

      Statement stat = connection.createStatement();
      stat.execute(log("DROP TABLE IF EXISTS " + tablename));
      int numberOfIntCols = numberOfBytes / 4;
      StringBuilder b = new StringBuilder();
      StringBuilder b2 = new StringBuilder();
      StringBuilder b3 = new StringBuilder();
      for (int i = 0; i < numberOfIntCols; i++) {
         b.append("key" + i + " INTEGER, ");
         b2.append("key" + i + ", ");
         b3.append("?, ");
      }
      String s2 = b2.substring(0, b2.length() - ", ".length());
      String s3 = b3 + "?";
      if (asIndividualINTs) {
         stat.execute(log("CREATE TABLE IF NOT EXISTS " + tablename + " (" + b + "string1 VARCHAR(254)" + (index ? ", INDEX index1 (" + s2 + ")" : "") + ")"));
      } else {
         stat.execute(log("CREATE TABLE IF NOT EXISTS " + tablename + " (key1 BINARY(" + numberOfBytes + "), string1 VARCHAR(254)"
               + (index ? ", INDEX index1 (key1)" : "") + ")"));
      }
      connection.setAutoCommit(false);
      Random gen = new Random();
      ArrayList<byte[]> keys = new ArrayList<byte[]>();
      // Prevent out of memory;
int batchSize  = 10000;
      int batches = keySpaceSize / batchSize;
      keySpaceSize = keySpaceSize * batchSize;
      for (int ba = 0; ba < batches; ba++) {
         PreparedStatement preparedStatement;
         if (asIndividualINTs) {
            preparedStatement = connection.prepareStatement(log("INSERT INTO " + tablename + "(" + s2 + ", string1) values (" + s3 + ")"));
            for (int i = 0; i < batchSize; i++) {
               byte[] key = new byte[numberOfBytes];

               for (int temp, byteindex = 0, j = numberOfIntCols; j != 0; j--) {
                  preparedStatement.setInt(j, temp = gen.nextInt());

                  for (int k = 24; k != -8; k -= 8) {
                     key[byteindex++] = (byte) ((temp >>> k) & 0xFF);
                  }
               }
               preparedStatement.setString(numberOfIntCols + 1, "someemail@gmail.com");
               preparedStatement.addBatch();
               if (ba == 0) {
                  // last batch gather keys
                  keys.add(key);
               }
            }
         } else {
            preparedStatement = connection.prepareStatement(log("INSERT INTO " + tablename + "(key1, string1) values (?, ?)"));
            for (int i = 0; i < batchSize; i++) {
               byte[] key = new byte[numberOfBytes];

               for (int temp, byteindex = 0, j = numberOfIntCols; j != 0; j--) {
                  temp = gen.nextInt();
                  for (int k = 24; k != -8; k -= 8) {
                     key[byteindex++] = (byte) ((temp >>> k) & 0xFF);
                  }
               }
               preparedStatement.setBytes(1, key);
               preparedStatement.setString(2, "someemail@gmail.com");
               preparedStatement.addBatch();
               if (ba == 0) {
                  // last batch gather keys
                  keys.add(key);
               }
            }
         }
         preparedStatement.executeBatch();
         connection.commit();
         preparedStatement = null;
         System.gc();
      }
      int[] indexesToSelect = new int[selectCount];
      for (int i = 0; i < indexesToSelect.length; i++) {
         indexesToSelect[i] = gen.nextInt(keys.size());
      }
      long start = System.nanoTime();
      if (asIndividualINTs) {
         String query = "SELECT * FROM " + tablename + "  where ";
         for (int i = 0; i < numberOfIntCols; i++) {
            query += "key" + i + " = ? and ";
         }
         query = query.substring(0, query.length() - (" and ".length()));
         final int max = numberOfIntCols * 4 - 1;
         for (int i = 0; i < selectCount; i++) {
            byte[] key = keys.get(indexesToSelect[i]);
            PreparedStatement preparedStatement2 = connection.prepareStatement(query);
            for (int j = 1; j <= numberOfIntCols; j++) {
               int temp2 = (j - 1) * 4;
               preparedStatement2.setInt(j, ((((int) key[max - (temp2 + 3)]) & 0xFF) << 24) | (((int) (key[max - (temp2 + 2)]) & 0xFF) << 16)
                     | ((((int) (key[max - (temp2 + 1)]) & 0xFF) << 8)) | ((((int) key[max - (temp2)]) & 0xFF)));
            }
            ResultSet result = preparedStatement2.executeQuery();
            if (!result.next()) {
               out.println("Missing " + new BigInteger(key).toString(Character.MAX_RADIX));
               break;
            }

         }

      } else {
         String query = log("SELECT * FROM " + tablename + "  where key1 = ?");
         for (int i = 0; i < selectCount; i++) {
            byte[] key = keys.get(indexesToSelect[i]);
            PreparedStatement preparedStatement2 = connection.prepareStatement(query);
            preparedStatement2.setBytes(1, key);
            ResultSet result = preparedStatement2.executeQuery();
            if (!result.next()) {
               out.println("Missing " + new BigInteger(key).toString(Character.MAX_RADIX));
               break;
            }
         }
      }
      out.println((System.nanoTime() - start) + " ns " + ((System.nanoTime() - start) / 1000000) + " ms " + "to select " + selectCount + " in a keyspace of "
            + keySpaceSize + (index ? "" : " INDEXED") + (asIndividualINTs ? (numberOfIntCols + " INTS") : "BYTE(" + numberOfBytes + ")"));
      keys = null;
      {

         ArrayList<byte[]> keys2 = new ArrayList<byte[]>();
         for (int i = 0; i < insertCount; i++) {
            byte[] key = new byte[numberOfBytes];
            for (int temp, byteindex = 0, j = numberOfIntCols; j != 0; j--) {
               temp = gen.nextInt();
               for (int k = 24; k != -8; k -= 8) {
                  key[byteindex++] = (byte) ((temp >>> k) & 0xFF);
               }
            }
            keys2.add(key);
         }
         long start2 = System.nanoTime();
         if (asIndividualINTs) {
            final int max = numberOfIntCols * 4 - 1;
            for (byte[] key : keys2) {
               PreparedStatement preparedStatement2 = connection.prepareStatement("INSERT INTO  " + tablename + "(" + s2 + ", string1) values (" + s3 + ")");

               for (int j = 1; j <= numberOfIntCols; j++) {
                  int temp2 = (j - 1) * 4;
                  preparedStatement2.setInt(j, ((((int) key[max - (temp2 + 3)]) & 0xFF) << 24) | (((int) (key[max - (temp2 + 2)]) & 0xFF) << 16)
                        | ((((int) (key[max - (temp2 + 1)]) & 0xFF) << 8)) | ((((int) key[max - (temp2)]) & 0xFF)));
               }
               preparedStatement2.setString(numberOfIntCols + 1, "someemail@gmail.com");
               preparedStatement2.execute();

            }
         } else {
            for (byte[] key : keys2) {
               PreparedStatement preparedStatement2 = connection.prepareStatement("INSERT INTO  " + tablename + "(key1, string1) values (?, ?)");
               preparedStatement2.setBytes(1, key);
               preparedStatement2.setString(2, "");
               preparedStatement2.execute();
            }
         }
         out.println((System.nanoTime() - start) + " ns " + ((System.nanoTime() - start) / 1000000) + " ms " + "to Insert " + insertCount
               + " in a keyspace of " + keySpaceSize + (index ? "" : " INDEXED")
               + (asIndividualINTs ? (numberOfIntCols + " INTS") : "BYTE(" + numberOfBytes + ")"));
      }

   }

   public static void main(String[] args) {
      try {
          test(400000, true, 4, true, 1000, 1000);
          test(400000, true, 4, false, 1000, 1000);
          test(400000, false, 4, true, 1000, 1000);
          test(400000, false, 4, false, 1000, 1000);
          test(400000, true, 20, true, 1000, 1000);
          test(400000, true, 20, false, 1000, 1000);
          test(400000, false, 20, true, 1000, 1000);
          test(400000, false, 20, false, 1000, 1000);
          test(400000, true, 40, true, 1000, 1000);
          test(400000, true, 40, false, 1000, 1000);
          test(400000, false, 40, true, 1000, 1000);
          test(400000, false, 40, false, 1000, 1000);

//         test(40000000, true, 40, false, 1000, 1000);
//         test(40000000, true, 40, true, 1000, 1000);

      } catch (Throwable e1) {

         e1.printStackTrace();
      }
   }
}