How to avoid JDBC memory overflow issues

Method One: Add two parameters after connecting the MySQL URL: usecursorfetch=true&defaultfetchsize=100

Complete form such as: jdbc:mysql://192.168.1.252:3306/lims?useunicode=true&charsetencoding=utf8&usecursorfetch= true&defaultfetchsize=100

This method is only valid for MySQL.

Method Two: Using the way of paging query, without using the connection pool, the same connection object can be used, and the PreparedStatement object and ResultSet object are closed after each operation. Then, at the next operation, reopen the PreparedStatement object and the ResultSet object. (Note that these two objects must be closed here)

SQL Server and Oracle:

Methods: In the way of paging query, without using connection pool, the same connection object can be used, and the PreparedStatement object and ResultSet object are closed after each operation. Then, at the next operation, reopen the PreparedStatement object and the ResultSet object.

Internal principle and discussion:

When using the Java JDBC Operations database, the default data caching approach is to cache data from database queries into local memory and then read data from memory (which is also why JDBC result sets support moving up and down, However, Oracle-provided JDBC implementations do not support this move up and down by default, so if the data obtained from the database is cached in local memory, the memory overflow can occur if the memory is exceeded.

Like the MySQL method one, using the URL plus the parameters of the way, this is actually using the MySQL cursor, the data will not be cached in the local memory, but the ResultSet object to the record in the database, even if the hundreds of millions of records, as long as the network constantly, there will be no abnormal situation.

Like MySQL method Two, still use the way of data caching, but in this case the amount of cached data is not too good, as long as the PreparedStatement object and ResultSet object closed, the cached data disappears, so there will be no memory overflow.