FWD » Core Development » Database

import java.sql.*;

import java.util.Random;

import java.util.concurrent.CountDownLatch;

import java.util.concurrent.ExecutorService;

import java.util.concurrent.Executors;

public class Test {

   private static final String URL = "jdbc:h2:mem:testdb;db_close_delay=-1;mv_store=true;rtrim=true;default_ignore_case=true;LOCK_TIMEOUT=1000";

   public static void main(String[] args) throws Exception {

      // Step 1: Create table

      try (Connection conn = DriverManager.getConnection(URL, "sa", "")) {

         conn.createStatement().execute(

                 "CREATE TABLE meta_connect (" +

                 "id BIGINT PRIMARY KEY, " +

                 "connect_usr INT, " +

                 "connect_name VARCHAR(255))"

         );

         System.out.println("Table created.");

      }

      int threadCount = 20000;

      Random random = new Random();

      ExecutorService executor = Executors.newFixedThreadPool(threadCount);

      CountDownLatch latch = new CountDownLatch(threadCount);

      for (int i = 0; i < threadCount; i++) {

         final int threadId = i;

         executor.submit(() -> {

            Connection conn = null;

            try {

               conn = DriverManager.getConnection(URL, "sa", "");

               conn.setAutoCommit(false);

               long newId = System.nanoTime();

               int connectUsr = 42; // same user for concurrency

               String name = "User-" + threadId;

               if (random.nextBoolean()) {

                  // 50% chance: MERGE (insert or update existing id)

                  PreparedStatement merge = conn.prepareStatement(

                          "MERGE INTO meta_connect (id, connect_usr, connect_name) KEY(id) VALUES (?, ?, ?)"

                  );

                  merge.setLong(1, newId); // new row

                  merge.setInt(2, connectUsr);

                  merge.setString(3, name);

                  merge.executeUpdate();

               } else {

                  // 50% chance: update an existing row if any

                  PreparedStatement select = conn.prepareStatement(

                          "SELECT id FROM meta_connect WHERE connect_usr = ? LIMIT 1"

                  );

                  select.setInt(1, connectUsr);

                  ResultSet rs = select.executeQuery();

                  if (rs.next()) {

                     long existingId = rs.getLong(1);

                     PreparedStatement update = conn.prepareStatement(

                             "UPDATE meta_connect SET connect_name = ? WHERE id = ?"

                     );

                     update.setString(1, name + "-upd");

                     update.setLong(2, existingId);

                     update.executeUpdate();

                  } else {

                     // fallback: insert new row

                     PreparedStatement insert = conn.prepareStatement(

                             "INSERT INTO meta_connect (id, connect_usr, connect_name) VALUES (?, ?, ?)"

                     );

                     insert.setLong(1, newId);

                     insert.setInt(2, connectUsr);

                     insert.setString(3, name);

                     insert.executeUpdate();

                  }

               }

               conn.commit();

            } catch (Exception e) {

               System.err.println("Thread " + threadId + " FAILED: " + e.getMessage());

               try {

                  if (conn != null) conn.rollback();

               } catch (Exception ignored) {}

            } finally {

               latch.countDown();

               try {

                  if (conn != null) conn.close();

               } catch (Exception ignored) {}

            }

         });

      }

      latch.await();

      executor.shutdown();

      System.out.println("\nAll threads finished.\n");

      // Step 3: Verify result

      try (Connection conn = DriverManager.getConnection(URL, "sa", "")) {

         ResultSet rs1 = conn.createStatement()

                             .executeQuery("SELECT COUNT(*) FROM meta_connect");

         rs1.next();

         int totalRows = rs1.getInt(1);

         System.out.println("Total rows: " + totalRows);

      }

   }

}