Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.time.Duration;
- import java.time.Instant;
- import java.util.Properties;
- public class OracleUpdate {
- public static void main(String[] args) throws Exception {
- Class.forName("oracle.jdbc.OracleDriver");
- String url = "jdbc:oracle:thin:@192.168.99.100:1521:ORCLCDB";
- String user = "TEST";
- String password = "TEST";
- Properties properties = new Properties();
- properties.setProperty("user", user);
- properties.setProperty("password", password);
- try (Connection c = DriverManager.getConnection(url, properties)) {
- for (int i = 0; i < 5; i++) {
- Instant ts;
- resetPost(c);
- ts = Instant.now();
- try (Statement s = c.createStatement();
- ResultSet rs = s.executeQuery(
- "SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
- )) {
- while (rs.next()) {
- try (PreparedStatement u = c.prepareStatement(
- "UPDATE post SET archived = 1 WHERE id = ?"
- )) {
- u.setInt(1, rs.getInt(1));
- u.executeUpdate();
- }
- }
- }
- System.out.println("Run " + i + ", Statement 1: " + Duration.between(ts, Instant.now()));
- resetPost(c);
- ts = Instant.now();
- try (Statement s = c.createStatement();
- ResultSet rs = s.executeQuery(
- "SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
- );
- PreparedStatement u = c.prepareStatement(
- "UPDATE post SET archived = 1 WHERE id = ?"
- )) {
- while (rs.next()) {
- u.setInt(1, rs.getInt(1));
- u.executeUpdate();
- }
- }
- System.out.println("Run " + i + ", Statement 2: " + Duration.between(ts, Instant.now()));
- resetPost(c);
- ts = Instant.now();
- try (Statement s = c.createStatement();
- ResultSet rs = s.executeQuery(
- "SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
- );
- PreparedStatement u = c.prepareStatement(
- "UPDATE post SET archived = 1 WHERE id = ?"
- )) {
- while (rs.next()) {
- u.setInt(1, rs.getInt(1));
- u.addBatch();
- }
- u.executeBatch();
- }
- System.out.println("Run " + i + ", Statement 3: " + Duration.between(ts, Instant.now()));
- resetPost(c);
- ts = Instant.now();
- try (Statement s = c.createStatement()) {
- s.executeUpdate("UPDATE post\n" +
- "SET archived = 1\n" +
- "WHERE archived = 0 AND creation_date < DATE '2018-01-01'\n");
- }
- System.out.println("Run " + i + ", Statement 4: " + Duration.between(ts, Instant.now()));
- }
- }
- }
- static void resetPost(Connection c) throws SQLException {
- try (Statement s = c.createStatement()) {
- s.executeUpdate("TRUNCATE TABLE post");
- s.executeUpdate("INSERT INTO post\n" +
- " SELECT \n" +
- " level,\n" +
- " lpad('a', 1000, 'a'),\n" +
- " 0,\n" +
- " DATE '2017-01-01' + (level / 10)\n" +
- " FROM dual\n" +
- " CONNECT BY level <= 10000");
- s.executeUpdate("BEGIN dbms_stats.gather_table_stats('TEST', 'POST'); END;");
- }
- }
- }
Add Comment
Please, Sign In to add comment