cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Valery Barysok (JIRA)" <>
Subject [jira] [Commented] (CAY-2014) Ensure SelectQuery.setFetchSize() actually takes affect on MySQL
Date Mon, 15 Jun 2015 13:25:01 GMT


Valery Barysok commented on CAY-2014:

SelectQuery contains setStatementFetchSize method instead of setFetchSize.

Required common jdbc conditions:
1) The Connection must not be in autocommit mode (Cursor will be closed on the end of transaction)
2) A ResultSet object must be not updatable and has a cursor that moves forward only ([it
is default by specification|])
3) Provide fetch size through Statement.setFetchSize or ResultSet.setFetchSize

Database specific conditions:
       1) useCursorFetch must be true ([Client and server cursors in MySQL|])
           through jdbc url:

           through properties:
Properties info = new Properties();
info.setProperty("useCursorFetch", "true");
// ...
Connection conn = DriverManager.getConnection(getJdbcUrl(), info);
       1) see [Getting results based on a cursor|]

Next code was used to validate correct work of fetch size in the Cayenne ORM for MySQL and

private static void testFetchSize(ObjectContext context) {
    SelectQuery<ManyRowsEntity> select = SelectQuery.query(ManyRowsEntity.class);
    ResultIterator<ManyRowsEntity> it = context.iterator(select);
    while (it.hasNextRow()) {
        // do some with

For MySQL you need to debug {code:java}com.mysql.jdbc.RowDataCursor.fetchMoreRows{code} method.
For PostgreSQL you need to debug {code:java}org.postgresql.core.v3.QueryExecutorImpl.fetch{code}

> Ensure SelectQuery.setFetchSize() actually takes affect on MySQL
> ----------------------------------------------------------------
>                 Key: CAY-2014
>                 URL:
>             Project: Cayenne
>          Issue Type: Improvement
>            Reporter: Andrus Adamchik
>            Assignee: Andrus Adamchik
> We supported SelectQuery.setFetchSize() since times immemorial, however it doesn't do
much on MySQL. According to [1] MySQL still reads the entire ResultSet in memory. So whenever
a query has fetch size set, we need to ensure that MySQL adapter creates PreparedStatement
using the following parameters (also from [1]) :
> {noformat}stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
>               java.sql.ResultSet.CONCUR_READ_ONLY);
> stmt.setFetchSize(Integer.MIN_VALUE);
> {noformat}
> For a bonus point, we'll need to research whether other DBs (PostgreSQL, Oracle, SQLServer)
actually support fetch size or whether they require special handling like MySQL.
> [1]

This message was sent by Atlassian JIRA

View raw message