Data migration method and device based on relational database and computer equipment
By synchronizing, filtering and mapping source data, setting migration rules, using Spring framework and JDBC technology to realize data migration, establishing data tables and business rules models, solving the problem that multiple table data cannot be migrated according to business rules in the existing technology. , implements a universal and configurable data migration solution.
Patent Information
- Application Number
- CN202310620248.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2023-05-29
- Publication Date
- 2024-11-29
AI Technical Summary
The prior art cannot migrate data from multiple tables in accordance with business rules according to business rules, resulting in the inability to meet business needs during the data migration process.
By obtaining source data for synchronization, filtering and field mapping, setting data migration rules, and migrating source data to target data according to the migration mode, using Spring framework and JDBC technologies to realize data migration, establishing data table objects and business rules Model to describe business relationships between tables.
It realizes data migration according to business rules, provides full library or on-demand migration, solves the problem that multiple table data cannot be migrated according to business rules in traditional methods, and implements a general and configurable data migration solution.
Smart Images

Figure CN119046257A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of data processing technology, and in particular to a data migration method, device, computer equipment and storage medium based on a relational database. Background Art
[0002] When developing application software based on relational databases, different project phases correspond to different personnel (development, testing, business, etc.), and the data content they focus on is also different. Therefore, there is a need to migrate the data created in a certain project phase environment to another project phase environment. For example, data in the development environment is migrated to the test environment for testers to do testing, or data in the production environment is migrated to the development environment for developers to locate application problems.
[0003] At present, the common data migration methods in the industry are roughly as follows: manual migration, manually copying data from the source system to the target system; database replication, using the database's own replication mechanism to copy data from one database instance to another; database backup and restore, using database backup tools to back up data to files, and then restore the files to the target system; data migration tools, using database migration tools to migrate data from one database to another. This usually supports different databases and provides data conversion and data processing functions; file import and export, exporting data from the original system to files, and then importing the files into the target system; data synchronization tools, using data synchronization tools to synchronize data from one system to another, focusing on differential data synchronization.
[0004] Although the above methods are different, they all ignore business relationship rules in their implementation. In other words, they simply select specific tables and the entire database from the database to migrate data, and cannot migrate data from multiple tables according to business rules. Summary of the invention
[0005] The purpose of the embodiments of the present invention is to propose a data migration method, device, computer equipment and storage medium based on a relational database to solve the problem in the prior art that data in multiple tables cannot be migrated together according to business rules during data migration.
[0006] In order to solve the above technical problems, the present invention provides a data migration method based on a relational database, which adopts the following technical solution, including:
[0007] Acquire source data and synchronize the source data;
[0008] Filter the synchronized source data and map the source data with the target data by fields.
[0009] Set data migration rules;
[0010] According to the data migration rule and migration mode, the source end data is migrated to the target end data.
[0011] Preferably, the step of setting data migration rules specifically includes:
[0012] Edit the unique identification field of the data table object;
[0013] According to the unique identification field, an export business model is created, the data table is mapped with the fields of the export business model, and the exported fields are set.
[0014] Preferably, the step of acquiring source data and synchronizing the source data specifically includes:
[0015] Get data from multiple sources, specify the JDBC connection string of the data source through the interface input or configuration file;
[0016] Use the JDBC connection string to establish a connection with the relational database through JDBC and generate a connection configuration;
[0017] Store the connection configuration with version number information.
[0018] Preferably, the step of screening the synchronized source data and performing field mapping between the source data and the target data specifically includes:
[0019] Query all tables and views under the current database instance through JDBC;
[0020] Select the tables and views that need to be synchronized, and use JDBC to query the table information of the corresponding database according to the table and view names selected on the interface.
[0021] Preferably, the step of migrating the source-end data to the target-end data according to the data migration rule and the migration mode specifically includes:
[0022] When the migration mode is incremental migration, the data value corresponding to the unique key set in the table object is used as a comparison condition to determine whether the source data exists in the target database. If not, the source data is migrated to the target data according to the data migration rule.
[0023] When the migration mode is full migration, the source-end data is migrated to the target-end data according to the data migration rule.
[0024] Preferably, the steps of creating an export business model according to the unique identification field, mapping the data table with the fields of the export business model, and setting the exported fields specifically include:
[0025] Select the field of the business model to be exported, and then select the corresponding data table object from the data table object list, and select the field to be mapped for association;
[0026] Map and export the intermediate table or associated fields from the table in the business model;
[0027] For a single data table or related data tables, set preset business screening rule conditions;
[0028] Set data conversion rules for fields, which can change the exported data for data cleaning and data desensitization;
[0029] When a field corresponds to multiple data tables, sort the list of associated tables to control the order of association.
[0030] Preferably, the step of migrating the source data to the target data specifically includes:
[0031] Use the Spring framework's jdbcTemplate.queryForList to query the query statements generated based on business rules and obtain the data set;
[0032] Based on Java's list collection List and map object Map, package and fill them into files;
[0033] According to the Scheduled task scheduling component of the Spring framework, the import operation is called on a scheduled basis.
[0034] In order to solve the above technical problems, the present invention further provides a data migration device based on a relational database, which adopts the following technical solution, including:
[0035] An acquisition module, used to acquire source data and synchronize the source data;
[0036] The mapping module is used to filter the synchronized source data and map the source data with the target data.
[0037] The settings module is used to set data migration rules;
[0038] A migration module is used to migrate the source end data to the target end data according to the data migration rule and the migration mode.
[0039] In order to solve the above technical problems, the present invention also provides a computer device, which adopts the technical solution described below, including a memory and a processor, wherein the memory stores computer-readable instructions, and when the processor executes the computer-readable instructions, the steps of the above-mentioned data migration method based on a relational database are implemented.
[0040] In order to solve the above technical problems, the present invention also provides a computer-readable storage medium, which adopts the technical solution described below, and stores computer-readable instructions on the computer-readable storage medium. When the computer-readable instructions are executed by the processor, the steps of the above-mentioned data migration method based on the relational database are implemented.
[0041] Compared with the prior art, the present invention mainly has the following beneficial effects: by establishing data table objects and business rule models, the business relationship between tables is modeled and described, so that data migration can be performed according to the agreed rules; according to the business rule model, on-demand import is performed, and business migration functions that were previously required to be achieved by either full database migration or individual table migration, followed by manual database maintenance, or by coding for specific migration functions are provided, which is truly universal and configurable; and the problem that the data of multiple tables cannot be migrated together according to business rules during the migration process of the business system is solved. BRIEF DESCRIPTION OF THE DRAWINGS
[0042] In order to more clearly illustrate the scheme in the present invention, a brief introduction is given below to the drawings required for use in the description of the embodiments of the present invention. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.
[0043] Figure 1 is a flow chart of an embodiment of a data migration method based on a relational database of the present invention;
[0044] Figure 2 It is a schematic diagram of a data model table and a data list used in the data migration method based on a relational database of the present invention;
[0045] Figure 3 is a schematic diagram of business rules used in the data migration method based on a relational database of the present invention;
[0046] Figure 4 It is a structural schematic diagram of an embodiment of a data migration device based on a relational database of the present invention;
[0047] Figure 5 It is a schematic structural diagram of an embodiment of a computer device of the present invention. DETAILED DESCRIPTION
[0048] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by technicians in the technical field of the present invention; the terms used in the specification of the application herein are only for the purpose of describing specific embodiments and are not intended to limit the present invention; the terms "including" and "having" and any variations thereof in the specification and claims of the present invention and the above-mentioned drawings are intended to cover non-exclusive inclusions. The terms "first", "second", etc. in the specification and claims of the present invention or the above-mentioned drawings are used to distinguish different objects, not to describe a specific order.
[0049] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present invention. The appearance of the phrase in various places in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.
[0050] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings.
[0051] It should be noted that the data migration method based on a relational database provided in the embodiment of the present invention is generally executed by a server / terminal device, and accordingly, the data migration device based on a relational database is generally arranged in a server / terminal device.
[0052] It should be understood that the number of terminal devices, networks and servers is merely illustrative and any number of terminal devices, networks and servers may be provided as required.
[0053] The embodiment of the present invention uses the back end developed in Java language, uses the Spring framework to integrate and call its related components (transaction management, file upload and JOB processing, etc.), and the database connection uses the Druid driver (it supports relational databases such as Oracle, MySql, Derby, Postgresql, SQL Server and H2). The front end is used for configuration and display, and is configured and displayed using the corresponding frameworks of html and javascript for explanation.
[0054] Embodiment 1
[0055] Continue to refer Figure 1, shows a flow chart of an embodiment of a data migration method based on a relational database of the present invention. The data migration method based on a relational database comprises the following steps:
[0056] Step S1, obtaining source data and synchronizing the source data.
[0057] In this embodiment, the electronic device (e.g., server / terminal device) on which the data migration method based on the relational database runs can receive the data migration request based on the relational database through a wired connection or a wireless connection. It should be noted that the above wireless connection method may include but is not limited to 3G / 4G / 5G connection, WiFi connection, Bluetooth connection, WiMAXX connection, Zigbee connection, UWB (ultra wideband) connection, and other wireless connection methods currently known or to be developed in the future.
[0058] In this embodiment, step S1 may further include the following steps:
[0059] S11. Obtain data from multiple sources and specify the JDBC connection string of the data source through interface input or configuration file.
[0060] S12. Use the JDBC connection string to establish a connection with a relational database through JDBC and generate a connection configuration.
[0061] Use JDBC connection string to establish connection with relational database such as Mysql, Oracle through JDBC.
[0062] S13. Add the connection configuration and version number information for storage.
[0063] The connection configuration plus the version is stored in the corresponding database for subsequent import and export selection.
[0064] In some optional implementations of this embodiment, the data synchronization direction may also be set, such as one-way synchronization and two-way synchronization. One-way synchronization refers to first selecting source data and destination data, which may be a one-way migration from source data to destination data, or a one-way migration from destination data to source data. Two-way synchronization refers to synchronous migration of source data and destination data.
[0065] Step S2, screening the synchronized source data, and performing field mapping between the source data and the target data.
[0066] In some optional implementations of this embodiment, the data to be migrated can also be filtered, for example, only data within a certain time period, only data in a specific data table can be synchronized, etc. The matching conditions of each field can be filtered to perform selective migration to avoid wasting time on migrating irrelevant data.
[0067] In this embodiment, step S2 may further include the following steps:
[0068] S21. Query all tables and views under the current database instance through JDBC.
[0069] Take MySQL database as an example: select * from information_schema.TABLEStwheret.TABLE_SCHEMA = 'corresponding database name'.
[0070] This result set includes information such as table name, table comment, type (table or view), update time, etc.
[0071] S22. Select the tables and views that need to be synchronized, and use JDBC to query the table information of the corresponding database according to the table and view names selected in the interface.
[0072] For example:
[0073] / / Get the database metadata
[0074] DatabaseMetaData dbMetaData=
[0075] jdbcTemplate.getDataSource().getConnection().getMetaData();
[0076] / / Define the column set
[0077] List <Map<String,Object> > columnsList = newArrayList <Map<String,Object> >();
[0078] / / According to the metadata, get the result set of the selection table
[0079] ResultSetresultSet = dbMetaData.getColumns(null,"database name","selected table name","%");
[0080] / / Traverse the result set
[0081] while(resultSet.next()){
[0082] / / Define a column
[0083] Map<String,Object> column = newHashMap<String,Object> ();
[0084] / / Get the column name
[0085] map.put("COLUMN_NAME",resultSet.getString("COLUMN_NAME"));
[0086] / / Get the column type
[0087] map.put("TYPE_NAME",resultSet.getString("TYPE_NAME"));
[0088] / / Add the column to the column collection
[0089] columnsList.add(column);
[0090] }
[0091] Then add the table information and column information results to the data model table and data list respectively, thus creating a model record related to the table.
[0092] Figure 2 Schematic diagram of the data model table and data list used in the data migration method based on relational database of the present invention. Figure 2 As shown, the data model table has a primary key with a unique number, the data table corresponds to an instance, the data table corresponds to a name, and the data table Chinese name, and the data list has a primary key with a unique number, the data table corresponds to a number, the data column corresponds to a name, and the data column corresponds to a Chinese name. Through the two main tables of the data model table and the data list, a table object is defined in the database. Figure 2 The required fields are shown, and other optional fields can be added according to actual needs.
[0093] Step S3: Setting data migration rules.
[0094] For example, during the life cycle of a software project, major changes need to be performed from time to time, which may require modifying the database to adapt to new behaviors. Most of the time, data changes include simple schema migrations, such as adding new tables, columns, or indexes, but it may actually be necessary to migrate the data itself. Changes in business logic or even new technical requirements may lead to the need to perform data migration. Typically, if data migration is required, it means that you already have a large amount of data. There are many risks in migrating a large amount of data, and there may be many edge cases to mess up the data. Therefore, setting data migration rules in advance and allowing the data to be migrated according to the migration rules can ensure that the data is migrated in an orderly manner and avoid data errors.
[0095] In this embodiment, step S3 may further include the following steps:
[0096] S31. Edit the unique identification field of the data table object.
[0097] After exporting data, this unique identification field will be used as the primary key unique identification for import. And set the Chinese field comment for field identification to facilitate the selection and configuration later.
[0098] S32: Create an export business model according to the unique identification field, map the data table with the fields of the export business model, and set the exported fields.
[0099] In this embodiment, step S32 may further include the following steps:
[0100] S321. Select the field of the exported business model, and then select the corresponding data table object from the data table object list, and select the field to be mapped for association.
[0101] The fields in the user model are associated with the mapping fields of the user table and the organization table respectively.
[0102] S322. Map and export the intermediate table in the business model or the associated fields of the slave table.
[0103] The 'User Number' field in the user model is associated with the 'User Number' field in the user-organization relationship table, and the 'Organization Number' field is associated with the 'Organization Number' field in the user-organization relationship table. Among them, the fields in the exported business model can correspond to the same fields of multiple data table objects. The 'User Number' field in the user model is associated with the 'User Number' field in the user table and the 'User Number' field in the user-organization relationship table. When associating multiple fields, you need to set the primary field for the field so that when the tool background queries the data, it can determine which table the data of the field comes from, so that the business rules can be determined based on these relationship dependencies.
[0104] S323. For a single data table or associated data tables, set preset business screening rule conditions.
[0105] For example, if a user only exports users with the surname Zhang and whose organization type belongs to the department, such a filter is configured as an export condition item in the rule format, and then the described containment, equals, and not equals are converted into logical symbols such as like, =, and ! = recognized by database query statements, and then filtered according to the conditions and constraints. For example, if the creation date is >'2020-10-1', the data with a creation date greater than October 1, 2020 is filtered out.
[0106] S324. Set data conversion rules for fields. The rules can change the exported data for data cleaning and data desensitization.
[0107] In some optional implementations of this embodiment, the source data may also be converted in format according to actual conditions. For example, the time format of the source data is converted to the time format required by the target end. This conversion may also undertake data cleaning tasks, such as desensitizing sensitive data. The data conversion rule may be a fixed value, a regular expression (retaining only data matched by the regular expression), or a function or procedure of a corresponding database (converting data through functions and procedures), etc. In this way, sensitive data may be processed, for example, all amounts may be converted to 0, or dates may be converted to another format, etc.
[0108] S325. When a field corresponds to multiple data tables, sort the associated table list to control the association order.
[0109] Data migration rules can be set by configuring business rules to support which tables need to be migrated through which associations or screening conditions under business rules. Read data from the data model table and data list stored in step S2 and display them. Query the data in the data model table and data list through JDBC, and then store them in the format shown in the following example, and then return them to the front end. Here, as an example, the structure assembly is shown and the corresponding values are given directly.
[0110] / / Define table
[0111] Map<String,Object> table = new HashMap<String,Object> ();
[0112] table.put("schema","schema1"); / / database name
[0113] table.put("tableId","1"); / / The data table corresponds to a unique code
[0114] table.put("tableName","T_USER"); / / Data table corresponding name
[0115] table.put("tableNameCN","User table"); / / The data table corresponds to the Chinese name
[0116] / / Omit other table attributes such as status, notes, etc.
[0117] / / Define columns
[0118] List <object>columns=new ArrayList <object>();
[0119] Map<String,Object> column = new HashMap<String,Object> ();
[0120] column.put("tableId","1"); / / Data table number
[0121] column.put("columnName","userId"); / / Column name
[0122] column.put("columnNameCN","user code"); / / The Chinese name of the column, read from the comment
[0123] / / Omit other column properties, such as column length, column type, column comment, etc.
[0124] / / Add the column to the column set. The column set can have multiple columns. Here we only add one column.
[0125] columns.add(column);
[0126] / / Add the column collection to the table properties so that the table has these columns.
[0127] table.put("columns",columns).
[0128] After configuring the business rules, you can create a business rule model file. The following is an example:
[0129]
[0130]
[0131]
[0132]
[0133] Step S4: Migrate the source data to the target data according to the data migration rules and the migration mode.
[0134] In this embodiment, step S4 may further include the following steps:
[0135] S41. When the migration mode is incremental migration, the data value corresponding to the unique key set in the table object is used as a comparison condition to determine whether the source data exists in the target database. If not, the source data is migrated to the target data according to the data migration rule.
[0136] Incremental migration only migrates data that is not in the target database.
[0137] S42: When the migration mode is full migration, migrate the source-end data to the target-end data according to the data migration rule.
[0138] The full migration mode not only adds data that is not in the target database, but also replaces the original data in the target database. This method can avoid the risk of losing existing data due to migration. You can choose incremental migration or full migration according to actual needs.
[0139] In some optional implementations of this embodiment, the step of migrating the source-end data to the target-end data specifically includes:
[0140] Use the Spring framework's jdbcTemplate.queryForList to query the query statements generated based on business rules and obtain the data set;
[0141] Based on Java's list collection List and map object Map, package and fill them into files;
[0142] According to the Scheduled task scheduling component of the Spring framework, the import operation is called on a scheduled basis.
[0143] The file content can be in the Excel file format generated by an Excel component (such as easyExcel, POI component, etc.), or it can be converted by the FastJSON component to generate JSON format text, etc.
[0144] Excel or JSON components can parse the file and get the data to be imported. According to the description of the import or export business rules, you can find which table each column of data needs to be imported into, and then encapsulate it into a Map object. Through the jdbcTemplate.getJdbcOperations().batchUpdate method of the Spring framework, a certain amount of data can be imported in batches to improve data import performance.
[0145] In some optional implementations of this embodiment, the migration job can also be configured, such as the migration job time setting, by setting a cron expression through the Scheduled task scheduling component of the Spring framework (for example, setting 0102 will execute data migration once a day at 2:10), performing task scheduling, supporting direct migration from source data to target data (by generating a temporary file of the migration data in the server. If the amount of migration data is not large, it can be directly exported to the memory, and then the task scheduling component is used to read the temporary file or memory for batch import into the system where the target data is located). It also supports uploading the data exported from the source system to the target system and then processing it regularly through task scheduling.
[0146] It should be noted that this embodiment is for data migration of relational databases, and the database configurations on both sides of the migration are preferably substantially the same, such as encoding format, etc. For the data table structures to be migrated to be basically consistent, at least it is necessary to ensure that the field structures corresponding to the migration can correspond and the field types are compatible.
[0147] Data migration involves input import and export. For example, when exporting data, you can add or select the database connection address used by the source system. If it is a new addition, you can create a corresponding data source version based on the application system version, because different application system versions correspond to different database table structures. Therefore, the data source version is used to constrain. For example, if the version number corresponding to the source system is 1.1, then the corresponding version number of the migrated system should also be 1.1, and the structure of the tables in their corresponding databases is the same. In this way, by establishing the data source version number, an agreement can be made to prevent errors in the migration process.
[0148] Data model tables and data lists can include the following three types:
[0149] First, select the table to synchronize the data table object. This method is recommended because it has the smallest granularity and is easiest to configure.
[0150] Second, select the view to synchronize the data table object. The view result should meet the fields and data results required for migration.
[0151] Third, write SQL statements to synchronize data table objects, which is used when you do not have permission to write views in the database.
[0152] When migrating data for export, the business rule model file is parsed and query statements are generated. The following is an example:
[0153] SELECT user table.user number,
[0154] User table. User name,
[0155] Organization table. Organization number,
[0156] Organization table. Organization name,
[0157] Convert organization type (organization table. Organization type)
[0158] FROM user table
[0159] INNER JOIN user-organization relationship table
[0160] ON user table. User number = user-organization relationship table. User number
[0161] AND user table. User name LIKE 'Zhang%'
[0162] INNER JOIN organization table
[0163] ON user-organization relationship table. Organization number = organization table. Organization number
[0164] AND organization table. Organization type = 'Company'
[0165] Convert organization type() is a database-defined function. One way of data conversion is to process data through database functions. For example, the organization type may be values such as 1, 2, 3, and after being processed by the function, it can be obtained as Company, Department, Section, etc.
[0166] The main rules are as follows: In the rule file, first process the fields of the main data table and the associated queries to ensure that the dependencies of the subsequent associated tables are not broken. Then, establish the association relationship through the set association conditions and filtering conditions. Finally, determine which table outputs the corresponding column data through the set display columns.
[0167] In this way, the data retrieved by the query can be bound to the business model, and then the data can be converted into different file formats: Excel, JSON, etc. Similar to Table 1 shown below:
[0168] Table 1
[0169] User Number Username Organization Number Organization Name Organization Type 1 Zhang San 1 Finance Department department 2 Li Si 1 Finance Department department 3 Wang Wu 2 X-Tech company
[0170] For data import, it is relatively simpler. Because the data table, business object, and import rules are all created during the export operation, and the import rules can directly use the export rules, the only difference is in the filtering conditions. Import does not require conditions, only the corresponding mapping relationships and dependencies are needed. Of course, import rules can also be defined according to the actual situation.
[0171] The steps of the import scenario are as follows:
[0172] 1) Select the target system data source and determine the data source version number.
[0173] 2) Select the import rule and upload the data exported from the source system.
[0174] 3) Use Excel components (such as easyExcel, POI components, etc.) or FastJson components to read Excel or JSON and convert it into List (list) and Map (map object) in Java. The conversion method is the same as when importing. Then, correspond the data to the columns in the above table and use JDBC to insert it into the corresponding table in the database.
[0175] 4) Import according to the export rules, but it will involve business dependencies on other tables, so the data exported from the source system will have one-to-many or many-to-many problems, that is, there may be duplicate data in the imported table. In this case, you need to rely on the unique identification field set in the rules for filtering. The import logic here is to split the business model according to the import rules, assign a business object data to the corresponding data table, and then deduplicate according to the unique identification field. When the code reads the source system data in a loop, the unique identifier of each data table is used as the key of the Map structure, and the key of the Map structure is unique, or when writing to the database, the query condition is brought in when inserting and modifying the data to determine the uniqueness, so that deduplication can be achieved.
[0176] Because a business export rule may export data from multiple tables, the data set will contain data from multiple tables when it is imported. Therefore, you can specify which tables' data to import from the corresponding data set. Figure 3 This is a schematic diagram of the business rules used in the data migration method based on a relational database of the present invention. A business rule model corresponds to a main data table, and its corresponding field is the main display field by default. If a field corresponds to multiple data tables, then for the field that is not the main data table, you need to set the association method, such as: inline, external, etc. And you can switch which data table object the display column is displayed by. For example, the data content in Table 1 above actually contains data from three tables, so according to Figure 3 The business rules can be split into user tables by selecting 'User Number' and 'User Name'. The organization table is to select 'Organization Number', 'Organization Name', and 'Organization Type'. The user-organization relationship table is to select the values of the corresponding fields of 'User Number' and 'Organization Number' for import.
[0177] If there is data that has not been successfully migrated, whether it is because the migration error is reported or the operation is stopped due to exceeding the set time, you need to return, provide feedback, and generate an unfinished data file to facilitate the next import.
[0178] The above example lists a user, organization, and user-organization association table, and these three tables are migrated together. Traditional data migration usually does not include this kind of association. User migration means migrating only the user table, while the organization and organization relationship corresponding to the user will not be migrated. If the traditional method is followed, an organization migration function may be added, but the organization relationship corresponding to the user cannot be migrated because there is no such relationship model.
[0179] The beneficial effects of implementing this embodiment are as follows: by establishing data table objects and business rule models, business relationships between tables are modeled and described, so that data migration can be performed according to agreed rules; on-demand import is performed according to the business rule model, providing business migration functions that previously required either full database migration or table-by-table migration, followed by manual database maintenance, or coding for specific migration functions, making the functions truly universal and configurable; and solving the problem that during the migration process of the business system, the data of multiple tables cannot be migrated together according to business rules.
[0180] The present invention can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present invention can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present invention can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.
[0181] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing the relevant hardware through computer-readable instructions, and the computer-readable instructions can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, the aforementioned storage medium can be a non-volatile storage medium such as a disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).
[0182] It should be understood that, although the steps in the flowchart of the accompanying drawings are displayed in sequence as indicated by the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least a part of the steps in the flowchart of the accompanying drawings may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be executed in turn or alternately with other steps or at least a part of the sub-steps or stages of other steps.
[0183] Embodiment 2
[0184] Further references Figure 4 , as a response to the above Figure 1 The present invention provides an embodiment of a data migration device based on a relational database. Figure 1 Corresponding to the method embodiment shown, the device can be specifically applied to various electronic devices.
[0185] like Figure 4 As shown, the data migration device 50 based on the relational database in this embodiment includes: an acquisition module 51, a mapping module 52, a setting module 53 and a migration module 54. Among them:
[0186] An acquisition module 51 is used to acquire source data and synchronize the source data;
[0187] A mapping module 52 is used to filter the synchronized source data and perform field mapping between the source data and the target data;
[0188] A setting module 53, used to set data migration rules;
[0189] The migration module 54 is used to migrate the source end data to the target end data according to the data migration rule and the migration mode.
[0190] In some optional implementations of this embodiment, the acquisition module 51 further includes:
[0191] The designation module 511 is used to obtain multiple source end data and specify the JDBC connection string of the data source through interface input or configuration file;
[0192] A connection module 512 is used to establish a connection with a relational database through JDBC using a JDBC connection string and generate a connection configuration;
[0193] The storage module 513 is used to store the connection configuration together with the version number information.
[0194] In some optional implementations of this embodiment, the mapping module 52 further includes:
[0195] Query module 521, used to query all tables and views under the current database instance through JDBC;
[0196] The selection module 522 is used to select the tables and views that need to be synchronized, and query the table information of the corresponding database according to the table and view names selected in the interface using JDBC.
[0197] In some optional implementations of this embodiment, the setting module 53 further includes:
[0198] An editing module 531 is used to edit a unique identification field of a data table object;
[0199] The creation module 532 is used to create an export business model according to the unique identification field, map the data table with the fields of the export business model, and set the exported fields.
[0200] In some optional implementations of this embodiment, the migration module 54 further includes:
[0201] Incremental migration module 541, used for, when the migration mode is incremental migration, using the data value corresponding to the unique key set in the table object as a comparison condition, determining whether the source data exists in the target database, and if not, migrating the source data to the target database according to the data migration rule;
[0202] The full migration module 542 is used to migrate the source-end data to the target-end data according to the data migration rule when the migration mode is full migration.
[0203] The beneficial effects of implementing this embodiment are as follows: by establishing data table objects and business rule models, business relationships between tables are modeled and described, so that data migration can be performed according to agreed rules; on-demand import is performed according to the business rule model, providing business migration functions that previously required either full database migration or table-by-table migration, followed by manual database maintenance, or coding for specific migration functions, making the functions truly universal and configurable; and solving the problem that during the migration process of the business system, the data of multiple tables cannot be migrated together according to business rules.
[0204] Embodiment 3
[0205] To solve the above technical problems, the embodiment of the present invention also provides a computer device. Figure 5 , Figure 5 This is a basic structural block diagram of the computer device in this embodiment.
[0206] The above-mentioned computer device 6 includes a memory 61, a processor 62, and a network interface 63 that are interconnected and communicated through a system bus. It should be noted that the figure only shows a computer device 6 having components of a memory 61, a processor 62, and a network interface 63, but it should be understood that it is not required to implement all the components shown, and more or fewer components can be implemented instead. Among them, those skilled in the art can understand that the computer device here is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to a microprocessor, an application specific integrated circuit (ASIC), a programmable gate array (FPGA), a digital processor (DSP), an embedded device, etc.
[0207] The computer device may be a desktop computer, a notebook computer, a PDA, a cloud server, etc. The computer device may interact with the user through a keyboard, a mouse, a remote control, a touch pad, or a voice control device.
[0208] The memory 61 includes at least one type of readable storage medium, and the readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (for example, SD or DX memory, etc.), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the memory 61 may be an internal storage unit of the computer device 6, such as a hard disk or memory of the computer device 6. In other embodiments, the memory 61 may also be an external storage device of the computer device 6, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (FlashCard), etc. equipped on the computer device 6. Of course, the memory 61 may also include both the internal storage unit of the computer device 6 and its external storage device. In this embodiment, the memory 61 is generally used to store the operating system and various application software installed on the computer device 6, such as computer-readable instructions of a data migration method based on a relational database, etc. In addition, the memory 61 can also be used to temporarily store various types of data that have been output or are to be output.
[0209] In some embodiments, the processor 62 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chips. The processor 62 is generally used to control the overall operation of the computer device 6. In this embodiment, the processor 62 is used to run the computer-readable instructions stored in the memory 61 or process data, such as running the computer-readable instructions of the data migration method based on the relational database.
[0210] The network interface 63 may include a wireless network interface or a wired network interface. The network interface 63 is generally used to establish a communication connection between the computer device 6 and other electronic devices.
[0211] The beneficial effects of implementing this embodiment are as follows: by establishing data table objects and business rule models, business relationships between tables are modeled and described, so that data migration can be performed according to agreed rules; on-demand import is performed according to the business rule model, providing business migration functions that previously required either full database migration or table-by-table migration, followed by manual database maintenance, or coding for specific migration functions, making the functions truly universal and configurable; and solving the problem that during the migration process of the business system, the data of multiple tables cannot be migrated together according to business rules.
[0212] Embodiment 4
[0213] The present invention also provides another embodiment, namely, providing a computer-readable storage medium, which stores computer-readable instructions, and the computer-readable instructions can be executed by at least one processor to enable the at least one processor to perform the steps of the data migration method based on a relational database as described above.
[0214] The beneficial effects of implementing this embodiment are as follows: by establishing data table objects and business rule models, business relationships between tables are modeled and described, so that data migration can be performed according to agreed rules; on-demand import is performed according to the business rule model, providing business migration functions that previously required either full database migration or table-by-table migration, followed by manual database maintenance, or coding for specific migration functions, making the functions truly universal and configurable; and solving the problem that during the migration process of the business system, the data of multiple tables cannot be migrated together according to business rules.
[0215] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, a magnetic disk, or an optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, a computer, a server, an air conditioner, or a network device, etc.) to execute the various embodiment methods of the present invention.
[0216] Obviously, the embodiments described above are only some embodiments of the present invention, rather than all embodiments. The preferred embodiments of the present invention are given in the accompanying drawings, but they do not limit the patent scope of the present invention. The present invention can be implemented in many different forms. On the contrary, the purpose of providing these embodiments is to make the understanding of the disclosure of the present invention more thorough and comprehensive. Although the present invention has been described in detail with reference to the aforementioned embodiments, for those skilled in the art, it is still possible to modify the technical solutions recorded in the aforementioned specific embodiments, or to perform equivalent replacements for some of the technical features therein. Any equivalent structure made using the contents of the specification and drawings of the present invention, directly or indirectly used in other related technical fields, is also within the scope of patent protection of the present invention.< / object> < / object>
Claims
1. A data migration method based on a relational database, characterized in that: The steps include: Acquire source data and synchronize the source data; Filter the synchronized source data and map the source data with the target data by fields. Set data migration rules; According to the data migration rule and migration mode, the source end data is migrated to the target end data.
2. The data migration method based on relational database according to claim 1, characterized in that: The step of obtaining source data and synchronizing the source data specifically includes: Get data from multiple sources, specify the JDBC connection string of the data source through the interface input or configuration file; Use the JDBC connection string to establish a connection with the relational database through JDBC and generate a connection configuration; Store the connection configuration with version number information.
3. The data migration method based on relational database according to claim 1, characterized in that: The step of filtering the synchronized source data and mapping the source data to the target data by fields specifically includes: Query all tables and views under the current database instance through JDBC; Select the tables and views that need to be synchronized, and use JDBC to query the table information of the corresponding database according to the table and view names selected on the interface.
4. The data migration method based on relational database according to claim 1, characterized in that: The step of setting data migration rules specifically includes: Edit the unique identification field of the data table object; According to the unique identification field, an export business model is created, the data table is mapped with the fields of the export business model, and the exported fields are set.
5. The data migration method based on relational database according to claim 1, characterized in that: The step of migrating the source data to the target data according to the data migration rule and the migration mode specifically includes: When the migration mode is incremental migration, the data value corresponding to the unique key set in the table object is used as a comparison condition to determine whether the source data exists in the target database. If not, the source data is migrated to the target data according to the data migration rule. When the migration mode is full migration, the source-end data is migrated to the target-end data according to the data migration rule.
6. The data migration method based on relational database according to claim 4 is characterized in that: The steps of creating an export business model according to the unique identification field, mapping the data table with the fields of the export business model, and setting the exported fields specifically include: Select the field of the business model to be exported, and then select the corresponding data table object from the data table object list, and select the field to be mapped for association; Map and export the intermediate table or associated fields from the table in the business model; For a single data table or related data tables, set preset business screening rule conditions; Set data conversion rules for fields, which can change the exported data for data cleaning and data desensitization; When a field corresponds to multiple data tables, sort the list of associated tables to control the order of association.
7. The data migration method based on a relational database according to any one of claims 1 to 6, characterized in that: The step of migrating the source data to the target data specifically includes: Use the Spring framework's jdbcTemplate.queryForList to query the query statements generated based on business rules and obtain the data set; Based on Java's list collection List and map object Map, package and fill them into files; According to the Scheduled task scheduling component of the Spring framework, the import operation is called on a scheduled basis.
8. A data migration device based on a relational database, characterized in that: include: An acquisition module, used to acquire source data and synchronize the source data; The mapping module is used to filter the synchronized source data and map the source data with the target data. The settings module is used to set data migration rules; A migration module is used to migrate the source end data to the target end data according to the data migration rule and the migration mode.
9. A computer device, comprising a memory and a processor, wherein the memory stores computer-readable instructions, and when the processor executes the computer-readable instructions, the steps of the data migration method based on a relational database as described in any one of claims 1 to 7 are implemented.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-readable instructions, and when the computer-readable instructions are executed by a processor, the steps of the data migration method based on a relational database as described in any one of claims 1 to 7 are implemented.
Citation Information
Cited By
Traffic management data fusion migration method and system
CN119621709A
Cross-model conversion synchronization method and device for data among multiple databases, electronic equipment and storage medium
CN120973859A
