Preventing memory overflow of jdbc underlying driver loading data mechanism -- Mysql

Keywords: Database MySQL JDBC server oom

1. Key objects

The connection object -- com.mysql.jdbc.JDBC4Connection contains all configuration parameters about mysql. The setting method is set in the form of parameters attached to the url

preparedStatement object -- com.mysql.jdbc.ServerPreparedStatement

2. Data result set reading method

There are three types:

One is in the form of stream. The result set is obtained multiple times for one query, and one line is read at a time. That is, while( is obtained from the server once at a time   The result set data is saved in the RowDataCursor object RowDataDynamic

Advantages: it is equivalent to reading the data in the table several times until it is finished, which perfectly solves the problem of memory overflow
Disadvantages: it increases the communication overhead. It may take many times to complete the communication after reading a table

Configuration method: stmt.enableStreamingResults(); Or stmt.setFetchSize(Integer.MIN_VALUE) requires only one line of code. There is no need to attach parameters to the url

One is to read all   After a query, all the result sets are loaded at one time, no matter how much data there is. The result set data is saved in the RowDataCursor object RowDataStatic

Advantages: reduced communication overhead and optimal performance
Disadvantages: it may cause jvm memory overflow

This is the default. No parameters need to be configured

One is to use a cursor to read   One query, the result set is obtained multiple times, and multiple rows are read at a time. The result set data is saved in the RowDataCursor object

The above two methods are balanced without memory overflow


The first method: JDBC: mysql:// 2? useCursorFetch=true&defaultFetchSize=4   Just configure the url with some parameters. All statement s will use cursor mode

The second method: configure JDBC with url parameters: mysql:// 2? useCursorFetch=true

Stmt.setfetchsize (parameter greater than 0) is configured in the code

3.RowDataDynamic   RowDataStatic   Differences between RowDataCursor

These three objects all save the result set. The only difference is the saving form of the result set.

RowDataStatic is the default result set saving form, which saves all the result data of a query. The amount of data may be large

RowDataDynamic only saves the current result set. When the next() method is called, it immediately gets the next data

RowDataCursor saves part of the result set. When next() is called, if the current batch of data has not reached the end (the end of the whole table), it will send another request to obtain the next batch of data.

4. Read the source code of the result set

MysqlIO.getResultSet() method

Code obtained by cursor:

The database version is above 5.0.2, and the parameter useCursorFetch=true, and the fetchsize size is not equal to 0 (greater than 0)

if (this.connection.versionMeetsMinimum(5, 0, 2)
				&& this.connection.getUseCursorFetch()
				&& isBinaryEncoded
				&& callingStatement != null
				&& callingStatement.getFetchSize() != 0
				&& callingStatement.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY) {
			ServerPreparedStatement prepStmt = (com.mysql.jdbc.ServerPreparedStatement) callingStatement;

			boolean usingCursor = true;

			// Server versions 5.0.5 or newer will only open
			// a cursor and set this flag if they can, otherwise
			// they punt and go back to mysql_store_results() behavior

			if (this.connection.versionMeetsMinimum(5, 0, 5)) {
				usingCursor = (this.serverStatus &

			if (usingCursor) {
				RowData rows = new RowDataCursor(

				ResultSetImpl rs = buildResultSetWithRows(
					rows, resultSetType, resultSetConcurrency, isBinaryEncoded);

				if (usingCursor) {

				return rs;

How to use flow: streamResults is true. The judgment of this parameter is in

protected boolean createStreamingResultSet() {
		try {//Judging resultsettype, resultsetconcurrency fetchsize must be Integer.MIN_VALUE
			synchronized (checkClosed().getConnectionMutex()) {
				return ((this.resultSetType == java.sql.ResultSet.TYPE_FORWARD_ONLY)
						&& (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY) && (this.fetchSize == Integer.MIN_VALUE));
		} catch (SQLException e) {
			// we can't break the interface, having this be no-op in case of error is ok
			return false;
 if (!streamResults) {//Read all and return a RowDataStatic object
            rowData = readSingleRowSet(columnCount, maxRows,
                    resultSetConcurrency, isBinaryEncoded,
                    (metadataFromCache == null) ? fields : metadataFromCache);
        } else {//Read by stream. Read only one record at a time. Return a RowDataDynamic object
            rowData = new RowDataDynamic(this, (int) columnCount,
            		(metadataFromCache == null) ? fields : metadataFromCache,
            this.streamingData = rowData;


Posted by WolfRage on Wed, 03 Nov 2021 20:06:46 -0700