JDBC-based multi-database access method, device and electronic device
By using the unified access interface developed by JDBC in ETL tools, the access complexity caused by inconsistent database types is solved, efficient and unified multi-database access is achieved, and the development and operation and maintenance process is simplified.
Patent Information
- Application Number
- CN202110886959.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-08-03
- Publication Date
- 2024-11-08
- Estimated Expiration
- 2041-08-03
AI Technical Summary
During the database access process, existing ETL tools have inconsistent database types due to the inconsistent database types at the source and target ends, resulting in complex and inconsistent access, making it difficult to achieve efficient multi-database access.
Using a unified access interface based on JDBC, the user-configured database connection information is obtained, the target database is connected and the connection object is obtained, thereby realizing data cleaning, conversion and loading, and blocking the differences between different databases.
It realizes efficient unified access to different databases, simplifies the development and operation and maintenance process, and improves the flexibility and convenience of data exchange.
Smart Images

Figure CN113641741B_ABST
Abstract
Description
Technical Field
[0001] The present disclosure relates to the field of data processing technology, and in particular to a JDBC-based multi-database access method, device and electronic device. Background Art
[0002] Extraction, transformation and loading of ETL (Extract-Transform-Load) data is an important step in the process of data processing in data centers to practical applications. The first step of ETL task configuration is usually database access. Currently, mainstream database engines include Oracle database, DB2 database, MySQL database, PostgreSQL database, etc., and the source database type is usually inconsistent with the target database type. Therefore, studying an ETL universal database access interface is the basis for providing services for all business systems of an enterprise. Summary of the invention
[0003] At least one embodiment of the present disclosure provides a JDBC-based multi-database access method, device and electronic device, which implements a unified database access interface based on the JDBC method, shields the differences between different types of databases, and realizes efficient and unified database access.
[0004] In a first aspect, the present disclosure provides a multi-database access method based on JDBC, which is applied to an ETL tool, wherein the ETL tool includes a unified access interface developed based on JDBC, and the method includes:
[0005] Acquire a target database selected by a user on the unified access interface;
[0006] Receive database connection information configured by a user, the database connection information including the access address of the target database, user account information and login password;
[0007] According to the database connection information, connecting to the target database corresponding to the access address through the unified access interface to obtain a connection object;
[0008] According to the relevant information of the target database obtained from the connection object, data is obtained from the target database through the unified access interface to perform data cleaning, conversion and loading.
[0009] In a second aspect, the present disclosure provides a multi-database access device based on JDBC, which is applied to an ETL tool. The ETL tool includes a unified access interface developed based on JDBC. The device includes:
[0010] A first acquisition module, used to acquire a target database selected by a user on the unified access interface;
[0011] A receiving module, configured to receive database connection information configured by a user, wherein the database connection information includes an access address of the target database, user account information and a login password;
[0012] A connection module, configured to connect to a target database corresponding to the access address through the unified access interface according to the database connection information, so as to obtain a connection object;
[0013] The second acquisition module is used to acquire data from the target database through the unified access interface to perform data cleaning, conversion and loading according to the relevant information of the target database obtained from the connection object.
[0014] In a third aspect, the present disclosure provides an electronic device, comprising: a processor and a memory; the processor is used to execute the steps of any JDBC-based multi-database access method provided in the embodiments of the present disclosure by calling a program or instruction stored in the memory.
[0015] In a fourth aspect, the present disclosure provides a computer-readable storage medium, wherein the computer-readable storage medium stores a program or instruction, wherein the program or instruction enables a computer to execute the steps of any JDBC-based multi-database access method provided in an embodiment of the present disclosure.
[0016] In a fifth aspect, the present disclosure provides a computer program product, which is used to execute the steps of any JDBC-based multi-database access method provided in the embodiments of the present disclosure.
[0017] Compared with the prior art, the technical solution provided by the embodiments of the present disclosure has at least the following advantages:
[0018] In the embodiment of the present disclosure, a unified access interface is developed based on JDBC in an ETL tool, a target database selected by a user on the unified access interface is obtained, and database connection information configured by the user is received, the database connection information includes an access address of the target database, user account information and a login password, and according to the database connection information, a target database corresponding to the access address is connected through a unified access interface to obtain a connection object, and then according to the relevant information of the target database obtained from the connection object, data is obtained from the target database through a unified access interface for data cleaning, conversion and loading, thereby establishing an efficient and unified database access interface that can realize access to different databases. BRIEF DESCRIPTION OF THE DRAWINGS
[0019] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.
[0020] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.
[0021] Figure 1 A flowchart of a JDBC-based multi-database access method provided in one embodiment of the present disclosure;
[0022] Figure 2 This is a schematic diagram of the overall architecture for connecting the ETL tool to the database through a unified access interface;
[0023] Figure 3 It is a structural diagram of a unified access interface based on JDBC;
[0024] Figure 4 A schematic diagram of the structure of a JDBC-based multi-database access device provided in one embodiment of the present disclosure. DETAILED DESCRIPTION
[0025] In order to more clearly understand the above-mentioned purposes, features and advantages of the present disclosure, the present disclosure is further described in detail below in conjunction with the accompanying drawings and embodiments. It is understandable that the described embodiments are part of the embodiments of the present disclosure, rather than all of the embodiments, and the specific embodiments described herein are only used to explain the present disclosure, rather than to limit the present disclosure. In the absence of conflict, the embodiments of the present disclosure and the features in the embodiments can be combined with each other. Based on the described embodiments of the present disclosure, all other embodiments obtained by ordinary technicians in the field belong to the scope of protection of the present disclosure.
[0026] In the following description, many specific details are set forth to facilitate a full understanding of the present disclosure, but the present disclosure may also be implemented in other ways different from those described herein; it is obvious that the embodiments in the specification are only part of the embodiments of the present disclosure, rather than all of the embodiments.
[0027] Figure 1A flow chart of a JDBC-based multi-database access method provided in an embodiment of the present disclosure is provided. The JDBC-based multi-database access method is applied to an ETL tool, and the ETL tool includes a unified access interface developed based on JDBC. The JDBC-based multi-database access method can be executed by a JDBC-based multi-database access device provided in an embodiment of the present disclosure. The JDBC-based multi-database access device can be implemented using software and / or hardware, and can be integrated on any electronic device with processing capabilities, such as a desktop computer, a tablet computer, and other devices.
[0028] like Figure 1 As shown, the JDBC-based multi-database access method provided in the embodiment of the present disclosure may include the following steps:
[0029] Step 101: Acquire a target database selected by a user on the unified access interface.
[0030] In this embodiment, the unified access interface can be implemented based on JDBC (Java Database Connectivity).
[0031] In ETL data access, JDBC is a public interface for general SQL database access and operation that is independent of a specific database management system. It defines a standard class library for accessing the database. This standard class library can be used to conveniently access database resources in a standard way. In this embodiment, the unified access interface developed based on JDBC provides a unified way to access different databases, shielding developers and operation and maintenance personnel from some details. The goal of establishing a unified access interface in this embodiment is to enable the ETL tool to connect to any database system that provides a JDBC driver through JDBC.
[0032] In this embodiment, database drivers of different databases may be preloaded in the unified access interface. When a user needs to connect to a database to obtain data in the database, the user may select the database on the unified access interface, and the database is the target database selected by the user.
[0033] Step 102, receiving database connection information configured by the user, wherein the database connection information includes the access address of the target database, user account information and login password.
[0034] In this embodiment, in order to realize the connection between the ETL tool and the target database, the user can configure the database connection information in the ETL tool so that the ETL tool can connect to the corresponding target database according to the received database connection information, wherein the database connection information at least includes the access address URL (Uniform Resource Locator) of the target database, user account information user and login password password, and may also include other information such as port and database name. The present disclosure does not impose any restrictions on this. The user account information may be but is not limited to account name, mobile phone number, email address, etc.
[0035] Exemplarily, a user may configure database connection information through a connection interface.
[0036] For example, the database connection information is configured through the statement "Connectionconn = DriverManager.getConnection("URL", "user", "password")".
[0037] Step 103: According to the database connection information, connect to the target database corresponding to the access address through the unified access interface to obtain a connection object.
[0038] In this embodiment, according to the database connection information configured by the user, a connection with the target database corresponding to the access address can be established through the unified access interface. When connecting, the target database to be connected is found according to the access address, and then the target database is logged in according to the user account information and login password to complete the connection between the unified access interface and the target database. After the connection is completed, the unified access interface will obtain a connection object.
[0039] Step 104 , according to the relevant information of the target database obtained from the connection object, obtain data from the target database through the unified access interface to perform data cleaning, conversion and loading.
[0040] In this embodiment, after obtaining the connection object, various information about the database management system can be obtained from the connection object, such as various tables in the database, various columns in the table, data types, triggers, stored procedures and other information. Based on this information, the ETL tool can access a database that is not known in advance through a unified access interface, obtain data from the database, and then perform data cleaning, conversion and loading. Therefore, by establishing an efficient unified access interface, different databases can be connected to complete horizontal and vertical data exchange problems. Developers do not need to care about the details of the differences in the underlying databases. As long as the correct database type is selected on the unified access interface and the correct database connection information is configured, the data in the database can be obtained, which realizes convenient and efficient access to different types of databases, greatly simplifying and accelerating the development and operation process.
[0041] The JDBC-based multi-database access method of this embodiment obtains the target database selected by the user on the unified access interface and receives the database connection information configured by the user, wherein the database connection information includes the access address of the target database, the user account information and the login password. According to the database connection information, the target database corresponding to the access address is connected through the unified access interface established on the ETL tool to obtain a connection object, and then according to the relevant information of the target database obtained from the connection object, data is obtained from the target database through the unified access interface for data cleaning, conversion and loading. Thus, an efficient and unified database access interface is established. Different databases can be accessed by using the unified access interface, thereby realizing unified access of different databases without considering the difference details of the databases, and having strong flexibility.
[0042] In a possible implementation of the disclosed embodiment, the JDBC driver corresponding to the database can be pre-loaded in the unified access interface to generate a unified access interface list. Among them, the JDBC driver needs to implement the Java.sql.Driver interface, which is provided to database vendors for use, and different database vendors provide different implementations. For example, the JDBC driver corresponding to databases such as Oracle, MySQL, and PgSQL can be loaded into the unified access interface to generate a unified access interface list, thereby unifying the access interfaces of all databases that support the JDBC connection method and shielding the differences between databases.
[0043] Further, in a possible implementation of the embodiment of the present disclosure, connecting to a target database corresponding to the access address through the unified access interface according to the database connection information to obtain a connection object may include:
[0044] According to the database connection information, query whether there is a target JDBC driver corresponding to the target database in the unified access interface list;
[0045] If the target JDBC driver corresponding to the target database exists in the unified access interface list, the target database corresponding to the access address is connected to through the unified access interface to obtain a connection object.
[0046] As an example, each JDBC driver in the unified access interface list can use the access address of the corresponding database as a unique identifier. After receiving the database connection information configured by the user, the unified access interface list can be queried according to the access address in the database connection information. If the same access address is queried, it is determined that the target JDBC driver of the target database corresponding to the access address exists in the unified access interface list, and then the target database corresponding to the access address is connected through the unified access interface to obtain a connection object.
[0047] Furthermore, in a possible implementation of the embodiment of the present disclosure, if an access address identical to the access address in the database connection information is not queried in the unified access interface list, it is determined that the target JDBC driver of the target database corresponding to the access address does not exist in the unified access interface list. In this case, the target JDBC driver of the target database added by the user in the unified access interface can be received, and the target JDBC driver can be added to the unified access interface list.
[0048] Exemplarily, the access address of the target database may be loaded into the unified access interface list as the identifier of the target JDBC driver.
[0049] That is to say, if the target database you want to access does not exist in the unified access interface list, you only need to provide the JDBC driver of the target database to add the target database to the unified access interface. You can then still access the newly added target database through the unified access interface to obtain data in the target database.
[0050] When the target JDBC driver corresponding to the target database does not exist in the unified access interface list, the target JDBC driver of the target database added by the user in the unified access interface is received, and the target JDBC driver is added to the unified access interface list. In this way, the unified access interface supports flexible and extensible support. New database types can be included in the unified access interface of ETL by providing relevant JDBC drivers.
[0051] In a possible implementation of the embodiment of the present disclosure, the unified access interface is composed of a driver interface, a driver manager, and a JDBC API (Application Programming Interface) interface.
[0052] Figure 2 This is a schematic diagram of the overall architecture of the ETL tool connecting to the database through the unified access interface. It should be noted that the unified access interface is a function of the data access node of the ETL tool. However, in order to facilitate understanding of how the unified access interface unifies different types of relational databases, Figure 2 In the article, ETL tools and unified access interfaces are distinguished, but it should be understood that the unified access interface is not independent of the entire ETL process. Figure 2 As shown, when connecting to different types of databases such as Oracle (providing an Oracle driver based on JDBC), MySQL (providing a MySQL driver based on JDBC), PgSQL (providing a PgSQL driver based on JDBC), etc., you can use the unified access interface to access them without having to worry about the differences in the details of the databases; for the newly added DM database, you only need to add the DM database driver (i.e., the JDBC-based DM driver) to the unified access interface, and you can use the same unified access interface to access the data information of the DM database.
[0053] Figure 3 This is a schematic diagram of the structure of the unified access interface based on JDBC, such as Figure 3 As shown, the unified access interface consists of JDBC API, driver manager and driver interface. The unified access interface realizes the loading of drivers for multiple types of relational databases such as Oracle (providing Oracle driver based on JDBC) and DB2 (providing DB2 driver based on JDBC) through the driver interface; after the driver is loaded, the driver manager obtains the database link, including the database URL, port, user name, password, etc.; the JDBC API is used to unify the interface and semantically convert the operations of multiple relational database engines, thereby realizing the decoupling of the abstract part and the implementation part, so that users do not need to manually select the database type when using the ETL data loading node, and only need to configure the database URL, port, user name, and password to log in. Figure 3In the bridging mode, the JDBC-based ETL tool uses the JDBC API, which is equivalent to the abstract extension of database operations and is considered the abstract part of the bridge mode. The specific interface implementation is completed by the driver, which is naturally equivalent to the implementation part of the bridge mode. The bridge mode is no longer to let the abstract part hold the implementation part, but to adopt a factory-like approach to connect the abstract part and the implementation part through the driver manager, thereby achieving decoupling of the abstract part and the implementation part.
[0054] In order to implement the above embodiments, the present disclosure also provides a JDBC-based multi-database access device.
[0055] Figure 4 The present invention provides a schematic diagram of the structure of a JDBC-based multi-database access device according to an embodiment of the present invention, wherein the JDBC-based multi-database access device is applied to an ETL tool, and the ETL tool includes a unified access interface developed based on JDBC. The JDBC-based multi-database access device can be implemented using software and / or hardware, and can be integrated on any electronic device with processing capabilities, such as a desktop computer, a tablet computer, and other devices.
[0056] like Figure 4 As shown, the JDBC-based multi-database access device 40 provided in the embodiment of the present disclosure may include a first acquisition module 401, a receiving module 402, a connection module 403, and a second acquisition module 404, wherein:
[0057] A first acquisition module 401 is used to acquire a target database selected by a user on the unified access interface;
[0058] The receiving module 402 is used to receive the database connection information configured by the user, wherein the database connection information includes the access address of the target database, the user account information and the login password;
[0059] A connection module 403 is used to connect to a target database corresponding to the access address through the unified access interface according to the database connection information to obtain a connection object;
[0060] The second acquisition module 404 is used to acquire data from the target database through the unified access interface to perform data cleaning, conversion and loading according to the relevant information of the target database acquired from the connection object.
[0061] Optionally, the JDBC-based multi-database access device 40 further includes:
[0062] The loading module is used to load the JDBC driver corresponding to the database in the unified access interface to generate a unified access interface list.
[0063] Optionally, the connection module 403 is specifically configured to:
[0064] According to the database connection information, query whether there is a target JDBC driver corresponding to the target database in the unified access interface list;
[0065] If the target JDBC driver corresponding to the target database exists in the unified access interface list, the target database corresponding to the access address is connected to through the unified access interface to obtain a connection object.
[0066] Optionally, the JDBC-based multi-database access device 40 further includes:
[0067] The adding module is used to receive the target JDBC driver of the target database added by the user in the unified access interface when the target JDBC driver corresponding to the target database does not exist in the unified access interface list; and add the target JDBC driver to the unified access interface list.
[0068] Optionally, the unified access interface consists of a driver interface, a driver manager and a JDBC API interface.
[0069] The JDBC-based multi-database access device that can be configured on an electronic device such as a desktop computer provided in the embodiments of the present disclosure can execute any JDBC-based multi-database access method that can be applied to an electronic device such as a desktop computer provided in the embodiments of the present disclosure, and has the corresponding functional modules and beneficial effects of the execution method. For the contents not fully described in the embodiments of the device of the present disclosure, reference can be made to the description in any method embodiment of the present disclosure.
[0070] The embodiment of the present disclosure also provides an electronic device, including a processor and a memory; the processor calls the program or instructions stored in the memory to execute the steps of each embodiment of the JDBC-based multi-database access method as described in the above embodiments. To avoid repeated description, they are not repeated here.
[0071] The embodiments of the present disclosure also provide a non-transitory computer-readable storage medium, which stores programs or instructions. The programs or instructions enable a computer to execute the steps of each embodiment of the JDBC-based multi-database access method as described in the aforementioned embodiments. To avoid repeated description, they are not repeated here.
[0072] The embodiments of the present disclosure also provide a computer program product, which is used to execute the steps of each embodiment of the JDBC-based multi-database access method as described in the above embodiments.
[0073] It should be noted that, in this article, relational terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.
[0074] The above description is only a specific embodiment of the present disclosure, so that those skilled in the art can understand or implement the present disclosure. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present disclosure. Therefore, the present disclosure will not be limited to the embodiments described herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.
Claims
1. A multi-database access method based on JDBC, characterized in that: Applied to ETL tools, the ETL tools include a unified access interface developed based on JDBC, the unified access interface consists of a driver interface, a driver manager and a JDBC API interface, the unified access interface implements the loading of a driver program for a database through the driver interface, obtains the connection information of the database through the driver manager after the driver program is loaded, and performs interface unification and semantic conversion on the database through the JDBC API interface; The method comprises: Acquire a target database selected by a user on the unified access interface; Receive database connection information configured by a user, the database connection information including the access address of the target database, user account information and login password; According to the database connection information, connecting to the target database corresponding to the access address through the unified access interface to obtain a connection object; According to the relevant information of the target database obtained from the connection object, data is obtained from the target database through the unified access interface to perform data cleaning, conversion and loading, wherein information of each table, each column in the table, data type, trigger and stored procedure in the target database can be obtained from the connection object; Wherein, connecting to the target database corresponding to the access address through the unified access interface according to the database connection information to obtain a connection object includes: According to the database connection information, query whether there is a target JDBC driver corresponding to the target database in the unified access interface list, wherein the unified access interface list is generated by loading the JDBC driver corresponding to the database in the unified access interface, and each JDBC driver in the unified access interface list uses the access address of the corresponding database as a unique identifier, and when an access address identical to the access address in the database connection information is queried from the unified access interface list, it is determined that the target JDBC driver exists in the unified access interface list; If the target JDBC driver corresponding to the target database exists in the unified access interface list, connecting to the target database corresponding to the access address through the unified access interface to obtain a connection object; The method further comprises: If the target JDBC driver corresponding to the target database does not exist in the unified access interface list, the target JDBC driver for the target database added by the user in the unified access interface is received, and the target JDBC driver is added to the unified access interface list.
2. A multi-database access device based on JDBC, characterized in that: Applied to ETL tools, the ETL tools include a unified access interface developed based on JDBC, the unified access interface consists of a driver interface, a driver manager and a JDBC API interface, the unified access interface implements the loading of a driver program for a database through the driver interface, obtains the connection information of the database through the driver manager after the driver program is loaded, and performs interface unification and semantic conversion on the database through the JDBC API interface; The device comprises: A first acquisition module, used to acquire a target database selected by a user on the unified access interface; A receiving module, configured to receive database connection information configured by a user, wherein the database connection information includes an access address of the target database, user account information and a login password; A connection module, configured to connect to a target database corresponding to the access address through the unified access interface according to the database connection information to obtain a connection object; A second acquisition module is used to acquire data from the target database through the unified access interface to perform data cleaning, conversion and loading according to the relevant information of the target database acquired from the connection object, wherein information of each table, each column in the table, data type, trigger and stored procedure in the target database can be obtained from the connection object; The device also includes: A loading module, used for loading a JDBC driver corresponding to a database in the unified access interface to generate a unified access interface list; The connection module is specifically used for: According to the database connection information, query whether there is a target JDBC driver corresponding to the target database in the unified access interface list, wherein the unified access interface list is generated by loading the JDBC driver corresponding to the database in the unified access interface, and each JDBC driver in the unified access interface list uses the access address of the corresponding database as a unique identifier, and when an access address identical to the access address in the database connection information is queried from the unified access interface list, it is determined that the target JDBC driver exists in the unified access interface list; If the target JDBC driver corresponding to the target database exists in the unified access interface list, connecting to the target database corresponding to the access address through the unified access interface to obtain a connection object; The device also includes: The adding module is used to receive the target JDBC driver of the target database added by the user in the unified access interface when the target JDBC driver corresponding to the target database does not exist in the unified access interface list, and add the target JDBC driver to the unified access interface list.
3. An electronic device, characterized in that: include: Processor and memory; The processor is used to execute the steps of the JDBC-based multi-database access method as claimed in claim 1 by calling the program or instruction stored in the memory.
4. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a program or instruction, and the program or instruction enables a computer to execute the steps of the JDBC-based multi-database access method as claimed in claim 1.
