import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.concurrent.*;

public class Test {

   private static final int THREADS = 500;
   private static final int ITERATIONS = 100;

   public static void main(String[] args) throws Exception {
      System.out.println("Starting H2 in-memory database tests...");

      System.out.println("\n--- Test 1: Without Connection Pool ---");
      testWithoutPool();

      System.out.println("\n--- Test 2: With C3P0 Connection Pool ---");
      testWithC3P0Pool();
   }

   private static void testWithoutPool() throws Exception {

      String url = "jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1";
      long start = System.currentTimeMillis();

      ExecutorService executor = Executors.newFixedThreadPool(THREADS);
      CountDownLatch latch = new CountDownLatch(THREADS * ITERATIONS);

      for (int it = 0; it < ITERATIONS; it++) {
         for (int t = 0; t < THREADS; t++) {

            executor.submit(() -> {
               try (Connection conn = DriverManager.getConnection(url);
                    PreparedStatement ps = conn.prepareStatement("SELECT 1")) {

                  ps.execute();

               } catch (Exception e) {
                  e.printStackTrace();
               } finally {
                  latch.countDown();
               }
            });

         }
      }

      latch.await();
      executor.shutdown();

      long duration = System.currentTimeMillis() - start;
      System.out.println("Test 1 (No Pool) completed in " + duration + " ms");
   }

   private static void testWithC3P0Pool() throws Exception {

      String url = "jdbc:h2:mem:test2;DB_CLOSE_DELAY=-1";
      long start = System.currentTimeMillis();

      ComboPooledDataSource cpds = new ComboPooledDataSource();
      cpds.setDriverClass("org.h2.Driver");
      cpds.setJdbcUrl(url);
      cpds.setUser("sa");
      cpds.setPassword("");

      cpds.setMaxPoolSize(THREADS);
      cpds.setMinPoolSize(2);
      cpds.setAcquireIncrement(2);
      cpds.setCheckoutTimeout(5000);

      ExecutorService executor = Executors.newFixedThreadPool(THREADS);
      CountDownLatch latch = new CountDownLatch(THREADS * ITERATIONS);

      for (int it = 0; it < ITERATIONS; it++) {
         for (int t = 0; t < THREADS; t++) {

            executor.submit(() -> {
               try (Connection conn = cpds.getConnection();
                    PreparedStatement ps = conn.prepareStatement("SELECT 1")) {

                  ps.execute();

               } catch (Exception e) {
                  e.printStackTrace();
               } finally {
                  latch.countDown();
               }
            });

         }
      }

      latch.await();
      executor.shutdown();
      cpds.close();

      long duration = System.currentTimeMillis() - start;
      System.out.println("Test 2 (C3P0 Pool) completed in " + duration + " ms");
   }
}