CN108733720B - On-line checking method and checking system for SQL (structured query language) query of multiple database types - Google Patents
On-line checking method and checking system for SQL (structured query language) query of multiple database types Download PDFInfo
- Publication number
- CN108733720B CN108733720B CN201710270100.XA CN201710270100A CN108733720B CN 108733720 B CN108733720 B CN 108733720B CN 201710270100 A CN201710270100 A CN 201710270100A CN 108733720 B CN108733720 B CN 108733720B
- Authority
- CN
- China
- Prior art keywords
- database
- query
- connection
- sql
- http
- 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
Images
Abstract
The invention discloses an online checking method and a checking system for SQL (structured query language) query of multiple database types, wherein the checking method comprises the following steps of: initiating an HTTP call request; screening according to the HTTP calling request to select a connection database, wherein the HTTP calling request comprises a database type, database connection information and an SQL statement; carrying out SQL query on the connected databases by using corresponding database drivers and returning a query result; and sending an HTTP response, and checking the query result. The invention can reduce the labor cost of database query, does not need to reestablish connection every query, provides convenience for direct query of an online system, and avoids the dependence of tools and the limitation of database types.
Description
Technical Field
The invention relates to the field of database Query, in particular to an online verification method and a verification system for SQL (Structured Query Language) Query of multiple database types.
Background
In conventional software testing, the results need to be verified by querying the database. The current database query adopts a query tool, connects to the database through a third-party client, and then queries the required result according to the database SQL query statement. Different third party tools are employed for different database queries to perform query processing separately for each type of database.
In this case, the database query requires a large number of database query statements to be written manually, and may require switching between different databases.
In addition, basically all the existing database queries are completed in the existing offline test environment, the query results of the database cannot be intuitively obtained through a front-end page in the online environment, and the database still needs to be directly operated in the background. Therefore, the existing database query does not support online verification, can only be completed in an online or test environment, and is difficult to be used with other web services in a unified way.
Therefore, the following disadvantages exist in the conventional database query: strong tool dependence; the query between different databases needs to be processed separately; the storage is complicated, and the dependence on data is strong; online verification cannot be performed; the interactivity with other systems is poor.
Disclosure of Invention
In view of this, embodiments of the present invention provide an online verification method and a related system for SQL query of multiple database types, which reduce labor cost for database query, avoid dependency of tools and limitation of database types, and enable online verification.
According to one aspect of the invention, an online verification method for SQL queries of multiple database types is provided. The method comprises the following steps: initiating an HTTP call request; screening according to the HTTP call request to select a connection database, wherein the HTTP (Hyper Text transfer Protocol) call request comprises a database type, database connection information and an SQL statement; carrying out SQL query on the connected databases by using corresponding database drivers and returning a query result; and sending an HTTP response, and checking the query result.
Preferably, the screening according to the information of the HTTP call request includes the steps of determining whether the corresponding database connection has been cached, and if so, directly returning to the already connected database; if not, connecting the database, if the connection is successful, putting the connection object into the cache and returning the connected database, and if the connection is unsuccessful, directly returning an HTTP response.
Preferably, the database connection information includes an IP address, a database connection port, a user name, and a password.
Preferably, the database driver comprises a MySQL database driver, an Oracle database driver, and a Sql Server database driver.
According to another aspect of the present invention, an online verification system for executing SQL queries of multiple database types is provided, which includes a WEB service layer, a data interface layer, and a database driver layer, wherein the WEB service layer provides a standard HTTP interface for receiving a call request, performs screening according to the HTTP call request to select a connection database, performs SQL queries on the connected databases using corresponding database drivers, and returns query results, wherein the call request includes a database type, database connection information, and SQL statements; the data interface layer is used for sending out query calls and carrying out uniform packaging processing on returned results; the database driver layer introduces each database driver of a third party to execute the SQL statement.
By adopting the online checking method and the checking system, the labor cost of database query can be reduced, connection does not need to be reestablished every query, convenience is provided for direct query of the online system, and the dependency of tools and the limitation of database types are avoided. The method can be flexibly applied to the interface test of the web application system, and a test program is not needed to write the connection and query codes of various databases. The query efficiency is improved, and the database data can be protected from being tampered and deleted at will.
According to still another aspect of the present invention, there is provided an electronic device terminal including: one or more processors; a storage device for storing one or more programs that, when executed by the one or more processors, cause the one or more processors to implement an online verification method for SQL queries of multiple database types provided in accordance with the invention.
According to yet another aspect of the present invention, there is provided a computer readable medium having stored thereon a computer program which, when executed by a processor, implements the method for online verification of SQL queries of multiple database types provided by the present invention.
Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.
Drawings
FIG. 1 is an architecture diagram of an online verification system for SQL queries of multiple database types to which the present invention relates;
FIG. 2 is a logical flow diagram of a method for online verification of SQL queries of multiple database types to which the present invention relates;
FIG. 3 is a step diagram of a method for online verification of SQL queries of multiple database types to which the present invention relates.
Fig. 4 is a schematic structural diagram of a computer system suitable for implementing an electronic device terminal according to an embodiment of the present application.
Detailed Description
The invention is described in further detail below with reference to the figures and the examples, but without limiting the invention.
As shown in FIG. 1, FIG. 1 shows a system architecture diagram for performing SQL queries and checks. In fig. 1, a 3-layer architecture is shown, including a Web services layer, a data interface layer, and a database driver layer, wherein the Web services layer is used to provide standard HTTP requests; the data interface layer is used for sending out query calls and uniformly packaging returned results, wherein the call is initiated to be a direct call, namely, an initialized database connection object is called for query, and the data processing is to package the query results and return the results for normally executed SQL queries; and the driving layer of each database is used for introducing each database driver of a third party, and executing the SQL statement so as to use the database of the third party, such as the database driver of the MySQL, and load the SQL statement. Preferably, the database driver comprises MySQL database driver, Oracle database driver and Sql Server database driver.
How to perform online verification of SQL query on multiple database types is described in detail below, and the verification method is shown in fig. 3, and specifically includes the following steps:
s301: and initiating an HTTP call request, wherein the HTTP call request comprises 3 parameters such as a database type, database connection information, SQL statements and the like. The database type is used for determining the type of the database, a preset fixed list mode is specifically adopted, and the method needs to be prepared in advance according to calling requirements; the database connection information is information for connecting database generation, and may be, for example, an IP address, database connection port information, a user name and a password, and the like. The SQL statement refers to the SQL statement needing to be executed.
S302: after receiving the HTTP call request, screening according to information of the HTTP call request transmitted from the front end, specifically, screening according to a type of the database and connection information of the database, and delivering the connection information of the database to a data interface layer of a lower layer after the screening is completed, so as to perform query operations on each database. By making the database query into web service, the uncertainty and the dependency of the database query are reduced, the database query can be used for an online system to the maximum extent, and in addition, switching among different types of databases is considered, and convenience is provided for simultaneously querying different databases.
Fig. 2 shows a process of filtering information of an HTTP call request, where the process includes the following steps, S201: judging whether the corresponding database connection is cached or not, if not, entering the step S202, and if so, directly returning the connected database object; s202: connecting a database; s203; judging whether the database connection is successful, if so, entering the step S204, and if not, directly returning an HTTP response for prompting the information of connection failure; s204: the join object is placed in a cache and the database objects that have been joined are returned.
Step S303: the SQL query is carried out on the connected database objects by using the database driver, and the query result is returned, wherein the returned result is as follows:
that is, the number of rows and specific values will be returned for use in verifying data and results with the requestor. All different database SQL query requests are returned in the same structured format.
Step S304: sending an HTTP response, and checking returned query results including line number and specific numerical value with a requester under the condition that SQL is normally executed; in the case of an SQL execution exception, the requestor needs to adjust according to the exception.
For example: and returning SQL error state under the condition of SQL execution exception:
{
Status:‘fail’，
message is abnormal content
}
Referring now to FIG. 4, shown is a block diagram of a computer system 400 suitable for use in implementing an electronic device terminal of an embodiment of the present application. The electronic device terminal shown in fig. 4 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present application.
As shown in fig. 4, the computer system 400 includes a Central Processing Unit (CPU)401 that can perform various appropriate actions and processes in accordance with a program stored in a Read Only Memory (ROM)402 or a program loaded from a storage section 408 into a Random Access Memory (RAM) 403. In the RAM 403, various programs and data necessary for the operation of the system 400 are also stored. The CPU 401, ROM 402, and RAM 403 are connected to each other via a bus 404. An input/output (I/O) interface 405 is also connected to bus 404.
The following components are connected to the I/O interface 405: an input section 406 including a keyboard, a mouse, and the like; an output section 407 including a display device such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage section 408 including a hard disk and the like; and a communication section 409 including a network interface card such as a LAN card, a modem, or the like. The communication section 409 performs communication processing via a network such as the internet. A driver 410 is also connected to the I/O interface 405 as needed. A removable medium 411 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 410 as necessary, so that a computer program read out therefrom is mounted into the storage section 408 as necessary.
In particular, according to the embodiments of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 409, and/or installed from the removable medium 411. The above-described functions defined in the system of the present application are executed when the computer program is executed by a Central Processing Unit (CPU) 401.
It should be noted that the computer readable medium shown in the present application may be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present application, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In this application, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.
The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present application. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The modules described in the embodiments of the present application may be implemented by software or hardware. The described modules may also be provided in a processor, which may be described as: a processor includes a sending module, an obtaining module, a determining module, and a first processing module. The names of these modules do not form a limitation on the modules themselves in some cases, and for example, the sending module may also be described as a "module sending a picture acquisition request to a connected server".
As another aspect, the present application also provides a computer-readable medium, which may be contained in the electronic device terminal described in the above embodiments; or may exist separately without being assembled into the electronic device terminal. The computer readable medium carries one or more programs which, when executed by the electronic device terminal, cause the electronic device terminal to include: initiating an HTTP call request; screening according to the HTTP call request to select a connection database, wherein the HTTP (Hyper Text transfer Protocol) call request comprises a database type, database connection information and an SQL statement; carrying out SQL query on the connected databases by using corresponding database drivers and returning a query result; and sending an HTTP response, and checking the query result.
According to the technical scheme of the embodiment of the invention, the labor cost of database query can be reduced, connection does not need to be reestablished every query, convenience is provided for direct query of an online system, and the dependency of tools and the limitation of database types are avoided. The method can be flexibly applied to the interface test of the web application system, and a test program is not needed to write the connection and query codes of various databases. The query efficiency is improved, and the database data can be protected from being tampered and deleted at will.
The product can execute the method provided by the embodiment of the invention, and has corresponding functional modules and beneficial effects of the execution method. For technical details that are not described in detail in this embodiment, reference may be made to the method provided by the embodiment of the present invention.
Although the embodiments of the present invention have been described in detail with reference to the accompanying drawings, the embodiments are only for explaining and illustrating the present invention and are not intended to limit the present invention. The scope of the invention is defined by the claims, in which changes, substitutions and the like of certain elements are apparent.
Claims (8)
1. An online verification method for SQL queries of multiple database types is characterized by comprising the following steps:
initiating an HTTP calling request, wherein the HTTP calling request comprises a database type, database connection information and an SQL statement;
screening according to the HTTP calling request to select a connection database;
SQL query is carried out on the connected databases by utilizing corresponding database drivers and query results are returned, wherein the structural formats of the query results of all the databases are the same and comprise line numbers and numerical values;
sending an HTTP response, and checking the query result;
the screening according to the information of the HTTP calling request comprises the following steps: judging whether the corresponding database connection is cached or not, and if so, directly returning to the connected database; if not, connecting the database, if the connection is successful, putting the connection object into the cache and returning the connected database, and if the connection is unsuccessful, directly returning an HTTP response.
2. An online verification method according to claim 1, wherein the database connectivity information comprises at least one of: IP address, database connection port, username, and password.
3. An online verification method according to claim 1, wherein the database driver comprises at least one of: MySQL database driver, Oracle database driver, Sql Server database driver.
4. An online verification system for performing SQL queries of multiple database types is characterized by comprising a WEB service layer, a data interface layer and a database driving layer, wherein,
the WEB service layer provides a standard HTTP interface for receiving a call request, wherein the call request comprises a database type, database connection information and SQL statements, the HTTP call request is screened to select a connection database, the connected databases are subjected to SQL query by using corresponding database drivers, and query results are returned, wherein the query results of all the databases have the same structural format and comprise line numbers and numerical values;
the data interface layer is used for sending out query calls and carrying out uniform packaging processing on returned results;
the database driver layer introduces each database driver of a third party to execute SQL statements;
the screening according to the information of the HTTP calling request comprises the following steps: judging whether the corresponding database connection is cached or not, and if so, directly returning to the connected database; if not, connecting the database, if the connection is successful, putting the connection object into the cache and returning the connected database, and if the connection is unsuccessful, directly returning an HTTP response.
5. The online verification system of claim 4, wherein the database connectivity information comprises at least one of: IP address, database connection port, username, and password.
6. The online verification system of claim 4, wherein the database driver comprises at least one of: MySQL database driver, Oracle database driver, Sql Server database driver.
7. An electronic device terminal, comprising:
one or more processors;
a storage device for storing one or more programs,
when executed by the one or more processors, cause the one or more processors to implement the method of any one of claims 1-3.
8. A computer-readable medium, on which a computer program is stored, which, when being executed by a processor, carries out the method according to any one of claims 1-3.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710270100.XA CN108733720B (en) | 2017-04-24 | 2017-04-24 | On-line checking method and checking system for SQL (structured query language) query of multiple database types |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710270100.XA CN108733720B (en) | 2017-04-24 | 2017-04-24 | On-line checking method and checking system for SQL (structured query language) query of multiple database types |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108733720A CN108733720A (en) | 2018-11-02 |
| CN108733720B true CN108733720B (en) | 2021-02-26 |
Family
ID=63934304
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710270100.XA Active CN108733720B (en) | 2017-04-24 | 2017-04-24 | On-line checking method and checking system for SQL (structured query language) query of multiple database types |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108733720B (en) |
Families Citing this family (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109828983A (en) * | 2018-12-15 | 2019-05-31 | 平安科技（深圳）有限公司 | PG data base processing method, device, electronic equipment and storage medium |
| CN111506553B (en) * | 2019-01-31 | 2023-07-04 | 阿里巴巴集团控股有限公司 | Function setting method and device for database |
| CN110471665A (en) * | 2019-06-28 | 2019-11-19 | 平安科技（深圳）有限公司 | Script file method of calibration, device, server and storage medium |
| CN110716960B (en) * | 2019-10-22 | 2022-07-15 | 北京锐安科技有限公司 | Database query method, device, equipment and storage medium |
| CN111367936B (en) * | 2020-02-28 | 2023-08-22 | 中国工商银行股份有限公司 | Offline verification method and device for structured query language grammar |
| CN112818051A (en) * | 2021-02-02 | 2021-05-18 | 上海达梦数据库有限公司 | Heterogeneous library communication method, terminal device and storage medium |
| CN113190607B (en) * | 2021-05-21 | 2024-04-16 | 上海申铁信息工程有限公司 | HTTP request-based database load balancing method, device and medium |
Family Cites Families (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101645074A (en) * | 2009-09-07 | 2010-02-10 | 浪潮集团山东通用软件有限公司 | Method for accessing relational databases of different types |
| CN101651707B (en) * | 2009-09-22 | 2012-05-23 | 西安交通大学 | Method for automatically acquiring user behavior log of network |
| CN102722575A (en) * | 2012-06-05 | 2012-10-10 | 河北世窗信息技术有限公司 | System and method for managing multi-type database connection pool |
| US20140280334A1 (en) * | 2013-03-12 | 2014-09-18 | Atomic Tower, Inc. | Reporting and analytics tool integrated with cloud service applications |
| CN103530564B (en) * | 2013-09-24 | 2016-04-13 | 国家电网公司 | A kind of SQL injection loophole test and validation method and system |
| CN104573115B (en) * | 2015-02-04 | 2019-03-22 | 北京慧辰资道资讯股份有限公司 | Support the realization method and system of the integrated interface of multi-type database operation |
| CN105389342A (en) * | 2015-10-23 | 2016-03-09 | 浪潮软件股份有限公司 | Database access tool, system and method |
| CN105975489A (en) * | 2016-04-26 | 2016-09-28 | 中国电子科技网络信息安全有限公司 | Metadata-based online SQL code completion method |
-
2017
- 2017-04-24 CN CN201710270100.XA patent/CN108733720B/en active Active
Also Published As
| Publication number | Publication date |
|---|---|
| CN108733720A (en) | 2018-11-02 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108733720B (en) | On-line checking method and checking system for SQL (structured query language) query of multiple database types | |
| US10742636B2 (en) | OAuth2 SAML token service | |
| WO2019015272A1 (en) | Information processing method and device | |
| CN111277639A (en) | Method and device for maintaining data consistency | |
| CN110874227A (en) | Shunting method and system for realizing gray scale release of API gateway and electronic equipment | |
| US11263542B2 (en) | Technologies for auto discover and connect to a rest interface | |
| CN110881224B (en) | Network long connection method, device, equipment and storage medium | |
| CN112948138A (en) | Method and device for processing message | |
| CN111414154A (en) | Method and device for front-end development, electronic equipment and storage medium | |
| CN111338928A (en) | Chrome-based browser testing method and device | |
| CN113127335A (en) | System testing method and device | |
| CN115858905A (en) | Data processing method and device, electronic equipment and storage medium | |
| US10681144B1 (en) | Method and apparatus for configuring a data source name (DSN) for use during a data source access | |
| CN114625458A (en) | Page data processing method and device, electronic equipment and storage medium | |
| CN112688982B (en) | User request processing method and device | |
| CN114356713A (en) | Thread pool monitoring method and device, electronic equipment and storage medium | |
| CN112783903B (en) | Method and device for generating update log | |
| CN109446462B (en) | Page-based data monitoring processing method, device, equipment and storage medium | |
| CN110554942A (en) | method and device for monitoring code execution | |
| CN110262756B (en) | Method and device for caching data | |
| CN113688152B (en) | System function self-checking method and device | |
| CN111125676B (en) | Joint authorization method and device | |
| CN113779078A (en) | Cache data acquisition method and device | |
| CN113934548A (en) | Method and device for establishing connection with dependent service | |
| CN113778847A (en) | Test report generation method and device |
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 |