import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

public class MariaDBCommitRollback {

    static final String DRIVER_CLASS = "org.mariadb.jdbc.Driver";
    static final String URL_PREFIX = "jdbc:mariadb://";
    static final String URL_OPTIONS = "?connectTimeout=30000&socketTimeout=300000&yearIsDateType=false&rewriteBatchedStatements=true&serverTimezone=UTC&sessionVariables=wait_timeout=600";
    static final String USER = "";
    static final String PASSWORD = "";
    static final String HOST = "";
    static final String PORT = "";
    static final String DATABASE = "";

    public static void main(String[] args) throws SQLException {
        final String url = URL_PREFIX + HOST + ":" + PORT + "/" + DATABASE + URL_OPTIONS;
        testConnect(url, USER, PASSWORD);
    }

    private static void testConnect(String url, String user, String password) {
        System.out.println("URL: " + url);
        final Properties props = new Properties();
        props.put("user", user);
        props.put("password", password);
        try {
            final Driver driver = (Driver) Class.forName(DRIVER_CLASS).newInstance();
            final String driverMajorVersion = "" + driver.getMajorVersion();
            final String driverMinorVersion = "" + driver.getMinorVersion();
            System.out.println("Driver version: " + driverMajorVersion + "." + driverMinorVersion);
            final Connection connection = driver.connect(url, props);
            final DatabaseMetaData metadata = connection.getMetaData();
            final String driverName = metadata.getDriverName();
            final String driverVersion = metadata.getDriverVersion();
            final String databaseProductName = metadata.getDatabaseProductName();
            final String databaseProductVersion = metadata.getDatabaseProductVersion();
            final int databaseProductMajorVersion = metadata.getDatabaseMajorVersion();
            final int databaseProductMinorVersion = metadata.getDatabaseMinorVersion();
            System.out.println("Driver name: " + driverName);
            System.out.println("Driver version name: " + driverVersion);
            System.out.println("Database product name: " + databaseProductName);
            System.out.println("Database product version name: " + databaseProductVersion);
            System.out.println("Database product version number: " + databaseProductMajorVersion + "." + databaseProductMinorVersion);
            System.out.println("JDBC version number: " + metadata.getJDBCMajorVersion() + "." + metadata.getJDBCMinorVersion());
            System.out.println();

            String create = "CREATE TABLE `emp` (`eid` bigint(20), `ename` varchar(128));";
            Statement stmt = connection.createStatement();
            stmt.executeUpdate(create);
            System.out.println("Table created");

            try {
                connection.setAutoCommit(false);
                String insert = "insert into emp (eid,ename) values (?, ?)";

                // Step 4: Execute the query
                PreparedStatement pstmt;
                pstmt = connection.prepareStatement(insert);
                for (int i = 1; i <= 5; i++) {
                    pstmt.setString(1, String.valueOf(i));
                    pstmt.setString(2, "XB" + i);
                    pstmt.addBatch();

                    if (i == 4) {
                        System.out.println("Roll back");
                        pstmt.clearBatch();
                        connection.rollback();
                    }

                    if (i == 3 || i == 5) {
                        pstmt.executeBatch();
                        System.out.println("Commit the batch");
                        connection.commit();
                    }
                }
            }
            catch (Exception e) {
                System.out.println("Error: " + e.getMessage());
            }

            ResultSet rs = stmt.executeQuery("SELECT * from emp");
            System.out.println("===========");
            while(rs.next()){
                System.out.println(rs.getInt("eid") + ", " + rs.getString("ename"));
            }
            System.out.println("===========");

            String drop = "DROP table emp";
            stmt.executeUpdate(drop);
            System.out.println("Table dropped");

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}