CN111274263B - Visual database change sentence generation method, device and storage medium - Google Patents
Visual database change sentence generation method, device and storage medium Download PDFInfo
- Publication number
- CN111274263B CN111274263B CN202010033126.4A CN202010033126A CN111274263B CN 111274263 B CN111274263 B CN 111274263B CN 202010033126 A CN202010033126 A CN 202010033126A CN 111274263 B CN111274263 B CN 111274263B
- 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.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; 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 OR CALCULATING; 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: according to the information of the target database server, requesting to access the target database server through a background interface, and writing the data needing to be detected and updated in the target database server into an application database server; selecting request data information needing to be exported in the data needing to be detected and updated, determining first feedback data corresponding to the request data by accessing an application database server through a background, 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; and obtaining the difference item and a 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 database change, and table structure modification can also be directly operated without writing sql sentences.
Description
Technical Field
The present invention relates to the field of data processing technologies, and in particular, to a method and apparatus for generating a change sentence of a visual database, and a computer readable storage medium.
Background
At present, the following two modes, namely, the mode one, are mainly adopted for SQL release of different environments in projects: manually collecting and storing SQL sentences according to a certain rule; mode two: released to the target environment by the persistent integration tool and automatically executed.
Wherein, in order to ensure information security, the second mode is difficult to optimize. While the first approach suffers from the following drawbacks: 1. is easy to miss. Specifically, when the structure or data of a certain field is modified, most of the field is directly edited in a graphical tool, and the SQL statement is rarely adopted, so that the file is not needed, and the change is easy to forget; 2. the operation is troublesome. Because the corresponding SQL sentences are required to be written and unified files are collected after each database change, the operation is inconvenient, and the realization is not easy under the condition of high change frequency; 3. a conflict is likely to exist. Since an application is often developed by multiple persons at the same time, each person may generate a change SQL, when the application is finally released, the SQL of all persons needs to be collected to ensure that there are no statements that conflict with each other, and the sequence of the statements needs to be ensured, for example, the SQL of one person is a statement for building a table, and the SQL of the other person is a data insertion statement for creating a table, so that the sequence of the statement for building a table needs to be ensured before the data is inserted. 4. Not safe. The method mainly refers to unsafe data, and manually collected SQL needs to determine the safety of sentences, such as whether the operation of clearing a database and clearing a table is involved or whether the prior data is influenced by deleting the sentences or not.
Disclosure of Invention
The invention provides a visual database change statement generation method, an electronic device and a computer readable storage medium, which mainly aim to perform visual processing on database change, so that a developer does not need to care whether each action can influence the table structure or data of the database, and only needs to perform visual operation when a version starts and ends.
In order to achieve the above object, the present invention provides a method for generating a visual database change sentence, which is applied to an electronic device, and the method includes:
S110: according to the information of the target database server, requesting to access the target database server through a background interface, and writing the data needing 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: and determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data and the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
Preferably, 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 library table information under the target database server;
the target database server returns the database table information to the background, and the data corresponding to the database table information is fed back to a client interface for display through the background;
Requesting a background interface according to target database server information based on feedback data and data to be detected and updated submitted by a client interface, and substituting the target database server information and the data 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 to be detected and updated to the background according to the background request;
the background writes the structure and/or data information of the currently acquired table to an application database server while generating a commit record for viewing by the client list.
Preferably, the background requests the target database server to acquire the database table information under the target database server; the target database server returns the database table information to the background, and the step of feeding back the data corresponding to the database table information to a client interface for displaying through the background comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all library table information under the target database server through a native sql statement;
and acquiring the structure and/or data information of all tables under the library table information corresponding to the target database server information, and feeding back to a client interface for display.
Preferably, the commit record includes a record table, a data table and a field table created in the application database server; wherein,
The record list 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 to be detected and updated in a json array mode;
The field table is used for storing the field of the table structure and/or data information corresponding to the data to be detected and updated, and the data type, the length and the default value of the field.
Preferably, step S120 further includes:
selecting request data information to be exported in the detected and updated data through a client interface, wherein the client requests a background interface and transmits in an ID corresponding to the request data;
The background accesses the application database server, determines first feedback data corresponding to the request data through the application database server, and meanwhile, queries the application database server for target database server information corresponding to the ID, and 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 target database server information as the second feedback data;
the target database server feeds back the table structure and/or data to the background.
In order to achieve the above object, the present invention also provides an electronic device including: the visual database change sentence generating program is executed by the processor and comprises the following steps:
S110: according to the input target database server information, requesting to access the target database server through a background interface, and writing the data needing 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: and comparing the first feedback data with the second feedback data to determine a difference item of the first feedback data and the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
Preferably, 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 library table information under the target database server;
the target database server returns the database table information to the background, and the data corresponding to the database table information is fed back to a client interface for display through the background;
Requesting a background interface according to target database server information based on feedback data and data to be detected and updated submitted by a client interface, and substituting the target database server information and the data 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 to be detected and updated to the background according to the background request;
the background writes the structure and/or data information of the currently acquired table to an application database server while generating a commit record for viewing by the client list.
Preferably, the background requests the target database server to acquire the database table information under the target database server; the target database server returns the database table information to the background, and the step of feeding back the data corresponding to the database table information to a client interface for displaying through the background comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all library table information under the target database server through a native sql statement;
and acquiring the structure and/or data information of all tables under the library table information corresponding to the target database server information, and feeding back to a client interface for display.
Preferably, the method comprises the steps of,
The submitting records comprise a record table, a data table and a field table which are created in the application database server; wherein,
The record list 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 to be detected and updated in a json array mode;
the field table is used for storing the field of the table structure and/or data information corresponding to the data to be detected and updated, and the data type, length and default value of the field
In addition, in order to achieve the above object, the present invention also provides a computer-readable storage medium including a visual database modification sentence generation program which, when executed by a processor, implements any of the steps in the visual database modification sentence generation method described above.
The visual database change statement generation method, the electronic device and the computer readable storage medium provided by the invention are used for comparing the data queried by the application database with the data queried by the target database, obtaining the difference items of the data queried by the application database and the target database, and performing visual processing, so that a developer does not need to care whether each action can influence the table structure or the data of the database, and all subsequent manual operations can be replaced by performing visual operations respectively at the beginning and the end of the version; in addition, the modification table structure can be directly operated without writing SQL sentences, so that the time of a developer can be greatly saved, and the effect is more remarkable under the condition of deployment and upgrading in two different environments.
Drawings
FIG. 1 is a schematic diagram of an application environment of an embodiment of a method for generating a visual database change sentence according to the present invention;
FIG. 2 is a block diagram of a preferred embodiment of the visual database change sentence generation program of FIG. 1;
FIG. 3 is a flowchart of a visual database change sentence generation method according to a preferred embodiment of the present invention;
FIG. 4 is a flowchart of a method for generating a visual database change sentence 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 achievement of the objects, functional features and advantages of the present invention will be further described with reference to the accompanying drawings, in conjunction with the embodiments.
Detailed Description
It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention.
The invention provides a visual database change sentence generation method which is applied to an electronic device 1. Referring to fig. 1, a schematic diagram of an application environment of a preferred embodiment of a method for generating a visual database change sentence according to the present invention is shown.
In this embodiment, the electronic apparatus 1 may be a terminal device having an operation 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 memory 11, etc. In some embodiments, the readable storage medium may be an internal storage unit of the electronic device 1, such as a hard disk of the electronic device 1. In other embodiments, the readable storage medium may also be the external memory 11 of the electronic device 1, such as a plug-in hard disk, a smart memory card (SMART MEDIA CARD, SMC), a Secure Digital (SD) card, a flash memory card (FLASH CARD) or 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 visual database modification sentence generating program 10 installed in the electronic device 1. The memory 11 may also be used for temporarily storing data that has been output or is to be output.
The processor 12 may in some embodiments be a central processing unit (Central Processing Unit, CPU), microprocessor or other data processing chip for running program code or processing data stored in the memory 11, such as executing the visual database change statement generation program 10, etc.
The network interface 14 may alternatively comprise a standard wired interface, a wireless interface (e.g. WI-FI interface), typically used to establish a communication connection between the electronic apparatus 1 and other electronic devices.
The communication bus 15 is used to enable connection communication between these components.
Fig. 1 shows only an electronic device 1 with components 11-15, but it is understood that not all shown components are required to be implemented, and that more or fewer components may be implemented instead.
Optionally, the electronic apparatus 1 may further comprise a user interface, which may comprise an input unit such as a Keyboard (Keyboard), a voice input device such as a microphone or the like with voice recognition function, a voice output device such as a sound box, a headset or the like, and optionally a standard wired interface, a wireless interface.
Optionally, the electronic device 1 may also comprise a display, which may also be referred to as a display screen or display unit. In some embodiments, the display may be an LED display, a liquid crystal display, a touch-control 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 device 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 a touch operation is referred to as a touch area. Further, the touch sensors described herein may be resistive touch sensors, capacitive touch sensors, and the like. The touch sensor may include not only a contact type touch sensor but also a proximity type touch sensor. Furthermore, 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 a touch operation triggered by a user based on a touch display screen.
Optionally, the electronic device 1 may further include a Radio Frequency (RF) circuit, a sensor, an audio circuit, etc., which are not described herein.
In the embodiment of the apparatus shown in fig. 1, an operating system, and a visual database modification sentence generating program 10 may be included in a memory 11 as a kind of computer storage medium; the processor 12 executes the visual database modification statement generation program 10 stored in the memory 11, and performs the following steps:
S110: according to the information of the target database server, requesting to access the target database server through a background interface, and writing the data needing 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, determining first feedback data corresponding to the request data by accessing an application database server through a background, 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: and determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data and the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
Preferably, step S110 further includes:
Requesting a background interface according to the information of the target database server input by the client interface, and requesting the target database server by the background to acquire the database table information under the target database server;
The target database server returns the database table information to the background, and the data corresponding to the database table information is fed back to the client interface for display through the background;
Requesting a background interface according to target database server information based on feedback data and data to be detected and updated submitted by a client interface, and substituting the target database server information and the data 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 to be detected and updated to the background according to the background request;
The background writes the structure and/or data information of the currently acquired table to the application database server while generating a commit record for viewing by the client list.
Preferably, the background requests to the target database server to acquire the database table information under the target database server; the target database server returns the database table information to the background, and the step of feeding the data corresponding to the database table information back to the client interface for display through the background comprises the following steps:
connecting a target database server in a native Jdbc mode, and inquiring all library table information under the target database server through a native sql statement;
And acquiring the structure and/or data information of all tables under the library table information corresponding to the target database server information, and feeding back to the client interface for display.
Preferably, the commit record includes a record table, a data table, and a field table created in the application database server; wherein,
The record list 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 to be detected and updated in the json array form;
The field table is used for storing the fields of the table structure and/or data information corresponding to the data to be detected and updated, and the data type, the length and the default value of the fields.
Preferably, step S120 further includes:
Selecting and detecting the information of the request data to be exported in the updated data through a client interface, requesting a background interface by the client, and transmitting an ID corresponding to the request data;
The background accesses the application database server, determines first feedback data corresponding to the request data through the application database server, and simultaneously, the background inquires the application database server about target database server information corresponding to the ID, 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 target database server information as second feedback data;
the target database server feeds the table structure and/or data back to the background.
In other embodiments, the visual database change statement generation program 10 may also be partitioned into one or more modules, one or more modules being stored in the memory 11 and executed by the processor 12 to complete the present invention. The invention may refer to a series of computer program instruction segments capable of performing a specified function.
Referring to FIG. 2, a block diagram of a preferred embodiment of the visual database change sentence generating program 10 of FIG. 1 is shown. The visual database change sentence generating program 10 may be divided into:
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 the data in the target database server, which needs to be detected and updated, into the application database server.
The feedback data obtaining unit 102 is configured to select information of the requested data to be derived from the data to be detected and updated, determine first feedback data corresponding to the requested data by accessing the application database server in the background, obtain information of a target database server corresponding to the requested data, and determine second feedback data corresponding to the requested data in the target database server.
And a change sentence acquisition unit 103 configured to determine a difference term between the first feedback data and the second feedback data by comparing the first feedback data and the second feedback data, and determine a change sentence corresponding to the difference term according to the difference term.
In addition, the invention also provides a visual database change sentence generation method. Referring to FIG. 3, a flowchart of a method for generating a visual database change sentence according to a preferred embodiment of the present invention is shown. Referring also to FIG. 4, a detailed flow of the visual database change statement generation method of the present invention is shown, which may be performed by an apparatus that may be implemented in software and/or hardware.
In this embodiment, the visual database change sentence generation method includes the steps of:
s110: and according to the information of the target database server, requesting to access the target database server through a background interface, and writing the data needing 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 the information of the target database server input by the client interface, and requesting the target database server by the background to acquire the database table information under the target database server;
2. The target database server returns the database table information to the background, and the data corresponding to the database table information is fed back to the client interface for display through the background;
3. Requesting a background interface according to target database server information based on feedback data and data to be detected and updated submitted by a client interface, and substituting the target database server information and the data to be detected and updated into the background interface;
4. the background requests the target database server to acquire 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 to be detected and updated to the background according to the background request;
6. the background writes the structure and/or data information of the currently acquired table to the application database server while generating a commit record for viewing by the client list.
In the method for generating the visual database change statement, the target database server is connected in a native Jdbc mode without specifying a specific library when the connection is created, all databases under the target database server are queried through native sql statements, and then all the tables under the target database server are acquired according to the databases selected by the user and returned to the front end interface.
The target database server information comprises information such as IP, port, user name and password of the target database server.
In step 5, the target database server is connected by adopting the native Jdbc method, unlike steps 1 and 2, in step 5, in the case of knowing the database and the table set, the column (field) of the table and the structure information of the column, such as the data type, the length, the default value, whether the column is empty or not, and if the user selects the data change detection, all the data stored in the table are further queried.
In step 6 above, since all connection information of the application database server is fixed, implementation using mybatis framework facilitates development, which requires information submitted by the front-end client. All the connection target database servers adopt a native Jdbc mode, and all the connection application database servers adopt a mybatis mode. Wherein mybatis is a persistent layer framework based on Jdbc encapsulation, which can improve development efficiency.
The column structure information generated in step 5 and all data of the table are saved in the application database server, so 3 tables need to be created in the application database: (1) record table-save user submitted information; (2) A field table-storing the structural information of all column in the step 5 and database, table corresponding to the column, and associating record ids generated in the record table in the current submission; (3) Data table-the data generated in step 5 is saved in the form of JSON array, including corresponding database, table, which also correlates 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 list 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 to be detected and updated in the json array form; the field table is used for storing the fields of the table structure and/or data information corresponding to the data to be detected and updated, and the data type, the length and the default value of the fields.
S120: and selecting the request data information needing to be exported in the data needing to be detected and updated, determining the first feedback data corresponding to the request data by accessing an application database server in the background, acquiring the target database server information corresponding to the request data information, and determining the second feedback data corresponding to the request data in the target database server.
Wherein, the step S120 further includes:
1. Selecting and detecting the information of the request data to be exported in the updated data through a client interface, requesting a background interface by the client, and transmitting an ID corresponding to the request data;
2. The background accesses the application database server, determines first feedback data corresponding to the request data through the application database server, and simultaneously, the background inquires the application database server about target database server information corresponding to the ID, and the application database server feeds the target database server information 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 target database server information as second feedback data;
4. The target database server feeds the table structure and/or data back 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 and 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 queried by the application database with the data queried by the target database, the comparison method for the column structure information and the table data is as follows:
The mybatis query application database can finally return a List type Javabean, jdbc query target database can also be converted into a List type Javabean, then the two List sets are compared through STREAM API of java8 to obtain a difference item, and then a change sql statement is spliced by a fixed sql template. These sql statements are finally exported as files for user download by way of Java IO streams.
Specifically, the first feedback data and the second feedback data both include (field) structures and data information of a plurality of tables, and in the comparison process, the structures and data information of each table are compared one by one.
First case: table structure comparison.
The first feedback data is directly obtained through a mybatis framework, and in java, the data structure belongs to a List < ColumnPo > type, namely a ColumnPo type set. ColumnPo is a custom type, which includes id, name, type, length, isNull, default, comment, index and other field attributes and database, tableName, which belongs to the library table. The second feedback data, although not of List < ColumnPo > type, may also be indirectly converted to this type by the setter method. The comparison can be started after the same type.
Further, the field structure change of the table is divided into three cases: field addition, field modification, field deletion. In the comparison process, firstly, the intersection of the fields of the first feedback data and the second feedback data is determined, the fields Id of the two fields are respectively taken out and stored in a Set by using stream (). Map (ColumnPo:: getId). Collect (collectors. Toset ()), and then the RETAINALL method of the Set is called to obtain the intersection of the two fields. The intersection of the two indicates fields in which the first feedback data and the second feedback data exist, the Set is traversed, columnPo of the first feedback and the second feedback are respectively obtained through the field Id, and all attribute values in 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 and data modification, new addition, deletion and the like, such as an ALTER TABLE { CHANGE COLUMN { } { } { } { } { } for field modification; wherein { } is placeholder, tableName, oldName, newName, type, isNull, default, comment in order, respectively. And when the attribute values in ColumnPo of the two are different, filling the attribute in ColumnPo of the two according to the sql template to complete a change sql. The fields with more intersections in the first feedback data belong to field deletion, and the fields with more intersections in the second feedback data belong to field addition. The field is added and deleted with corresponding sql templates, and the change sql of the field addition and deletion can be completed only by replacing placeholders in the templates.
Second case: table data comparison.
The comparison of table data is slightly different from the table structure, one piece of data in the target database table is converted into a JSON format and stored in one field of one piece of data of the application database server, and besides, the data contains a database and a table to which the JSON format data belongs.
The data in the JSON format in the first feedback data is inversely sequenced into Map type data in java, the Map is mainly stored in a key-value form, such as { "id": "10001", "name": "Zhang Sang", "age": "25", "gender": "Man" }, 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 as to be compared.
The primary key field (the field that uniquely locates the piece of data), such as id, of the table is queried from the target database server, and then the intersection of the data is obtained in the same manner as the intersection of the table structure described above. The type of the sql of the data change is also divided into three types, namely, adding, deleting and modifying, the template sql is also correspondingly three 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 generated changes sql to a client browser through OutputStream, and popup downloading in the browser in the form of an attachment.
As a specific example, fig. 5 shows a structure of a client interface of a visual database change sentence generation method according to an embodiment of the present invention.
As shown in FIG. 5, the client list interface displays all the task lists completed by neutralization, can be used for recording new addition and editing, and pops up a new addition or editing interface after clicking, as shown in the following figure, through which the host address, port number, user name and password of the database can be input, and then the interface is connected to the database of the environment, so that the database and all the tables below the database can be selected in a linkage mode. The table needs to be checked and no change information of the table will not be detected without checking. A tick option may be located at the back of the table to indicate whether table data change detection is enabled. The structure of the table is checked for changes by default, but in some cases it will be necessary to synchronize the data as well, and then to check the item.
According to the visual database change statement generation method, the data queried by the application database and the data queried by the target database are compared, the difference items of the data queried by the application database and the data queried by the target database are obtained, the visualization processing is carried out, a developer does not need to care whether each action can influence the table structure or the data of the database, and all subsequent manual operations can be replaced only by carrying out the visualization operation respectively when the version starts and ends; modifying the table structure can also operate directly without writing SQL statements.
In addition, the embodiment of the invention also provides a computer readable storage medium, wherein the computer readable storage medium comprises a visual database change sentence generating program, and the visual database change sentence generating program realizes the following operations when being executed by a processor:
S110: according to the information of the target database server, requesting to access the target database server through a background interface, and writing the data needing 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, determining first feedback data corresponding to the request data by accessing an application database server through a background, 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: and determining a difference item of the first feedback data and the second feedback data by comparing the first feedback data and the second feedback data, and determining a change statement corresponding to the difference item according to the difference item.
The embodiment of the computer readable storage medium of the present invention is substantially the same as the embodiment of the visual database change sentence generating method and the electronic device, and will not be described herein.
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 one … …" does not exclude the presence of other like elements in a process, apparatus, article, or method that comprises the element.
The foregoing embodiment numbers of the present invention are merely for the purpose of description, and do not represent the advantages or disadvantages of the embodiments. From the above description of the embodiments, it will be clear to those skilled in the art that the above-described embodiment method may be implemented by means of software plus a necessary general hardware platform, but of course may also be implemented by means of hardware, but in many cases the former is a preferred embodiment. Based on such understanding, the technical solution of the present invention may be embodied essentially or in a part contributing to the prior art in the form of a software product stored in a storage medium (e.g. ROM/RAM, magnetic disk, optical disk) as described above, comprising instructions for causing a terminal device (which may be a mobile phone, a computer, a server, or a network device, etc.) to perform the method according to the embodiments of the present invention.
The foregoing description is only of the preferred embodiments of the present invention, and is not intended to limit the scope of the invention, but rather is intended to cover any equivalents of the structures or equivalent processes disclosed herein or in the alternative, which may be employed directly or indirectly in other related arts.
Claims (8)
1. A visual database change sentence generating method, applied to an electronic device, characterized in that the method comprises:
S110: according to the information of the target database server, requesting to access the target database server through a background interface, and writing the data needing 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 and the second feedback data, and determining a change statement corresponding to the difference item according to the difference item; 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 library table information under the target database server;
the target database server returns the database table information to the background, and the data corresponding to the database table information is fed back to a client interface for display through the background;
Requesting a background interface according to target database server information based on feedback data and data to be detected and updated submitted by a client interface, and substituting the target database server information and the data 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 to be detected and updated to the background according to the background request;
the background writes the structure and/or data information of the currently acquired table to an application database server while generating a commit record for viewing by the client list.
2. The method for generating a visual database modification sentence according to claim 1, wherein,
The background requests the target database server to acquire the database table information under the target database server; the target database server returns the database table information to the background, and the step of feeding back the data corresponding to the database table information to a client interface for displaying through the background comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all library table information under the target database server through a native sql statement;
and acquiring the structure and/or data information of all tables under the library table information corresponding to the target database server information, and feeding back to a client interface for display.
3. The method for generating a visual database modification sentence according to claim 1, wherein,
The submitting records comprise a record table, a data table and a field table which are created in the application database server; wherein,
The record list 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 to be detected and updated in a json array mode;
The field table is used for storing the field of the table structure and/or data information corresponding to the data to be detected and updated, and the data type, the length and the default value of the field.
4. The visual database modification sentence generation method according to claim 1, wherein step S120 further includes:
selecting request data information to be exported in the detected and updated data through a client interface, wherein the client requests a background interface and transmits in an ID corresponding to the request data;
The background accesses the application database server, determines first feedback data corresponding to the request data through the application database server, and meanwhile, queries the application database server for target database server information corresponding to the ID, and 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 target database server information as the second feedback data;
the target database server feeds back the table structure and/or data to the background.
5. An electronic device, comprising: the visual database change sentence generating program is executed by the processor and comprises the following steps:
S110: according to the information of the target database server, requesting to access the target database server through a background interface, and writing the data needing 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 and the second feedback data, and determining a change statement corresponding to the difference item according to the difference item;
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 library table information under the target database server;
the target database server returns the database table information to the background, and the data corresponding to the database table information is fed back to a client interface for display through the background;
Requesting a background interface according to target database server information based on feedback data and data to be detected and updated submitted by a client interface, and substituting the target database server information and the data 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 to be detected and updated to the background according to the background request;
the background writes the structure and/or data information of the currently acquired table to an application database server while generating a commit record for viewing by the client list.
6. The electronic device of claim 5, wherein the electronic device comprises a plurality of electronic devices,
The background requests the target database server to acquire the database table information under the target database server; the target database server returns the database table information to the background, and the step of feeding back the data corresponding to the database table information to a client interface for displaying through the background comprises the following steps:
connecting the target database server in a native Jdbc mode, and inquiring all library table information under the target database server through a native sql statement;
and acquiring the structure and/or data information of all tables under the library table information corresponding to the target database server information, and feeding back to a client interface for display.
7. The electronic device of claim 5, wherein the electronic device comprises a plurality of electronic devices,
The submitting records comprise a record table, a data table and a field table which are created in the application database server; wherein,
The record list 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 to be detected and updated in a json array mode;
The field table is used for storing the field of the table structure and/or data information corresponding to the data to be detected and updated, and the data type, the length and the default value of the field.
8. A computer-readable storage medium, wherein a visualized database change sentence generation program is included in the computer-readable storage medium, which when executed by a processor, implements the steps of the visualized database change sentence generation method according to any one of claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010033126.4A CN111274263B (en) | 2020-01-13 | 2020-01-13 | Visual database change sentence generation method, device and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010033126.4A CN111274263B (en) | 2020-01-13 | 2020-01-13 | Visual database change sentence generation method, device and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111274263A CN111274263A (en) | 2020-06-12 |
| CN111274263B true CN111274263B (en) | 2024-07-05 |
Family
ID=70996881
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010033126.4A Active CN111274263B (en) | 2020-01-13 | 2020-01-13 | Visual database change sentence generation method, device and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111274263B (en) |
Families Citing this family (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113111239B (en) * | 2021-04-08 | 2024-03-29 | 北京联创新天科技有限公司 | General database operation method, device and storage medium thereof |
| CN113239014A (en) * | 2021-05-18 | 2021-08-10 | 安翰科技(武汉)股份有限公司 | Management device and application system for maintaining SQL records |
| 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 |
| CN114328552A (en) * | 2021-12-28 | 2022-04-12 | 商派软件有限公司 | Database management method, system and computer readable medium |
| CN114896244B (en) * | 2022-04-20 | 2025-09-16 | 京东科技控股股份有限公司 | Method, apparatus, device and computer readable medium for configuring database tables |
| CN118377833B (en) * | 2024-06-19 | 2024-09-03 | 霖久智慧(广东)科技有限公司 | Database synchronization method, device, equipment and storage medium based on difference detection |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102929878A (en) * | 2011-08-09 | 2013-02-13 | 阿里巴巴集团控股有限公司 | Method and device for managing database changes |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7117225B2 (en) * | 2001-08-13 | 2006-10-03 | Jasmin Cosic | Universal data management interface |
| JP2016053919A (en) * | 2014-09-04 | 2016-04-14 | 東芝テック株式会社 | Server device and program |
| CN108200220B (en) * | 2018-04-08 | 2021-07-23 | 武汉斗鱼网络科技有限公司 | Data synchronization method, server and storage medium |
| CN109992589B (en) * | 2019-04-11 | 2020-04-10 | 北京启迪区块链科技发展有限公司 | Method, device, server and medium for generating SQL (structured query language) statements based on visual page |
-
2020
- 2020-01-13 CN CN202010033126.4A patent/CN111274263B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102929878A (en) * | 2011-08-09 | 2013-02-13 | 阿里巴巴集团控股有限公司 | Method and device for managing database changes |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111274263A (en) | 2020-06-12 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111274263B (en) | Visual database change sentence generation method, device and storage medium | |
| CN101611422B (en) | Web data usage platform | |
| US7996380B2 (en) | Method and apparatus for processing metadata | |
| US20220284060A1 (en) | Question Answering Method and Apparatus Based on Knowledge Graph | |
| US10235391B2 (en) | Method and apparatus for implementing dynamic database traversal | |
| CN107463693A (en) | A kind of data processing method, device, terminal and computer-readable recording medium | |
| WO2019153592A1 (en) | User authority data management device and method, and computer readable storage medium | |
| WO2019085474A1 (en) | Calculation engine implementing method, electronic device, and storage medium | |
| JP2005346717A (en) | Method, system and device for detecting and connecting data source | |
| JP2017511924A (en) | Temporary eventing system and method | |
| CN101292242A (en) | Manage relationships between resources stored in the warehouse | |
| CN111427577A (en) | Code processing method and device and server | |
| JP2011191862A (en) | File management apparatus, file management system, and file management program | |
| WO2019071898A1 (en) | Electronic device, database query script generation method and storage medium | |
| CN105843867A (en) | Metadata model-based search method and search device based on metadata model | |
| US20070185832A1 (en) | Managing tasks for multiple file types | |
| US8515960B2 (en) | Aggregating content from multiple content contributors | |
| CN112559913B (en) | Data processing method, device, computing equipment and readable storage medium | |
| US7895337B2 (en) | Systems and methods of generating a content aware interface | |
| US20070073770A1 (en) | Methods, systems, and computer program products for resource-to-resource metadata association | |
| CN116127098A (en) | Construction method and device of knowledge map | |
| WO2019080412A1 (en) | Data service method, electronic device and storage medium | |
| US11366796B2 (en) | Systems and methods for compressing keys in hierarchical data structures | |
| US10817272B2 (en) | Generation and usage of language-converted script | |
| CN112015466A (en) | Information retrieval method, information retrieval device, computer equipment 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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |