package com.qlusters.qrm.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Bug16636 {

    private static String SELECT = "select * from test_table where id=1";
    private static String UPDATE = "update test_table set field='new' where id=1";

    private static Object obj = new Object();

    private static Object obj1 = new Object();

    public static void main(String[] args) {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/db_name?useServerPrepStmts=false&zeroDateTimeBehavior=convertToNull";
            Connection conn = DriverManager
                    .getConnection(url, "root", "");

            Connection conn1 = DriverManager.getConnection(url, "root",
                "");

            Thread1 th1 = new Thread1(conn);
            Thread2 th2 = new Thread2(conn1);
            th1.start();
            th2.start();

            Thread.sleep(2000);
            
            synchronized (obj) {
                obj.notifyAll();
            }

        } catch (Throwable th) {
            th.printStackTrace();
        }
    }

    static class Thread1 extends Thread {

        Connection conn;

        @Override
        public void run() {
            Statement stmt = null;
            try {
                conn.setAutoCommit(false);
                stmt = conn.createStatement();
                stmt.execute("start transaction;");
                System.out.println(Thread.currentThread().getName()
                        + " starting...");
                //stmt.execute(SELECT_IDS);
                System.out.println(Thread.currentThread().getName()
                        + " got selected ids...");
            } catch (SQLException e) {
                e.printStackTrace();
            }

            synchronized (obj) {
                try {
                    obj.wait();

                    ResultSet rs = stmt.executeQuery(SELECT);
                    while (rs.next()) {
                        System.out.println(Thread.currentThread().getName()
                                + " result is: " + rs.getString(2));
                    }
                    stmt.executeUpdate(UPDATE);
                    ResultSet rs2 = stmt.executeQuery(SELECT);
                    while (rs2.next()) {
                        System.out.println(Thread.currentThread().getName()
                                + " result is: " + rs2.getString(2));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } catch (InterruptedException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (Throwable e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } finally {
                    try {
                        stmt.execute("commit;");
                        System.out.println(Thread.currentThread().getName()
                                + "commited");
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

        }

        public Thread1(Connection conn) {
            this.conn = conn;
            this.setName("th1");
        }
    }

    static class Thread2 extends Thread {

        Connection conn;

        public Thread2(Connection conn) {
            this.conn = conn;
            this.setName("th2");
        }

        @Override
        public void run() {
            Statement stmt = null;
            try {

                conn.setAutoCommit(false);
                stmt = conn.createStatement();
                stmt.execute("start transaction;");
                System.out.println(Thread.currentThread().getName()
                        + " starting...");
                //stmt.execute(SELECT_IDS);
                System.out.println(Thread.currentThread().getName()
                        + " got selected ids...");
            } catch (SQLException e) {
                e.printStackTrace();
            }

            synchronized (obj) {
                try {
                    obj.wait();

                    ResultSet rs = stmt.executeQuery(SELECT);
                    while (rs.next()) {
                        System.out.println(Thread.currentThread().getName()
                                + " result is: " + rs.getString(2));
                    }
                    //stmt.executeUpdate(UPDATE);
                    ResultSet rs2 = stmt.executeQuery(SELECT);
                    while (rs2.next()) {
                        System.out.println(Thread.currentThread().getName()
                                + " result is: " + rs2.getString(2));
                    }

                } catch (SQLException e) {
                    e.printStackTrace();
                } catch (InterruptedException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (Throwable e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } finally {
                    try {
                        stmt.execute("commit;");
                        System.out.println(Thread.currentThread().getName()
                                + "commited");
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
}