import java.sql.*;
import java.util.*;

public class JdbcVivaDemo {
    static void say(String q, String s) { System.out.printf("%s %s%n", q, s); }

    public static void main(String[] args) throws Exception {
        // BEGIN q55
        String url = "jdbc:h2:mem:viva";                       // an in-memory H2 database; any JDBC URL works the same way
        try (Connection con = DriverManager.getConnection(url, "sa", "")) {          // 1. connect (the driver registers itself)
            try (Statement st = con.createStatement()) {                              // 2. create a statement
                st.execute("CREATE TABLE emp(id INT PRIMARY KEY, name VARCHAR(40), salary INT)");
                st.execute("INSERT INTO emp VALUES (1,'Asha',50000),(2,'Ravi',60000),(3,'Meera',55000)");
            }
            try (PreparedStatement ps = con.prepareStatement("SELECT id, name FROM emp WHERE salary > ? ORDER BY id")) {
                ps.setInt(1, 52000);                                                  // 3. bind parameters
                try (ResultSet rs = ps.executeQuery()) {                              // 4. execute, then 5. read rows
                    while (rs.next()) System.out.println("Q55 row: " + rs.getInt("id") + " " + rs.getString("name"));
                }
            }                                                                         // 6. try-with-resources closes ResultSet, statement, connection
            DatabaseMetaData md = con.getMetaData();
            say("Q55", "driver=" + md.getDriverName().split(" ")[0] + ", autoCommit default=" + con.getAutoCommit());
            // END q55

            // BEGIN q56
            String evil = "x' OR '1'='1";
            int viaStatement = 0, viaPrepared = 0;
            try (Statement st = con.createStatement();
                 ResultSet rs = st.executeQuery("SELECT * FROM emp WHERE name = '" + evil + "'")) {   // string concatenation
                while (rs.next()) viaStatement++;
            }
            try (PreparedStatement ps = con.prepareStatement("SELECT * FROM emp WHERE name = ?")) {  // bound parameter
                ps.setString(1, evil);
                try (ResultSet rs = ps.executeQuery()) { while (rs.next()) viaPrepared++; }
            }
            say("Q56", "input \"" + evil + "\": Statement returned " + viaStatement + " rows, PreparedStatement returned " + viaPrepared);
            // END q56

            // BEGIN q57
            con.setAutoCommit(false);
            try (Statement st = con.createStatement()) {
                st.executeUpdate("UPDATE emp SET salary = salary - 1000 WHERE id = 1");
                st.executeUpdate("UPDATE emp SET salary = salary + 1000 WHERE id = 2");
                con.rollback();                                                       // pretend step 3 failed: undo both updates
            }
            try (Statement st = con.createStatement(); ResultSet rs = st.executeQuery("SELECT salary FROM emp WHERE id IN (1,2) ORDER BY id")) {
                rs.next(); int a = rs.getInt(1); rs.next(); int b = rs.getInt(1);
                say("Q57", "after rollback salaries are " + a + " and " + b);
            }
            try (Statement st = con.createStatement()) {
                st.executeUpdate("UPDATE emp SET salary = salary - 1000 WHERE id = 1");
                st.executeUpdate("UPDATE emp SET salary = salary + 1000 WHERE id = 2");
                con.commit();
            }
            try (Statement st = con.createStatement(); ResultSet rs = st.executeQuery("SELECT salary FROM emp WHERE id IN (1,2) ORDER BY id")) {
                rs.next(); int a = rs.getInt(1); rs.next(); int b = rs.getInt(1);
                say("Q57", "after commit salaries are " + a + " and " + b);
            }
            con.setAutoCommit(true);
            // END q57

            // BEGIN q58
            try (PreparedStatement ps = con.prepareStatement("INSERT INTO emp VALUES (?,?,?)")) {
                for (int i = 10; i < 15; i++) { ps.setInt(1, i); ps.setString(2, "temp" + i); ps.setInt(3, 1000 * i); ps.addBatch(); }
                int[] counts = ps.executeBatch();
                say("Q58", "executeBatch returned " + Arrays.toString(counts));
            }
            try (Statement st = con.createStatement(); ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM emp")) { rs.next(); say("Q58", "rows now: " + rs.getInt(1)); }
            // END q58

            // BEGIN q59
            try (Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                 ResultSet rs = st.executeQuery("SELECT name FROM emp ORDER BY id")) {
                rs.last(); String last = rs.getString(1); rs.first(); String first = rs.getString(1);
                say("Q59", "scrollable ResultSet: first=" + first + ", last=" + last);
            }
            // END q59
        }
    }
}