Database adaptation system, method, device, medium and product
By automatically converting SQL statements through a database adaptation system, the problem of SQL language differences during migration or expansion between different databases is solved, achieving seamless compatibility and unification of multiple databases, and reducing code maintenance workload and error rate.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- BEIJING HUANENG XINRUI CONTROL TECH
- Filing Date
- 2026-02-26
- Publication Date
- 2026-06-05
AI Technical Summary
When migrating or expanding between different databases, differences in SQL languages require developers to manually modify a large number of SQL statements, resulting in a heavy workload for code maintenance and a high risk of introducing errors.
A database adaptation system is provided, including a database adaptation layer, an SQL processing layer, and a database management layer. By automatically identifying the database corresponding to the SQL statement to be processed, the system calls the appropriate database adapter to perform the conversion, ensuring that the converted SQL statement conforms to the syntax requirements of the target database.
It achieves compatibility and unification across multiple databases without requiring modifications to business code, reducing errors and improving the efficiency and accuracy of migration and expansion processes.
Smart Images
Figure CN122152790A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of computers, and more particularly to a database adaptation system, method, device, medium, and product. Background Technology
[0002] With the rapid development of the domestic IT innovation environment and domestic databases, enterprise application systems often need to support multiple different types of databases in actual deployments, such as MySQL, PostgreSQL, DM Database, and Kingbase. However, due to significant differences between different databases in SQL statement processing, most applications typically only support a single database.
[0003] When a system needs to be migrated or expanded between different databases, developers must manually modify a large number of SQL statements due to the differences in SQL language support among different databases, resulting in a large workload for code maintenance and an easy introduction of errors. Summary of the Invention
[0004] This invention addresses the problem in existing technologies where errors can easily arise due to differences in SQL language support among different databases when migrating or expanding between them. It proposes a database adaptation system, method, device, medium, and product.
[0005] In a first aspect, the present invention provides a database adaptation system, the system comprising: The database adaptation layer includes multiple database adapters, each corresponding to a database. The SQL processing layer is used to obtain the SQL statement to be processed and call the target database adapter in the database adaptation layer to transform the SQL statement to be processed. The database corresponding to the target database adapter corresponds to the database of the SQL statement to be processed. The transformed SQL statement to be processed is used for data processing in the corresponding database.
[0006] Preferably, each of the database adapters includes one or more of a pagination SQL converter, a function converter, and a keyword converter.
[0007] Preferably, the system further includes: Database management layer, used for registering and / or switching databases.
[0008] Preferably, the database management layer is also used to monitor the online status of the database via JDBC.
[0009] Preferably, the SQL processing layer is also used to record abnormal states during the transformation process of the SQL statement to be processed; Preferably, the SQL processing layer is also used to cache the transformed SQL statements to be processed.
[0010] Preferably, the database adaptation layer also includes an adapter interface for each database adapter, which is invoked by the SQL processing layer.
[0011] Secondly, the present invention provides a database adaptation method, the method comprising: Retrieve the SQL statements to be processed; The target database adapter among multiple database adapters is invoked to transform the SQL statement to be processed. Each database adapter corresponds to a database, and the database corresponding to the target database adapter corresponds to the database of the SQL statement to be processed. The transformed SQL statement to be processed is used for data processing in the corresponding database.
[0012] Thirdly, the present invention provides an electronic device comprising a processor and a memory, wherein the memory stores at least one instruction, at least one program, code set, or instruction set, and the at least one instruction, at least one program, code set, or instruction set is loaded and executed by the processor to implement the database adaptation method as described in the second aspect.
[0013] Fourthly, the present invention provides a computer-readable storage medium storing at least one instruction or at least one program, wherein the at least one instruction or at least one program is loaded and executed by a processor to implement the database adaptation method as described in the second aspect.
[0014] Fifthly, the present invention provides a computer program product, including a computer program that, when executed by a processor, implements the database adaptation method as described in the second aspect.
[0015] The database adaptation system, method, device, medium, and product provided by this invention have the following technical effects: After receiving the SQL statement to be processed from the application layer, the system automatically selects the database adapter corresponding to the SQL statement by identifying the database to be processed. The adapter then converts the SQL statement to be processed so that the converted SQL statement can meet the syntax requirements of the target database. This achieves compatibility and unification of multiple databases without modifying the business code, eliminating the need for manual intervention and reducing the occurrence of errors.
[0016] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit the invention. Attached Figure Description
[0017] The accompanying drawings, which form part of this specification, are used to provide a further understanding of the invention. The illustrative embodiments of the invention and their descriptions are used to explain the invention and do not constitute an undue limitation of the invention. In the drawings: Figure 1 A first structural diagram of a database adaptation system provided as an exemplary embodiment of the present invention; Figure 2 A second structural diagram of a database adaptation system provided as an exemplary embodiment of the present invention; Figure 3 A flowchart illustrating a database adaptation method provided in an exemplary embodiment of the present invention; Figure 4 This is a structural diagram of an electronic device provided as an exemplary embodiment of the present invention.
[0018] Reference numerals: Database adaptation layer 11, SQL processing layer 12, Database management layer 13, Electronic device 400, Central processing unit 410, Storage medium 420, Memory 430, Application program 423, Data 422, Operating system 421, Input / output interface 440, Power supply 460, Wired and wireless network interface 450. Detailed Implementation
[0019] The present invention will now be described in detail with reference to the accompanying drawings and embodiments. It should be noted that, unless otherwise specified, the embodiments and features described herein can be combined with each other.
[0020] The following detailed description is exemplary and intended to provide further detailed explanation of the invention. Unless otherwise specified, all technical terms used in this invention have the same meaning as commonly understood by one of ordinary skill in the art. The terminology used in this invention is for describing particular embodiments only and is not intended to limit the scope of exemplary embodiments according to the invention.
[0021] Existing database adaptation solutions include: The manual adaptation approach involves writing different SQL mapping files for each database and managing the configuration for different environments through Maven Profiles. However, this approach is costly to maintain, prone to errors, and cannot achieve dynamic switching at runtime.
[0022] Database proxy middleware, such as MyCat and ShardingSphere, is used for SQL forwarding and transformation. However, this approach requires the introduction of additional components, increasing system complexity and resulting in significant performance overhead.
[0023] This ORM (Object-Relational Mapping) framework extension achieves dialect adaptation by rewriting MyBatis's SQL generator. However, this approach has incomplete adaptation, limited support for complex SQL, and poor upgrade compatibility.
[0024] It is evident that existing database adaptation solutions cannot achieve true seamless switching and compatibility across multiple databases while maintaining code simplicity. Therefore, a lightweight and highly compatible method is needed to enable seamless system switching between multiple databases.
[0025] Based on this, an exemplary embodiment of the present invention provides a database adaptation system, preferably applied to a SpringBoot+Mybatis architecture, with SpringBoot (a rapid development framework based on the Spring framework) as the application layer and the database adaptation system set in Mybatis (an ORM architecture), but it is not limited to this and can be selected according to the actual situation.
[0026] See Figure 1 The system includes: The database adaptation layer 11 includes multiple database adapters, each of which corresponds to a database.
[0027] The SQL processing layer 12 is used to obtain the SQL (Structured Query Language, the standard language for managing and manipulating relational databases) statement to be processed, and to call the target database adapter in the database adaptation layer 11 to transform the SQL statement to be processed. The database corresponding to the target database adapter corresponds to the database of the SQL statement to be processed, and the transformed SQL statement to be processed is used for data processing in the corresponding database.
[0028] Among them, multiple databases include, but are not limited to, MySQL, DM Database, and Kingbase, and therefore multiple database adapters also include, but are not limited to, MySQL adapter, DM Database adapter, and Kingbase adapter.
[0029] For example, when the SQL statement to be processed contains MySQL-style LIMIT pagination syntax, and the database corresponding to the SQL statement to be processed is DM database, the SQL processing layer 12 will call the DM database adapter to convert the LIMIT syntax into a nested pagination statement based on ROWNUM.
[0030] The SQL processing layer 12 is used to achieve SQL syntax compatibility across multiple databases. The SQL statements to be processed typically originate from the application layer. The database for the SQL statements to be processed can be specified by the application layer, by the database management layer 13, or by the SQL processing layer 12 based on the currently active database among the multiple databases.
[0031] For example, the processing flow for the SQL statement to be processed in the SQL processing layer 12 is as follows: Obtain the SQL statement to be processed. Identify the currently active database from multiple databases as the database corresponding to the SQL statement. Determine the target database adapter based on the database corresponding to the SQL statement, and then use the target adapter to transform the SQL statement. The code can be seen in the example below, but is not limited to: @Component @Intercepts({@Signature(type = Executor.class, method = "query", args= {...})}) public class SqlTemplateInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { / / Get the SQL statement to be processed BoundSql boundSql = ...; String originalSql = boundSql.getSql(); / / Get the target database adapter DatabaseAdapter adapter = adapterFactory.getCurrentAdapter(); / / SQL conversion String transformedSql = sqlTransformer.transform(originalSql,adapter); / / Generate the converted SQL statement to be processed BoundSql newBoundSql = createNewBoundSql(ms, boundSql, transformedSql); return invocation.proceedWithNewBoundSql(newBoundSql); } } In addition, the converted SQL statements are used to perform data processing such as data querying, data insertion, data updating, data deletion, database structure creation and modification in the corresponding database.
[0032] In this embodiment, after receiving the SQL statement to be processed from the application layer, the database adapter corresponding to the current SQL statement to be processed is automatically selected by identifying the database corresponding to the current SQL statement to be processed. The database adapter then converts the SQL statement to be processed so that the converted SQL statement to be processed can meet the syntax requirements of the target database. Thus, compatibility and unification of multiple databases can be achieved without modifying the business code.
[0033] In one embodiment, the database adaptation layer 11 further includes an adapter interface for each database adapter, which is invoked by the SQL processing layer 12.
[0034] In one embodiment, each of the database adapters includes one or more of a pagination SQL converter, a function converter, and a keyword converter.
[0035] For example, some database adapters may only include a pagination SQL converter, while others may include both a function converter and a keyword converter, depending on the configuration requirements.
[0036] In one embodiment, the SQL processing layer 12 is also used to record abnormal states during the transformation process of the SQL statement to be processed.
[0037] Among them, abnormal states include conversion timeout, conversion failure, etc. In addition to recording abnormal states, the SQL processing layer 12 can also record the reasons that cause abnormal states.
[0038] In addition, the SQL processing layer 12 is also used to issue alerts for abnormal states during the transformation process of the SQL statement to be processed. The code can be seen in the following example, but is not limited thereto: @Component public class SqlTransformMonitor { public void recordTransformMetric(String originalSql, StringtransformedSql, DbType dbType, long costTime) { / / Conversion process record metricCollector.record("sql.transform.cost", costTime); metricCollector.record("sql.transform.count", 1); / / Alert if (costTime>threshold) { alertService.sendAlert("SQL conversion warning", ...); } } } In one embodiment, the SQL processing layer 12 is also used to cache the transformed SQL statements to be processed.
[0039] In this embodiment, in order to improve performance, the SQL processing layer 12 can also cache the converted SQL statement to be processed, so that when the same SQL statement to be processed is used again under the same database, it can be read directly from the cache, thereby reducing the performance loss caused by repeated conversion.
[0040] In one embodiment, see Figure 2 The system also includes: Database management layer 13 is used to register and / or switch databases.
[0041] During system initialization, each database can be registered using corresponding configuration files or code, and multiple databases can be integrated into a unified management system via the database management layer 13. When database access is required during operation, the database corresponding to the SQL statement to be processed can be indexed by setting the corresponding database identifier.
[0042] In an optional implementation, to ensure the stability of system operation, the database management layer 13 can also monitor the online status of each database via JDBC, for example, by periodically sending heartbeat queries to determine whether the database is available. The code for this can be seen in the following example, but is not limited thereto: @Service public class DataSourceSwitchService { public void switchDataSource(String dataSourceKey) { / / Verify that the database exists DynamicDataSourceContextHolder.setDataSourceKey(dataSourceKey); / / Monitor database online status performHealthCheck(dataSourceKey); } } When the database corresponding to the SQL statement to be processed is offline, the database management layer 13 can also switch the database corresponding to the SQL statement to be processed. Specifically, it can specify any database from the online databases as the database corresponding to the SQL statement to be processed.
[0043] An exemplary embodiment of the present invention also provides a database adaptation method, see below. Figure 3 The method includes: S301. Obtain the SQL statement to be processed.
[0044] S302. Call the target database adapter among multiple database adapters to transform the SQL statement to be processed.
[0045] The database adapter corresponds one-to-one with the database, and the database corresponding to the target database adapter corresponds to the database of the SQL statement to be processed. The converted SQL statement to be processed is used for data processing in the corresponding database.
[0046] In one embodiment, each of the database adapters includes one or more of a pagination SQL converter, a function converter, and a keyword converter.
[0047] In one embodiment, the method further includes: Register the database.
[0048] In one embodiment, the method further includes: Switch databases.
[0049] In one embodiment, the method further includes: The online status of the database is monitored using JDBC.
[0050] In one embodiment, the method further includes: Record any abnormal states that occur during the conversion process of the SQL statement to be processed; In one embodiment, the method further includes: The converted SQL statements to be processed are cached.
[0051] In one embodiment, each database adapter is invoked through its corresponding adapter interface.
[0052] An exemplary embodiment of the present invention also provides an electronic device, see below. Figure 4 The electronic device 400 can vary significantly due to differences in configuration or performance. It may include one or more central processing units (CPUs) 410 (CPUs 410 may include, but are not limited to, microprocessors (MCUs) or programmable logic devices (FPGAs), a memory 430 for storing data, and one or more storage media 420 (e.g., one or more mass storage devices) for storing application programs 423 or data 422. The memory 430 and storage media 420 may be temporary or persistent storage. The storage media 420 stores at least one instruction, at least one program, code set, or instruction set. The processor loads and executes the at least one instruction, at least one program, code set, or instruction set to implement the aforementioned database adaptation method.
[0053] Furthermore, the central processing unit 410 can be configured to communicate with the storage medium 420 and execute a series of instructions stored in the storage medium 420 on the electronic device 400. The electronic device 400 may also include one or more power supplies 460, one or more wired or wireless network interfaces 450, one or more input / output interfaces 440, and / or one or more operating systems 421, such as Windows Server™, MacOSX™, Unix™, Linux™, FreeBSD™, etc.
[0054] The input / output interface 440 can be used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of the electronic device 400. In one example, the input / output interface 440 includes a network interface controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the input / output interface 440 may be a radio frequency (RF) module used for wireless communication with the Internet.
[0055] Those skilled in the art will understand that Figure 4 The structure shown is for illustrative purposes only and does not limit the structure of the electronic device described above. For example, electronic device 400 may also include... Figure 4 The more or fewer components shown, or having the same Figure 4 The different configurations shown.
[0056] Embodiments of the present invention also provide a computer-readable storage medium, which can be disposed in a server to store at least one instruction, at least one program, code set, or instruction set related to implementing a database adaptation method in an embodiment of the database adaptation method. The at least one instruction, the at least one program, the code set, or the instruction set is loaded and executed by the processor to implement the above-described database adaptation method.
[0057] Optionally, in this embodiment, the storage medium may be located at at least one of the multiple network servers in a computer network. Optionally, in this embodiment, the storage medium may include, but is not limited to, 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.
[0058] Embodiments of the present invention also provide a computer program product, including a computer program that, when executed by a processor, implements the above-described database adaptation method.
[0059] The program code for executing the computer program product disclosed herein can be written in any combination of one or more programming languages. The program code can be executed entirely on a user device, partially on a user device, as a stand-alone software package, partially on a user device and partially on a remote device, or entirely on a remote device.
[0060] As is known from common technical knowledge, this invention can be implemented through other embodiments that do not depart from its spirit or essential characteristics. Therefore, the disclosed embodiments described above are merely illustrative in all respects and are not the only ones. All modifications within the scope of this invention or its equivalents are included in this invention.
[0061] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0062] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0063] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0064] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0065] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and not to limit it. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that modifications or equivalent substitutions can still be made to the specific implementation of the present invention. Any modifications or equivalent substitutions that do not depart from the spirit and scope of the present invention should be covered within the scope of protection of the claims of the present invention.
Claims
1. A database adaptation system, characterized in that, The system includes: The database adaptation layer includes multiple database adapters, each corresponding to a database. The SQL processing layer is used to obtain the SQL statement to be processed and call the target database adapter in the database adaptation layer to transform the SQL statement to be processed. The database corresponding to the target database adapter corresponds to the database of the SQL statement to be processed. The transformed SQL statement to be processed is used for data processing in the corresponding database.
2. The database adaptation system as described in claim 1, characterized in that, Each of the database adapters includes one or more of a pagination SQL converter, a function converter, and a keyword converter.
3. The database adaptation system as described in claim 1, characterized in that, The system also includes: Database management layer, used for registering and / or switching databases.
4. The database adaptation system as described in claim 3, characterized in that, The database management layer is also used to monitor the online status of the database via JDBC.
5. The database adaptation system as described in claim 1, characterized in that, The SQL processing layer is also used to record abnormal states during the transformation process of the SQL statement to be processed; And / or, the SQL processing layer is also used to cache the transformed SQL statements to be processed.
6. The database adaptation system as described in claim 1, characterized in that, The database adaptation layer also includes an adapter interface for each database adapter, which is called by the SQL processing layer.
7. A database adaptation method, characterized in that, The method includes: Retrieve the SQL statements to be processed; The target database adapter among multiple database adapters is invoked to transform the SQL statement to be processed. Each database adapter corresponds to a database, and the database corresponding to the target database adapter corresponds to the database of the SQL statement to be processed. The transformed SQL statement to be processed is used for data processing in the corresponding database.
8. An electronic device, characterized in that, The electronic device includes a processor and a memory, wherein the memory stores at least one instruction, at least one program, code set, or instruction set, and the processor loads and executes the at least one instruction, at least one program, code set, or instruction set to implement the database adaptation method as described in claim 7.
9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores at least one instruction or at least one program, which is loaded and executed by a processor to implement the database adaptation method as described in claim 7.
10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by the processor, it implements the database adaptation method as described in claim 7.