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.sql.DatabaseMetaData;
import java.util.Date;
import java.io.ByteArrayInputStream;


public class PreparedStmtTest {
    private Connection connect = null;
    private Statement statementMsg = null;
    private PreparedStatement ps = null;
    private PreparedStatement psAlt = null;
    private ResultSet resultSet = null;
    private Integer beforeResetsCnt = null;
    private Integer afterResetsCnt = null;

    public void startTest(String testName) {
        System.out.printf("[Line: %3d] %-55s", + Thread.currentThread().getStackTrace()[2].getLineNumber(), testName );
        try {
            if (beforeResetsCnt == null) {
                resultSet = statementMsg.executeQuery("SELECT COUNT(*) AS reset_stmt_count FROM mysql.general_log WHERE command_type='Reset stmt'");
                resultSet.next();
                beforeResetsCnt = resultSet.getInt("reset_stmt_count");
            }
            System.out.printf("Resets cnt before: %-2d", beforeResetsCnt);
        } catch (Exception e) {
            System.out.println("Exception: " + e.toString());
        }      
    }

    public void endTest() {
        try {
            resultSet = statementMsg.executeQuery("SELECT COUNT(*) AS reset_stmt_count FROM mysql.general_log WHERE command_type='Reset stmt'");
            resultSet.next();
            afterResetsCnt = resultSet.getInt("reset_stmt_count");
        } catch (Exception e) {
            System.out.println("Exception: " + e.toString());
        }    
        System.out.printf(" / after: %-2d (delta: %-2d)\n",afterResetsCnt, (afterResetsCnt - beforeResetsCnt));
        beforeResetsCnt = afterResetsCnt;
    }

    public void testIt() {
        try {
            startTest("Running ps with discrete values, prepare inside loop");
            String selectSql = "/* prepared on each iteration */ select * from test.audited_instance where class=?";
            for (int i = 1; i <= 5; i++) {
                ps = connect.prepareStatement(selectSql);
                ps.setInt(1, i);
                ps.execute();
                ps.close();
            }
            endTest();

            startTest("Running ps with discrete values, prepare outside loop");
            selectSql = "/* prepared once before for loop */ select * from test.audited_instance where class=?";
            ps = connect.prepareStatement(selectSql);
            for (int i = 1; i <= 5; i++) {
                ps.setInt(1, i);
                ps.execute();
            }
            ps.close();
            endTest();
            
            selectSql = "SELECT class, inst, iv FROM test.audited_instance WHERE class = ? AND inst = ? AND iv != ?";

            startTest("Running clearParameters() once...");
            ps.clearParameters();
            endTest();

            startTest("Running clearParameters() twice...");
            ps.clearParameters();
            endTest();

            startTest("Preparing ps ");
            ps = connect.prepareStatement(selectSql);
            endTest();

            startTest("Preparing psAlt ");
            psAlt = connect.prepareStatement(selectSql);
            endTest();
            
            startTest("Running ps with long data");            
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBinaryStream(3, new ByteArrayInputStream(new byte[] { 1 }));
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running clearParameters()");
            ps.clearParameters();
            endTest();
            
            startTest("Running psAlt with long data");            
            psAlt.setLong(1, 33);
            psAlt.setLong(2, 4);
            psAlt.setBinaryStream(3, new ByteArrayInputStream(new byte[] { 1 }));
            resultSet = psAlt.executeQuery();
            endTest();

            startTest("Running ps with discrete value");
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBytes(3, new byte[] { 1 });
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running clearParameters()");
            ps.clearParameters();
            endTest();

            startTest("Running ps with discrete value");
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBytes(3, new byte[] { 1 });
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running ps with long data");
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBinaryStream(3, new ByteArrayInputStream(new byte[] { 1 }));
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running ps with long data");
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBinaryStream(3, new ByteArrayInputStream(new byte[] { 1 }));
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running ps with discrete value");
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBytes(3, new byte[] { 1 });
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running ps with discrete value");
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBytes(3, new byte[] { 1 });
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running ps with no freshly binded values");
            try { 
                resultSet = ps.executeQuery(); 
            } catch (SQLException ex) {
                System.out.println("Exception: " + ex.toString()); 
            }
            endTest();

            startTest("Running clearParameters()");
            ps.clearParameters();
            endTest();

            startTest("Running ps with no binded values");
            try { 
                resultSet = ps.executeQuery(); 
            } catch (SQLException ex) {  
                System.out.print("Exception: " + ex.toString()); 
            }
            endTest();

            startTest("Running ps with discrete value");
            ps.setLong(1, 33);
            ps.setLong(2, 4);
            ps.setBytes(3, new byte[] { 1 });
            resultSet = ps.executeQuery();
            endTest();

            startTest("Running clearParameters()");
            ps.clearParameters();
            endTest();


        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (Exception e) {
            System.out.println("Exception: " + e.toString());
        }

        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statementMsg != null) {
                statementMsg.close();
            }
            if (connect != null) {
                connect.close();
            }
        } catch (Exception ex) {
            System.out.println("Exception while cleaning up resources: " + ex.toString());
        }
    }

    public PreparedStmtTest() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://127.0.0.1:8032/test?user=msandbox&password=msandbox&useServerPrepStmts=true&cachePrepStmts=true");
            DatabaseMetaData meta = connect.getMetaData();
            System.out.println("Connector/J: " + meta.getDriverVersion());
            System.out.println("MySQL: " + meta.getDatabaseProductVersion());
            
            statementMsg = connect.createStatement();
            statementMsg.executeUpdate("SET GLOBAL log_output='TABLE'");
            statementMsg.executeUpdate("TRUNCATE mysql.general_log");

            statementMsg.executeUpdate("CREATE DATABASE IF NOT EXISTS test");
            statementMsg.executeUpdate("CREATE TABLE IF NOT EXISTS test.audited_instance (class INT NOT NULL, inst INT NOT NULL, iv VARCHAR(255) NOTNULL)");

        } catch (Exception e) {
            System.out.println("Exception: " + e.toString());
        }
    }

    public static void main(String[] argsa) {
        System.out.println("Starting...");
        PreparedStmtTest x = new PreparedStmtTest();        
        x.testIt();
        System.out.println("Done...");
    }

}