Thursday, September 17, 2015

Java Test Harness to Generate Random Inserts/Upserts/Deletes

/*
As part of GoldenGate 12c bi-directional replication testing, used this to generate random insert/updates/deletes against a test schema on both nodes to see how GG handles various scenarios such as shutting down one site and how it would recover transactions.
*/
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class trxnTest
{

   private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
   private static final String DB_CONNECTION = "jdbc:oracle:thin:@rac01:1521:db01";
   private static final String DB_USER = "oggsrc";
   private static final String DB_PASSWORD = "Password1";

   public static void main(String[] argv) throws SQLException
   {

      int eID = 0;
      int rN = 0;
      int ggbyGDPYear = 0;
      int gbyGDPYear = 0;
      int gdpValue = 0;
      int sleepTime = 0;

      System.out.println("Transaction Test Harness");

      Scanner sc = new Scanner(System.in);
      System.out.print("Enter sleep time between transactions in ms: ");
      sleepTime = Integer.parseInt((sc.next()));

      System.out.println("Sleep time = " + sleepTime);
      // Get a database connection
      Connection dbConn = getDBConnection();

      // Get max(GDP_YEAR) from GDP_BY_YEAR and GDP_GROWTH_BY_YEAR
      ggbyGDPYear = selectMaxGDPYear(dbConn, "GDP_GROWTH_BY_YEAR");
      gbyGDPYear = selectMaxGDPYear(dbConn, "GDP_BY_YEAR");

      // Start the transactions
      do
      //while(true)
      {
         try
         {
            Thread.sleep(sleepTime);
            // Get a random ENTITY_ID
            eID = (int)(Math.random() * (238 - 1)) + 1;
            gdpValue = (int)(Math.random() * (1000));
            ggbyGDPYear++;
            gbyGDPYear++;
            System.out.println(eID);

            // Do an INSERT
            insertRecord(dbConn, "GDP_BY_YEAR", eID, gbyGDPYear, gdpValue);
            insertRecord(dbConn, "GDP_GROWTH_BY_YEAR", eID, ggbyGDPYear, gdpValue);
            // Always do an INSERT followed by either an UPDATE or DELETE
            // Randomize our I/U/D
            rN = (int)(Math.random() * (3 - 1)) + 1;
            System.out.println("Random Number = " + rN);
            if (rN == 2)
            {
               // Update
               updateRecord(dbConn, "GDP_BY_YEAR", eID, gbyGDPYear, gdpValue);
               updateRecord(dbConn, "GDP_GROWTH_BY_YEAR", eID, ggbyGDPYear, gdpValue);
            }
            else
            {
              // Delete
               deleteRecord(dbConn, "GDP_BY_YEAR", eID, gbyGDPYear, gdpValue);
               deleteRecord(dbConn, "GDP_GROWTH_BY_YEAR", eID, ggbyGDPYear, gdpValue);
            }
         // Close connection otherwise we hit ORA-01000: max open cursors
         dbConn.close();
         // Get a database connection
         dbConn = getDBConnection();
         }
         catch (InterruptedException e)
         {
            e.printStackTrace();
         }
      } while (true); // for testing, use this ->  while (eID != 237);

      // Close the connection
      // dbConn.close();
   }


   private static Connection getDBConnection()
   {
      Connection dbConnection = null;

      try
      {
          Class.forName(DB_DRIVER);
      }
      catch (ClassNotFoundException e)
      {
         e.printStackTrace();
      }

      try
      {
         dbConnection = DriverManager.getConnection(
            DB_CONNECTION, DB_USER, DB_PASSWORD);
         return dbConnection;
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }

      return dbConnection;
   }

   // Select
   private static int selectMaxGDPYear(Connection dbConn, String tableName) throws SQLException
   {
      int maxGDPYear = 0;
      PreparedStatement pStmt = null;
      String sSQL = "select max(GDP_YEAR) from " + tableName;
      try
      {
         pStmt = dbConn.prepareStatement(sSQL);
         ResultSet rs = pStmt.executeQuery();
         if (rs.next())
         {
            maxGDPYear = rs.getInt(1);
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }

      return maxGDPYear;
   }

   // Update
   private static void updateRecord(Connection dbConn, String tableName, int eID, int gdpYear, double gdpValue) throws SQLException
   {
      PreparedStatement pStmt = null;
      String uSQL = "update " + tableName
                  + " set GDP_VALUE = ?"
                  + " where ENTITY_ID = ?"
                  + " and GDP_YEAR = ?";

      // Before we update, change the gdpValue
      gdpValue = (double)(Math.random() * (gdpValue - 1)) + 1;
      System.out.println(gdpValue + " " + gdpYear + " " + eID);
      System.out.println(uSQL);
      try
      {
         pStmt = dbConn.prepareStatement(uSQL);
         pStmt.setDouble(1, gdpValue);
         pStmt.setInt(2, eID);
         pStmt.setInt(3, gdpYear);
         pStmt.executeUpdate();
         dbConn.commit();
      } catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   // Insert
   private static void insertRecord(Connection dbConn, String tableName, int eID, int gdpYear, int gdpValue) throws SQLException
   {
      PreparedStatement pStmt = null;
      String iSQL = "insert into " + tableName
                  + "(ENTITY_ID, GDP_YEAR, GDP_VALUE) values "
                  + "(?, ?, ?)";

      try
      {
         pStmt = dbConn.prepareStatement(iSQL);
         pStmt.setInt(1, eID);
         pStmt.setInt(2, gdpYear);
         pStmt.setInt(3, gdpValue);
         pStmt.executeUpdate();
         dbConn.commit();
      } catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   // Delete
   private static void deleteRecord(Connection dbConn, String tableName, int eID, int gdpYear, int gdpValue) throws SQLException
   {
      PreparedStatement pStmt = null;
      String dSQL = "delete " + tableName
                  + " where ENTITY_ID = ?"
                  + " and GDP_YEAR = ?"
                  + " and GDP_VALUE = ?";

      System.out.println(dSQL);
      try
      {
         pStmt = dbConn.prepareStatement(dSQL);
         pStmt.setInt(1, eID);
         pStmt.setInt(2, gdpYear);
         pStmt.setInt(3, gdpValue);
         pStmt.executeUpdate();
         dbConn.commit();
      } catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}