Configuration-based Underlying General Query Method and System

Abstract

The present invention provides a configuration-based underlying general query method and system, including: Step 1: Construct an SQL pool, and the construction elements include an SQL storage medium, a management page for adding, deleting, querying, and modifying SQL, and a management page for executing SQL. The SQL pool is used to centrally store the SQL statements used for querying; Step 2: Develop a general query method, and the query uses parameters including the ID of the SQL and the parameters corresponding to the SQL placeholder; Step 3: Package the query method in Step 2 in the format of a JAR; Step 4: Manage the SQL pool, including automated review and intelligent warning. The present invention simplifies development, improves work efficiency, centrally manages SQL, reduces development costs, and improves system maintenance efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of data query, and in particular, to a configuration-based underlying general query method and system. Background Art

[0002] During the system development process, there are usually quite a few custom SQLs (database structured query statements). Each time a new SQL is added, code for operating this SQL needs to be added, resulting in a large amount of similar work. In addition, all SQLs are placed in the code project, making SQL review a difficult task. A large number of non-compliant SQLs are put into production without the review of the DBA, doubling the database pressure. A large number of slow SQLs can only be discovered after going online. If there are many active users in the system, the database will soon be overwhelmed.

[0003] Patent document CN110019350A (application number: CN201710631092.7) discloses a data query method and device based on configuration information, which relates to the field of computer technology. A specific embodiment of this method includes: receiving a query request of an application system based on a unified service framework; the query request includes: query parameters, and an index identifier ID that uniquely points to the target database corresponding to the application system; obtaining configuration information according to the index ID; the configuration information includes: a query statement template; dynamically generating an executable query statement according to the query parameters and the query statement template; obtaining a query result according to the executable query statement. Summary of the Invention

[0004] Aiming at the defects in the prior art, the purpose of the present invention is to provide a configuration-based underlying general query method and system.

[0005] According to the configuration-based underlying general query method provided by the present invention, it includes:

[0006] Step 1: Construct an SQL pool, and the construction elements include an SQL storage medium, a management page for adding, deleting, querying, and modifying SQLs, and a management page for executing SQLs. The SQL pool is used to centrally store SQL statements for query use.

[0007] Step 2: Develop a general query method, and the query use parameters include the ID of the SQL and the parameters corresponding to the SQL placeholder.

[0008] Step 3: Package the query method in Step 2 in the format of a jar.

[0009] Step 4: Manage the SQL pool, including automated review and intelligent warning.

[0010] Preferably, the Step 1 includes:

[0011] Step 1.1: Each stored SQL is equipped with a unique identification ID;

[0012] Step 1.2: The parameters of each SQL statement are replaced with question mark placeholders, and tag characters can be added to support dynamic SQL concatenation;

[0013] Step 1.3: The development management page is used to operate the storage, modification, and deletion of SQL;

[0014] Step 1.4: The development management page is used to execute SQL, set parameters on the page, and configure the database address.

[0015] Preferably, the said Step 2 includes:

[0016] Step 2.1: Configure the database connection pool and obtain the corresponding connection from the database connection pool;

[0017] Step 2.2: According to the incoming ID, obtain the SQL with the specified ID from the SQL pool;

[0018] Step 2.3: Cache the SQL according to the ID and set the time limit at the same time;

[0019] Step 2.4: Parse the tags used to support dynamic concatenation in the SQL statement according to the parameters;

[0020] Step 2.5: Precompile the SQL statement using PreparedStatement;

[0021] Step 2.6: Pass the SQL parameters passed in the query method to the PreparedStatement object;

[0022] Step 2.7: Call the executeQuery method to execute the SQL and obtain the returned data;

[0023] Step 2.8: Cache the execution time and execution frequency, and report the execution time and frequency data to the SQL governance system every hour;

[0024] Step 2.9: Perform table-level or field-level data permission management on all SQLs.

[0025] Preferably, the said Step 4 includes:

[0026] Step 4.1: Develop an SQL management platform and obtain data from the SQL pool;

[0027] Step 4.2: Conduct automated review, regularly obtain the execution plan information for all SQLs, analyze whether the indexes are correctly utilized, and save the results;

[0028] Step 4.3: Perform intelligent early warning. When it is scanned that the SQL execution fails or the index is not used correctly, an email warning is sent. When the execution frequency of a certain SQL is too high or the execution time is too long and exceeds the threshold, an email warning is also sent.

[0029] Preferably, before each batch of SQLs goes online for production, relevant personnel are organized to review the SQLs. When it is found that there are errors in the SQL statements or optimization is needed, the SQLs specified in the SQL pool are modified to achieve the purpose of taking effect without releasing a new version. When abnormalities are found after modifying the SQLs or email warnings are received, it is possible to quickly roll back to the previous version.

[0030] According to the underlying general query system based on configuration provided by the present invention, it includes:

[0031] Module M1: Build an SQL pool. The building elements include an SQL storage medium, a management page for adding, deleting, querying, and modifying SQLs, and a management page for executing SQLs. The SQL pool is used to centrally store the SQL statements used for querying.

[0032] Module M2: Develop a general query method. The query uses parameters including the ID of the SQL and the parameters corresponding to the SQL placeholder.

[0033] Module M3: Package the query method of Module M2 in the format of a jar.

[0034] Module M4: Manage the SQL pool, including automated review and intelligent early warning.

[0035] Preferably, the Module M1 includes:

[0036] Module M1.1: Each stored SQL is equipped with a unique identifier ID.

[0037] Module M1.2: The parameters of each SQL statement are replaced with question mark placeholders, and label characters can be added to support dynamic SQL splicing.

[0038] Module M1.3: Develop a management page to operate the storage, modification, and deletion of SQLs.

[0039] Module M1.4: Develop a management page to execute SQLs, and set parameters and configure the database address on the page.

[0040] Preferably, the Module M2 includes:

[0041] Module M2.1: Configure the database connection pool and obtain the corresponding connection from the database connection pool.

[0042] Module M2.2: According to the incoming ID, obtain the SQL with the specified ID from the SQL pool.

[0043] Module M2.3: Cache SQL according to the ID and set the time limit at the same time;

[0044] Module M2.4: Parse the tags used to support dynamic splicing in the SQL statement according to the parameters;

[0045] Module M2.5: Precompile the SQL statement using PreparedStatement;

[0046] Module M2.6: Pass the SQL parameters passed in the query method to the PreparedStatement object;

[0047] Module M2.7: Call the executeQuery method to execute the SQL and obtain the returned data;

[0048] Module M2.8: Cache the execution time and execution frequency, and report the execution time and frequency data to the SQL governance system every hour;

[0049] Module M2.9: Perform data permission management at the table level or field level for all SQLs.

[0050] Preferably, the module M4 includes:

[0051] Module M4.1: Develop an SQL management platform and obtain data from the SQL pool;

[0052] Module M4.2: Conduct automated reviews, regularly obtain execution plan information for all SQLs, analyze whether the indexes are correctly utilized, and save the results;

[0053] Module M4.3: Conduct intelligent warnings. When it is scanned that an SQL has an execution failure or does not correctly use an index, send an email warning. When the execution frequency of a certain SQL is too high or the execution time is too long and exceeds the threshold, also send an email warning.

[0054] Preferably, before each batch of SQLs goes online for production, organize relevant personnel to review the SQLs. When it is found that there are errors or optimizations needed in the SQL statements, modify the SQLs specified in the SQL pool to achieve the purpose of taking effect without releasing a new version. When it is found that there are abnormalities after modifying the SQLs or an email warning is received, it can be quickly rolled back to the previous version.

[0055] Compared with the prior art, the present invention has the following beneficial effects:

[0056] (1) The present invention simplifies development, improves work efficiency, centrally manages SQLs, makes it possible for automated governance and the intervention of database administrators (DBAs), and effectively controls the online SQLs;

[0057] (2) The DAO layer code in the database operation of the present invention reduces the development cost and improves the system maintenance efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] Other features, objects, and advantages of the present invention will become more apparent from the following detailed description of non-limiting embodiments read in conjunction with the accompanying drawings:

[0059] Figure 1 is the system structure diagram;

[0060] Figure 2 is the SQL governance function structure diagram;

[0061] Figure 3 is the SQL pool system structure diagram. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0062] The present invention will be described in detail below with reference to specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but do not limit the present invention in any form. It should be noted that those of ordinary skill in the art can make several changes and improvements without departing from the concept of the present invention. These all belong to the protection scope of the present invention.

[0063] Embodiment:

[0064] As Figure 1 , the present invention provides a configuration-based underlying general query technology, including:

[0065] Unify and centrally place the queried SQL statements in a certain place, such as a database or a configuration center (named SQL pool).

[0066] By adopting the means of encapsulating a unified query method at the underlying layer, obtaining the specified SQL from the SQL pool, executing the SQL after passing in the parameters, and obtaining the data, the problems of repeatedly writing query code and the inability to centrally manage SQL statements are solved, making it possible to perform intelligent review or manual review of SQL.

[0067] The specific steps are as follows (taking Java language development as an example):

[0068] Step 1: Construct an SQL statement pool: The construction elements include an SQL storage medium, a management page for adding, deleting, querying, and modifying SQL, and a management page for executing SQL. Refer to Figure 3 , the steps are as follows:

[0069] Step 1.1, each stored SQL needs to have a unique identifier id;

[0070] Step 1.2, use question mark placeholders for the parameters of each SQL statement, and tag characters can be added to support dynamic SQL splicing. Example:

[0071] select name, age from user

[0072] where name =?

[0073] <if age!= null> and age =?

[0074] Step 1.3, develop a management page to operate the storage, modification, and deletion of SQL;

[0075] Step 1.4, develop a management page to execute SQL, and the page can set parameters and configure the database address.

[0076] Step 2: Develop a general query method: The parameters include the ID of the SQL and the parameters corresponding to the SQL placeholder;

[0077] Step 2.1, configure a database connection pool and obtain the corresponding connection from the database connection pool;

[0078] Step 2.2, the query method obtains the SQL with the specified ID from the SQL pool according to the passed-in ID;

[0079] Step 2.3, cache the SQL according to the ID to prevent the same SQL from being fetched from the SQL pool every time, and the cache needs to set a time limit;

[0080] Step 2.4, parse the tags used to support dynamic splicing in the SQL statement according to the parameters;

[0081] Step 2.5, use PreparedStatement to precompile the SQL statement;

[0082] Step 2.6, pass the SQL parameters passed in the query method to the PreparedStatement object;

[0083] Step 2.7, call the executeQuery method to execute the SQL and obtain the returned data;

[0084] Step 2.8, cache the execution time and execution frequency, and report the execution time and frequency and other data to the SQL governance system every hour.

[0085] Step 2.9: Scalability: Since all SQLs are converged here, it can be used for table-level or field-level data permission management.

[0086] Step 3: Package the method in Step 2 into a JAR for easy reference by other systems.

[0087] Step 4: SQL pool governance: The included functions are shown inFigure 2 ;

[0088] Step 4.1: Develop an SQL management platform with data from the SQL pool.

[0089] Step 4.2: Automated review: Regularly obtain execution plan information for all SQLs, analyze whether indexes are correctly utilized, and save the results.

[0090] Step 4.3: Intelligent warning: Send an email warning when it is scanned that an SQL execution fails or the index is not correctly used. When the execution frequency of a certain SQL is too high or the execution time is too long and exceeds the threshold, an email warning can also be sent.

[0091] Step 4.4: Since the SQLs are centrally managed, before each batch of SQLs goes online to production, the DBA can organize relevant personnel to review the SQLs.

[0092] Step 4.5: When it is found that there are errors in the SQL statements or optimization is needed, the SQL specified in the SQL pool can be modified to achieve the purpose of taking effect without releasing a new version.

[0093] Step 4.6: Do a good job in the version management of SQLs. When it is found that there are anomalies after modifying the SQL or an email warning is received, it can be quickly rolled back to the previous version.

[0094] The specific embodiments of the present invention have been described above. It should be understood that the present invention is not limited to the above specific embodiments, and those skilled in the art can make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. Without conflict, the embodiments of the present application and the features in the embodiments can be arbitrarily combined with each other.

Claims

1. A configuration-based underlying general query method, characterized in that, Including: Step 1: Build an SQL pool. The building elements include an SQL storage medium, a management page for adding, deleting, querying, and modifying SQL, and a management page for executing SQL. The SQL pool is used to centrally store SQL statements for querying. Step 2: Develop a general query method. The query parameters include the ID of the SQL and the parameters corresponding to the SQL placeholder. Step 3: Package the query method in Step 2 in the format of a JAR. Step 4: Govern the SQL pool, including automated review and intelligent warning. The said Step 2 includes: Step 2.1: Configure the database connection pool and obtain the corresponding connection from the database connection pool. Step 2.2: According to the incoming ID, obtain the SQL with the specified ID from the SQL pool. Step 2.3: Cache the SQL according to the ID and set the time limit at the same time. Step 2.4: Parse the tags used to support dynamic splicing in the SQL statement according to the parameters. Step 2.5: Precompile the SQL statement using PreparedStatement. Step 2.6: Pass the SQL parameters passed in the query method to the PreparedStatement object. Step 2.7: Call the executeQuery method to execute the SQL and obtain the returned data. Step 2.8: Cache the execution time and execution frequency, and report the execution time and frequency data to the SQL governance system every hour. Step 2.9: Perform data permission management at the table level or field level for all SQLs.

2. The configuration-based underlying general query method according to claim 1, wherein The said Step 1 includes: Step 1.1: Each stored SQL is equipped with a unique identifier ID. Step 1.2: The parameters of each SQL statement are replaced with question mark placeholders, and tag characters can be added to support dynamic SQL splicing. Step 1.3: Develop a management page to operate the storage, modification, and deletion of SQL. Step 1.4: Develop a management page to execute SQL, set parameters on the page, and configure the database address.

3. The underlying general query method based on configuration according to claim 1, wherein The said Step 4 includes: Step 4.1: Develop an SQL management platform and obtain data from the SQL pool. Step 4.2: Conduct automated review, regularly obtain the execution plan information for all SQLs, analyze whether the indexes are correctly utilized, and save the results. Step 4.3: Conduct intelligent warning. When it is scanned that an SQL has execution failure or does not correctly use the index, an email warning is sent. When the execution frequency of a certain SQL is too high or the execution time is too long exceeding the threshold, an email warning is also sent.

4. The underlying general query method based on configuration according to claim 3, wherein Before each batch of SQLs goes online for production, relevant personnel are organized to review the SQLs. When it is found that there are errors or optimizations are needed in the SQL statements, by modifying the SQLs specified in the SQL pool, the purpose of taking effect without releasing a new version can be achieved. When it is found that there are anomalies after modifying the SQLs or an email warning is received, it can be quickly rolled back to the previous version.

5. A configuration-based underlying general query system, characterized in that, Including: Module M1: Build an SQL pool. The building elements include an SQL storage medium, a management page for adding, deleting, querying, and modifying SQL, and a management page for executing SQL. The SQL pool is used to centrally store SQL statements for querying. Module M2: Develop a general query method, where the query parameters include the id of the SQL and the parameters corresponding to the SQL placeholder; Module M3: Package the query method of Module M2 in the format of a JAR; Module M4: Manage the SQL pool, including automated review and intelligent warning; The said Module M2 includes: Module M2.1: Configure the database connection pool and obtain the corresponding connection from the database connection pool; Module M2.2: Obtain the SQL with the specified id from the SQL pool according to the incoming id; Module M2.3: Cache the SQL according to the id and set the time limit at the same time; Module M2.4: Parse the tags used to support dynamic splicing in the SQL statement according to the parameters; Module M2.5: Compile the SQL statement using PreparedStatement; Module M2.6: Pass the SQL parameters passed in the query method to the PreparedStatement object; Module M2.7: Call the executeQuery method to execute the SQL and obtain the returned data; Module M2.8: Cache the execution time and execution frequency, and report the execution time and frequency data to the SQL management system every hour; Module M2.9: Perform table-level or field-level data permission management on all SQLs.

6. The configuration-based underlying general query system according to claim 5, wherein The said Module M1 includes: Module M1.1: Each stored SQL is equipped with a unique identifier id; Module M1.2: The parameters of each SQL statement are replaced with question mark placeholders, and tag characters can be added to support dynamic SQL splicing; Module M1.3: Develop a management page to operate the storage, modification and deletion of SQL; Module M1.4: Develop a management page to execute SQL, set parameters and configure the database address on the page.

7. The configuration-based underlying general query system according to claim 5, characterized in that The said Module M4 includes: Module M4.1: Develop an SQL management platform and obtain data from the SQL pool; Module M4.2: Conduct automated review, regularly obtain the execution plan information for all SQLs, analyze whether the indexes are correctly utilized, and save the results; Module M4.3: Conduct intelligent warning. When it is scanned that an SQL fails to execute or does not correctly use the index, an email warning is sent. When the execution frequency of a certain SQL is too high or the execution time is too long and exceeds the threshold, an email warning is also sent.

8. The configuration-based underlying general query system according to claim 7, wherein Before each batch of SQLs goes online for production, relevant personnel are organized to review the SQLs. When it is found that there are errors or optimizations are needed in the SQL statements, the SQLs specified in the SQL pool are modified to achieve the purpose of taking effect without releasing a new version. When it is found that there are abnormalities after modifying the SQL or an email warning is received, it can be quickly rolled back to the previous version.

Images