Java – how does limit in MySQL query make it possible to cancel flow

I want to know how the limit in the query prevents the application thread read from the MySQL stream from hanging during the shutdown operation, and why it is invalid to restrict enabling query cancellation

Statement statement = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);

// Statement statement = connection.createStatement(); // this can be canceled

    new Thread(new Runnable() {

        // tries to cancel query after streaming starts
        @Override
        public void run() {
            try {
                Thread.sleep(5);
                statement.cancel(); // does nothing when streaming
               // statement.close(); // makes application thread hang
            } catch (sqlException | InterruptedException e) {
                e.printStackTrace();
            }
        }
    }).start();

    // adding limit to the query makes it possible to cancel stream,even if limit is not yet reached
    resultSet = statement.executeQuery("SOME_LONG_RUNNING_QUERY");

    int i = 0;
    while (resultSet.next()) {
        System.out.println(++i);
    }

    connection.close();

You can safely cancel regular (non streaming) queries with or without restrictions However, in streaming mode, the close / cancel operation only causes the application thread to suspend / not perform any operation, possibly when performing a blocking read on the socket

If I add some large limits to a long-running query, as expected, the result of the cancel () operation is:

com.MysqL.jdbc.exceptions.jdbc4.MysqLQueryInterruptedException: Query execution was interrupted

I understand that there are several problems in this issue, but none of them discusses the following aspects:

>Why can limit cancel streaming query > it can rely on this bug function and can be changed in the next version. Is there any official explanation?

Solution

Limit make only extracts a certain number of records from the database Restrictions are useful if you have a large query that can be suspended

In your case, when you transfer a query with limit and close statements, it follows the order of operations Since limit appears first, the result will end your query This explains why even if you have a close statement, it does not reach it and why you receive exceptions

I hope this will solve some of your problems