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 PDF

Info

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
Application number
CN202010033126.4A
Other languages
Chinese (zh)
Inventor
李佳文
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Ping An International Smart City Technology Co Ltd
Original Assignee
Ping An International Smart City Technology Co Ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Ping An International Smart City Technology Co Ltd filed Critical Ping An International Smart City Technology Co Ltd
Priority to CN202010033126.4A priority Critical patent/CN111274263A/en
Publication of CN111274263A publication Critical patent/CN111274263A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24Querying
    • G06F16/242Query formulation
    • G06F16/2433Query languages
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24Querying
    • G06F16/248Presentation 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

Visual database change statement generation method and device and storage medium
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.
CN202010033126.4A 2020-01-13 2020-01-13 Visual database change statement generation method and device and storage medium Pending CN111274263A (en)

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)

* Cited by examiner, † Cited by third party
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)

* Cited by examiner, † Cited by third party
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

Patent Citations (5)

* Cited by examiner, † Cited by third party
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)

* Cited by examiner, † Cited by third party
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