Database adaptation method and system for database data interaction
By detecting and replacing database keywords and functions, data interaction between different databases is achieved, adaptation difficulties and duplicate work problems caused by mismatch in database types are solved, and efficiency and accuracy are improved.
Patent Information
- Application Number
- CN202411015523.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-26
- Publication Date
- 2024-12-06
AI Technical Summary
When processing different types of databases, the prior art requires frequent adjustment of database connection methods and SQL statements, which are time-consuming and error-prone, and the adaptation work between different projects is highly repetitive.
By obtaining the source database type, generating the corresponding database instance and SQL processor, using preset interceptors to intercept SQL operations, detecting and replacing specific keywords and functions of the target database, realizing data interaction across database types.
It reduces manual operations, improves data interaction efficiency, avoids errors caused by type mismatch, and realizes general adaptation to different databases.
Smart Images

Figure CN119088784A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the technical field of database data management, and in particular to a database adaptation method and system for database data interaction. Background Art
[0002] At present, when delivering computing software systems, we often encounter situations where the customer's database and the database type that the software system relies on are inconsistent. At this time, we need to readjust the system's database connection method and SQL statements according to the customer's database type. This method is not only time-consuming, but also prone to errors and omissions. And when connecting to other projects, the same work may need to be repeated. Therefore, it is necessary to design a more general database adaptation method to be compatible and adaptable to different databases. Summary of the invention
[0003] The main technical problem solved by the present invention is how to adapt and be compatible with different types of databases to achieve normal data interaction.
[0004] According to the first aspect, an embodiment provides a database adaptation method for database data interaction, including: Get the database type of the source database to be interacted with; Obtaining a database instance generation method and an SQL processor of the source database according to the database type; Generate a connection instance for data interaction with the source database; Intercepting SQL operations of the source database during data interaction through a preset interceptor; Detecting database keywords and / or database functions that are different from those of the target database in each SQL statement in the SQL operation; Different database keywords and / or database functions in the SQL operation are replaced, and the target database is operated according to the replaced SQL operation to complete data interaction from the source database to the target database.
[0005] In one embodiment, the step of obtaining the database type of the source database to be interacted with includes: Dynamically obtain the type of the source database according to the nacos configuration of calling the source database; The type of the source database includes a MySQL database; The type of the target database is DAMO database or OceanBase database.
[0006] In one embodiment, the step of obtaining a database instance generation method and an SQL processor of the source database according to the database type includes: Applying the data source instance factory to generate a connection instance of the source database according to the database instance generation method and / or data pool connection parameters; The data pool connection parameters include database connection URL, database connection port, database name, database connection user name, database connection password, database type, database driver name, connection pool initialization size, connection pool maximum number and / or connection pool minimum number.
[0007] In one embodiment, the detecting that each SQL statement in the SQL operation has a database keyword and / or a database function that is different from that of the target database includes: Check database keywords or database functions in the SQL statement that are different from those in the target database one by one by calling a chain; The database keywords and / or database functions include: `, concat, date_add, date_sub, date_format, str_to_date, ifnull and / or now().
[0008] In one embodiment, replacing different database keywords and / or database functions in the SQL operation and operating the target database according to the replaced SQL operation includes: When the database keyword and / or the database function to be replaced is not matched, it is directly skipped; When a database keyword or database function that needs to be replaced is matched, the SQL statement is replaced by synchronizing each database keyword and database function in a call chain manner; The database operation object is repackaged according to the replaced SQL statement to complete the operation on the target database.
[0009] In one embodiment, intercepting the SQL operation of the source database during data interaction by a preset interceptor includes: The preset interceptor includes Mybatis interceptor; The SQL operation includes a query operation on a database and / or a modification operation on database data.
[0010] The database adaptation method as described in claim 1 is characterized in that the database instance generation method includes a Mysql instance generation method, a DAMO database generation method and / or an OceanBase database generation method.
[0011] According to the second aspect, an embodiment provides a computer-readable storage medium, on which a computer program is stored. The computer program can be executed by a processor to implement the database adaptation method as described in the first aspect.
[0012] According to the third aspect, an embodiment provides a computer program product, including a computer program and / or instructions, which implement the database adaptation method described in the first aspect when executed by a processor.
[0013] According to a fourth aspect, an embodiment provides a database adaptation system for database data interaction, characterized in that it is used to apply the database adaptation method as described in the first aspect, and the database adaptation system includes: A type acquisition unit, used to acquire the database type of the source database to be interacted with; A parameter acquisition unit, used to acquire a database instance generation method and an SQL processor of the source database according to a database type; An instance acquisition unit, used to generate a connection instance for data interaction with the source database; An interception unit, used to intercept SQL operations of the source database during data interaction through a preset interceptor; A difference detection unit, used for detecting database keywords and / or database functions that are different from those of the target database in each SQL statement in the SQL operation; The replacement execution unit is used to replace different database keywords and / or database functions in the SQL operation, and operate the target database according to the replaced SQL operation to complete the data interaction from the source database to the target database.
[0014] According to the database adaptation method of the above embodiment, for databases with mismatched types, data interaction is achieved by replacing different database keywords and database function methods in SQL statements, thereby achieving adaptation of the system database. This not only reduces manual operations but also greatly improves the work efficiency of data interaction. BRIEF DESCRIPTION OF THE DRAWINGS
[0015] Figure 1 A schematic diagram of a flow chart of a database adaptation method in an embodiment; Figure 2 A structural block diagram of a database adaptation system in an embodiment; Figure 3 The figure is a flowchart of a database adaptation method in another embodiment. DETAILED DESCRIPTION
[0016] The present invention is further described in detail below by specific embodiments in conjunction with the accompanying drawings. Wherein similar elements in different embodiments adopt associated similar element numbers. In the following embodiments, many detailed descriptions are for making the present application better understood. However, those skilled in the art can easily recognize that some features can be omitted in different situations, or can be replaced by other elements, materials, methods. In some cases, some operations related to the present application are not shown or described in the specification, this is to avoid the core part of the present application being overwhelmed by too much description, and for those skilled in the art, it is not necessary to describe these related operations in detail, and they can fully understand the related operations according to the description in the specification and the general technical knowledge in the art.
[0017] In addition, the features, operations or characteristics described in the specification can be combined in any appropriate manner to form various implementations. At the same time, the steps or actions in the method description can also be interchanged or adjusted in a manner that is obvious to those skilled in the art. Therefore, the various sequences in the specification and the drawings are only for the purpose of clearly describing a certain embodiment and are not meant to be a required sequence, unless otherwise specified that a certain sequence must be followed.
[0018] The serial numbers assigned to the components herein, such as "first", "second", etc., are only used to distinguish the objects described and do not have any order or technical meaning. The "connection" and "coupling" mentioned in this application, unless otherwise specified, include direct and indirect connections (couplings).
[0019] When the software system is deployed locally, the database type mismatch problem is usually solved by directly modifying the system's database connection method and SQL statement, which is time-consuming, labor-intensive, and error-prone. In one embodiment of the present application, a customized database adaptation system is used to quickly complete the adaptation of the system database by replacing different database keywords and / or database functions in SQL statements, which not only avoids manpower loss, but also greatly shortens the project delivery cycle.
[0020] Embodiment 1: Please refer to Figure 1 , is a flowchart of a database adaptation method in an embodiment, the database adaptation method includes: Step 10: Get the database type.
[0021] Obtain the database type of the source database to be interacted with. In one embodiment, the type of the source database is dynamically obtained according to the nacos configuration of the source database. In one embodiment, the type of the source database includes a Mysql database.
[0022] Step 20: Obtain the instance generation method.
[0023] The database instance generation method and SQL processor of the source database are obtained according to the database type. In one embodiment, the data source instance factory is applied to generate a connection instance of the source database according to the database instance generation method and / or data pool connection parameters. The data pool connection parameters include database connection URL, database connection port, database name, database connection user name, database connection password, database type, database driver name, connection pool initialization size, connection pool maximum number and / or connection pool minimum number.
[0024] Step 30: Generate a connection instance.
[0025] Generate a connection instance for data interaction with a source database. In one embodiment, the database instance generation method includes a Mysql instance generation method, a DAMO database generation method, and / or an OceanBase database generation method.
[0026] Step 40: intercept SQL operations.
[0027] The SQL operation of the source database during data interaction is intercepted by a preset interceptor. In one embodiment, the preset interceptor includes a Mybatis interceptor. The SQL operation includes a query operation on the database and / or a modification operation on the database data.
[0028] Step 50: Detect SQL statement differences.
[0029] Detect database keywords and / or database functions that are different from the target database in each SQL statement in the SQL operation. In one embodiment, the type of the target database is a DAMO database or an OceanBase database. In one embodiment, the database keywords or database functions that are different from the target database in the SQL statement are checked one by one by calling a chain. Among them, the database keywords and / or database functions include: `, concat, date_add, date_sub, date_format, str_to_date, ifnull and / or now().
[0030] Step 60, completing data interaction.
[0031] Replace different database keywords and / or database functions in SQL operations, and operate the target database according to the replaced SQL operations to complete the data interaction from the source database to the target database. In one embodiment, when the database keyword and / or database function that needs to be replaced is not matched, it is directly skipped; when the database keyword or database function that needs to be replaced is matched, the SQL statement is replaced by synchronizing each database keyword and database function through the call chain. For example: if it is a keyword or a database function without parameters, such as "`", "now()", use the regular expression parameter in replaceAll in java to match all for replacement; if it is a database function with parameters, it is necessary to extract the parameters in the database function, assemble it according to the function parameter assembly method of the new database, and then replace the function method keyword.
[0032] In one embodiment, the database operation object is repackaged according to the replaced SQL statement to complete the operation on the target database.
[0033] In one embodiment, when the current database of the software system is MySQL and needs to be replaced with a DAMO database or an OceanBase (Oracle mode) database, it is only necessary to configure the database type that needs to be replaced in nacos; when the system has an interface request involving database operations, the database adapter plug-in will automatically replace the msyql database statement with a database statement that can be executed by the current database, and call the database to complete the interface operation request.
[0034] Please refer to Figure 2 , is a structural block diagram of a database adaptation system in an embodiment. In an embodiment of the present application, a database adaptation system for database data interaction is also disclosed, which is used to apply the database adaptation method as described above. The database adaptation system includes a type acquisition unit 1, a parameter acquisition unit 2, an instance acquisition unit 3, an interception unit 4, a difference detection unit 5 and a replacement execution unit 6. The type acquisition unit 1 is used to obtain the database type of the source database to be interacted with. The parameter acquisition unit 2 is used to obtain the database instance generation method and SQL processor of the source database according to the database type. The instance acquisition unit 3 is used to generate a connection instance for data interaction with the source database. The interception unit 4 is used to intercept the SQL operation of the source database when the data is interacted with through a preset interceptor. The difference detection unit 5 is used to detect the database keywords and / or database functions that are different from the target database in each SQL statement in the SQL operation. The replacement execution unit 6 is used to replace the different database keywords and / or database functions in the SQL operation, and operate the target database according to the replaced SQL operation to complete the data interaction from the source database to the target database.
[0035] In order to facilitate understanding of the database adaptation method disclosed in the present application, the following describes an implementation of the database adaptation method through a specific embodiment, which specifically includes: Please refer to Figure 3 , is a flowchart of a database adaptation method in another embodiment, which specifically includes: Step S100, dynamically obtain the system database type and database instance generation method according to the system's nacos configuration.
[0036] When the system service is started, the system will pull the database type in the nacos configuration. The data source instance factory initializes the corresponding database connection instance based on the Alibaba Druid framework according to the database type. The core parameters for initializing the database instance include: database connection URL, database connection port, database name, database connection user name, database connection password, database type, database driver name, connection pool initialization size, maximum number of connection pools, minimum number of connection pools, and database health check SQL.
[0037] Step S200, generating a current database connection instance.
[0038] Step S300, when the system has an interface request involving database operations, the database SQL operation is intercepted by a custom mybatis interceptor.
[0039] When the user operates the system or starts to execute a system scheduled task, if the above request contains database operations, all database query and modification requests are intercepted by a custom interceptor plug-in. The custom interceptor is implemented by implementing the Interceptor interface under the mybatis framework plugin package. The custom interceptor class declares the operation type, operation method, and operation parameters to be intercepted through @Intercepts. According to the intercept method of the overloaded Interceptor interface, the three core parameters of MappedStatement, Parameter, and BoundSql can be obtained, and then the database SQL for this operation can be obtained according to the BoundSql object. Step S400, obtaining the corresponding database SQL processor.
[0040] After obtaining the database SQL, build the database SQL into the SQL executor parameter context and call the chain execution manager of the current database; in the chain execution manager, loop all SQL keyword or function replacement processors and call the doExecute method in the processor to complete the SQL keyword or function replacement.
[0041] Step S500: Check whether there are SQL keywords or SQL functions in the SQL statement that are different from those in the local database.
[0042] Step S600: If no database keyword or key function that needs to be replaced is matched, it is skipped directly.
[0043] In the SQL keyword or function replacement processor, if the database SQL does not contain the SQL keyword or SQL function to be replaced, the original database SQL is directly returned, otherwise it continues. When matching SQL keywords or functions, it should be noted that SQL keywords or functions may exist as a substring in other SQL keywords or parameters, so if the SQL keyword or SQL function to be replaced is matched, it is also necessary to check whether the characters before and after the keyword are regular characters. If so, the match is skipped directly.
[0044] Step S700, matching the database keywords or key functions that need to be replaced, and replacing the database keywords or key functions one by one by calling the chain.
[0045] If a database keyword or key function that needs to be replaced is matched, the database keyword or key function is replaced one by one through the call chain. When the database keyword or parameterless function needs to be replaced, for example, Mysql indicates that the current time uses the parameterless function "now()", but in the syntax of OceanBase (Oracle mode), the current time uses sysdate, so we only need to match all now() functions through the java function Pattern.compile("now\\(\\)", Pattern.CASE_INSENSITIVE), and then use the replaceAll method of Matcher to complete the replacement of all now() functions in the request SQL.
[0046] As shown in step S700, when the function that needs to be replaced is a function with parameters in the database, it is necessary to first extract all the functions with parameters in the request SQL, replace the parameters in the function with parameters according to the new database syntax, and then reassemble all the replaced functions with parameters with the original SQL to form a new SQL. Take the date_format function replaced with the to_char function of OceanBase (Oracle mode) as an example, if the original request SQL of the system is as follows: select name,date_format(create_date,'%Y-%m-%d %H:%i:%s') as create_time,date_format(update_date,'%Y-%m-%d %H:%i:%s') as update_time from ba_agency; STEP 1: Filter all date_format functions and put them into the SQL function object queue.
[0047] Loop through the characters in the SQL statement, check the start position index and end position index of the date_format keyword in the SQL statement; if "(" appears, add 1 to the number of brackets, if ")" appears, subtract 1 from the number of brackets, and when the number of brackets is 0, the end position index of the date_format function can be obtained, thereby obtaining the SQL sub-statement of the date_format function. Finally, assemble the SQL sub-statement, function keyword start index, function keyword end index, function end index, and SQL sub-statements between functions into a function object and put it in the queue. The function object is as follows: [{oldFunSql:"date_format(create_date,'%Y-%m-%d %H:%i:%s')",keywordStartIndex: "12",keywordEndIndex:"23",funEndIndex:"54",beforeFuncSql:"selectname,"}, {oldFunSql:"date_format(update_date,'%Y-%m-%d %H:%i:%s')", keywordStartIndex:”73”,keywordEndIndex:”84”,funEndIndex:”115”beforeFuncSql: "as create_time,"}]; STEP2: Replace all functions in the SQL statement according to the function syntax of the current database. Loop through the function object queue and obtain the function keywords, for example: The keyword of date_format(create_date,'%Y-%m-%d %H:%i:%s') is date_format, and date_format is replaced with to_char; then replace the function parameters, replace %Y with YYYY to represent the year, replace %m with MM to represent the month, replace %d with DD to represent the day, replace %H with HH24 to represent the hour, replace %i with MI to represent the minute, and replace %s with SS to represent the second. Then replace the original function with to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'), and add the function SQL to the function object. The function object is as follows: [{oldFunSql:"date_format(create_date,'%Y-%m-%d %H:%i:%s')", keywordStartIndex:”12”,keywordEndIndex:”23”,funEndIndex:”54”, beforeFuncSql:"select name," newFunSql:"to_char(create_date,'YYYY-MM-DD HH24:MI:SS')"}, {oldFunSql:"date_format(update_date,'%Y-%m-%d %H:%i:%s')", keywordStartIndex:”73”,keywordEndIndex:”84”,funEndIndex:”115” beforeFuncSql:” as create_time,” newFunSql:"to_char(update_date,'YYYY-MM-DD HH24:MI:SS')"}]; STEP3: Loop through the function object queue and reassemble new SQL statements.
[0048] Circulate the function object queue and reassemble the SQL statement according to the beforeFuncSql field and newFunSql. The example is as follows: "select name," + "to_char(create_date,'YYYY-MM-DD HH24:MI:SS')" + as create_time, + "to_char(update_date,'YYYY-MM-DDHH24:MI:SS')"; According to the function end index of the last function object in the queue as the starting position, the SQL substatement after the last function can be obtained through the substring(startIndex) method of the original SQL, and assembled into a new SQL statement. The example is as follows: "select name," + "to_char(create_date,'YYYY-MM-DD HH24:MI:SS')" + as create_time, + ”to_char(update_date,'YYYY-MM-DDHH24:MI:SS')” + "as update_time from ba_agency"; The new SQL is as follows: select name,to_char(create_date,'YYYY-MM-DD HH24:MI:SS') as create_time, to_char(update_date,'YYYY-MM-DD HH24:MI:SS') as update_time from ba_agency.
[0049] Step S800: re-encapsulate the database operation object according to the replaced statement to complete the database operation.
[0050] After obtaining the new request SQL statement, you need to re-encapsulate the new SQL statement into the BoundSql statement object, then encapsulate BoundSql into the MappedStatement object, and complete subsequent database operation requests based on the new MappedStatement.
[0051] The embodiment of the present application discloses a database adaptation method for database data interaction, firstly obtaining the database type of the source database to be interacted with, and obtaining the database instance generation method and SQL processor of the source database according to the database type; then generating a connection instance for data interaction with the source database; then intercepting the SQL operation of the source database during data interaction through a preset interceptor, and detecting the database keywords and database functions that are different from the target database in each SQL statement in the SQL operation; finally replacing the different database keywords and database functions in the SQL operation, and operating the target database according to the replaced SQL operation to complete the data interaction. Since data interaction is achieved for databases of mismatched types by replacing different database keywords and database functions in SQL statements, and then achieving adaptation of the system database, it not only reduces manual operations, but also greatly improves the working efficiency of data interaction.
[0052] Those skilled in the art will appreciate that all or part of the functions of the various methods in the above-mentioned embodiments can be implemented by hardware or by computer programs. When all or part of the functions in the above-mentioned embodiments are implemented by computer programs, the program can be stored in a computer-readable storage medium, and the storage medium can include: read-only memory, random access memory, disk, optical disk, hard disk, etc., and the program is executed by a computer to implement the above-mentioned functions. For example, the program is stored in the memory of the device, and when the program in the memory is executed by the processor, all or part of the above-mentioned functions can be implemented. In addition, when all or part of the functions in the above-mentioned embodiments are implemented by computer programs, the program can also be stored in a storage medium such as a server, another computer, disk, optical disk, flash disk or mobile hard disk, and can be downloaded or copied and saved in the memory of the local device, or the system of the local device is updated, and when the program in the memory is executed by the processor, all or part of the functions in the above-mentioned embodiments can be implemented.
[0053] The above specific examples are used to illustrate the present invention, which is only used to help understand the present invention and is not intended to limit the present invention. For those skilled in the art, according to the concept of the present invention, some simple deductions, modifications or substitutions can be made.
Claims
1. A database adaptation method for database data interaction, It is characterized in that include: Get the database type of the source database to be interacted with; Obtaining a database instance generation method and an SQL processor of the source database according to the database type; Generate a connection instance for data interaction with the source database; Intercepting SQL operations of the source database during data interaction through a preset interceptor; Detecting database keywords and / or database functions that are different from those of the target database in each SQL statement in the SQL operation; Different database keywords and / or database functions in the SQL operation are replaced, and the target database is operated according to the replaced SQL operation to complete data interaction from the source database to the target database.
2. The database adaptation method according to claim 1, It is characterized in that The step of obtaining the database type of the source database to be interacted with includes: Dynamically obtain the type of the source database according to the nacos configuration of calling the source database; The type of the source database includes a MySQL database; The type of the target database is DAMO database or OceanBase database.
3. The database adaptation method according to claim 1, It is characterized in that The step of obtaining a database instance generation method and an SQL processor of the source database according to the database type includes: Applying the data source instance factory to generate a connection instance of the source database according to the database instance generation method and / or data pool connection parameters; The data pool connection parameters include database connection URL, database connection port, database name, database connection user name, database connection password, database type, database driver name, connection pool initialization size, connection pool maximum number and / or connection pool minimum number.
4. The database adaptation method according to claim 1, It is characterized in that The detecting that each SQL statement in the SQL operation has a different database keyword and / or database function from that of the target database includes: Check database keywords or database functions in the SQL statement that are different from those in the target database one by one by means of a call chain; The database keywords and / or database functions include: `, concat, date_add, date_sub, date_format, str_to_date, ifnull and / or now().
5. The database adaptation method according to claim 4, It is characterized in that The replacing different database keywords and / or database functions in the SQL operation and operating the target database according to the replaced SQL operation includes: When the database keyword and / or the database function to be replaced is not matched, it is directly skipped; When a database keyword or database function that needs to be replaced is matched, the SQL statement is replaced by synchronizing each database keyword and database function in a call chain manner; The database operation object is repackaged according to the replaced SQL statement to complete the operation on the target database.
6. The database adaptation method according to claim 1, It is characterized in that The intercepting the SQL operation of the source database during data interaction by a preset interceptor includes: The preset interceptor includes Mybatis interceptor; The SQL operation includes a query operation on a database and / or a modification operation on database data.
7. The database adaptation method according to claim 1, It is characterized in that The database instance generation method includes a Mysql instance generation method, a DAMO database generation method and / or an OceanBase database generation method.
8. A computer-readable storage medium, It is characterized in that The medium stores a computer program, which can be executed by a processor to implement the database adaptation method according to any one of claims 1 to 7.
9. A computer program product comprising a computer program and / or instructions, It is characterized in that When the computer program and / or the instructions are executed by a processor, the database adaptation method according to any one of claims 1 to 7 is implemented.
10. A database adaptation system for database data interaction, It is characterized in that For applying the database adaptation method according to any one of claims 1 to 7, the database adaptation system comprises: A type acquisition unit, used to acquire the database type of the source database to be interacted with; A parameter acquisition unit, used to acquire a database instance generation method and an SQL processor of the source database according to a database type; An instance acquisition unit, used to generate a connection instance for data interaction with the source database; An interception unit, used to intercept SQL operations of the source database during data interaction through a preset interceptor; A difference detection unit, used for detecting database keywords and / or database functions that are different from those of the target database in each SQL statement in the SQL operation; The replacement execution unit is used to replace different database keywords and / or database functions in the SQL operation, and operate the target database according to the replaced SQL operation to complete the data interaction from the source database to the target database.
