Read-write separation multi-data source component and its working method

Abstract

The present invention proposes a read-write separation multi-data source component and a working method thereof. First, the data source and the read-write data source are configured in a file in yml format. When the application executes a statement, the interceptor reads the data source group configuration information from the cache to determine the read data source and the write data source. Then, the obtained SQL statement is parsed to parse out the statement type and the specified data source information. The data source strategy to be selected in the next step is determined based on this information. Then, the data source of the current thread is switched according to the strategy, and the current statement is executed. After the statement is executed, the current thread data source is cleared and restored to the state before the switch. The business project is referenced in the corresponding pom file, and the component is registered on the startup class, thereby completing the component installation work. The present invention not only reduces the complexity of code writing and improves the maintainability of the system, but also can even achieve zero coding in the automatic switching scenario, thereby ensuring the quality of application development.

Description

Technical Field

[0001] The invention belongs to the technical field of application development, and in particular relates to a read-write separation multi-data source component and a working method thereof. Background Art

[0002] In high-concurrency application scenarios, even if caching technology is used, some read operations (because of cache misses or cache expiration) and all write operations still need to access the database. Therefore, when the number of users and concurrency reaches a certain scale, the database will still become a performance bottleneck of the system due to excessive load. At this time, a read-write separation database architecture system will be adopted. Under this system, special data access components are generally used on the server side to make the database's read-write separation mechanism transparent to the application.

[0003] Existing data access components are generally divided into the following two types:

[0004] 1. Specify the data source through Java annotations, such as the dynamic-datasource-spring-boot-starter component.

[0005] 2. Application middleware based on some kind of database, such as MyCat.

[0006] However, the above components have the following two problems:

[0007] 1. Developers must code to specify the data source, which is not only error-prone but also increases the workload of developers (the first type).

[0008] 2. Strongly bound to a certain database and does not support multiple databases. For example, MyCat is strongly bound to the MySQL database and does not support the PostgreSQL database. Under the premise of moving away from Oracle, many applications now need to migrate from Oracle database to PostgreSQL database because the syntax of PostgreSQL database is very similar to that of Oracle database. As PostgreSQL database becomes more and more popular, it is also required that data access components must support PostgreSQL database (the second type). Summary of the invention

[0009] The purpose of the present invention is to provide a read-write separation multi-data source component and a working method thereof in view of the defects and shortcomings of the above-mentioned prior art. It can realize automatic switching of read-write separation, and support configuration switching to specify the data source, so as to realize personalized data source switching. This not only reduces the complexity of code writing and improves the maintainability of the system, but also can even achieve zero coding in the automatic switching scenario, thereby ensuring the quality of application development.

[0010] The present invention specifically adopts the following technical solutions:

[0011] A read-write separation multi-data source component, characterized by:

[0012] According to the business scenario, define multiple data sources that need to be accessed in yml format; and define a data source group, which stores the names of the master and slave data sources;

[0013] In the application startup scenario, the system initializes the data source related configuration information;

[0014] In the SQL statement execution scenario, statements are intercepted and data source switching is performed based on the read and write data source information in the data source group, thereby realizing the read-write separation automatic switching execution function or the specified data source execution function;

[0015] In the business engineering usage scenario, the read-write separation multi-data source component is packaged into a jar package and published to the Maven private server; other business projects only need to reference it in the corresponding pom file and register the component on the startup class of the SpringBoot framework to realize the automatic switching function of the read-write separation data source.

[0016] Furthermore, the data source information is initialized by the dynamic-datasource-spring-boot-starter component.

[0017] Furthermore, the data source group information is loaded into the cache in the form of a singleton object.

[0018] Furthermore, for business scenarios where statements need to be executed in a specified data source, if the specified data source is not the configured master-slave data source, the name of the data source to be executed is specified by a special prefix before the statement.

[0019] Furthermore, in the SQL statement execution scenario, by intercepting the statement and switching the data source according to the read and write data source information in the data source group, the following steps are specifically included:

[0020] Step 1: Use the MyBatis interceptor to intercept the statements to be executed, including: add, insert, update and delete statements;

[0021] Step 2: Read the data source group configuration information from the cache to determine the read data source and write data source;

[0022] Step 3: Parse the obtained SQL statement to extract the statement type and the specified data source information;

[0023] Step 4: If the specified data source is found, the "specified data source" strategy is executed;

[0024] Step 5: If the specified data source is not found, the statement type is further determined; if the statement type is query, the "read data source" strategy is executed; if the statement type is insert, add, update, function or stored procedure, the "write data source" strategy is executed;

[0025] Step 6: According to the corresponding data source strategy, switch the data source of the current thread to the required data source;

[0026] Step 7: Execute the current statement in the switched data source environment;

[0027] Step 8: After the statement is executed, clear the current thread data source and restore it to the state before switching.

[0028] And a computer storage medium, characterized in that it includes computer instructions, when the computer instructions are executed on an electronic device, the electronic device executes the read-write separation multi-data source component or its working method as described above.

[0029] A system for implementing a read-write separation multi-data source component, characterized in that it includes a memory and a processor, wherein the memory stores computer instructions for implementing the read-write separation multi-data source component or its working method as described above, and the processor executes the computer instructions stored in the memory during operation.

[0030] The present invention and its preferred solution first configure the data source and the read-write data source in a file in yml format. When the application executes a statement, the interceptor reads the data source group configuration information from the cache to determine the read data source and the write data source. Then parse the acquired SQL statement to parse out the statement type and the specified data source information. According to this information, the data source strategy to be selected in the next step is determined. Then, according to the strategy, the data source of the current thread is switched, and the current statement is executed. After the statement is executed, the current thread data source is cleared and restored to the state before the switch. The business project is referenced in the corresponding pom file, and the component is registered on the startup class, thereby completing the component installation. The present invention not only reduces the complexity of code writing and improves the maintainability of the system, but also can even achieve zero coding in the automatic switching scenario, thereby ensuring the quality of application development.

[0031] Compared with the prior art, the present invention and its preferred solution have the following beneficial effects: by using the YML data format to configure the read data source and the write data source in advance, and cooperating with the interceptor method of MyBatis, the function of automatically switching to the specified data source before the statement is executed is realized, thereby avoiding the drawback of developers hard-coding to specify the data source, which is not only less prone to errors, but also reduces the workload of developers. Support multiple databases. As long as the database provides a JDBC driver package, the components provided by the present invention can support it, so that it can be applied to business engineering projects based on various heterogeneous databases to the greatest extent. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] The present invention is further described in detail below with reference to the accompanying drawings and specific embodiments:

[0033] Figure 1 It is a basic working logic diagram of the components provided by the embodiment of the present invention.

[0034] Figure 2 It is a schematic diagram of the working process of an embodiment of the present invention. DETAILED DESCRIPTION

[0035] In order to make the features and advantages of this patent more obvious and easy to understand, the following embodiments are specifically described in detail as follows:

[0036] It should be noted that the following detailed descriptions are illustrative and are intended to provide further explanation of the present application. Unless otherwise specified, all technical and scientific terms used herein have the same meanings as those commonly understood by those skilled in the art to which the present application belongs.

[0037] It should be noted that the terms used herein are only for describing specific embodiments and are not intended to limit the exemplary embodiments according to the present application. As used herein, unless the context clearly indicates otherwise, the singular form is also intended to include the plural form. In addition, it should be understood that when the terms "comprise" and / or "include" are used in this specification, it indicates the presence of features, steps, operations, devices, components and / or combinations thereof.

[0038] like Figure 1 as well as Figure 2 As shown, this embodiment provides a read-write separation multi-data source component and a working method thereof, including the following settings:

[0039] According to the business scenario, define multiple data sources that need to be accessed in yml format; and define a data source group, which stores the names of the master and slave data sources.

[0040] In the application startup scenario, the system will initialize the data source related configuration information.

[0041] In the SQL statement execution scenario, statements are intercepted and data sources are switched based on the read and write data source information in the data source group, thereby realizing the read-write separation automatic switching execution function or the specified data source execution function.

[0042] In the business engineering usage scenario, the read-write separation multi-data source component is packaged into a jar package and published to the Maven private server. In this way, other business projects only need to reference it in the corresponding pom file and register the component in the startup class of the SpringBoot framework to realize the automatic switching function of the read-write separation data source.

[0043] In this embodiment, the data source information is initialized by the dynamic-datasource-spring-boot-starter component.

[0044] In this embodiment, the data source group information is loaded into the cache in the form of a singleton object.

[0045] In this embodiment, the SQL statement execution scenario specifically includes the following steps:

[0046] Step 1: Intercept the statements to be executed (including add, insert, update and delete statements) through MyBatis interceptor;

[0047] Step 2: Read the data source group configuration information from the cache to determine the read data source and write data source;

[0048] Step 3: Parse the obtained SQL statement to extract the statement type and the specified data source information;

[0049] Step 4: If the specified data source is found, use the "specified data source" strategy;

[0050] Step 5: If the specified data source is not found, further determine the statement type. If the statement type is query, use the "read data source" strategy; if the statement type is insert, add, update, function or stored procedure, use the "write data source" strategy;

[0051] Step 6: According to the corresponding data source strategy, switch the data source of the current thread to the required data source;

[0052] Step 7: Execute the current statement in the switched data source environment;

[0053] Step 8: After the statement is executed, clear the current thread data source and restore it to the state before switching.

[0054] In this embodiment, the data source group is configured through a file in yml format. Its biggest advantage is that it is easy to read, similar to the following format:

[0055] datasource:

[0056] profile: Data source group A

[0057] configs:

[0058] - name: Data source group A

[0059] main-datasource: write the data source name

[0060] slave-datasource: read data source name

[0061] - name: Data Source Group B

[0062] main-datasource: write the data source name

[0063] slave-datasource: read data source name

[0064] - …

[0065] It supports pre-configuring multiple data source groups, and then specifying the effective data source group through profile.

[0066] In this embodiment, in some business scenarios, statements need to be executed in a specified data source, but the specified data source is not the master-slave data source configured previously. In this case, a special prefix can be used before the statement to specify the name of the data source to be executed. Statements are generally configured in XML, and the syntax is based on Mybatis, similar to the following form:

[0067] <insert id="插入语句ID">

[0068] / *--Data source name--* / insert into ...

[0069] < / insert>

[0070] By using database annotations to specify the data source name, it is helpful to reduce the invasiveness of the component. Even if the component is no longer used for some reason, there is no need to modify the special prefix that has been defined before.

[0071] This component also supports specifying data sources for custom stored procedures or functions. The configuration file and syntax are the same as the above example, similar to the following format:

[0072] <select id="存储过程 ID" statementtype="CALLABLE" usecache="false">

[0073] {

[0074] call stored procedure name(

[0075] #{input parameter name, mode=IN}, / *--data source name--* /

[0076] #{output parameter name, mode=OUT,jdbcType=VARCHAR}

[0077] …)

[0079] }

[0080] < / select>

[0081] Due to the particularity of the stored procedure or function syntax, it is not supported to define comments before the call keyword, so we define the specified data source name after the first input parameter name; if there is no input parameter, it is defined after the first left bracket "(".

[0082] In this embodiment, a Mybatis interceptor is defined to intercept and switch the data source of the current thread before the statement is executed. In practice, it is found that some projects use the PageHelper component, which is used for single-table or multi-table paging query scenarios. The PageHelper component is essentially a custom Mybatis interceptor. Since it does not return the result to the next interceptor after interception, it must be loaded after the PageHelper component during integration (the interceptors in the interceptor chain are executed in sequence from back to front).

[0083] In summary, this embodiment first configures the data source and the read-write data source in a file in yml format. When the application executes a statement, the interceptor will read the data source group configuration information from the cache to determine the read data source and the write data source. Then parse the acquired SQL statement, and parse out the statement type and the specified data source information. Then determine the data source strategy to be selected in the next step based on this information. Then switch the data source of the current thread according to the selected strategy, execute the current statement, and after the statement is executed, clear the current thread data source and restore it to the state before switching. The business project is referenced in the corresponding pom file, and the component is registered on the startup class to complete the component installation. The present invention can reduce the complexity of code writing, improve the maintainability of the system, and even achieve zero coding in the automatic switching scenario, ultimately ensuring the development quality of the application.

[0084] This embodiment has the following advantages:

[0085] 1. Zero coding: Use the YML data format to configure the read data source and the write data source in advance, and cooperate with the MyBatis interceptor method to realize the function of automatically switching to the specified data source before the statement is executed, thereby avoiding the disadvantages of developers hard coding to specify the data source. This is not only less error-prone, but also reduces the workload of developers.

[0086] 2. Personalization: In some business scenarios, you need to execute statements in a specified data source, but the specified data source is not the master-slave data source configured previously. In this case, you can specify the name of the data source to be executed by using a special prefix before the statement.

[0087] 3. Support multiple databases: This component supports any database that can provide a JDBC driver package. This allows it to be applied to business engineering projects based on various heterogeneous databases to the greatest extent possible, thereby meeting the needs of project database migration in actual business scenarios.

[0088] 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 combining software and hardware. Moreover, the present application may adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes.

[0089] 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.

[0090] 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.

[0091] 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 A step that specifies a function in one or more boxes.

[0092] This patent is not limited to the above-mentioned optimal implementation mode. Anyone can derive other various forms of read-write separation multi-data source components and their working methods under the inspiration of this patent. All equal changes and modifications made according to the scope of the patent application of the present invention should be covered by this patent.

Claims

1. A read-write separation multi-data source component, characterized in that: According to the business scenario, define multiple data sources that need to be accessed in yml format; and define a data source group, which stores the names of the master and slave data sources; In the application startup scenario, the system initializes the data source related configuration information; In the SQL statement execution scenario, statements are intercepted and data source switching is performed based on the read and write data source information in the data source group, thereby realizing the read-write separation automatic switching execution function or the specified data source execution function; In the business engineering usage scenario, the read-write separation multi-data source component is packaged into a jar package and published to the Maven private server; other business projects only need to reference it in the corresponding pom file and register the component in the startup class of the SpringBoot framework to realize the automatic switching function of the read-write separation data source; In the SQL statement execution scenario, the data source is switched by intercepting the statement and based on the read and write data source information in the data source group. The specific steps include: Step 1: Use the MyBatis interceptor to intercept the statements to be executed, including: add, insert, update and delete statements; Step 2: Read the data source group configuration information from the cache to determine the read data source and write data source; Step 3: Parse the obtained SQL statement to extract the statement type and the specified data source information; Step 4: If the specified data source is found, the "specified data source" strategy is executed; Step 5: If the specified data source is not found, further determine the statement type; if the statement type is query, execute the "read data source" strategy; if the statement type is insert, add, update, function or stored procedure, execute the "write data source" strategy; Step 6: According to the corresponding data source strategy, switch the data source of the current thread to the required data source; Step 7: Execute the current statement in the switched data source environment; Step 8: After the statement is executed, clear the current thread data source and restore it to the state before switching.

2. The read-write separation multi-data source component according to claim 1, characterized in that: The data source information is initialized by the dynamic-datasource-spring-boot-starter component.

3. The read-write separation multi-data source component according to claim 1, characterized in that: The data source group information is loaded into the cache in the form of a singleton object.

4. The read-write separation multi-data source component according to claim 1, characterized in that: For business scenarios where statements need to be executed in a specified data source, if the specified data source is not the configured master-slave data source, specify the name of the data source to be executed through a special prefix before the statement.

5. A computer storage medium, characterized in that: It comprises computer instructions, and when the computer instructions are executed on an electronic device, the electronic device executes the read-write separation multi-data source component or the working method thereof as described in any one of claims 1 to 4.

6. A system for implementing a read-write separation multi-data source component, characterized in that: It comprises a memory and a processor, wherein the memory stores computer instructions for implementing the read-write separation multi-data source component or its working method as described in any one of claims 1 to 4, and the processor executes the computer instructions stored in the memory during operation.

Images