SHARE
TWEET

Untitled

a guest Apr 26th, 2018 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.time.Duration;
  8. import java.time.Instant;
  9. import java.util.Properties;
  10.  
  11. public class OracleUpdate {
  12.  
  13.     public static void main(String[] args) throws Exception {
  14.         Class.forName("oracle.jdbc.OracleDriver");
  15.  
  16.         String url = "jdbc:oracle:thin:@192.168.99.100:1521:ORCLCDB";
  17.         String user = "TEST";
  18.         String password = "TEST";
  19.  
  20.         Properties properties = new Properties();
  21.         properties.setProperty("user", user);
  22.         properties.setProperty("password", password);
  23.  
  24.         try (Connection c = DriverManager.getConnection(url, properties)) {
  25.             for (int i = 0; i < 5; i++) {
  26.                 Instant ts;
  27.  
  28.                 resetPost(c);
  29.                 ts = Instant.now();
  30.  
  31.                 try (Statement s = c.createStatement();
  32.                     ResultSet rs = s.executeQuery(
  33.                         "SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
  34.                     )) {
  35.  
  36.                     while (rs.next()) {
  37.                         try (PreparedStatement u = c.prepareStatement(
  38.                             "UPDATE post SET archived = 1 WHERE id = ?"
  39.                         )) {
  40.                             u.setInt(1, rs.getInt(1));
  41.                             u.executeUpdate();
  42.                         }
  43.                     }
  44.                 }
  45.  
  46.                 System.out.println("Run " + i + ", Statement 1: " + Duration.between(ts, Instant.now()));
  47.  
  48.                 resetPost(c);
  49.                 ts = Instant.now();
  50.  
  51.                 try (Statement s = c.createStatement();
  52.                     ResultSet rs = s.executeQuery(
  53.                         "SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
  54.                     );
  55.                     PreparedStatement u = c.prepareStatement(
  56.                         "UPDATE post SET archived = 1 WHERE id = ?"
  57.                     )) {
  58.  
  59.                     while (rs.next()) {
  60.                         u.setInt(1, rs.getInt(1));
  61.                         u.executeUpdate();
  62.                     }
  63.                 }
  64.  
  65.                 System.out.println("Run " + i + ", Statement 2: " + Duration.between(ts, Instant.now()));
  66.  
  67.                 resetPost(c);
  68.                 ts = Instant.now();
  69.  
  70.                 try (Statement s = c.createStatement();
  71.                     ResultSet rs = s.executeQuery(
  72.                         "SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
  73.                     );
  74.                     PreparedStatement u = c.prepareStatement(
  75.                         "UPDATE post SET archived = 1 WHERE id = ?"
  76.                     )) {
  77.  
  78.                     while (rs.next()) {
  79.                         u.setInt(1, rs.getInt(1));
  80.                         u.addBatch();
  81.                     }
  82.  
  83.                     u.executeBatch();
  84.                 }
  85.                 System.out.println("Run " + i + ", Statement 3: " + Duration.between(ts, Instant.now()));
  86.  
  87.                 resetPost(c);
  88.                 ts = Instant.now();
  89.  
  90.                 try (Statement s = c.createStatement()) {
  91.                     s.executeUpdate("UPDATE post\n" +
  92.                         "SET archived = 1\n" +
  93.                         "WHERE archived = 0 AND creation_date < DATE '2018-01-01'\n");
  94.                 }
  95.  
  96.                 System.out.println("Run " + i + ", Statement 4: " + Duration.between(ts, Instant.now()));
  97.             }
  98.         }
  99.     }
  100.  
  101.     static void resetPost(Connection c) throws SQLException {
  102.         try (Statement s = c.createStatement()) {
  103.             s.executeUpdate("TRUNCATE TABLE post");
  104.             s.executeUpdate("INSERT INTO post\n" +
  105.                 "    SELECT \n" +
  106.                 "      level,\n" +
  107.                 "      lpad('a', 1000, 'a'),\n" +
  108.                 "      0,\n" +
  109.                 "      DATE '2017-01-01' + (level / 10)\n" +
  110.                 "    FROM dual\n" +
  111.                 "    CONNECT BY level <= 10000");
  112.             s.executeUpdate("BEGIN dbms_stats.gather_table_stats('TEST', 'POST'); END;");
  113.         }
  114.     }
  115. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top