CN111274263A - Visual database change statement generation method and device and storage medium - Google Patents
Visual database change statement generation method and device and storage medium Download PDFInfo
- Publication number
- CN111274263A CN111274263A CN202010033126.4A CN202010033126A CN111274263A CN 111274263 A CN111274263 A CN 111274263A CN 202010033126 A CN202010033126 A CN 202010033126A CN 111274263 A CN111274263 A CN 111274263A
- Authority
- CN
- China
- Prior art keywords
- data
- database server
- information
- target database
- background
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 230000008859 change Effects 0.000 title claims abstract description 70
- 238000000034 method Methods 0.000 title claims abstract description 52
- 230000000007 visual effect Effects 0.000 title claims abstract description 27
- 239000000126 substance Substances 0.000 claims description 5
- 230000004075 alteration Effects 0.000 claims 2
- 238000012545 processing Methods 0.000 abstract description 9
- 238000012986 modification Methods 0.000 abstract description 7
- 230000004048 modification Effects 0.000 abstract description 7
- 238000012800 visualization Methods 0.000 description 8
- 230000008569 process Effects 0.000 description 7
- 238000012217 deletion Methods 0.000 description 6
- 230000037430 deletion Effects 0.000 description 6
- 238000004891 communication Methods 0.000 description 4
- 238000010586 diagram Methods 0.000 description 4
- 230000009471 action Effects 0.000 description 3
- 230000006870 function Effects 0.000 description 3
- 244000277285 Cassia obtusifolia Species 0.000 description 2
- 235000006719 Cassia obtusifolia Nutrition 0.000 description 2
- 235000014552 Cassia tora Nutrition 0.000 description 2
- 238000001514 detection method Methods 0.000 description 2
- 238000011161 development Methods 0.000 description 2
- 239000004973 liquid crystal related substance Substances 0.000 description 2
- 230000002085 persistent effect Effects 0.000 description 2
- 238000007792 addition Methods 0.000 description 1
- 238000004590 computer program Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005538 encapsulation Methods 0.000 description 1
- 238000003384 imaging method Methods 0.000 description 1
- 238000003780 insertion Methods 0.000 description 1
- 230000037431 insertion Effects 0.000 description 1
- 230000010354 integration Effects 0.000 description 1
- 239000002184 metal Substances 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000001960 triggered effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to the technical field of data processing, and provides a visual database change statement generation method, a device and a storage medium, wherein the method comprises the following steps: requesting to access the target database server through a background interface according to the information of the target database server, and writing data which needs to be detected and updated in the target database server into an application database server; the method comprises the steps of selecting request data information needing to be exported in data needing to be detected and updated, determining first feedback data corresponding to the request data through a background access application database server, obtaining target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in a target database server; and obtaining the difference item and the change statement corresponding to the difference item by comparing the first feedback data with the second feedback data. The invention can realize the visual processing of the database change, and the table structure modification can also be directly operated without writing sql statements.
Description
Technical Field
The invention relates to the technical field of data processing, in particular to a visual database change statement generation method and device and a computer readable storage medium.
Background
Currently, the SQL release for different environments in a project mainly adopts the following two ways, namely: manually collecting and storing the SQL change statements according to certain rules; the second method comprises the following steps: published to the target environment through persistent integration tools and executed automatically.
Wherein, in order to ensure the information security, the second mode is difficult to be optimized. The first method has the following disadvantages: 1. easily missing. Specifically, most of the fields are directly edited in a graphical tool when the structure or data of a certain field is modified, and the SQL statement mode is rarely adopted, so that the files are not saved, and the change is easily forgotten; 2. the operation is troublesome. Because the corresponding SQL statements need to be written and unified files need to be collected after each database change, the operation is inconvenient, and the realization is difficult under the condition of high change frequency; 3. collisions are prone to occur. Since many people develop an application at the same time, each person may generate the changed SQL, when the application is finally released, the SQL of all people needs to be collected to ensure that there are no conflicting statements, and the order of the statements also needs to be ensured, for example, if one person's SQL is a table building statement and another person's SQL is a newly-built data insertion statement, the order of the table building statement is ensured before the data is inserted. 4. It is not safe. The data is mainly unsafe, and the manually collected SQL needs to manually determine the safety of the statement, such as whether the statement relates to a database clearing operation or not, whether the existing data is influenced by deleting the statement or not, and the like.
Disclosure of Invention
The invention provides a visualized database change statement generation method, an electronic device and a computer readable storage medium, and aims to perform visualization processing on database changes, so that developers do not need to care whether actions of each time affect a table structure or data of a database, and only need to perform visualization operation at the beginning and the end of a version.
In order to achieve the above object, the present invention provides a method for generating a visual database change statement, which is applied to an electronic device, and the method includes:
s110: requesting to access a target database server through a background interface according to the information of the target database server, and writing data which needs to be detected and updated in the target database server into an application database server;
s120: selecting request data information needing to be exported in the data needing to be detected and updated, accessing the application database server through a background to determine first feedback data corresponding to the request data, acquiring target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in the target database server;
s130: determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
Preferably, the step S110 further includes:
requesting a background interface according to target database server information input by a client interface, wherein the background requests the target database server to acquire base table information under the target database server;
the target database server returns the base table information to the background, and the data corresponding to the base table information is fed back to a client interface through the background for display;
requesting a background interface according to target database server information based on feedback data and data needing to be detected and updated submitted by a client interface, and substituting the target database server information and the data needing to be detected and updated into the background interface;
the background requests the target database server to acquire the data needing to be detected and updated again;
the target database server feeds back the structure and/or data information of the table corresponding to the data needing to be detected and updated to the background according to the request of the background;
and the background writes the structure and/or data information of the currently acquired table into the application database server and simultaneously generates a submission record for the client list to view.
Preferably, the background requests the target database server to acquire the base table information of the target database server; the target database server returns the base table information to the background, and the step of feeding back the data corresponding to the base table information to the client interface through the background for display comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all base table information under the target database server through a native sql statement;
and acquiring the structures and/or data information of all tables under the base table information corresponding to the target database server information, and feeding back the structures and/or data information to the client interface for display.
Preferably, the submission record includes a record table, a data table and a field table created in the application database server; wherein the content of the first and second substances,
the record table is used for storing information submitted by a user;
the data table is used for storing the structure and/or data information of the table corresponding to the data needing to be detected and updated in a json array form;
the field table is used for storing the structure of the table corresponding to the data needing to be detected and updated and/or the field of the data information, and the data type, the length and the default value of the field.
Preferably, the step S120 further includes:
selecting request data information needing to be exported in the detected and updated data through a client interface, wherein the client requests a background interface and transmits an ID (identity) corresponding to the request data;
the background accesses the application database server, first feedback data corresponding to the request data is determined through the application database server, meanwhile, the background inquires target database server information corresponding to the ID from the application database server, and the application database server feeds the target database server information back to the background;
the background acquires a table structure and/or data corresponding to the ID from the target database server based on the information of the target database server as the second feedback data;
and the target database server feeds the table structure and/or the data back to the background.
To achieve the above object, the present invention also provides an electronic device, including: the system comprises a memory and a processor, wherein the memory comprises a visual database change statement generation program, and the visual database change statement generation program realizes the following steps when being executed by the processor:
s110: according to input target database server information, requesting to access the target database server through a background interface, and writing data which needs to be detected and updated in the target database server into an application database server;
s120: selecting request data information needing to be exported in the data needing to be detected and updated, accessing the application database server through a background to determine first feedback data corresponding to the request data, acquiring target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in the target database server;
s130: determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
Preferably, the step S110 further includes:
requesting a background interface according to target database server information input by a client interface, wherein the background requests the target database server to acquire base table information under the target database server;
the target database server returns the base table information to the background, and the data corresponding to the base table information is fed back to a client interface through the background for display;
requesting a background interface according to target database server information based on feedback data and data needing to be detected and updated submitted by a client interface, and substituting the target database server information and the data needing to be detected and updated into the background interface;
the background requests the target database server to acquire the data needing to be detected and updated again;
the target database server feeds back the structure and/or data information of the table corresponding to the data needing to be detected and updated to the background according to the request of the background;
and the background writes the structure and/or data information of the currently acquired table into the application database server and simultaneously generates a submission record for the client list to view.
Preferably, the background requests the target database server to acquire the base table information of the target database server; the target database server returns the base table information to the background, and the step of feeding back the data corresponding to the base table information to the client interface through the background for display comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all base table information under the target database server through a native sql statement;
and acquiring the structures and/or data information of all tables under the base table information corresponding to the target database server information, and feeding back the structures and/or data information to the client interface for display.
Preferably, the first and second electrodes are formed of a metal,
the submission record includes a record table, a data table, and a field table created in the application database server; wherein the content of the first and second substances,
the record table is used for storing information submitted by a user;
the data table is used for storing the structure and/or data information of the table corresponding to the data needing to be detected and updated in a json array form;
the field table is used for storing fields of the structure and/or data information of the table corresponding to the data needing to be detected and updated and the data type, length and default values of the fields
In addition, to achieve the above object, the present invention further provides a computer-readable storage medium, which includes a visualized database change statement generation program, and when the visualized database change statement generation program is executed by a processor, the visualized database change statement generation program implements any step of the visualized database change statement generation method as described above.
According to the visualized database change statement generation method, the electronic device and the computer readable storage medium, data inquired by the application database and data inquired by the target database are compared, the difference items of the two are obtained, visualization processing is carried out, developers do not need to care whether actions of each time can affect the table structure or the data of the database, and all follow-up manual work can be replaced by respectively carrying out visualization operation at the beginning and the end of the version; in addition, the modification table structure can be directly operated without writing SQL statements, so that the time of a developer can be greatly saved, and the effect is more obvious under the condition of deployment and upgrade in two different environments.
Drawings
FIG. 1 is a schematic diagram of an application environment of a method for generating visual database change statements according to an embodiment of the present invention;
FIG. 2 is a block diagram illustrating a preferred embodiment of a visual database change statement generation program of FIG. 1;
FIG. 3 is a flowchart illustrating a method for generating visual database change statements according to a preferred embodiment of the present invention;
fig. 4 is a flowchart illustrating a visualized database change statement generation method according to an embodiment of the present invention;
fig. 5 is a schematic diagram of a client interface according to an embodiment of the present invention.
The implementation, functional features and advantages of the objects of the present invention will be further explained with reference to the accompanying drawings.
Detailed Description
It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
The invention provides a visual database change statement generation method, which is applied to an electronic device 1. Fig. 1 is a schematic view of an application environment of a method for generating a visual database change statement according to a preferred embodiment of the present invention.
In the present embodiment, the electronic device 1 may be a terminal device having an arithmetic function, such as a server, a smart phone, a tablet computer, a portable computer, or a desktop computer.
The electronic device 1 includes: a processor 12, a memory 11, an imaging device 13, a network interface 14, and a communication bus 15.
The memory 11 includes at least one type of readable storage medium. The at least one type of readable storage medium may be a non-volatile storage medium such as a flash memory, a hard disk, a multimedia card, a card-type memory 11, and the like. In some embodiments, the readable storage medium may be an internal storage unit of the electronic apparatus 1, such as a hard disk of the electronic apparatus 1. In other embodiments, the readable storage medium may also be an external memory 11 of the electronic device 1, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card), and the like, which are provided on the electronic device 1.
In the present embodiment, the readable storage medium of the memory 11 is generally used for storing the visualized database change statement generating program 10 installed in the electronic device 1. The memory 11 may also be used to temporarily store data that has been output or is to be output.
The processor 12 may be a Central Processing Unit (CPU), a microprocessor or other data Processing chip in some embodiments, and is used for executing program codes stored in the memory 11 or Processing data, such as executing the visual database change statement generating program 10.
The network interface 14 may optionally include a standard wired interface, a wireless interface (e.g., WI-FI interface), and is typically used to establish a communication link between the electronic apparatus 1 and other electronic devices.
The communication bus 15 is used to realize connection communication between these components.
Fig. 1 only shows the electronic device 1 with components 11-15, but it is to be understood that not all of the shown components are required to be implemented, and that more or fewer components may alternatively be implemented.
Optionally, the electronic device 1 may further include a user interface, the user interface may include an input unit such as a Keyboard (Keyboard), a voice input device such as a microphone (microphone) or other equipment with a voice recognition function, a voice output device such as a sound box, a headset, etc., and optionally the user interface may further include a standard wired interface, a wireless interface.
Optionally, the electronic device 1 may further comprise a display, which may also be referred to as a display screen or a display unit. In some embodiments, the display device may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, an Organic Light-Emitting Diode (OLED) touch device, or the like. The display is used for displaying information processed in the electronic apparatus 1 and for displaying a visualized user interface.
Optionally, the electronic device 1 further comprises a touch sensor. The area provided by the touch sensor for the user to perform touch operation is called a touch area. Further, the touch sensor described herein may be a resistive touch sensor, a capacitive touch sensor, or the like. The touch sensor may include not only a contact type touch sensor but also a proximity type touch sensor. Further, the touch sensor may be a single sensor, or may be a plurality of sensors arranged in an array, for example.
The area of the display of the electronic device 1 may be the same as or different from the area of the touch sensor. Optionally, a display is stacked with the touch sensor to form a touch display screen. The device detects touch operation triggered by a user based on the touch display screen.
Optionally, the electronic device 1 may further include a Radio Frequency (RF) circuit, a sensor, an audio circuit, and the like, which are not described herein again.
In the apparatus embodiment shown in fig. 1, the memory 11, which is a kind of computer storage medium, may include therein an operating system, and a visual database change statement generation program 10; the processor 12, when executing the visualization database change statement generation program 10 stored in the memory 11, implements the following steps:
s110: requesting to access the target database server through a background interface according to the information of the target database server, and writing data which needs to be detected and updated in the target database server into an application database server;
s120: the method comprises the steps of selecting request data information needing to be exported in data needing to be detected and updated, determining first feedback data corresponding to the request data through a background access application database server, obtaining target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in a target database server;
s130: and determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
Preferably, the step S110 further includes:
requesting a background interface according to target database server information input by a client interface, and requesting the target database server by a background to acquire base table information under the target database server;
the target database server returns the base table information to the background, and the data corresponding to the base table information is fed back to the client interface through the background for display;
requesting a background interface according to target database server information based on feedback data and data needing to be detected and updated submitted by a client interface, and substituting the target database server information and the data needing to be detected and updated into the background interface;
the background requests the target database server to obtain the data needing to be detected and updated again;
the target database server feeds back the structure and/or data information of the table corresponding to the data needing to be detected and updated to the background according to the request of the background;
and the background writes the structure and/or data information of the currently acquired table into the application database server, and simultaneously generates a submission record for the client list to view.
Preferably, the background requests the target database server to acquire the base table information under the target database server; the target database server returns the base table information to the background, and the step of feeding back the data corresponding to the base table information to the client interface through the background for display comprises the following steps:
connecting a target database server in a native Jdbc mode, and inquiring all base table information under the target database server through a native sql statement;
and acquiring the structures and/or data information of all tables under the table information corresponding to the target database server information, and feeding back the structures and/or data information to the client interface for display.
Preferably, the commit record comprises a record table, a data table and a field table created in the application database server; wherein the content of the first and second substances,
the record table is used for storing information submitted by a user;
the data table is used for storing the structure and/or data information of the table corresponding to the data needing to be detected and updated in a json array form;
the field table is used for storing fields of the structure and/or data information of the table corresponding to the data needing to be detected and updated, and data types, lengths and default values of the fields.
Preferably, the step S120 further includes:
the method comprises the steps that request data information needing to be exported in updated data is detected through a client interface, the client requests a background interface, and an ID corresponding to the request data is transmitted;
the background accesses the application database server, first feedback data corresponding to the request data is determined through the application database server, meanwhile, the background inquires information of a target database server corresponding to the ID from the application database server, and the application database server feeds the information of the target database server back to the background;
the background acquires a table structure and/or data corresponding to the ID from the target database server based on the information of the target database server as second feedback data;
the target database server feeds back the table structure and/or data to the background.
In other embodiments, the visualization database change statement generating program 10 may also be divided into one or more modules, which are stored in the memory 11 and executed by the processor 12 to accomplish the present invention. The modules referred to herein are referred to as a series of computer program instruction segments capable of performing specified functions.
Referring to fig. 2, a block diagram of a preferred embodiment of the visualized database change statement generation program 10 of fig. 1 is shown. The visualization database change statement generation program 10 may be divided into:
and the data writing unit 101 is configured to request access to the target database server through the background interface according to the input target database server information, and write data that needs to be detected and updated in the target database server into the application database server.
The feedback data acquiring unit 102 is configured to select request data information that needs to be derived from data that needs to be detected and updated, determine first feedback data corresponding to the request data by accessing the application database server through the background, acquire target database server information corresponding to the request data information, and determine second feedback data corresponding to the request data in the target database server.
A change statement acquisition unit 103 configured to determine a difference item between the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determine a change statement corresponding to the difference item according to the difference item.
In addition, the invention also provides a visual database change statement generation method. Fig. 3 is a flowchart illustrating a method for generating a visual database change statement according to a preferred embodiment of the present invention. Referring to fig. 4, a detailed flow of the visualized database change statement generation method according to the present invention is shown, and the method may be executed by a device, and the device may be implemented by software and/or hardware.
In this embodiment, the method for generating a visual database change statement includes the following steps:
s110: and requesting to access the target database server through a background interface according to the information of the target database server, and writing the data which needs to be detected and updated in the target database server into the application database server.
Specifically, the step S110 further includes:
1. requesting a background interface according to target database server information input by a client interface, and requesting the target database server by a background to acquire base table information under the target database server;
2. the target database server returns the base table information to the background, and the data corresponding to the base table information is fed back to the client interface through the background for display;
3. requesting a background interface according to target database server information based on feedback data and data needing to be detected and updated submitted by a client interface, and substituting the target database server information and the data needing to be detected and updated into the background interface;
4. the background requests the target database server to obtain the data needing to be detected and updated again;
5. the target database server feeds back the structure and/or data information of the table corresponding to the data needing to be detected and updated to the background according to the request of the background;
6. and the background writes the structure and/or data information of the currently acquired table into the application database server, and simultaneously generates a submission record for the client list to view.
In the steps 1 and 2, the connection target database server is different from the connection application database server, and the address, the port, the user name and the password of the target database server are all filled by the user, so that the connection needs to be dynamically created.
The target database server information includes information such as a target database server IP, a port, a user name, and a password.
In the above step 5, the target database server is connected in the same manner as in the native Jdbc, and unlike steps 1 and 2, in step 5, structural information such as column (field) and column data type, length, default value, and whether the column is empty is obtained for the known database and table set, and if the user selects data change detection, further query is performed on all data stored in the table.
In the step 6, since all the connection information of the application database server is fixed, the mybatis framework is used for implementation, so that the development is convenient, and the step needs the information submitted by the front-end client. All connection target database servers adopt a native Jdbc mode, and all connection application database servers adopt a mybatis mode. The mybatis is a persistent layer framework based on Jdbc encapsulation, and development efficiency can be improved.
The column structure information and all data of the table generated in step 5 are stored in the application database server, so 3 tables need to be created in the application database: (1) log sheet-save information submitted by users; (2) a field table-stores the structural information of all columns in the step 5 and the database and table corresponding to the columns, and associates the record id generated in the record table submitted this time; (3) the data table stores the data generated in step 5 in the form of JSON array, including corresponding database and table, and also associates the record id generated in the record table by the present submission.
In other words, the commit record includes a record table, a data table, and a field table created in the application database server; the record table is used for storing information submitted by a user; the data table is used for storing the structure and/or data information of the table corresponding to the data needing to be detected and updated in a json array form; the field table is used for storing fields of the structure and/or data information of the table corresponding to the data needing to be detected and updated, and data types, lengths and default values of the fields.
S120: the method comprises the steps of selecting request data information needing to be exported in data needing to be detected and updated, determining first feedback data corresponding to the request data through a background access application database server, obtaining target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in a target database server.
Wherein, the step S120 further includes:
1. the method comprises the steps that request data information needing to be exported in updated data is detected through a client interface, the client requests a background interface, and an ID corresponding to the request data is transmitted;
2. the background accesses the application database server, first feedback data corresponding to the request data is determined through the application database server, meanwhile, the background inquires information of a target database server corresponding to the ID from the application database server, and the application database server feeds the information of the target database server back to the background;
3. the background acquires a table structure and/or data corresponding to the ID from the target database server based on the information of the target database server as second feedback data;
4. the target database server feeds back the table structure and/or data to the background.
S130: and determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
In the process of comparing the data inquired by the application database with the data inquired by the target database, the method for comparing column structure information with table data is as follows:
the mybatis query application database can finally return a List-type Javabean, the Jdbc query target database can also be converted into the List-type Javabean, the two List sets are compared through the stream API of java8 to obtain a difference item, and then a change sql statement is spliced by using a fixed sql template. And finally, exporting the sql sentences into files for the user to download in a JavaIO stream mode.
Specifically, the first feedback data and the second feedback data both include (field) structures and data information of a plurality of tables, and the structures and the data information of each table are compared one by one in the comparison process.
In the first case: and (5) comparing table structures.
The first feedback data is directly acquired through a mybatis framework, and the data structure in java belongs to a List < ColumnPo > type, namely a ColumnPo type set. ColumnPo is a custom type and contains field attributes such as id, name, type, length, isNull, default, comment, index and the like, and library tables to which fields such as database and tableName belong. The second feedback data is not of the List < ColumnPo > type, but may be indirectly converted to this type by the setter method. The comparison can be started after the types are the same.
Further, the field structure change of the table is divided into three cases: adding fields, modifying fields and deleting fields. In the comparison process, firstly, the intersection of the fields of the two (the first feedback data and the second feedback data) is determined, the fields Id of the two are respectively taken out by using stream (). map (columnPo:: getId). collect (collectors.toset ()) and stored by Set, and then the intersection of the two can be found by calling the retainAll method of the Set. And the intersection of the first feedback data and the second feedback data represents the fields of the first feedback data and the second feedback data, then the Set is traversed, ColumnPo of the first feedback and the second feedback is respectively obtained through the field Id, and all attribute values in the ColumnPo are compared one by one. The invention also maintains an sql template class, and has corresponding templates sql for field modification, new addition, deletion and the like as well as data modification, new addition, deletion and the like, for example, the sql template for field modification is ALTER TABLE { } CHANGE COLUMN { } { } { } { }; wherein { } is a placeholder, and is tableName, oldName, newName, type, isNull, default and comment respectively in sequence. And when the attribute values in the ColumnPo of the two are different, filling the attribute in the ColumnPo of the latter according to the sql template to complete a change sql. Fields which are more than the intersection in the first feedback data belong to field deletion, and fields which are more than the intersection in the second feedback data belong to field addition. The field addition and deletion are provided with corresponding sql templates, and the change sql of the field addition and deletion can be completed only by replacing the placeholders in the templates.
In the second case: table data comparison.
The contrast of the table data is slightly different from the table structure, a piece of data in the target database table is converted into JSON format and stored in a field of a piece of data of the application database server, and besides the piece of data also comprises database and table to which the JSON format data belongs.
The JSON format data in the first feedback data is deserialized into Map type data in java, the Map is stored mainly in the form of key-value, such as { "id": 10001 "," name ": zhang", "age": 25 "," sender ": male", and the second feedback data can also be converted into the same Map form. Considering that there may be a field name change in the table, the key value of the Map data of the first feedback needs to be updated to be consistent with the second feedback data, so that the comparison can be performed.
The primary key field (the field capable of uniquely locating the piece of data) of the table, such as id, is inquired from the target database server, and then the intersection of the data is obtained in the same way as the intersection of the table structures. The sql types of the data change are also divided into three types, namely addition, deletion and modification, the template sql is also provided with three corresponding types, and the scheme for generating the data change sql according to the template is basically consistent with the scheme for generating the table structure change sql.
And finally outputting all the generated changes sql to a client browser through OutputStream, and popping and downloading in the browser in the form of attachments.
As a specific example, fig. 5 illustrates a structure of a client interface of a visualization database change statement generation method according to an embodiment of the present invention.
As shown in fig. 5, the list interface of the client displays all the task lists that have been neutralized, and can perform addition and editing of records, and after clicking, a new or editing interface is popped up, as shown in the following figure, and the host address, the port number, the user name, and the password of the database can be input through the interface, and then the new or editing interface is connected to the database of the environment, so as to select the library and all the tables below the library in a linkage manner. The table needs to be checked, and the change information of the table cannot be detected if the table is not checked. There is a hook option behind the table to indicate whether table data change detection is enabled. By default, the table structure is checked for changes, but in some cases synchronization of the data is required, and the item needs to be checked.
According to the visual database change statement generation method, the data inquired by the application database is compared with the data inquired by the target database, the difference items of the two are obtained, and visual processing is carried out, so that a developer does not need to care whether each action influences the table structure or the data of the database, and only needs to respectively carry out visual operation at the beginning and the end of the version, and all subsequent manual operations can be replaced; modifying the table structure may also operate directly without writing SQL statements.
In addition, an embodiment of the present invention further provides a computer-readable storage medium, where the computer-readable storage medium includes a visualized database change statement generation program, and when executed by a processor, the visualized database change statement generation program implements the following operations:
s110: requesting to access the target database server through a background interface according to the information of the target database server, and writing data which needs to be detected and updated in the target database server into an application database server;
s120: the method comprises the steps of selecting request data information needing to be exported in data needing to be detected and updated, determining first feedback data corresponding to the request data through a background access application database server, obtaining target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in a target database server;
s130: and determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
The specific implementation of the computer-readable storage medium of the present invention is substantially the same as the above-mentioned visual database change statement generation method and the electronic device, and will not be described herein again.
It should be noted that, in this document, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, apparatus, article, or method that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, apparatus, article, or method. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, apparatus, article, or method that includes the element.
The above-mentioned serial numbers of the embodiments of the present invention are merely for description and do not represent the merits of the embodiments. Through the above description of the embodiments, those skilled in the art will clearly understand that the method of the above embodiments can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware, but in many cases, the former is a better implementation manner. Based on such understanding, the technical solution of the present invention may be embodied in the form of a software product, which is stored in a storage medium (e.g., ROM/RAM, magnetic disk, optical disk) as described above and includes instructions for enabling a terminal device (e.g., a mobile phone, a computer, a server, or a network device) to execute the method according to the embodiments of the present invention.
The above description is only a preferred embodiment of the present invention, and not intended to limit the scope of the present invention, and all modifications of equivalent structures and equivalent processes, which are made by using the contents of the present specification and the accompanying drawings, or directly or indirectly applied to other related technical fields, are included in the scope of the present invention.
Claims (10)
1. A visual database change statement generation method is applied to an electronic device and is characterized by comprising the following steps:
s110: requesting to access a target database server through a background interface according to the information of the target database server, and writing data which needs to be detected and updated in the target database server into an application database server;
s120: selecting request data information needing to be exported in the data needing to be detected and updated, accessing the application database server through a background to determine first feedback data corresponding to the request data, acquiring target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in the target database server;
s130: determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
2. The visualized database alteration statement generation method according to claim 1, wherein step S110 further comprises:
requesting a background interface according to target database server information input by a client interface, wherein the background requests the target database server to acquire base table information under the target database server;
the target database server returns the base table information to the background, and the data corresponding to the base table information is fed back to a client interface through the background for display;
requesting a background interface according to target database server information based on feedback data and data needing to be detected and updated submitted by a client interface, and substituting the target database server information and the data needing to be detected and updated into the background interface;
the background requests the target database server to acquire the data needing to be detected and updated again;
the target database server feeds back the structure and/or data information of the table corresponding to the data needing to be detected and updated to the background according to the request of the background;
and the background writes the structure and/or data information of the currently acquired table into the application database server and simultaneously generates a submission record for the client list to view.
3. The visual database change statement generation method according to claim 2,
the background requests the target database server to acquire the base table information under the target database server; the target database server returns the base table information to the background, and the step of feeding back the data corresponding to the base table information to the client interface through the background for display comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all base table information under the target database server through a native sql statement;
and acquiring the structures and/or data information of all tables under the base table information corresponding to the target database server information, and feeding back the structures and/or data information to the client interface for display.
4. The visual database change statement generation method according to claim 2,
the submission record includes a record table, a data table, and a field table created in the application database server; wherein the content of the first and second substances,
the record table is used for storing information submitted by a user;
the data table is used for storing the structure and/or data information of the table corresponding to the data needing to be detected and updated in a json array form;
the field table is used for storing the structure of the table corresponding to the data needing to be detected and updated and/or the field of the data information, and the data type, the length and the default value of the field.
5. The visualized database alteration statement generation method according to claim 1, wherein step S120 further comprises:
selecting request data information needing to be exported in the detected and updated data through a client interface, wherein the client requests a background interface and transmits an ID (identity) corresponding to the request data;
the background accesses the application database server, first feedback data corresponding to the request data is determined through the application database server, meanwhile, the background inquires target database server information corresponding to the ID from the application database server, and the application database server feeds the target database server information back to the background;
the background acquires a table structure and/or data corresponding to the ID from the target database server based on the information of the target database server as the second feedback data;
and the target database server feeds the table structure and/or the data back to the background.
6. An electronic device, comprising: the system comprises a memory and a processor, wherein the memory comprises a visual database change statement generation program, and the visual database change statement generation program realizes the following steps when being executed by the processor:
s110: requesting to access a target database server through a background interface according to the information of the target database server, and writing data which needs to be detected and updated in the target database server into an application database server;
s120: selecting request data information needing to be exported in the data needing to be detected and updated, accessing the application database server through a background to determine first feedback data corresponding to the request data, acquiring target database server information corresponding to the request data information, and determining second feedback data corresponding to the request data in the target database server;
s130: determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data with the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
7. The electronic device of claim 6,
step S110 further includes:
requesting a background interface according to target database server information input by a client interface, wherein the background requests the target database server to acquire base table information under the target database server;
the target database server returns the base table information to the background, and the data corresponding to the base table information is fed back to a client interface through the background for display;
requesting a background interface according to target database server information based on feedback data and data needing to be detected and updated submitted by a client interface, and substituting the target database server information and the data needing to be detected and updated into the background interface;
the background requests the target database server to acquire the data needing to be detected and updated again;
the target database server feeds back the structure and/or data information of the table corresponding to the data needing to be detected and updated to the background according to the request of the background;
and the background writes the structure and/or data information of the currently acquired table into the application database server and simultaneously generates a submission record for the client list to view.
8. The electronic device of claim 7,
the background requests the target database server to acquire the base table information under the target database server; the target database server returns the base table information to the background, and the step of feeding back the data corresponding to the base table information to the client interface through the background for display comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all base table information under the target database server through a native sql statement;
and acquiring the structures and/or data information of all tables under the base table information corresponding to the target database server information, and feeding back the structures and/or data information to the client interface for display.
9. The electronic device of claim 7,
the submission record includes a record table, a data table, and a field table created in the application database server; wherein the content of the first and second substances,
the record table is used for storing information submitted by a user;
the data table is used for storing the structure and/or data information of the table corresponding to the data needing to be detected and updated in a json array form;
the field table is used for storing the structure of the table corresponding to the data needing to be detected and updated and/or the field of the data information, and the data type, the length and the default value of the field.
10. A computer-readable storage medium, characterized in that a visualized database change statement generation program is included in the computer-readable storage medium, and when executed by a processor, the visualized database change statement generation program realizes the steps of the visualized database change statement generation method according to any one of claims 1 to 5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010033126.4A CN111274263A (en) | 2020-01-13 | 2020-01-13 | Visual database change statement generation method and device and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010033126.4A CN111274263A (en) | 2020-01-13 | 2020-01-13 | Visual database change statement generation method and device and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111274263A true CN111274263A (en) | 2020-06-12 |
Family
ID=70996881
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010033126.4A Pending CN111274263A (en) | 2020-01-13 | 2020-01-13 | Visual database change statement generation method and device and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111274263A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113111239A (en) * | 2021-04-08 | 2021-07-13 | 北京联创新天科技有限公司 | Universal database operation method, device and storage medium thereof |
| CN114089979A (en) * | 2021-11-29 | 2022-02-25 | 中国联合网络通信集团有限公司 | Persistent layer code updating method, system, terminal device and computer storage medium |
| CN114116664A (en) * | 2021-11-19 | 2022-03-01 | 招联消费金融有限公司 | Database table building statement processing method and device, computer equipment and storage medium |
| WO2022242635A1 (en) * | 2021-05-18 | 2022-11-24 | 安翰科技(武汉)股份有限公司 | Management device for sql records for maintenance and application system |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030065662A1 (en) * | 2001-08-13 | 2003-04-03 | Jasmin Cosic | Universal data management interface |
| CN102929878A (en) * | 2011-08-09 | 2013-02-13 | 阿里巴巴集团控股有限公司 | Method and device for managing database changes |
| JP2016053919A (en) * | 2014-09-04 | 2016-04-14 | 東芝テック株式会社 | Server device and program |
| CN108200220A (en) * | 2018-04-08 | 2018-06-22 | 武汉斗鱼网络科技有限公司 | A kind of method of data synchronization, server and storage medium |
| CN109992589A (en) * | 2019-04-11 | 2019-07-09 | 北京启迪区块链科技发展有限公司 | Method, apparatus, server and the medium of SQL statement are generated based on visual page |
-
2020
- 2020-01-13 CN CN202010033126.4A patent/CN111274263A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030065662A1 (en) * | 2001-08-13 | 2003-04-03 | Jasmin Cosic | Universal data management interface |
| CN102929878A (en) * | 2011-08-09 | 2013-02-13 | 阿里巴巴集团控股有限公司 | Method and device for managing database changes |
| JP2016053919A (en) * | 2014-09-04 | 2016-04-14 | 東芝テック株式会社 | Server device and program |
| CN108200220A (en) * | 2018-04-08 | 2018-06-22 | 武汉斗鱼网络科技有限公司 | A kind of method of data synchronization, server and storage medium |
| CN109992589A (en) * | 2019-04-11 | 2019-07-09 | 北京启迪区块链科技发展有限公司 | Method, apparatus, server and the medium of SQL statement are generated based on visual page |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113111239A (en) * | 2021-04-08 | 2021-07-13 | 北京联创新天科技有限公司 | Universal database operation method, device and storage medium thereof |
| CN113111239B (en) * | 2021-04-08 | 2024-03-29 | 北京联创新天科技有限公司 | General database operation method, device and storage medium thereof |
| WO2022242635A1 (en) * | 2021-05-18 | 2022-11-24 | 安翰科技(武汉)股份有限公司 | Management device for sql records for maintenance and application system |
| CN114116664A (en) * | 2021-11-19 | 2022-03-01 | 招联消费金融有限公司 | Database table building statement processing method and device, computer equipment and storage medium |
| CN114116664B (en) * | 2021-11-19 | 2024-04-02 | 招联消费金融股份有限公司 | Database table-building sentence processing method, device, computer equipment and storage medium |
| CN114089979A (en) * | 2021-11-29 | 2022-02-25 | 中国联合网络通信集团有限公司 | Persistent layer code updating method, system, terminal device and computer storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10462222B2 (en) | Cloud storage methods and systems | |
| CN111274263A (en) | Visual database change statement generation method and device and storage medium | |
| US7325188B1 (en) | Method and system for dynamically capturing HTML elements | |
| US8595259B2 (en) | Web data usage platform | |
| CA2684822C (en) | Data transformation based on a technical design document | |
| CN108874924B (en) | Method and device for creating search service and computer-readable storage medium | |
| CN109933571B (en) | Database design document generation method, device and computer readable storage medium | |
| WO2019085474A1 (en) | Calculation engine implementing method, electronic device, and storage medium | |
| US9967370B2 (en) | OData enabled mobile software applications | |
| JP2005011339A (en) | Context association schema for computer system architecture | |
| CN108536745B (en) | Shell-based data table extraction method, terminal, equipment and storage medium | |
| US20070073770A1 (en) | Methods, systems, and computer program products for resource-to-resource metadata association | |
| CN109710220B (en) | Relational database query method, relational database query device, relational database query equipment and storage medium | |
| CN110865834B (en) | Application program interface updating method and device, readable storage medium and terminal equipment | |
| US8413109B2 (en) | Systems and methods for metamodel transformation | |
| CN112364083A (en) | Data dictionary management method, system and storage medium based on configuration file | |
| WO2019161620A1 (en) | Application dependency update method, terminal and device, and storage medium | |
| US20070185832A1 (en) | Managing tasks for multiple file types | |
| CN111427577A (en) | Code processing method and device and server | |
| CN110597508A (en) | Interface dynamic configuration method, device and storage medium | |
| CN112559913B (en) | Data processing method, device, computing equipment and readable storage medium | |
| CN112257005A (en) | Form component generation method and device | |
| US20180165068A1 (en) | Generation and usage of language-converted script | |
| CN115062084B (en) | Method and device for constructing API (application programming interface) based on database metadata | |
| CN111125205A (en) | Signboard data display method, terminal and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |