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.util.logging.Logger;

/**
 *
 */
public class MysqlJdbcTest {

	private Logger logger = Logger.getLogger( getClass().getName() );

	public static void main( String[] args ) throws Exception {
		MysqlJdbcTest test = new MysqlJdbcTest();
		test.createData();

		test.testDifferentConnection();
		test.testSameConnectionSameTransaction();

		System.out.println( "Done." );

	}

	public Connection createConnection(boolean trace) throws SQLException {

		try {
			Class.forName( "com.mysql.jdbc.Driver" );
			new com.mysql.jdbc.Driver();
		} catch ( ClassNotFoundException e ) {
			throw new IllegalStateException( e );
		}

		String user = "*";
		String pwd = "*";
		String url = "*";

		//not setting useCursorFetch , it will work
		url += "useCursorFetch=true";

		if (trace) {
			url += "&traceProtocol=true";
		}


		return  DriverManager.getConnection( url, user, pwd );

	}

	public void createData() throws SQLException {
		Connection conn = createConnection(false);
		executeUpdate( conn, "drop table if exists CURSOR_TEST" );
		executeUpdate( conn, "create table  CURSOR_TEST(id integer)" );
		executeUpdate( conn, "insert into CURSOR_TEST values (1)" );
		conn.close();
	}

	public void testSameConnectionSameTransaction() throws SQLException{
		logger.info("testSameConnectionSameTransaction");
		Connection conn = createConnection(true);
		conn.setAutoCommit( false );

		executeQuery( conn, true );
		//now without setting the fetch size, the thread will hang
		executeQuery( conn, false );

		conn.commit();
		conn.close();
	}

	public void testDifferentConnection() throws SQLException{
		logger.info("testDifferentConnection");

		Connection conn = createConnection(true);
		executeQuery( conn, true );
		conn.close();

		conn = createConnection(true);
		executeQuery( conn, false );
		conn.close();
	}

	private void executeUpdate( Connection connection, String sql ) throws SQLException {
		Statement statement = connection.createStatement();
		statement.executeUpdate( sql );
		statement.close();
	}

	private void executeQuery( Connection connection,  boolean fetch ) throws SQLException {
		int n = 0;

		String sql = "select id from CURSOR_TEST ";

		PreparedStatement statement = connection.prepareStatement( sql );

		if ( fetch ) {
			statement.setFetchSize( 100 );
		}
		logger.info("Executing  " + sql + " ..." );
		ResultSet rs = statement.executeQuery();
		while ( rs.next() ) {
			n++;
		}
		logger.info("Read " + n + " row" );
		rs.close();
		statement.close();
	}
}