Structural and data dynamic synchronization and data query method, system, equipment and medium

Abstract

The invention discloses a structure and data dynamic synchronization and data query method, system and device and a medium, and the method comprises the steps: obtaining a table name, and obtaining a structure map set of an original library table according to the table name; circulating a result object of a table structure in the structure map set, and obtaining a table attribute name and a table attribute type; generating a table building sql according to the table name, the table attribute name and the table attribute type; connecting an h2 database, and executing table establishment sql to establish a table; querying original library table data according to the table name; circulating the structure list of the table structure, and generating an insert attribute line; circulating the data set of the original library, circulating the table structure list, generating insert value row content according to the data type of the table structure and the result column of the table data, and obtaining insertion data sql; the method, the system, the equipment and the medium have the advantage that the pressure of the database can be effectively relieved by the aid of the method, the system, the equipment and the medium.

Description

Technical Field

[0001] The present invention belongs to the field of new energy operation and maintenance, and relates to a method, system, equipment and medium for dynamic synchronization of structure and data and data query. Background Art

[0002] The increase in system users and the increasing number of service functions have led to an increasing number of database accesses, which has put enormous pressure on the database and made the database maintenance cost very high. Therefore, we need to consider how to effectively alleviate the database pressure. Summary of the invention

[0003] The purpose of the present invention is to overcome the shortcomings of the above-mentioned prior art and provide a method, system, device and medium for dynamic synchronization of structure and data and data query, which can effectively alleviate the pressure on the database.

[0004] To achieve the above object, the present invention discloses a method for dynamic synchronization of structure and data and data query, comprising:

[0005] Get the table name, and get the structure map set of the original library table according to the table name;

[0006] Loop through the result objects of the table structure in the map collection to obtain the table attribute name and table attribute type;

[0007] Generate table creation SQL according to table name, table attribute name and table attribute type;

[0008] Connect to the h2 database and execute the table creation sql to create a table;

[0009] Query the original database table data based on the table name;

[0010] Loop through the structure list of the table structure and generate the insert attribute row;

[0011] Loop through the data set of the original library, loop through the table structure list, generate the insert values ​​row content according to the data type of the table structure and the result column of the table data, and obtain the SQL for inserting data;

[0012] Connect to the h2 database and execute the insert data sql to complete table data synchronization.

[0013] Furthermore, the method also includes: initializing the system.

[0014] Furthermore, the process of initializing the system is as follows:

[0015] The service is started and a connection with the original database is established;

[0016] The service starts, and the configuration file is pulled regularly to obtain the set of table names that need to be synchronized;

[0017] Loop through the table name collection and call table structure and data synchronization methods.

[0018] Furthermore, it also includes:

[0019] Define the interface, the parameters are table name, the returned attribute column name set, and the filtering condition;

[0020] Concatenate select statements based on table name and query attribute column set;

[0021] Parse the filter conditions and splice the parsing results into the select statement;

[0022] Connect to the h2 database, execute the select statement, and obtain the response result.

[0023] The present invention discloses a system for dynamic synchronization of structure and data and data query, comprising:

[0024] The first acquisition module is used to obtain the table name and obtain the structure map set of the original library table according to the table name;

[0025] The second acquisition module is used to loop through the result objects of the table structure in the structure map set to obtain the table attribute name and table attribute type;

[0026] The first generation module is used to generate a table creation SQL according to the table name, table attribute name and table attribute type;

[0027] The first execution module is used to connect to the h2 database and execute the table creation sql to create a table;

[0028] The query module is used to query the original database table data according to the table name;

[0029] The second generation module is used to loop through the structure list of the table structure and generate an insert attribute row;

[0030] The third generation module is used to loop the data set of the original library, loop the table structure list, generate the insert values ​​row content according to the data type of the table structure and the result column of the table data, and obtain the insert data sql;

[0031] The second execution module is used to connect to the h2 database and execute the SQL to insert data to complete table data synchronization.

[0032] Furthermore, it also includes: an initialization module, which is used to initialize the system.

[0033] Furthermore, the initialization module includes:

[0034] Create a module for service startup and establish a connection with the original database;

[0035] The third acquisition module is used to start the service, pull the configuration file regularly, and obtain the table name set that needs to be synchronized;

[0036] The calling module is used to loop through the table name set, call the table structure and data synchronization method.

[0037] Furthermore, it also includes:

[0038] Definition module, used to define the interface, the parameters are table name, the returned attribute column name set, and the filtering condition;

[0039] The first splicing module is used to splice the select statement according to the table name and the query attribute column set;

[0040] The second module is used to parse the filter conditions and splice the parsing results into the select statement;

[0041] The third execution module is used to connect to the h2 database, execute the select statement, and obtain the response result.

[0042] The present invention discloses a computer device, comprising a memory, a processor and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of the method for dynamic synchronization of structure and data and data query are implemented.

[0043] The present invention discloses a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method for dynamic synchronization of structure and data and data query are implemented.

[0044] The present invention has the following beneficial effects:

[0045] The method, system, device and medium for dynamic synchronization of structure and data and data query described in the present invention can effectively reduce the number of database access times by using the h2 database to dynamically synchronize database data to h2 at a fixed time during specific operation, and provide data query functions to the outside through the service where h2 is located, and because h2 is an in-memory database, it has excellent performance and provides data to the outside very quickly and stably. By dynamically synchronizing table structure and data, one-time deployment can be achieved, and subsequent new table structure and data can be synchronized by calling the interface once. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] The accompanying drawings constituting a part of the present invention are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the accompanying drawings:

[0047] Figure 1 The figure is a flow chart of the method of the present invention. DETAILED DESCRIPTION

[0048] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0049] In the description of the present invention, it should be understood that the terms “include” and “comprises” indicate the presence of described features, wholes, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, wholes, steps, operations, elements, components and / or collections thereof.

[0050] It should also be understood that the terms used in the present specification are only for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in the present specification and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.

[0051] It should be further understood that the term "and / or" used in the present specification and the appended claims refers to any combination of one or more of the associated listed items and all possible combinations, and includes these combinations. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. In addition, the character " / " in the present invention generally indicates that the associated objects are in an "or" relationship.

[0052] It should be understood that, although the terms first, second, third, etc. may be used to describe preset ranges, etc. in the embodiments of the present invention, these preset ranges should not be limited to these terms. These terms are only used to distinguish preset ranges from each other. For example, without departing from the scope of the embodiments of the present invention, the first preset range may also be referred to as the second preset range, and similarly, the second preset range may also be referred to as the first preset range.

[0053] The word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining" or "in response to detecting", depending on the context. Similarly, the phrases "if it is determined" or "if (stated condition or event) is detected" may be interpreted as "when it is determined" or "in response to determining" or "when detecting (stated condition or event)" or "in response to detecting (stated condition or event)", depending on the context.

[0054] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. The components of the embodiments of the present invention described and shown in the drawings here can usually be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the claimed invention, but merely represents selected embodiments of the present invention. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0055] Various structural schematic diagrams of the embodiments disclosed in the present invention are shown in the accompanying drawings. These figures are not drawn to scale, and some details are magnified and some details may be omitted for the purpose of clear expression. The shapes of various regions and layers shown in the figures and the relative sizes and positional relationships therebetween are only exemplary, and may deviate in practice due to manufacturing tolerances or technical limitations, and those skilled in the art may additionally design regions / layers with different shapes, sizes, and relative positions according to actual needs.

[0056] Embodiment 1

[0057] refer to Figure 1 The structure and data dynamic synchronization and data query method of the present invention comprises the following steps:

[0058] 1) Dynamic table creation and data synchronization interface;

[0059] 11) Define the interface, the parameter is the table name.

[0060] 12) Get the structure map set of the original library table by table name.

[0061] 13) Loop through the result objects of the table structure in the map collection to obtain the table attribute name and table attribute type.

[0062] 14) Generate table creation SQL based on table name, table attribute name and table attribute type.

[0063] 15) Connect to the h2 database and execute the h2 create table sql to create the table.

[0064] 16) Query the original library table data.

[0065] 17) Loop through the result list of the table structure and generate the insert attribute row.

[0066] 18) Loop through the original database data set, loop through the table structure list, generate the insert values ​​row content based on the data type of the table structure and the table data result column, and complete the insert SQL splicing.

[0067] 19) Connect to the h2 database and execute the insert data sql to complete the table data synchronization.

[0068] 2) Initialization process;

[0069] 21) The service starts and establishes a connection with the original database.

[0070] 22) The service starts, and the configuration file is pulled regularly to obtain the set of table names that need to be synchronized.

[0071] 23) Loop through the table name collection and call table structure and data synchronization methods.

[0072] 3) Custom query;

[0073] 31) Define the interface, the parameters are the table name, the returned attribute column name set, and the filtering conditions.

[0074] 32) Concatenate the select statement based on the table name and query attribute column set.

[0075] 33) Analyze the filter conditions and splice the analysis results into the select statement.

[0076] 34) Connect to the h2 database and execute the select statement to obtain the response result.

[0077] It should be noted that the present invention has the following characteristics:

[0078] The present invention uses the h2 memory database to synchronize database data to h2 dynamically at regular intervals, and provides data query functions to the outside through the service where h2 is located, which can effectively reduce the number of database accesses. Moreover, because h2 is a memory database, it has excellent performance and provides data to the outside very quickly and stably. By dynamically synchronizing table structures and data, one-time deployment can be achieved, and subsequent new table structures and data can be synchronized by calling an interface once.

[0079] Embodiment 2

[0080] The structure and data dynamic synchronization and data query system of the present invention includes:

[0081] The first acquisition module is used to obtain the table name and obtain the structure map set of the original library table according to the table name;

[0082] The second acquisition module is used to loop through the result objects of the table structure in the structure map set to obtain the table attribute name and table attribute type;

[0083] The first generation module is used to generate a table creation SQL according to the table name, table attribute name and table attribute type;

[0084] The first execution module is used to connect to the h2 database and execute the table creation sql to create a table;

[0085] The query module is used to query the original database table data according to the table name;

[0086] The second generation module is used to loop through the structure list of the table structure and generate an insert attribute row;

[0087] The third generation module is used to loop the data set of the original library, loop the table structure list, generate the insert values ​​row content according to the data type of the table structure and the result column of the table data, and obtain the insert data sql;

[0088] The second execution module is used to connect to the h2 database and execute the SQL to insert data to complete table data synchronization.

[0089] As an implementation mode of the present invention, the initialization module includes:

[0090] Create a module for service startup and establish a connection with the original database;

[0091] The third acquisition module is used to start the service, pull the configuration file regularly, and obtain the table name set that needs to be synchronized;

[0092] The calling module is used to loop through the table name set, call the table structure and data synchronization method.

[0093] As an embodiment of the present invention, it also includes:

[0094] Definition module, used to define the interface, the parameters are table name, the returned attribute column name set, and the filtering condition;

[0095] The first splicing module is used to splice the select statement according to the table name and the query attribute column set;

[0096] The second module is used to parse the filter conditions and splice the parsing results into the select statement;

[0097] The third execution module is used to connect to the h2 database, execute the select statement, and obtain the response result.

[0098] The division of modules in the embodiments of the present application is schematic and is only a logical function division. There may be other division methods in actual implementation. In addition, each functional module in each embodiment of the present application may be integrated into a processor, or may exist physically separately, or two or more modules may be integrated into one module. The above-mentioned integrated modules may be implemented in the form of hardware or in the form of software functional modules.

[0099] Embodiment 3

[0100] A computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of the method for dynamic synchronization of structure and data and data query are implemented, for example, comprising: obtaining a table name, and obtaining a structure map set of an original library table according to the table name; looping through result objects of the table structure in the structure map set to obtain table attribute names and table attribute types; generating a table creation SQL according to the table name, table attribute names and table attribute types; connecting to an h2 database and executing the table creation SQL to create a table; querying the original library table data according to the table name; looping through a structure list of the table structure to generate an insert attribute row; looping through a data set of the original library and a table structure list to generate insert values ​​row contents according to the data type of the table structure and the result column of the table data to obtain an insert data SQL; connecting to an h2 database and executing the insert data SQL to complete table data synchronization. The memory may include a memory, such as a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk memory, etc. The processor, network interface, and memory are interconnected through an internal bus, and the internal bus may be an industrial standard architecture bus, a peripheral component interconnection standard bus, an extended industrial standard architecture bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. The memory is used to store programs. Specifically, the program may include a program code, and the program code includes computer operation instructions. The memory may include a memory and a non-volatile memory, and provide instructions and data to the processor.

[0101] Embodiment 4

[0102] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method for dynamic synchronization of structure and data and data query are implemented, for example, including: obtaining a table name, obtaining a structure map set of an original library table according to the table name; looping the result object of the table structure in the structure map set, obtaining the table attribute name and the table attribute type; generating a table creation SQL according to the table name, the table attribute name and the table attribute type; connecting to an h2 database, executing the table creation SQL to create a table; querying the original library table data according to the table name; looping the structure list of the table structure, generating an insert attribute row; looping the data set of the original library, looping the table structure list, generating insert values ​​row content according to the data type of the table structure and the result column of the table data, and obtaining the insert data SQL; connecting to an h2 database, and executing the insert data SQL to complete the table data synchronization. Specifically, the computer-readable storage medium includes, but is not limited to, for example, a volatile memory and / or a non-volatile memory. The volatile memory may include a random access memory (RAM) and / or a cache memory, etc. The non-volatile memory may include a read-only memory (ROM), a hard disk, a flash memory, an optical disk, a magnetic disk, etc.

[0103] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0104] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0105] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0106] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0107] Those skilled in the art will readily appreciate other embodiments of the present invention after considering the specification and disclosure of the invention. This application is intended to cover any variations, uses or adaptations of the present invention that follow the general principles of the present invention and include common knowledge or customary techniques in the art that are not disclosed by the present invention. The specification and examples are to be considered exemplary only, and the true scope and spirit of the present invention are indicated by the following claims.

[0108] It should be understood that the present invention is not limited to the exact construction that has been described above and shown in the drawings and that various modifications and changes may be made without departing from the scope thereof. The scope of the present invention is limited only by the appended claims.

[0109] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any way. Any simple modification, change and equivalent structural change made to the above embodiment based on the technical essence of the present invention still falls within the protection scope of the technical solution of the present invention.

Claims

1. A method for dynamic synchronization of structure and data and data query, characterized in that: include: Get the table name, and get the structure map set of the original library table according to the table name; Loop through the result objects of the table structure in the map collection to obtain the table attribute name and table attribute type; Generate table creation SQL according to table name, table attribute name and table attribute type; Connect to the h2 database and execute the table creation sql to create a table; Query the original database table data based on the table name; Loop through the structure list of the table structure and generate the insert attribute row; Loop through the data set of the original library, loop through the table structure list, generate the insert values ​​row content according to the data type of the table structure and the result column of the table data, and obtain the SQL for inserting data; Connect to the h2 database and execute the insert data sql to complete table data synchronization.

2. The method for dynamic synchronization of structure and data and data query according to claim 1, characterized in that: Also includes: Initialize the system.

3. The method for dynamic synchronization of structure and data and data query according to claim 1, characterized in that: The process of initializing the system is as follows: The service is started and a connection with the original database is established; The service starts, and the configuration file is pulled regularly to obtain the set of table names that need to be synchronized; Loop through the table name collection and call table structure and data synchronization methods.

4. The method of structure and data dynamic synchronization and data query according to claim 1, characterized in that: Also includes: Define the interface, the parameters are table name, the returned attribute column name set, and the filtering condition; Concatenate select statements based on table name and query attribute column set; Parse the filter conditions and splice the parsing results into the select statement; Connect to the h2 database, execute the select statement, and obtain the response result.

5. A system for dynamic synchronization of structure and data and data query, characterized in that: include: The first acquisition module is used to obtain the table name and obtain the structure map set of the original library table according to the table name; The second acquisition module is used to loop through the result objects of the table structure in the structure map set to obtain the table attribute name and table attribute type; The first generation module is used to generate a table creation SQL according to the table name, table attribute name and table attribute type; The first execution module is used to connect to the h2 database and execute the table creation sql to create a table; The query module is used to query the original database table data according to the table name; The second generation module is used to loop through the structure list of the table structure and generate an insert attribute row; The third generation module is used to loop the data set of the original library, loop the table structure list, generate the insert values ​​row content according to the data type of the table structure and the result column of the table data, and obtain the insert data sql; The second execution module is used to connect to the h2 database and execute the SQL to insert data to complete table data synchronization.

6. The system for dynamic synchronization of structure and data and data query according to claim 5, characterized in that: Also includes: The initialization module is used to initialize the system.

7. The system for dynamic synchronization of structure and data and data query according to claim 6, characterized in that: The initialization module includes: Create a module for service startup and establish a connection with the original database; The third acquisition module is used to start the service, pull the configuration file regularly, and obtain the table name set that needs to be synchronized; The calling module is used to loop through the table name set, call the table structure and data synchronization method.

8. The system for dynamic synchronization of structure and data and data query according to claim 5, characterized in that: Also includes: Definition module, used to define the interface, the parameters are table name, the returned attribute column name set, and the filtering condition; The first splicing module is used to splice the select statement according to the table name and the query attribute column set; The second module is used to parse the filter conditions and splice the parsing results into the select statement; The third execution module is used to connect to the h2 database, execute the select statement, and obtain the response result.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method for dynamic synchronization of structure and data and data query as described in any one of claims 1 to 4 are implemented.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the method for dynamic synchronization of structure and data and data query as described in any one of claims 1 to 4 are implemented.

Images