Multi-database type adaptive software development method and system based on MyBatis ecology and medium

Abstract

The invention discloses a multi-database type adaptation software development method and system based on MyBatis ecology and a medium. The method comprises the steps that a multi-layer architecture system composed of a MyBatis core framework, a MyBatis-Plus extension assembly and a MyBatis-Plus-Join association query assembly is constructed; a universal CRUD interface provided by MyBatis-Plus is dynamically combined through Java business logic codes, and basic database operation is achieved; for a complex business scene, a chain type API of MyBatis-Plus-Join is adopted to realize multi-table association query, and direct compiling of an SQL is avoided; when SQL must be compiled, a multi-version SQL mapping file library is established, and SQL statements corresponding to dialects are dynamically loaded according to the type of an actually deployed database; and the optimal execution scheme is automatically matched through a runtime database type detection module. According to the method, by automatically identifying database dialects, unifying API interfaces and dynamically loading strategies, decoupling of codes and database types is achieved, and development efficiency and system expansibility are remarkably improved.

Description

Technical Field

[0001] This invention relates to the field of hydrological database development, and in particular to a method, system, and medium for developing multi-database type adaptation software based on the MyBatis ecosystem. Background Technology

[0002] The necessity of multi-database adaptation: With the digital transformation of enterprises, application systems often need to support multiple databases (such as MySQL, Oracle, PostgreSQL, and domestic databases) to meet the performance, cost, and domestic substitution requirements of different scenarios. However, the differences in SQL dialects among different databases (such as pagination syntax and function calls) lead to a surge in development and maintenance costs.

[0003] Limitations of ORM frameworks: While mainstream ORM frameworks (such as MyBatis and Hibernate) simplify basic CRUD operations, they lack sufficient support for dynamic adaptation to multiple databases. For example:

[0004] MyBatis requires manual writing of differentiated SQL and cannot automatically adapt to database types;

[0005] While MyBatis-Plus offers a pagination plugin, it lacks cross-framework collaboration capabilities (such as dynamic syntax transformation for join queries).

[0006] Similar implementation schemes

[0007] Option 1: Multi-data source configuration based on MyBatis-Plus

[0008] Implementation principle:

[0009] Use the @DS annotation in MyBatis-Plus to switch data sources, relying on DynamicDataSourceAutoConfiguration to automatically load the connection pool.

[0010] Generate basic pagination SQL using the PaginationInterceptor pagination plugin (only supports some databases).

[0011] limitation:

[0012] Insufficient dialect compatibility: The pagination plugin only supports common databases such as MySQL and PostgreSQL, and cannot automatically adapt to the pagination syntax of domestic databases (such as DM).

[0013] Missing join query: Cross-database multi-table join operations cannot be performed via API; SQL still needs to be written manually.

[0014] Option 2: Database middleware based on ShardingSphere

[0015] Implementation principle:

[0016] The dialect was rewritten using the SQL parsing engine to unify the syntax for pagination, sorting, and other functions.

[0017] It supports read / write separation and database sharding, but requires independent deployment of middleware services.

[0018] limitation:

[0019] Highly invasive: Existing SQL statements need to be modified to adapt to ShardingSphere's parsing rules, increasing migration costs.

[0020] Performance overhead: SQL parsing and routing increase network latency, making it unsuitable for high-concurrency scenarios.

[0021] Option 3: Hibernate multi-dialect support based on JPA

[0022] Implementation principle:

[0023] Use Hibernate's Dialect class to generate database-specific SQL, specifying the target database by configuring hibernate.dialect.

[0024] It supports HQL syntax abstraction, reducing the need for direct SQL writing.

[0025] limitation:

[0026] Poor flexibility: Complex queries (such as dynamic condition concatenation) rely on the Criteria API, resulting in low code readability.

[0027] Domestic databases have weak compatibility: Hibernate's official support for databases such as DM and Shentong is lagging behind, requiring custom dialect classes.

[0028] Disadvantages of existing technology:

[0029] Code redundancy and maintenance difficulties: Traditional solutions require writing multiple sets of SQL statements for different databases, for example:

[0030] Pagination differences: MySQL uses LIMIT, while Oracle relies on ROWNUM, requiring developers to manually maintain two sets of logic.

[0031] Function compatibility: For example, the date functions NOW() (MySQL) and SYSDATE (Oracle) need to be handled separately.

[0032] Dynamic data source switching is complex: Existing solutions (such as Spring dynamic data source) require manual configuration of multiple connection pools and cannot automatically identify the database type, resulting in configuration redundancy and runtime errors.

[0033] Lack of cross-framework collaboration capabilities: A single framework (such as MyBatis-Plus) only solves some problems and lacks a mechanism to integrate other components (such as MyBatis-Plus-Join), resulting in poor cross-database compatibility in complex scenarios such as multi-table queries and transaction management. Summary of the Invention

[0034] The purpose of this application is to provide a multi-database type adaptation software development method, system, and medium based on the MyBatis ecosystem. By automatically identifying database dialects, unifying API interfaces, and using dynamic loading strategies, it achieves decoupling of code and database types, significantly improving development efficiency and system scalability.

[0035] To achieve the above objectives, this application provides the following technical solution:

[0036] In a first aspect, embodiments of this application provide a method for developing multi-database type adaptation software based on the MyBatis ecosystem, comprising the following steps:

[0037] Construct a multi-layered architecture system consisting of the MyBatis core framework, MyBatis-Plus extended components, and MyBatis-Plus-Join relational query components;

[0038] Basic database operations can be implemented by dynamically combining the generic CRUD interfaces provided by MyBatis-Plus through Java business logic code.

[0039] For complex business scenarios, the chained API of MyBatis-Plus-Join is used to implement multi-table join queries, avoiding the need to write SQL directly;

[0040] When SQL must be written, a multi-version SQL mapping file library is established to dynamically load the corresponding dialect SQL statements according to the actual database type deployed.

[0041] The optimal execution plan is automatically matched through the runtime database type detection module.

[0042] The general CRUD interface uses a dynamic condition builder to generate type-safe query conditions. The condition combination methods include: nested condition expression tree construction; dynamic field name mapping verification; and type-adaptive parameter value processing.

[0043] During the pre-release phase, an automated testing framework is used to verify the compatibility of all SQL statements with the target database.

[0044] Secondly, this application provides a multi-database type adaptation software development system based on the MyBatis ecosystem. The system includes a memory and a processor. The memory includes a program for a multi-database type adaptation software development method based on the MyBatis ecosystem. When the program for the multi-database type adaptation software development method based on the MyBatis ecosystem is executed by the processor, it performs the following steps: constructing a multi-layer architecture system composed of the MyBatis core framework, MyBatis-Plus extension components, and MyBatis-Plus-Join association query components.

[0045] Basic database operations can be implemented by dynamically combining the generic CRUD interfaces provided by MyBatis-Plus through Java business logic code.

[0046] For complex business scenarios, the chained API of MyBatis-Plus-Join is used to implement multi-table join queries, avoiding the need to write SQL directly;

[0047] When SQL must be written, a multi-version SQL mapping file library is established to dynamically load the corresponding dialect SQL statements according to the actual database type deployed.

[0048] The optimal execution plan is automatically matched through the runtime database type detection module.

[0049] Thirdly, embodiments of this application provide a computer-readable storage medium storing program code, which, when executed by a processor, implements the steps of the multi-database type adaptation software development method based on the MyBatis ecosystem as described above.

[0050] Compared with the prior art, the beneficial effects of the present invention are:

[0051] Fully automatic SQL dialect adaptation: Based on dynamic database identification (databaseId) and a custom dialect processor, it automatically converts syntax differences such as pagination and functions (e.g., converting MySQL's LIMIT to Oracle's ROWNUM), reducing the amount of adaptation code by 80% and solving the pain point of traditional solutions requiring manual writing of multiple sets of SQL.

[0052] Unified cross-database operation interface: Integrates MyBatis-Plus-Join's chained API to achieve unified syntax for multi-table joins (such as selectJoinPage() automatically generating Join statements), improving development efficiency by 60%, while middleware such as ShardingSphere requires rewriting SQL and incurs high performance overhead.

[0053] Lightweight dynamic scalability: Through annotation-driven (@DS) and plug-in architecture, it supports rapid integration with domestic databases (DM, OceanBase), shortening the adaptation cycle from 2 weeks to 1 day, and eliminating middleware network latency, improving response speed by 40% in high-concurrency scenarios. Attached Figure Description

[0054] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments of this application will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this application and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0055] Figure 1 This is a flowchart of the method of the present invention. Detailed Implementation

[0057] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without inventive effort are within the scope of protection of this invention.

[0058] It should be noted that the descriptions involving "first," "second," etc., in this invention are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined with "first" or "second" may explicitly or implicitly include at least one of that feature. Furthermore, the technical solutions of the various embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. If the combination of technical solutions is contradictory or impossible to implement, it should be considered that such a combination of technical solutions does not exist and is not within the scope of protection claimed by this invention.

[0059] Please see Figure 1 This application provides a method for developing multi-database type adaptation software based on the MyBatis ecosystem, including the following steps:

[0060] Construct a multi-layered architecture system consisting of the MyBatis core framework, MyBatis-Plus extended components, and MyBatis-Plus-Join relational query components;

[0061] By dynamically combining the general CRUD interfaces provided by MyBatis-Plus (including but not limited to selectList, updateById, lambdaQuery) through Java business logic code, more than 80% of basic database operations can be achieved.

[0062] For complex business scenarios, the chained API of MyBatis-Plus-Join is used to implement multi-table join queries, avoiding the need to write SQL directly;

[0063] When SQL must be written, a multi-version SQL mapping file library is established to dynamically load the corresponding dialect SQL statements according to the actual deployed database type (MySQL / Oracle / PostgreSQL, etc.);

[0064] The optimal execution plan is automatically matched through the runtime database type detection module.

[0065] The general CRUD interface calls use dynamic condition constructors (such as QueryWrapper and LambdaQueryWrapper) to generate type-safe query conditions, and the condition combination methods include:

[0066] Nested conditional expression tree construction,

[0067] Dynamic field name mapping validation

[0068] Type-adaptive parameter value handling.

[0069] The multi-version SQL mapping file library adopts the following storage structure:

[0070] text

[0071] / sql-mappings /

[0072] ├── order_operation /

[0073] │ ├── mysql.xml

[0074] │ ├── oracle.xml

[0075] │ └── postgresql.xml

[0076] └── user_operation /

[0077] ├── mysql.xml

[0078] └── oracle.xml

[0079] When loading a file, the corresponding mapping file in the subdirectory is automatically selected based on the current data source identifier.

[0080] It also includes a SQL dialect adaptation layer, which provides:

[0081] Automatic conversion of pagination statements (converting MyBatis-Plus pagination objects to dialects such as ROWNUM / LIMIT-OFFSET).

[0082] Function name mapping table (e.g., mapping NOW() to SYSDATE)

[0083] Batch operation syntax optimizer.

[0084] During the pre-release phase, an automated testing framework is used to verify the compatibility of all SQL statements with the target database, including: a syntax compatibility testing module, an execution plan analysis module, and a performance benchmark comparison module.

[0085] The core of this invention lies in integrating the MyBatis framework family (MyBatis, MyBatis-Plus, MyBatis-Plus-Join) and introducing a dynamic adaptation layer to achieve seamless compatibility across multiple databases through the following modules:

[0086] Framework Collaboration Layer: Utilizes MyBatis to provide basic ORM capabilities, MyBatis-Plus enhances CRUD operations and pagination dialect adaptation, and MyBatis-Plus-Join enables cross-database table join queries.

[0087] Dynamic adaptation layer: Automatically identifies the database type based on databaseId and loads the corresponding dialect processor (such as pagination and function syntax conversion).

[0088] Unified API Layer: Encapsulates interfaces from multiple frameworks, provides standardized data manipulation APIs (such as selectJoinPage()), and shields the underlying database differences.

[0089] 2. Implementation details of core modules

[0090] 2.1 Dynamic Data Source Management

[0091] Multiple data source configuration: Define multiple data sources using Spring Boot's @Configuration annotation and dynamically switch between them using the @DS("data source identifier") annotation (example):

[0092] 1. Dynamic data source configuration (Spring Boot YAML)

[0093]

[0094] 2. Dynamic data source routing configuration class

[0095]

[0096] Dynamic routing algorithm: Based on DataSourceContextHolder, obtain the current thread's database identifier, and implement dynamic switching of the connection pool through AbstractRoutingDataSource.

[0097] Dynamic routing algorithm code example

[0098] 1. Custom dynamic data source routing class

[0099]

[0100] 2. Data source context holder (implemented using ThreadLocal)

[0101]

[0102] 2.2 Automatic Dialect Adaptation in Database

[0103] Dialect processor registration: Custom DialectHandler interface to implement syntax conversion logic for different databases (core code example):

[0104] 3. Custom dialect processor (pagination syntax adaptation)

[0105]

[0106] Automatic identification and loading: The current database type is identified through MyBatis's databaseIdProvider (e.g., Oracle returns oracle), and the corresponding dialect handler is dynamically registered in conjunction with the Spring container.

[0107] 4. Dynamic Dialect Interceptor (MyBatis Plugin)

[0108]

[0109] 2.3 Unified Query Interface Design

[0110] Standardize CRUD operations: Inherit from MyBatis-Plus's BaseMapper, extend common methods (such as selectByCondition()), and automatically adapt to the concatenation of WHERE conditions for different databases.

[0111] Multi-table query encapsulation: Using MyBatis-Plus-Join's Wrapper to construct Join conditions and automatically generate cross-database compatible SQL (example):

[0112] 5. Unified Query Interface (MyBatis-Plus-Join Example)

[0113]

[0114] 3. Key Innovations

[0115] Dynamic adaptation mechanism: Through databaseId and SPI (Service Provider Interface) mechanism, plug-in extension of domestic databases (such as DM) can be realized without modifying the core code.

[0116] Performance optimization strategy: Use caching preheating technology to preload commonly used database dialect processors to reduce runtime performance loss. Actual test results show that query response time is reduced by 30%.

[0117] Unified transaction management: Based on Spring AOP interception of the @Transactional annotation, combined with ChainedTransactionManager, cross-data source transaction consistency is achieved.

[0118] 4. Verification of technical effectiveness

[0119] Compatibility testing: Supports 6 databases including MySQL, Oracle, PostgreSQL, and DM, with a 100% success rate for executing the same API.

[0120] Efficiency comparison: Compared to traditional solutions, the development cycle is shortened by 50%, and the code reuse rate is increased from 40% to 90%.

[0121] Performance metrics: Single data source switching time <10ms, pagination query performance difference from native SQL <5%.

[0122] This application provides a multi-database type adaptation software development system based on the MyBatis ecosystem. The system includes a memory and a processor. The memory includes a program for a multi-database type adaptation software development method based on the MyBatis ecosystem. When the program for the multi-database type adaptation software development method based on the MyBatis ecosystem is executed by the processor, it implements the steps of the multi-database type adaptation software development method based on the MyBatis ecosystem as described above.

[0123] This application provides a computer-readable storage medium storing program code. When the program code is executed by a processor, it implements the steps of the multi-database type adaptation software development method based on the MyBatis ecosystem as described above.

[0124] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application 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.

[0125] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. 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... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

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

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

[0128] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0129] Memory may include non-persistent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0130] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0131] The above description is merely an embodiment of this application and is not intended to limit the scope of protection of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of protection of this application.

[0132] The above description is merely an embodiment of this application and is not intended to limit the scope of protection of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of protection of this application.

Claims

1. A multi-database type adaptation software development method based on MyBatis ecology, characterized in that, The method comprises the following steps: a multi-layer architecture system composed of a MyBatis core framework, a MyBatis-Plus extension component and a MyBatis-Plus-Join associated query component is constructed; basic database operations are implemented by dynamically combining a general CRUD interface provided by MyBatis-Plus through Java business logic code; for a complex business scenario, a chain API of MyBatis-Plus-Join is used to implement multi-table associated query, so as to avoid direct SQL writing; when it is necessary to write SQL, a multi-version SQL mapping file library is established, and SQL statements of a corresponding dialect are dynamically loaded according to an actually deployed database type; an optimal execution scheme is automatically matched through a runtime database type detection module.

2. The multi-database type adaptation software development method based on MyBatis ecology according to claim 1, characterized in that, The general CRUD interface is called by using a dynamic condition constructor to generate type-safe query conditions, and the condition combination mode includes: nested condition expression tree construction, dynamic field name mapping verification and type-adaptive parameter value processing.

3. The multi-database type adaptation software development method based on MyBatis ecology according to claim 1, characterized in that, In the pre-release stage, all SQL statements are verified for compatibility with a target database through an automatic test framework.

4. A multi-database type adaptation software development system based on MyBatis ecology, characterized in that, The system comprises a memory and a processor, the memory comprises a program of a MyBatis ecological multi-database type adaptation software development method, and the program, when executed by the processor, implements the following steps: a multi-layer architecture system composed of a MyBatis core framework, a MyBatis-Plus extension component and a MyBatis-Plus-Join associated query component is constructed; basic database operations are implemented by dynamically combining a general CRUD interface provided by MyBatis-Plus through Java business logic code; for a complex business scenario, a chain API of MyBatis-Plus-Join is used to implement multi-table associated query, so as to avoid direct SQL writing; when it is necessary to write SQL, a multi-version SQL mapping file library is established, and SQL statements of a corresponding dialect are dynamically loaded according to an actually deployed database type; an optimal execution scheme is automatically matched through a runtime database type detection module. 5.A computer readable storage medium, the computer readable storage medium stores program code, the program code is executed by a processor to implement the steps of the MyBatis ecological multi-database type adaptation software development method in any one of claims 1-3.

Images