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

/**
 *
 */
public class MysqlJdbcTest {

	private Connection connection;

	public static void main( String[] args ) throws Exception {
		MysqlJdbcTest test = new MysqlJdbcTest();
		test.testQuery();
		System.out.println( "Done." );

	}

	public Connection getConnection() throws SQLException {
		if ( connection != null ) {
			return connection;
		}
		try {
			Class.forName( "com.mysql.jdbc.Driver" );
		} catch ( ClassNotFoundException e ) {
			throw new IllegalStateException( e );
		}

		String url = "jdbc:mysql://***:***/***?";
		//not setting useCursorFetch , it will work
		url += "useCursorFetch=true";

		//url += "&traceProtocol=true";

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

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

	}

	public void createData() throws SQLException {
		getConnection().createStatement().executeUpdate( "drop table if exists CURSOR_TEST" );
		getConnection().createStatement().executeUpdate( "create table  CURSOR_TEST(id integer)" );
		getConnection().createStatement().executeUpdate( "insert into CURSOR_TEST values (1)" );
	}

	public void testQuery() throws Exception {

		createData();

		getConnection().setAutoCommit( false );

		String sql = "select id from CURSOR_TEST ";

		exectuteQuery( sql, true );

		//now without setting the fetch size, the thread will hand
		exectuteQuery( sql, false );


	}

	private void exectuteQuery( String sql, boolean fetch ) throws Exception {
		int n = 0;
		PreparedStatement statement = getConnection().prepareStatement( sql );

		if ( fetch ) {
			statement.setFetchSize( 100 );
		}
		System.out.println( "Executing  " + sql + " ..." );
		ResultSet rs = statement.executeQuery();
		while ( rs.next() ) {
			n++;
		}
		rs.close();
		statement.close();

		System.out.println( "Read " + n + " row" );

	}
}