Localized caching and resettable access methods for JDBC streaming result sets

Abstract

The application provides a local cache and resettable access method for a JDBC streaming result set, comprising the following steps: S1: creating a smart overflow result set container object; S2: writing data in a ResultSet result set into the smart overflow result set container object; and S3: accessing the data through a uniform data iterator of the smart overflow result set container object. The method adopts a block chain type linked list and column name index mapping, avoids memory loss of set expansion and key-value pair storage, reduces overflow risk, and improves read-write efficiency. In combination with metadata and a JVM model, memory is estimated, and over-limit data is periodically detected and automatically overflowed to a disk to realize intelligent memory management and control and adapt to massive data processing. Meanwhile, the database connection and business logic are decoupled, the connection is released as soon as the data pulling is completed, the connection pool turnover is accelerated, and the system concurrent capability is effectively improved.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a localized caching and resettable access method for JDBC streaming result sets. Background Technology

[0002] In enterprise-level Java database development scenarios, JDBC streaming result sets have become the mainstream implementation solution for massive data queries, batch synchronization, and report export due to their advantages of retrieving data row by row on demand and low initial memory consumption. They can avoid JVM memory overflow failures caused by loading all data. However, native streaming result sets only serve as a temporary data transmission channel between the database and the application, and the data can only be read sequentially once and cannot be repeatedly traversed. Existing technologies generally use general collections such as ArrayList and HashMap to cache result data, which makes it difficult to balance the temporary transmission attribute of streaming result sets with the actual need for multiple reuses of full data in business applications. The existing solution has the following drawbacks: 1. Lack of dedicated storage carrier, high risk of memory runaway: Existing solutions rely on general containers such as ArrayList and HashMap to store result set data; ArrayList stores data based on dynamic arrays combined with a 1.5x expansion rule. When writing massive amounts of data, array expansion will instantly request a large amount of contiguous memory, forming a memory peak and causing memory overflow; HashMap stores column names and field data in key-value pairs. The container's own hash structure and key object storage bring more than 40% additional memory consumption, further compressing the available memory; 2. Lack of dynamic memory management: Existing technology lacks a memory detection mechanism, cannot predict memory usage in real time, and cannot determine the timing of data writing to disk based on memory quotas; after massive amounts of data are continuously written, they all remain in memory, which can easily exceed the JVM limit and trigger memory overflow. 3. Strong coupling between database connection and business processing, resulting in poor utilization of connection resources: Streaming result sets depend on the database connection to survive, requiring the connection to be occupied throughout the entire process until all business processing is completed. Long-term exclusive use of the connection can easily exhaust the idle resources of the connection pool, blocking other businesses from requesting database connections and affecting the utilization of the connection pool. Summary of the Invention

[0003] The purpose of this invention is to address the shortcomings of existing technologies by proposing a localized caching and resettable access method for JDBC streaming result sets. To achieve the above objectives, the present invention adopts the following technical solution: The localized caching and resettable access method for JDBC streaming result sets includes the following sub-steps: S1: Create a smart spill result set container object; S11: Initialize the smart spill result set container object; The application calls the constructor of the smart spill result set container to create a smart spill result set container object and passes the configuration parameters to the container object to complete the initialization of the container object; the configuration parameters include the maximum memory quota and the memory check frequency; Set the maximum memory quota passed in to the maximum memory quota of the container object, and set the memory check frequency passed in to the memory check frequency of the container object. Initialize the block-linked list of the container object to an empty list, initialize the spill file list of the container object to an empty list, and set the total number of rows used to count the data in the ResultSet to 0; The spill file list is a List collection, and the elements of the collection are spill file objects, which are used to identify the actual spill files on the disk; S12: Obtain the estimated memory size for a single row of data; The application executes SQL statements to query the database and obtains a ResultSet result set through the JDBC interface; Retrieve the corresponding ResultSetMetaData metadata object from the ResultSet result set, iterate through each column in the ResultSetMetaData metadata object, obtain the metadata contained in each column, and calculate the actual bytes occupied by a single column of data in the heap memory; The metadata includes data type, column type name, column display size, column precision, scale, etc. Add up the actual bytes occupied by all columns in the ResultSetMetaData metadata object to get the baseline memory for a single row; multiply the baseline memory for a single row by a preset buffer coefficient to get the estimated memory size for a single row of data. S13: Initialize column name information; Call the ResultSetMetaData metadata object's interface to get the total number of columns in the ResultSet result set, create an array of strings of equal length based on the total number of columns, and name it the column name array; Iterate through each column in the ResultSetMetaData metadata object, read the column name corresponding to each column stored in the ResultSetMetaData metadata object, and store the column names in order into the column name array; assign the filled column name array to the smart spill result set container object as the column name information of the smart spill result set container object.

[0004] S2: Write the data in the ResultSet to the smart overflow result set container object; S21: Read and encapsulate data line by line; The resultSet.next method is used to determine whether the next row of data exists in the resultSet. If it does not exist, proceed to step S24; if it does exist, the resultSet.getObject method is called in a loop to read all column values ​​corresponding to the current row and encapsulates all column values ​​into an Object array with a length equal to the total number of columns. Write the Object array into a block-linked list. For each row of data written, increment the total number of rows in the smart spill result set container object by 1; at the same time, increment the current cache line count in the block-linked list by 1. S22: Dynamic memory assessment and spill triggering; After successfully writing a single row of data to the block-linked list, check whether the current cache line count in the block-linked list is an integer multiple of the preset memory check frequency. If it is not an integer multiple, continue reading the next row of data in the ResultSet; if it is an integer multiple, perform a memory evaluation. The memory evaluation method is as follows: Multiply the estimated memory size of the single row of data obtained in advance by the current cache line count in the current block linked list to obtain the estimated memory usage in the linked list; The estimated memory usage is compared with the maximum memory quota. If the estimated memory usage is greater than the maximum memory quota, it is determined that the memory has reached the limit and proceeds to step S23; if the estimated memory usage is less than or equal to the maximum memory quota, the next row of data in the ResultSet is read. S23: Perform a data spill operation; The serialization framework is invoked to serialize the block-linked list into a binary byte stream by using the block-linked list as the serialization source object and calling the corresponding serialization interface. Create a uniquely named disk file in a preset temporary directory, write the binary byte stream to the disk file through a file output stream, close the file output stream and generate a corresponding spill file object; add the spill file object to the spill file list; Reset the current cache line count of the block-linked list to 0; S24: Release the ResultSet resource; Once all data in the ResultSet has been read and processed, call the close method to close the ResultSet and release the associated database connection resources.

[0005] S3: Access data through a unified data iterator of a smart spill result set container object; The application calls the iterator method of the smart spill result set container object to generate a unified data iterator, and accesses the data through the unified data iterator. The specific access method is as follows: S31: Traverse the data in the block-linked list that has not undergone data overflow operations; Determine whether there is any data in the current block-chain linked list that has not been written out. If it exists, first traverse the data, and then proceed to step S32 after the traversal is complete; otherwise, proceed directly to step S32. S32: Traverse the spill files in the disk files and read them; Read the spill files in the spill file list in sequence, open the file input stream, and use the serialization framework to deserialize the binary byte stream in the disk file back into a block-linked list. The block-linked list interface is called to generate a list iterator. The list iterator is used to read the data in the restored block-linked list line by line. After reading all the data in the restored block-linked list, the file input stream is closed. Get the next spill file in the spill file list, and execute the above steps in step S32 until all spill files in the spill file list have been traversed and processed, then the iteration ends. If you need to iterate through all the data again from the beginning, simply call the iterator method of the smart spill result set container to get a new iterator.

[0006] Compared with the prior art, the beneficial effects of the present invention are as follows: This method uses a block-linked list as a dedicated storage medium. It avoids the array copying and instantaneous memory peak problems caused by the dynamic expansion of ArrayList by relying on the fixed data block chain structure. At the same time, it uses column name arrays combined with index mapping to replace the HashMap key-value pair storage mode, which effectively reduces memory redundancy and structural overhead, greatly reduces the risk of memory overflow, and improves memory utilization and data reading efficiency. This method combines database metadata and JVM memory model to calculate the memory usage of a single row of data and adds a safety buffer. It performs memory evaluation periodically at a set frequency. When memory usage reaches the preset quota, it automatically serializes and spills the data to disk, realizing dynamic monitoring and intelligent management of memory usage. It can stably process massive amounts of data in a limited memory environment. This method decouples the database connection lifecycle from business processing logic, only occupying the connection during the data retrieval phase, and immediately releasing the result set and database connection after the data caching is complete. This significantly shortens the connection holding time, accelerates the turnover of connection pool resources, effectively avoids the exhaustion of connection resources, and improves the overall concurrent processing capability of the system. Attached Figure Description

[0007] Figure 1This is a flowchart of the steps of the localized caching and resettable access method for JDBC streaming result sets proposed in this invention. Detailed Implementation

[0008] To provide a further understanding of the purpose, structure, features, and functions of the present invention, detailed descriptions are provided below with reference to specific embodiments.

[0009] Please refer to the reference. Figure 1 This invention provides a localized caching and resettable access method for JDBC streaming result sets, including the following sub-steps: S1: Create a smart spill result set container object; S11: Initialize the smart spill result set container object; The application calls the constructor of the smart spill result set container to create a smart spill result set container object and passes the configuration parameters to the container object to complete the initialization of the container object; the configuration parameters include the maximum memory quota and the memory check frequency; Set the maximum memory quota passed in to the maximum memory quota of the container object, and set the memory check frequency passed in to the memory check frequency of the container object. The maximum memory quota limits the maximum number of bytes that the container object can occupy in main memory, and the memory check frequency specifies that a memory usage assessment should be performed after processing a specified number of rows of data. Initialize the block-linked list of the container object to an empty list, initialize the spill file list of the container object to an empty list, and set the total number of rows used to count the data in the ResultSet to 0; The block-linked list is used to store each row of data read from the result set. It consists of multiple interconnected nodes. Each node contains two parts: one is an array of data blocks of type Object, which is used to store multiple rows of data; the other is a successor pointer pointing to the next node. All nodes are connected through successor pointers. Data rows within the same node are stored in contiguous memory space according to the index order of the data block array; adjacent nodes are connected across memory regions through successor pointers; Furthermore, when new data is added (i.e., each new row of data read from the result set), a node is created and the node is associated with the successor pointer of the previous node; The spill file list is a List collection, and the elements of the collection are spill file objects, which are used to identify the actual spill files on the disk; S12: Obtain the estimated memory size for a single row of data; The application executes SQL statements to query the database and obtains a ResultSet result set through the JDBC interface; Retrieve the corresponding ResultSetMetaData metadata object from the ResultSet result set, iterate through each column in the ResultSetMetaData metadata object, obtain the metadata contained in each column, and calculate the actual bytes occupied by a single column of data in the heap memory; The metadata includes data type, column type name, column display size, column precision, scale, etc. Specifically, the number of bytes occupied by a single column of data in heap memory is calculated as follows: Based on a preset mapping rule table, the Java wrapper object corresponding to a single column of data is matched. The preset mapping rule table uses data type and column type name as a set of joint matching conditions, and each set of matching conditions is uniquely bound to a Java wrapper object. The fixed memory overhead for this column is calculated based on the known JVM heap memory model: The JVM heap memory model specifies that a 12-byte object header is allocated for Java wrapper objects. The fixed memory overhead for this column is obtained by adding the 12-byte object header to the fixed memory bytes corresponding to the built-in member variables of the Java wrapper object. The dynamic storage overhead is calculated based on the bound Java wrapper object: If the bound Java wrapper object is a string type, the column display size is read from the metadata, and the column display size is multiplied by the maximum number of bytes occupied by a single character to obtain the character array memory as the dynamic storage overhead for that column; if it is a high-precision decimal type, the column precision and scale are read from the metadata, and the column precision is used as the array length of the underlying numerical array. The array length is multiplied by the preset number of bytes occupied by a single number to obtain the memory bytes occupied by the underlying numerical array as the dynamic storage overhead for that column; the dynamic storage overhead for other types is 0. Add the fixed memory overhead and the dynamic storage overhead of the column to get the total memory usage of the column; pad the data with extra bytes according to the byte alignment rules set in the JVM heap memory model to get the actual number of bytes occupied by the data in the column in the heap. Add up the actual bytes occupied by all columns in the ResultSetMetaData metadata object to get the baseline memory for a single row; multiply the baseline memory for a single row by a preset buffer coefficient to get the estimated memory size for a single row of data. S13: Initialize column name information; Call the ResultSetMetaData metadata object's interface to get the total number of columns in the ResultSet result set, create an array of strings of equal length based on the total number of columns, and name it the column name array; Iterate through each column in the ResultSetMetaData metadata object, read the column name corresponding to each column stored in the ResultSetMetaData metadata object, and store the column names in order into the column name array; assign the filled column name array to the smart spill result set container object as the column name information of the smart spill result set container object.

[0010] S2: Write the data in the ResultSet to the smart overflow result set container object; S21: Read and encapsulate data line by line; The resultSet.next method is used to determine whether the next row of data exists in the resultSet. If it does not exist, proceed to step S24; if it does exist, the resultSet.getObject method is called in a loop to read all column values ​​corresponding to the current row and encapsulates all column values ​​into an Object array with a length equal to the total number of columns. The Object array is written into a block-linked list. Each time a row of data is written, the total number of rows in the smart spill result set container object is incremented by 1. At the same time, the current cache line count in the block-linked list is incremented by 1. The current cache line count in the block-linked list is used to count the number of data rows to be spilled in the current block-linked list. S22: Dynamic memory assessment and spill triggering; After successfully writing a single row of data to the block-linked list, check whether the current cache line count in the block-linked list is an integer multiple of the preset memory check frequency. If it is not an integer multiple, continue reading the next row of data in the ResultSet; if it is an integer multiple, perform a memory evaluation. The memory evaluation method is as follows: Multiply the estimated memory size of the single row of data obtained in advance by the current cache line count in the current block linked list to obtain the estimated memory usage in the linked list; The estimated memory usage is compared with the maximum memory quota. If the estimated memory usage is greater than the maximum memory quota, it is determined that the memory has reached the limit and proceeds to step S23; if the estimated memory usage is less than or equal to the maximum memory quota, the next row of data in the ResultSet is read. S23: Perform a data spill operation; The serialization framework (such as Protobuf, Kryo, FST, etc.) is invoked to use the block-linked list as the serialization source object, and the corresponding serialization interface is called to serialize the block-linked list into a binary byte stream; Create a uniquely named disk file in a preset temporary directory, write the binary byte stream to the disk file through a file output stream, close the file output stream and generate a corresponding spill file object; add the spill file object to the spill file list; Reset the current cache line count of the block-linked list to 0; S24: Release the ResultSet resource; Once all data in the ResultSet has been read and processed, call the close method to close the ResultSet and release the associated database connection resources.

[0011] S3: Access data through a unified data iterator of a smart spill result set container object; The application calls the iterator method of the smart spill result set container object to generate a unified data iterator, and accesses the data through the unified data iterator. The specific access method is as follows: S31: Traverse the data in the block-linked list that has not undergone data overflow operations; Determine whether there is any data in the current block-chain linked list that has not been written out. If it exists, first traverse the data, and then proceed to step S32 after the traversal is complete; otherwise, proceed directly to step S32. S32: Traverse the spill files in the disk files and read them; Read the spill files in the spill file list in sequence, open the file input stream, and use the serialization framework to deserialize the binary byte stream in the disk file back into a block-linked list. The block-linked list interface is called to generate a list iterator. The list iterator is used to read the data in the restored block-linked list line by line. After reading all the data in the restored block-linked list, the file input stream is closed. Get the next spill file in the spill file list, and execute the above steps in step S32 until all spill files in the spill file list have been traversed and processed, then the iteration ends. If you need to iterate through all the data again from the beginning, simply call the iterator method of the smart spill result set container to get a new iterator.

[0012] This method relies on a hybrid storage mode of memory and disk and a unified iterator interface to support multiple data reset traversals without repeatedly executing SQL statements. This saves database computing power, ensures data consistency during multiple rounds of data processing, and masks the storage differences between memory and disk, simplifying code logic and reducing development workload. A dynamic memory detection mechanism is introduced, combined with a preset memory threshold. When memory usage approaches the upper limit, data blocks are automatically serialized and spilled to disk. This mechanism ensures that there are no memory spikes in the entire data processing process, achieves smooth control of memory usage, and ensures stable processing of tens of millions of data volumes in a limited memory environment.

[0013] By shielding the differences between memory and disk storage media through a unified iterator interface, business code does not need to be aware of the underlying data location or write additional adaptation logic; development complexity is significantly reduced, code volume is reduced, development efficiency is significantly improved, and system maintainability is enhanced.

[0014] Breaking away from the traditional "one-time consumption" limitation of streaming result sets, it supports multi-round and multi-dimensional processing operations on the full dataset, such as full deduplication, cross-stage statistics, and step-by-step export. It can be widely adapted to complex business scenarios such as data cleaning, cross-system data synchronization, and full report generation, thus improving business scalability.

[0015] The present invention has been described in the above-described embodiments; however, these embodiments are merely examples for implementing the present invention. It must be noted that the disclosed embodiments do not limit the scope of the present invention. Conversely, any modifications and refinements made without departing from the spirit and scope of the present invention are within the scope of patent protection of the present invention.

Claims

1. A localized caching and resettable access method for JDBC streaming result sets, characterized in that: Includes the following steps: S1: Create a smart spill result set container object; S11: Initialize the smart spill result set container object; Initialize the block-linked list of the container object to an empty list, initialize the spill file list of the container object to an empty list, and set the total number of rows used to count the data in the ResultSet to 0; The spill file list is a List collection, and the elements of the collection are file objects, which are used to identify the actual spill files on the disk; S12: Obtain the estimated memory size for a single row of data; The application executes SQL statements to query the database and obtains a ResultSet result set through the JDBC interface; Retrieve the corresponding ResultSetMetaData metadata object from the ResultSet result set; S13: Initialize column name information; Call the ResultSetMetaData metadata object's interface to get the total number of columns in the ResultSet result set, create an array of strings of equal length based on the total number of columns, and name it the column name array; Iterate through each column in the ResultSetMetaData metadata object, read the column name corresponding to each column stored in the ResultSetMetaData metadata object column by column, and store the column names in order into the column name array; assign the filled column name array to the smart spill result set container object as the column name information of the smart spill result set container object; S2: Write the data in the ResultSet to the smart overflow result set container object; S21: Read and encapsulate data line by line; The resultSet.next method is used to determine whether the next row of data exists in the resultSet. If it does not exist, proceed to step S24; if it does exist, the resultSet.getObject method is called in a loop to read all column values ​​corresponding to the current row and encapsulates all column values ​​into an Object array with a length equal to the total number of columns. Write the Object array into a block-linked list; S22: Dynamic memory assessment and spill triggering; After successfully writing a single row of data to the block-linked list, check whether the current cache line count in the block-linked list is an integer multiple of the preset memory check frequency. If it is not an integer multiple, continue reading the next row of data in the ResultSet; if it is an integer multiple, perform a memory evaluation. The memory evaluation method is as follows: Multiply the estimated memory size of the single row of data obtained in advance by the current cache line count in the current block linked list to obtain the estimated memory usage in the linked list; Compare the estimated memory usage with the maximum memory quota. If the estimated memory usage is greater than the maximum memory quota, it is determined that the memory has reached the limit and proceed to step S23. If the estimated memory usage is less than or equal to the maximum memory quota, continue reading the next row of data in the ResultSet; S23: Perform a data spill operation; S24: Release the ResultSet resource; S3: Access data through a unified data iterator of a smart spill result set container object; The application calls the iterator method of the smart spill result set container object to generate a unified data iterator, and accesses the data through the unified data iterator.

2. The method for localized caching and resettable access to JDBC streaming result sets as described in claim 1, characterized in that: The specific contents of steps S11 and S12 are as follows: S11: Initialize the smart spill result set container object; The application calls the constructor of the smart spill result set container to create a smart spill result set container object and passes the configuration parameters to the container object to complete the initialization of the container object; the configuration parameters include the maximum memory quota and the memory check frequency; Set the maximum memory quota passed in to the maximum memory quota of the container object, and set the memory check frequency passed in to the memory check frequency of the container object. S12: Obtain the estimated memory size for a single row of data; Iterate through each column in the ResultSetMetaData metadata object, retrieve the metadata contained in each column, and calculate the actual bytes occupied by a single column of data in the heap memory; The metadata includes data type, column type name, column display size, column precision, and scale; Add up the actual bytes occupied by all columns in the ResultSetMetaData metadata object to get the baseline memory for a single row; multiply the baseline memory for a single row by a preset buffer coefficient to get the estimated memory size for a single row of data.

3. The method for localized caching and resettable access to JDBC streaming result sets as described in claim 1, characterized in that: In step S21, for each row of data written, the total number of rows in the smart spill result set container object is incremented by 1; at the same time, the count of the current cached row in the block-linked list is incremented by 1. In step S23, the serialization framework is invoked to use the block-linked list as the serialization source object, and the corresponding serialization interface is invoked to serialize the block-linked list into a binary byte stream. Create a uniquely named disk file in a preset temporary directory, write the binary byte stream to the disk file through a file output stream, close the file output stream and generate a corresponding spill file object; add the spill file object to the spill file list; Reset the current cache line count of the block-linked list to 0; S24: Release the ResultSet resource; Once all data in the ResultSet has been read and processed, call the close method to close the ResultSet and release the associated database connection resources.

4. The method for localized caching and resettable access to JDBC streaming result sets as described in claim 1, characterized in that: The specific access method for step S3 is as follows: S31: Traverse the data in the block-linked list that has not undergone data overflow operations; Determine whether there is any data in the current block-chain linked list that has not been written out. If it exists, first traverse the data, and then proceed to step S32 after the traversal is complete; otherwise, proceed directly to step S32. S32: Traverse the spill files in the disk files and read them; Read the spill files in the spill file list in sequence, open the file input stream, and use the serialization framework to deserialize the binary byte stream in the disk file back into a block-linked list. The block-linked list interface is called to generate a list iterator. The list iterator is used to read the data in the restored block-linked list line by line. After reading all the data in the restored block-linked list, the file input stream is closed. Get the next spill file in the spill file list, and execute the above steps in step S32 until all spill files in the spill file list have been traversed and processed, then the iteration ends. If you need to iterate through all the data again from the beginning, simply call the iterator method of the smart spill result set container to get a new iterator.

5. The method for localized caching and resettable access to JDBC streaming result sets as described in claim 2, characterized in that: The method for calculating the number of bytes occupied by a single column of data in heap memory is as follows: Based on a preset mapping rule table, the Java wrapper object corresponding to a single column of data is matched. The preset mapping rule table uses data type and column type name as a set of joint matching conditions, and each set of matching conditions is uniquely bound to a Java wrapper object. The fixed memory overhead for this column is calculated based on the known JVM heap memory model: The JVM heap memory model specifies that a 12-byte object header is allocated for Java wrapper objects. The fixed memory overhead for this column is obtained by adding the 12-byte object header to the fixed memory bytes corresponding to the built-in member variables of the Java wrapper object. The dynamic storage overhead is calculated based on the bound Java wrapper object: If the bound Java wrapper object is a string type, the column display size is read from the metadata, and the column display size is multiplied by the maximum number of bytes occupied by a single character to obtain the character array memory as the dynamic storage overhead of that column; if it is a high-precision decimal type, the column precision and scale are read from the metadata, and the column precision is used as the array length of the underlying numerical array. The array length is multiplied by the preset number of bytes occupied by a single number to obtain the memory bytes occupied by the underlying numerical array as the dynamic storage overhead of that column. The dynamic storage overhead for other types is 0; Add the fixed memory overhead of this column to the dynamic storage overhead of this column to get the total memory usage of this column; The padding bytes are filled based on the byte alignment rules set in the JVM heap memory model to obtain the actual number of bytes occupied by the data in this column in the heap.

Images