Database-based file processing method and device, storage medium and electronic equipment

Abstract

The application discloses a kind of based on database's file processing method, device, storage medium and electronic equipment.It relates to big data technology field, financial technology field or other related fields.The method comprises: obtaining file download instruction;Based on file download instruction, obtain the file configuration information of target file, and based on file download instruction and file configuration information, obtain the original business data of target file from database, wherein file download instruction is independently executed by target thread pool in N thread pool;Based on file configuration information and original business data, generate target SQL statement, wherein target SQL statement is used to adjust the data format of original business data;Execute target SQL statement, generate target file, and store target file to server.The application solves the technical problem that the effect of data acquisition from a single database based on a fixed template, asynchronous file generation, file generation and download is not good in related technologies.

Description

Technical Field

[0001] This invention relates to the fields of big data technology, fintech, or other related fields. Specifically, it relates to a database-based file processing method, apparatus, storage medium, and electronic device. Background Technology

[0002] Currently, file generation and downloading form a crucial infrastructure system in most applications. Most file generation methods rely on open-source tools like FrameMaker (a combination of web templates and data models) and POI (an open-source library) to generate files using fixed templates. New file types require separate template configuration and code development. For large files, asynchronous generation is necessary, storing the generated files on the current server for download.

[0003] Because the files in these technologies are generated based on fixed templates, each new file type requires a separate template configuration, resulting in a large workload. After development, the scalability is poor, making it difficult to adjust the position and format of fields within the template. Adding new file types requires redeveloping corresponding file templates and content, and the necessary data can only be obtained from a single database. Regarding file downloads, the asynchronous generation and storage methods used in these technologies easily lead to queue congestion. There is a lack of effective control over the generation and queue management of large files, and the absence of a public file storage server often results in disk exhaustion, long processing times, and slow downloads if not handled properly.

[0004] There is currently no effective solution to the above problems. Summary of the Invention

[0005] This invention provides a database-based file processing method, apparatus, storage medium, and electronic device to at least solve the technical problems in related technologies such as poor performance in retrieving data from a single database based on a fixed template, asynchronously generating files, and file generation and downloading.

[0006] According to one aspect of the present invention, a database-based file processing method is provided, comprising: obtaining a file download instruction, wherein the file download instruction is used to request the download of a target file; based on the file download instruction, obtaining file configuration information of the target file, and based on the file download instruction and the file configuration information, obtaining original business data of the target file from a database, wherein the file download instruction is executed independently through a target thread pool among N thread pools, the file configuration information includes at least: the file format of the target file, field information of the target file, and a file type identifier, the target thread pool is used to process download instructions associated with files of the same file type as the target file, and N is a positive integer greater than 1; generating a target SQL statement based on the file configuration information and the original business data, wherein the target SQL statement is used to adjust the data format of the original business data; executing the target SQL statement to generate the target file, and storing the target file in a server, wherein the server is used to provide files that clients need to download.

[0007] Further, based on the file configuration information and the original business data, a target SQL statement is generated, including: adding the original business data to a data table, wherein the data table is used to temporarily store the original business data, and the field order of the data table is consistent with the field order number in the configuration information; and generating the target SQL statement based on the field information of the data table and the file configuration information.

[0008] Further, based on the field information of the data table and the file configuration information, the target SQL statement is generated, including: generating column names based on the field sequence number and the field information of the data table, wherein the column names are used to locate data in the data table; and generating the target SQL statement based on the field information of the target file, the field adjustment function, and the column names.

[0009] Furthermore, before obtaining the file download instruction, the method further includes: obtaining basic configuration information of the target file, wherein the basic configuration information includes at least: the file format of the target file, file type identifier, information of the database, and data query statement for querying data in the database; obtaining field information of the target file, wherein the field information of the target file includes at least one of the following: field format, processing rules of the field format, and field sequence number; and determining the file configuration information based on the basic configuration information and the field information of the target file.

[0010] Furthermore, the information in the file download instruction includes at least: query conditions and the file type identifier. Based on the file download instruction and the file configuration information, obtaining the original business data of the target file from the database includes: connecting to the database based on the file type identifier in the file download instruction; generating a target query statement based on the data query statement in the query conditions and the file configuration information; and obtaining the original business data from the database through the target query statement.

[0011] Further, storing the target file on the server includes: generating a primary key identifier for the target file and establishing an association between the primary key identifier and the file download instruction; adding the primary key identifier to the download primary key field of the download instruction table, storing the target file on the server, and generating a subdirectory named after the primary key identifier, wherein the download instruction table includes at least: a file type identifier field, a query condition field, and a download primary key field.

[0012] Furthermore, after storing the target file on the server, the method further includes: responding to a file download command initiated by the target object, verifying the download permission of the target object, and if the verification result of the download permission indicates that the verification is successful, obtaining the primary key identifier based on the download instruction table; querying the subdirectory named with the primary key identifier based on the primary key identifier; and downloading the target file in the subdirectory named with the primary key identifier.

[0013] Furthermore, the download permission includes at least: whether to allow downloading the target file, a threshold for the number of times the target file can be downloaded, and a time period during which the target file can be downloaded.

[0014] Furthermore, after obtaining the file download instruction, the method further includes: selecting the target thread pool from the N thread pools based on the file type identifier of the target file; adding the file download instruction to a queue in the target thread pool, wherein the queue is used to control the asynchronous execution of download instructions associated with files of the same file type.

[0015] According to another aspect of the present invention, a database-based file processing apparatus is also provided, comprising: a first acquisition unit, configured to acquire a file download instruction, wherein the file download instruction is used to request the download of a target file; a first processing unit, configured to acquire file configuration information of the target file based on the file download instruction, and acquire original business data of the target file from a database based on the file download instruction and the file configuration information, wherein the file download instruction is executed independently through a target thread pool in N thread pools, the file configuration information includes at least: the file format of the target file, field information of the target file, and file type identifier, the target thread pool is used to process download instructions associated with files of the same file type as the target file, and N is a positive integer greater than 1; a generation unit, configured to generate a target SQL statement based on the file configuration information and the original business data, wherein the target SQL statement is used to adjust the data format of the original business data; and a second processing unit, configured to execute the target SQL statement, generate the target file, and store the target file in a server, wherein the server is used to provide files that clients need to download.

[0016] Further, the generation unit includes: an adding subunit, used to add the original business data to a data table, wherein the data table is used to temporarily store the original business data, and the field order of the data table is consistent with the field order number in the configuration information; and a first generation subunit, used to generate the target SQL statement based on the field information of the data table and the file configuration information.

[0017] Further, the first generation subunit includes: a first generation module, used to generate column names based on the field sequence number and the field information of the data table, wherein the column names are used to locate data in the data table; and a second generation module, used to generate the target SQL statement based on the field information of the target file, the field adjustment function, and the column names.

[0018] Furthermore, the database-based file processing device further includes: a second acquisition unit, configured to acquire basic configuration information of the target file before acquiring a file download instruction, wherein the basic configuration information includes at least: the file format of the target file, a file type identifier, information of the database, and a data query statement for querying data in the database; a third acquisition unit, configured to acquire field information of the target file, wherein the field information of the target file includes at least one of the following: field format, processing rules for the field format, and field sequence number; and a determination unit, configured to determine the file configuration information based on the basic configuration information and the field information of the target file.

[0019] Furthermore, the information in the file download instruction includes at least: query conditions and the file type identifier. The first processing unit includes: a connection subunit, used to connect to the database based on the file type identifier in the file download instruction; a second generation subunit, used to generate a target query statement based on the query conditions and the data query statement in the file configuration information; and an acquisition subunit, used to acquire the original business data from the database through the target query statement.

[0020] Furthermore, the second processing unit includes: a third generation subunit, used to generate a primary key identifier for the target file and establish an association between the primary key identifier and the file download instruction; and a storage subunit, used to add the primary key identifier to the download primary key field of the download instruction table, store the target file on the server, and generate a subdirectory named after the primary key identifier, wherein the download instruction table includes at least: a file type identifier field, a query condition field, and a download primary key field.

[0021] Furthermore, the database-based file processing device further includes: a third processing unit, configured to, after storing the target file on the server, respond to a file download command initiated by the target object, verify the download permission of the target object, and, if the verification result of the download permission indicates that the verification is successful, obtain the primary key identifier based on the download instruction table; a query unit, configured to query a subdirectory named after the primary key identifier based on the primary key identifier; and a download unit, configured to download the target file from the subdirectory named after the primary key identifier.

[0022] Furthermore, the download permission includes at least: whether to allow downloading the target file, a threshold for the number of times the target file can be downloaded, and a time period during which the target file can be downloaded.

[0023] Furthermore, the database-based file processing device further includes: a selection unit, configured to select a target thread pool from N thread pools based on the file type identifier of the target file after obtaining a file download instruction; and an adding unit, configured to add the file download instruction to a queue in the target thread pool, wherein the queue is used to control the asynchronous execution of download instructions associated with files of the same file type.

[0024] According to another aspect of the present invention, an electronic device is also provided, including: a processor; and a memory for storing executable instructions of the processor; wherein the processor is configured to execute the database-based file processing method of any one of the above-mentioned methods by executing the executable instructions.

[0025] According to another aspect of the present invention, a computer-readable storage medium is also provided, which stores a computer program, wherein the computer program controls the device where the computer-readable storage medium is located to execute any of the above-mentioned database-based file processing methods when it is running.

[0026] In this invention, a file download instruction is obtained, which requests the download of a target file. Based on the file download instruction, file configuration information of the target file is obtained. Then, based on the file download instruction and the file configuration information, the original business data of the target file is retrieved from the database. The file download instruction is executed independently through a target thread pool from N thread pools. The file configuration information includes at least: the file format of the target file, the field information of the target file, and the file type identifier. The target thread pool is used to process download instructions associated with files of the same file type as the target file, where N is a positive integer greater than 1. Based on the file configuration information and the original business data, a target SQL statement is generated, which adjusts the data format of the original business data. The target SQL statement is executed to generate the target file, which is then stored on a server. The server is used to provide the file that the client needs to download. This solves the technical problems of related technologies, such as the poor performance of retrieving data from a single database based on a fixed template, asynchronously generating files, and the ineffective file generation and downloading processes. In this invention, a target SQL statement is dynamically generated based on the file download instruction and file configuration information. The data format of the file is adjusted through the target SQL statement, avoiding the situation in related technologies where files are generated using fixed templates and are difficult to adjust flexibly. This achieves the technical effect of improving the flexibility of file generation. Attached Figure Description

[0027] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this application, illustrate exemplary embodiments of the invention and, together with their description, serve to explain the invention and do not constitute an undue limitation thereof. In the drawings:

[0028] Figure 1 This is a flowchart of an optional database-based file processing method according to an embodiment of the present invention;

[0029] Figure 2 This is a schematic diagram of an optional file configuration information configuration page according to an embodiment of the present invention;

[0030] Figure 3 This is a schematic diagram of an optional database-based file processing apparatus according to an embodiment of the present invention;

[0031] Figure 4 This is an internal structure diagram of an optional file configuration module according to an embodiment of the present invention;

[0032] Figure 5 This is an internal structure diagram of an optional asynchronous scheduling module according to an embodiment of the present invention;

[0033] Figure 6 This is an internal structure diagram of an optional data acquisition module according to an embodiment of the present invention;

[0034] Figure 7 This is an internal structure diagram of an optional file generation module according to an embodiment of the present invention;

[0035] Figure 8 This is an internal structure diagram of an optional file storage module according to an embodiment of the present invention;

[0036] Figure 9 This is a schematic diagram of another optional database-based file processing apparatus according to an embodiment of the present invention;

[0037] Figure 10 This is a schematic diagram of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0038] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0039] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0040] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, and data displayed) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use, and processing of the relevant data must comply with the relevant laws, regulations, and standards of the relevant countries and regions, and corresponding operation entry points are provided for users to choose to authorize or refuse.

[0041] Example 1

[0042] According to an embodiment of the present invention, an optional database-based file processing method is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0043] Figure 1 This is a flowchart of an optional database-based file processing method according to an embodiment of the present invention, such as... Figure 1 As shown, the method includes the following steps:

[0044] Step S101: Obtain a file download instruction, wherein the file download instruction is used to request the download of the target file.

[0045] The aforementioned file download instructions may include, but are not limited to: application number, file type identifier, query conditions (which can be separated by "|" and selected by the user subsequently), submission time of the instruction, etc. The file type can be used to indicate the data format of the business data associated with the file downloaded by the download instruction, such as: field format, data format within the field, field order, etc.

[0046] Step S102: Based on the file download instruction, obtain the file configuration information of the target file, and based on the file download instruction and the file configuration information, obtain the original business data of the target file from the database. The file download instruction is executed independently through the target thread pool in N thread pools. The file configuration information includes at least: the file format of the target file, the field information of the target file and the file type identifier. The target thread pool is used to process the download instructions associated with files of the same file type as the target file. N is a positive integer greater than 1.

[0047] The aforementioned file configuration information may include, but is not limited to: file format (e.g., csv, xls, txt, zip, etc.), target file field information (e.g., field order, field data format), target file type identifier, database information where the data associated with the target file is located, and data retrieval statements for retrieving the data from the database. In order to flexibly adjust the file template of the generated file, in this embodiment, the file configuration information can be retrieved from the database storing the file configuration information. The file configuration information can be dynamically adjusted in the configuration page of the file configuration information. In addition, thread scheduling tools such as Quarts and Schedule can be used to replace the thread pool.

[0048] To avoid situations where the generation time of a single file is too long, causing queue congestion, or where an exception in a thread pool causes the entire file download system to become unavailable, the file type field can be used to distinguish and add independent thread pools for processing. That is, one thread pool processes target files of one file type.

[0049] Based on the file configuration identifier in the file download command, the file configuration information of the target file can be obtained. Based on the download conditions of the file download command and the data retrieval statement in the file configuration information, the original business data of the target file can be retrieved from the corresponding database.

[0050] Step S103: Based on the file configuration information and the original business data, generate the target SQL statement, which is used to adjust the data format of the original business data.

[0051] To avoid the problem of generating files based on fixed templates in related technologies, which requires separate template configuration for new file types, resulting in a large workload and poor scalability after development, the target SQL statement mentioned above can be used to adjust the data format of the original business data in this embodiment. That is, in this embodiment, the target SQL statement is dynamically generated based on the file configuration information and the original business data. For example, based on the file configuration information, a target SQL (Structured Query Language) statement is generated to adjust the data format of the original business data to the format described in the file configuration information.

[0052] Step S104: Execute the target SQL statement, generate the target file, and store the target file on the server. The server is used to provide the file that the client needs to download.

[0053] In this embodiment, the original business data can be converted into the format described in the file configuration information by executing the target SQL statement to obtain the target file. The target file is then stored on the server, and the server provides the client with a download service for the target file.

[0054] Through the above steps, in this embodiment, a target SQL statement is dynamically generated based on the file download instruction and file configuration information. The file's data format is then adjusted using this target SQL statement, avoiding the limitations of related technologies that generate files using fixed templates, which are difficult to adjust flexibly. This significantly improves the flexibility of file generation. Furthermore, it solves the technical problems of related technologies that rely on fixed templates to retrieve data from a single database, asynchronously generate files, and achieve poor results in file generation and downloading.

[0055] Optionally, based on the file configuration information and the original business data, a target SQL statement is generated, including: adding the original business data to a data table, wherein the data table is used to temporarily store the original business data, and the field order of the data table is consistent with the field order number in the configuration information; and generating the target SQL statement based on the field information of the data table and the file configuration information.

[0056] In this embodiment, the original business data can be added to a data table used for temporary storage of the original business data, and then the target SQL statement can be generated based on the field information and file configuration information in the temporary table.

[0057] For example: If the field structure of the public temporary table (TEMP_COMMON) (corresponding to the data table) is as shown in Table 1:

[0058] Table 1

[0059]

[0060] The default field order in the file configuration information corresponds one-to-one with the original business data in the public temporary table. The file sequence number can be concatenated to the "COLNUM" field to form the required column name. The data format can be adjusted using the field adjustment function. The generated dynamic SQL (corresponding to the target SQL statement) is: Select FUNC_RPAD(COLNUM1,4,CHR(32)), FUNC_RPAD(COLNUM2,2,CHR(32)), FUNC_RPAD(COLNUM3,20,CHR(32)), FUNC_RPAD(COLNUM4,30,CHR(32))FROM TEMP_COMMON where APPNO=1.

[0061] It should be noted that after generating the target SQL statement, the data format of the target file can still be adjusted, for example:

[0062] After the file format was adjusted (e.g., the payment notification number and transaction category were swapped, and the length of the payment notification number field increased), the adjustments are shown in Table 2:

[0063] Table 2

[0064] File type Column names Data generation rules File sequence number Document A Transaction serial number X:24 1 Document A Payment Notice Number X:25 3 Document A Transaction Category X:2 2 Document A Annuity Plan Code X:30 4

[0065] The dynamic SQL statement reading rules will also be automatically adjusted after configuration, and will be adjusted to: Select FUNC_RPAD(COLNUM1,4,CHR(32)), FUNC_RPAD(COLNUM3,25,CHR(32)), FUNC_RPAD(COLNUM2,2,CHR(32)), FUNC_RPAD(COLNUM4,30,CHR(32))FROM TEMP_COMMON where APPNO=1.

[0066] Optionally, based on the field information and file configuration information of the data table, a target SQL statement is generated, including: generating column names based on the field sequence number and the field information of the data table, wherein the column names are used to locate the data in the data table; and generating a target SQL statement based on the field information, field adjustment functions, and column names of the target file.

[0067] You can concatenate the file sequence number to the "COLNUM" field to form the required column name, and use the field adjustment function (FUNC_RPAD) to adjust the data format (format adjustment such as: field padding, number decimal adjustment, date format conversion, etc.) to generate dynamic SQL (corresponding to the target SQL statement).

[0068] Optionally, before obtaining the file download instruction, the method further includes: obtaining basic configuration information of the target file, wherein the basic configuration information includes at least: the target file's file format, file type identifier, database information, and data query statements for querying data in the database; obtaining the target file's field information, wherein the target file's field information includes at least one of the following: field format, field format processing rules, and field sequence number; and determining file configuration information based on the basic configuration information and the target file's field information.

[0069] In this embodiment, basic configuration information for the target file can be set, for example:

[0070] 1. Set file name / file type identifier;

[0071] 2. Set the file format, such as generating csv, xls, txt, and zip files. You can also store the settings in a database for use in generating target files.

[0072] 3. Set the database where the data resides and the data retrieval statement.

[0073] The basic configuration information is recorded in Table 3:

[0074] Table 3

[0075]

[0076] In this embodiment, field information of the target file can also be set, for example:

[0077] Set file fields (corresponding to field formats) and format processing rules, and specify the field order for generating file content. The field order and field format can be set by the front-end operator, which facilitates subsequent file modifications and reduces the development workload required for later maintenance of the target file.

[0078] Examples of field format processing rules:

[0079] If the character type is X:10, then the character data will be automatically padded to 10 characters.

[0080] Numeric type: N:17,2, retain two decimal places, automatically convert 1 to 1.00.

[0081] Date type: D:yyyymmdd, converts the date to 20200101 format.

[0082] Figure 2 This is a schematic diagram of an optional file configuration information configuration page according to an embodiment of the present invention, such as... Figure 2 As shown, this page can include field definitions, selections, field names, Chinese descriptions, types, lengths, remarks, and field order. The page also includes a field editing section, specifically: input boxes for field name, Chinese description, type, length, and remarks; and buttons for add, modify, delete, insert, move up, move down, confirm, and cancel.

[0083] The file configuration information can be composed of the basic configuration information and field information of the target file. After determining the file configuration information, then... Figure 2 For example, the database record format is shown in Table 4, which includes the transaction serial number, transaction list, payment notice number, and the client's annuity plan code.

[0084] Table 4

[0085] File type Column names Data generation rules File sequence number Document A Transaction serial number X:24 1 Document A Transaction Category X:2 2 Document A Payment Notice Number X:20 3 Document A Annuity Plan Code X:30 4

[0086] The generation order and rules of the file fields in Table 4 can be dynamically configured and adjusted by the user, such as... Figure 2 The operation page allows users to move up and down, add, and modify field formats. The final modified results can also be recorded in the database to generate target files, achieving the technical effect of flexibly adjusting the data format of target files or generating file templates.

[0087] Optionally, the information in the file download instruction includes at least: query conditions and file type identifier. Based on the file download instruction and file configuration information, the original business data of the target file is obtained from the database, including: connecting to the database based on the file type identifier in the file download instruction; generating a target query statement based on the data query statement in the query conditions and file configuration information; and obtaining the original business data from the database through the target query statement.

[0088] Table 5 provides information included in an optional file download command, such as: application number, file type, search criteria, submission time, and download key.

[0089] Table 5

[0090] Application number File type Query conditions Submission time Download key 001 Document A Company Name | 2020 2023-01-01 12:00:00 002 Document B Company Name | Employee Name 2023-01-01 13:00:00

[0091] In this embodiment, the data retrieval statement (corresponding to the data query statement) in the file configuration information representing the file type in the file download instruction can be combined with the query conditions in the file download instruction to connect to the corresponding database (MYSQL / ORACLE / GAUSSDB). By obtaining the data query statement and query conditions, a target query statement can be formed to retrieve the original business data from the database and insert it into a common temporary table, thus achieving the technical effect of accurately retrieving the original business data.

[0092] Optionally, storing the target file on the server includes: generating a primary key identifier for the target file and establishing an association between the primary key identifier and the file download instruction; adding the primary key identifier to the download primary key field in the download instruction table, storing the target file on the server, and generating a subdirectory named after the primary key identifier. The download instruction table includes at least: a file type identifier field, a query condition field, and a download primary key field.

[0093] In this embodiment, a unique primary key ID (corresponding to the primary key identifier) ​​of the target file can be generated, associated with the file download instruction, recorded in the download instruction table (as shown in Table 6), the file is uploaded to the public storage server (corresponding to the server), a subdirectory named with the primary key ID is generated, and the target file is placed in it. By saving the target file, the technical effect of improving the download speed of subsequent target files is achieved.

[0094] Table 6

[0095] Application number File type Query conditions Submission time Download key 001 Document A Company Name | 2020 2023-01-01 12:00:00 Primary key ID1 002 Document B Company Name | Employee Name 2023-01-01 13:00:00 Primary key ID2

[0096] Optionally, after storing the target file on the server, the method further includes: responding to a file download command initiated by the target object, verifying the download permission of the target object, and if the verification result indicates that the verification is successful, obtaining the primary key identifier based on the download instruction table; querying the subdirectory named after the primary key identifier based on the primary key identifier; and downloading the target file from the subdirectory named after the primary key identifier.

[0097] In this embodiment, for the successfully generated target file, the generated file type, generation time, and generation count can be recorded in the log to control download permissions for file types containing sensitive customer information. After the target object initiates a file download command, the download permissions of the target object can be verified based on the data recorded in the log. If the verification result indicates that the download permission has passed, the primary key identifier is obtained based on the download instruction table. Based on the primary key identifier, the subdirectory named with the primary key identifier is queried, and the target file is downloaded from the subdirectory named with the primary key identifier.

[0098] For example, when a user queries a file download command, after the permission verification is successful, the corresponding download key (i.e., the primary key identifier) ​​is sent. The corresponding file is then retrieved from the relevant subdirectory on the file download server for the user to download. This achieves the technical effect of improving the download efficiency of the target file.

[0099] Optionally, download permissions may include at least: whether to allow downloading the target file, a threshold for the number of times the target file can be downloaded, and a time period during which the target file can be downloaded.

[0100] Download permissions can include: the number of downloads per user per time period (e.g., per day), time periods during which downloads are not allowed (e.g., downloads are not allowed on weekends and holidays), and the ability for users to only download files they have submitted. This achieves the technical effects of improving data security and reducing system load.

[0101] Optionally, the database-based file processing device further includes: a selection unit, configured to select a target thread pool from N thread pools based on the file type identifier of the target file after obtaining the file download instruction; and an adding unit, configured to add the file download instruction to a queue in the target thread pool, wherein the queue is used to control the asynchronous execution of download instructions associated with files of the same file type.

[0102] In this embodiment, file download instructions for generating the same file type can be added to a queue in the same thread pool. The queue in the thread pool controls the asynchronous execution of file download instructions of the same file type. For example, a file download instruction for generating file type A is first added to the queue in the target thread pool, and another file download instruction for generating file type A is added to the queue in the target thread pool later. The file download instruction added first can be executed first, and the one added later can be executed later. That is, by controlling the asynchronous execution of file download instructions of the same file type through the queue of the thread pool, the purpose of file download instructions of different file types can be executed concurrently, and file download instructions of the same file type can be executed asynchronously is achieved, avoiding the queue blocking situation that is easy to occur when processing multiple files through the same thread pool.

[0103] In this embodiment, the required field order and format can be flexibly defined based on database configuration, asynchronous thread pools, dynamic SQL, and file storage servers. Raw data can be obtained from various types of databases and processed into the final required file. For business scenarios such as adding new file types and modifying fields, the corresponding changes can be completed through client configuration, minimizing development workload and offering strong scalability. File generation is effectively controlled through a thread pool, avoiding the problem of the file download system becoming unavailable due to long generation times for a single file. The public file storage server effectively controls the use of file disk resources, improving the robustness and stability of the entire system.

[0104] Example 2

[0105] Embodiment 2 of this application provides an optional database-based file processing device. Figure 3 This is a schematic diagram of an optional database-based file processing apparatus according to an embodiment of the present invention, such as... Figure 3The system includes a file configuration module, an asynchronous scheduling module, a rule acquisition module, a data generation module, a file generation module, a file storage module, and a log recording module. The file configuration module allows users to set necessary rules for file generation, such as the file name, file format, field order, field format, database location, and data acquisition statements, which are configured in the corresponding tables in the database. The asynchronous scheduling module receives file generation instructions (corresponding to file download instructions) from the client and adds them to an independent thread pool for processing according to file type, avoiding queue blocking due to long generation times for individual files. The asynchronous scheduling module is not limited to the thread pool in this embodiment; thread scheduling tools such as Quarts and Scheduler can also be used. The rule acquisition module obtains user-preset sorting rules and data format conversion rules. The data generation module connects to the specified database using a system-preset query statement and input conditions to filter out the required data and stores it in a public temporary table (corresponding to the data table in Implementation 1). The file generation module uses preset field rules and the public temporary table to compose dynamic SQL (corresponding to the target SQL statement), selects the data required for the final file, and generates the file according to the specified format. The file storage module creates subdirectories based on the unique primary key generated by the system and stores the files for subsequent downloads. Logs are used to track the number of file downloads and download times, primarily for controlling download permissions and monitoring transaction volume.

[0106] Figure 4 This is an internal structure diagram of an optional file configuration module according to an embodiment of the present invention, such as... Figure 4 As shown, the file configuration module includes: file basic information 401, used to set basic file information, and file field information 402, used to set file field information.

[0107] Specifically, step 401: Setting basic file information:

[0108] 1) Set the file name / file type identifier;

[0109] 2) Set the file format. This system supports generating CSV, XLS, TXT, and ZIP files. The settings will be stored in the database for subsequent file generation.

[0110] 3) Set the database where the data is located and the data retrieval statement as shown in Table 3.

[0111] Step 402: Setting up file field information: Set up file fields and format processing rules, and specify the field order for generating file content. The field order and field format are set by the front-end operator, which facilitates subsequent file modifications and reduces the development workload required for later maintenance.

[0112] Example of field format processing rules:

[0113] 1) Character type: X:10, then the character data will be automatically padded to 10 digits.

[0114] 2) Numeric type: N:17,2, retain two decimal places, automatically convert 1 to 1.00.

[0115] 3) Date type: D:yyyymmdd, converts the date to 20200101 format.

[0116] The configuration results are recorded in the database, to Figure 2 For example, the database record format is shown in Table 4, which includes the transaction sequence number, transaction list, payment notice number, and client annuity plan code.

[0117] The generation order and rules of the file fields in Table 4 can be dynamically configured and adjusted by the user. Figure 2 The buttons for moving up, moving down, adding, and modifying fields adjust their formatting. The final results are recorded in the database for use in subsequent file generation.

[0118] Figure 5 This is an internal structure diagram of an optional asynchronous scheduling module according to an embodiment of the present invention. The following is in conjunction with... Figure 5 Explanation:

[0119] Step 501: The user selects the file to be generated, enters the generation conditions, and submits the file download instruction to the asynchronous scheduling module. The download instruction includes the application number, file type, query conditions (separated by "|", with subsequent data selected by the user), submission time, and other information, as shown in Table 5.

[0120] Step 502: The asynchronous scheduling module selects the file download command submitted in step 501, distinguishes it by the file type field, and adds it to an independent thread pool for processing. This avoids situations where the generation time of a single file is too long, causing queue blockage, or where an abnormality of a certain thread pool causes the entire file download system to become unavailable.

[0121] Figure 6 This is an internal structure diagram of an optional data acquisition module according to an embodiment of the present invention. The following is in conjunction with... Figure 6 Explanation:

[0122] Step 601: Based on the file type in the file download instruction, obtain the data retrieval statement set in step 401, and connect to the corresponding database (MYSQL / ORACLE / GAUSSDB) in combination with the query conditions in the instruction.

[0123] Step 602: Based on the query statement and query conditions obtained in step 401, form a query statement, query out the original business data (i.e., select the data from the file in the corresponding database), and insert it into the common temporary table.

[0124] The field structure of the public temporary table (TEMP_COMMON) is shown in Table 1.

[0125] Figure 7 This is an internal structure diagram of an optional file generation module according to an embodiment of the present invention. The following is in conjunction with... Figure 7 Explanation:

[0126] Step 701: Using the file rules set in step 402 and the raw data obtained in step 601, concatenate the dynamic SQL (corresponding to the target SQL statement in Example 1) to generate the data in the final business file.

[0127] Taking the configuration in step 402 as an example: the default field order and the original business data in the temporary table must correspond one-to-one. The file sequence number is appended to the "COLNUM" field to form the required column name. The data format is adjusted using the field adjustment function (FUNC_RPAD) (format adjustment: field padding, decimal place adjustment, date format conversion, etc.). The generated dynamic SQL is:

[0128] Select FUNC_RPAD(COLNUM1,4,CHR(32)), FUNC_RPAD(COLNUM2,2,CHR(32)), FUNC_RPAD(COLNUM3,20,CHR(32)), FUNC_RPAD(COLNUM4,30,CHR(32)) FROM TEMP_COMMONwhere APPNO=1;

[0129] After the file format is adjusted (e.g., the payment notice number and transaction category are swapped, and the length of the payment notice number field becomes longer), the above dynamic SQL statement reading rules will also be automatically adjusted after configuration: The dynamic SQL statement corresponding to Table 2 is: Select FUNC_RPAD(COLNUM1,4,CHR(32)), FUNC_RPAD(COLNUM3,25,CHR(32)), FUNC_RPAD(COLNUM2,2,CHR(32)), FUNC_RPAD(COLNUM4,30,CHR(32))FROM TEMP_COMMON where APPNO=1.

[0130] Step 702: Using the data obtained in step 501 and the file format configuration in step 201, generate a file in the specified format.

[0131] Step 703: Log the following for access control. For successfully generated files, the logging module records the file type, generation time, and number of generations. The logs are primarily used to control download permissions for file types containing sensitive customer information. Control methods for sensitive reports mainly include:

[0132] 1) Control the number of downloads per user per day;

[0133] 2) Downloads will not be allowed on weekends and holidays;

[0134] 3) You can only download the file download command that you submitted.

[0135] Figure 8 This is a diagram of the internal structure of the file storage module, shown below in conjunction with... Figure 8 For example:

[0136] Step 801: Generate a unique primary key ID, associate it with the file download instruction, record it in the download instruction table (as shown in Table 6), upload the file to the public storage server, and generate a subdirectory named after the primary key ID (e.g., ...). Figure 8 The subdirectory corresponding to file A (primary key ID1), the subdirectory corresponding to file B (primary key ID2), and the subdirectory corresponding to file N (primary key ID(N)) are placed into the file.

[0137] Step 802: Users can query file download instructions on the client. After the permission verification is passed, the corresponding download key is sent, and the corresponding file is retrieved from the corresponding subdirectory on the file download server for the user to download.

[0138] In this embodiment, the required field order and format can be flexibly defined based on database configuration, asynchronous thread pools, dynamic SQL, and file storage servers. Raw data can be obtained from various types of databases and processed into the final required file. For business scenarios such as adding new file types and modifying fields, the corresponding changes can be completed through client configuration, minimizing development workload and offering strong scalability. File generation is effectively controlled through a thread pool, avoiding the problem of the file download system becoming unavailable due to long generation times for a single file. The public file storage server effectively controls the use of file disk resources, improving the robustness and stability of the entire system.

[0139] Example 3

[0140] Embodiment 3 of this application provides another optional database-based file processing apparatus, wherein each implementation unit in the file processing apparatus corresponds to each implementation step in Embodiment 1.

[0141] Figure 9This is a schematic diagram of an optional database-based file processing apparatus according to an embodiment of the present invention, such as... Figure 9 As shown, the file processing device includes: a first acquisition unit 91, a first processing unit 92, a generation unit 93, and a second processing unit 94.

[0142] The first acquisition unit 91 is used to acquire a file download instruction, wherein the file download instruction is used to request the download of a target file;

[0143] The first processing unit 92 is used to obtain the file configuration information of the target file based on the file download instruction, and to obtain the original business data of the target file from the database based on the file download instruction and the file configuration information. The file download instruction is executed independently through the target thread pool in N thread pools. The file configuration information includes at least: the file format of the target file, the field information of the target file and the file type identifier. The target thread pool is used to process the download instructions associated with files of the same file type as the target file. N is a positive integer greater than 1.

[0144] The generation unit 93 is used to dynamically generate target SQL statements based on file configuration information and original business data. The target SQL statements are used to adjust the data format of the original business data.

[0145] The second processing unit 94 is used to execute the target SQL statement, generate the target file, and store the target file on the server, whereby the server is used to provide the file that the client needs to download.

[0146] In the database-based file processing apparatus provided in Embodiment 3 of this application, a file download instruction can be obtained through a first acquisition unit 91, which requests the download of a target file. Based on the file download instruction, a first processing unit 92 obtains the file configuration information of the target file and, based on the file download instruction and file configuration information, retrieves the original business data of the target file from the database. The file download instruction is executed independently through a target thread pool from N thread pools. The file configuration information includes at least the file format of the target file, the field information of the target file, and a file type identifier. The target thread pool is used to process download instructions associated with files of the same file type as the target file, where N is a positive integer greater than 1. Based on the file configuration information and the original business data, a generation unit 93 generates a target SQL statement, which is used to adjust the data format of the original business data. The target SQL statement is executed by a second processing unit 94 to generate a target file, which is then stored on a server. The server is used to provide the file that the client needs to download. This solves the technical problems in related technologies regarding the poor performance of data acquisition from a single database based on a fixed template, asynchronous file generation, and file generation and download. In this embodiment, a target SQL statement is dynamically generated based on the file download instruction and file configuration information. The data format of the file is adjusted through the target SQL statement, avoiding the situation in related technologies where files are generated using fixed templates and are difficult to adjust flexibly. This achieves the technical effect of improving the flexibility of file generation.

[0147] Optionally, in the database-based file processing apparatus provided in Embodiment 3 of this application, the generation unit includes: an adding subunit, used to add original business data to a data table, wherein the data table is used to temporarily store the original business data, and the field order of the data table is consistent with the field order number in the configuration information; and a first generation subunit, used to generate a target SQL statement based on the field information of the data table and the file configuration information.

[0148] Optionally, in the database-based file processing apparatus provided in Embodiment 3 of this application, the first generation subunit includes: a first generation module, used to generate column names based on field sequence numbers and field information of a data table, wherein the column names are used to locate data in the data table; and a second generation module, used to generate target SQL statements based on field information, field adjustment functions, and column names of a target file.

[0149] Optionally, in the database-based file processing apparatus provided in Embodiment 3 of this application, the database-based file processing apparatus further includes: a second acquisition unit, configured to acquire basic configuration information of a target file before acquiring a file download instruction, wherein the basic configuration information includes at least: the target file's file format, file type identifier, database information, and a data query statement for querying data in the database; a third acquisition unit, configured to acquire field information of the target file, wherein the target file's field information includes at least one of the following: field format, field format processing rules, and field sequence number; and a determination unit, configured to determine file configuration information based on the basic configuration information and the target file's field information.

[0150] Optionally, in the database-based file processing apparatus provided in Embodiment 3 of this application, the information in the file download instruction includes at least: query conditions and file type identifier. The first processing unit includes: a connection subunit for connecting to the database based on the file type identifier in the file download instruction; a second generation subunit for generating a target query statement based on the data query statement in the query conditions and file configuration information; and an acquisition subunit for acquiring raw business data from the database through the target query statement.

[0151] Optionally, in the database-based file processing apparatus provided in Embodiment 3 of this application, the second processing unit includes: a third generation subunit, used to generate a primary key identifier for the target file and establish an association between the primary key identifier and the file download instruction; and a storage subunit, used to add the primary key identifier to the download primary key field of the download instruction table, store the target file on the server, and generate a subdirectory named after the primary key identifier, wherein the download instruction table includes at least: a file type identifier field, a query condition field, and a download primary key field.

[0152] Optionally, in the database-based file processing apparatus provided in Embodiment 3 of this application, the database-based file processing apparatus further includes: a third processing unit, configured to, after storing the target file on the server, respond to a file download command initiated by the target object, verify the download permission of the target object, and, if the verification result of the download permission indicates that the verification is successful, obtain the primary key identifier based on the download instruction table; a query unit, configured to query the subdirectory named with the primary key identifier based on the primary key identifier; and a download unit, configured to download the target file from the subdirectory named with the primary key identifier.

[0153] Optionally, in the database-based file processing device provided in Embodiment 3 of this application, the download permission includes at least: whether to allow downloading the target file, a threshold for the number of times the target file can be downloaded, and a time period during which the target file can be downloaded.

[0154] Optionally, the database-based file processing device further includes: a selection unit, configured to select a target thread pool from N thread pools based on the file type identifier of the target file after obtaining the file download instruction; and an adding unit, configured to add the file download instruction to a queue in the target thread pool, wherein the queue is used to control the asynchronous execution of download instructions associated with files of the same file type.

[0155] The aforementioned database-based file processing device may further include a processor and a memory. The first acquisition unit 91, the first processing unit 92, the generation unit 93, and the second processing unit 94 are all stored in the memory as program units, and the processor executes the aforementioned program units stored in the memory to realize the corresponding functions.

[0156] The aforementioned processor contains a kernel, which retrieves the corresponding program units from memory. One or more kernels can be configured, and by adjusting kernel parameters, target SQL statements can be dynamically generated based on file download instructions and file configuration information. These target SQL statements then adjust the file's data format, avoiding the limitations of related technologies that generate files using fixed templates, thus significantly improving the flexibility of file generation.

[0157] The aforementioned memory may include non-permanent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0158] According to another aspect of the present invention, an electronic device is also provided, including: a processor; and a memory for storing executable instructions of the processor; wherein the processor is configured to execute the database-based file processing method of any one of the above-mentioned methods by executing the executable instructions.

[0159] According to another aspect of the present invention, a computer-readable storage medium is also provided, which stores a computer program, wherein the computer program controls the device where the computer-readable storage medium is located to execute any of the above-mentioned database-based file processing methods when it is running.

[0160] Figure 10 This is a schematic diagram of an electronic device according to an embodiment of the present invention, such as... Figure 10 As shown, an embodiment of the present invention provides an electronic device, which includes a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, it implements any of the above-mentioned database-based file processing methods.

[0161] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0162] In the above embodiments of the present invention, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0163] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units can be a logical functional division, and in actual implementation, there may be other division methods. For instance, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0164] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0165] Furthermore, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0166] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0167] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A database-based file processing method, characterized in that, include: Obtain a file download instruction, wherein the file download instruction is used to request the download of a target file; Based on the file type identifier of the target file, select an independent thread pool from N thread pools dedicated to handling download instructions associated with files of the same file type as the target file, thus obtaining the target thread pool; add the file download instructions to a queue in the target thread pool, wherein the queue is used to control the asynchronous execution of download instructions associated with files of the same file type, and N is a positive integer greater than 1; Based on the file download instruction, the file configuration information of the target file is obtained, and based on the file download instruction and the file configuration information, the original business data of the target file is obtained from the database. The file download instruction is executed independently through the target thread pool in N thread pools, and the file configuration information includes at least: the file format of the target file, the field information of the target file, and the file type identifier. Based on the file configuration information and the original business data, a target SQL statement is generated. The target SQL statement includes column names generated based on field sequence numbers, and a function call to perform format conversion on the data corresponding to the column names based on field adjustment functions. The target SQL statement is used to adjust the data format of the original business data. The target SQL statement is executed to generate the target file, and the target file is stored on the server, wherein the server is used to provide the file that the client needs to download.

2. The file processing method according to claim 1, characterized in that, Based on the file configuration information and the original business data, a target SQL statement is generated, including: The original business data is added to a data table, wherein the data table is used to temporarily store the original business data, and the field order of the data table is consistent with the field order number in the configuration information; The target SQL statement is generated based on the field information of the data table and the file configuration information.

3. The file processing method according to claim 2, characterized in that, Based on the field information of the data table and the file configuration information, the target SQL statement is generated, including: Based on the field sequence number and the field information of the data table, column names are generated, wherein the column names are used to locate the data in the data table; Based on the field information, field adjustment functions, and column names of the target file, the target SQL statement is generated.

4. The file processing method according to claim 1, characterized in that, Before obtaining the file download instruction, it also includes: Obtain the basic configuration information of the target file, wherein the basic configuration information includes at least: the file format of the target file, the file type identifier, the information of the database, and the data query statement for querying data in the database; Obtain the field information of the target file, wherein the field information of the target file includes at least one of the following: field format, processing rules of the field format, and field sequence number; Based on the basic configuration information and the field information of the target file, the file configuration information is determined.

5. The file processing method according to claim 1, characterized in that, The information in the file download instruction includes at least: query conditions, the file type identifier, and, based on the file download instruction and the file configuration information, the original business data of the target file is obtained from the database, including: Connect to the database based on the file type identifier in the file download instruction; Based on the query conditions and the data query statements in the file configuration information, a target query statement is generated; The original business data is obtained from the database using the target query statement.

6. The file processing method according to claim 1, characterized in that, Storing the target file on the server includes: Generate a primary key identifier for the target file and establish an association between the primary key identifier and the file download instruction; The primary key identifier is added to the download primary key field of the download instruction table, and the target file is stored on the server, generating a subdirectory named after the primary key identifier. The download instruction table includes at least: a file type identifier field, a query condition field, and a download primary key field.

7. The document processing method according to claim 6, characterized in that, After storing the target file on the server, the process also includes: In response to a file download command initiated by the target object, the download permission of the target object is verified, and if the verification result of the download permission indicates that the verification is successful, the primary key identifier is obtained based on the download instruction table; Based on the primary key identifier, query the subdirectories named with the primary key identifier; Download the target file from the subdirectory named after the primary key identifier.

8. The document processing method according to claim 7, characterized in that, The download permissions include at least: whether to allow downloading the target file, a threshold for the number of times the target file can be downloaded, and a time period during which the target file can be downloaded.

9. A database-based file processing device, characterized in that, include: The first acquisition unit is used to acquire a file download instruction, wherein the file download instruction is used to request the download of a target file; The selection unit is used to select an independent thread pool from N thread pools based on the file type identifier of the target file. This thread pool is dedicated to processing download instructions associated with files of the same file type as the target file, thus obtaining the target thread pool. The addition unit is used to add the file download instructions to a queue in the target thread pool. The queue is used to control the asynchronous execution of download instructions associated with files of the same file type, where N is a positive integer greater than 1. The first processing unit is configured to obtain the file configuration information of the target file based on the file download instruction, and obtain the original business data of the target file from the database based on the file download instruction and the file configuration information. The file download instruction is executed independently through the target thread pool in N thread pools, and the file configuration information includes at least: the file format of the target file, the field information of the target file, and the file type identifier. The generation unit is used to generate a target SQL statement based on the file configuration information and the original business data. The target SQL statement includes column names generated based on field sequence numbers and function calls that perform format conversion on the data corresponding to the column names based on field adjustment functions. The target SQL statement is used to adjust the data format of the original business data. The second processing unit is used to execute the target SQL statement, generate the target file, and store the target file on the server, wherein the server is used to provide the file that the client needs to download.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, wherein when the computer program is executed, it controls the device where the computer-readable storage medium is located to perform the database-based file processing method according to any one of claims 1 to 8.

11. An electronic device, characterized in that, It includes one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the database-based file processing method according to any one of claims 1 to 8.

Images