CN104239508A - Data query method and data query device - Google Patents
Data query method and data query device Download PDFInfo
- Publication number
- CN104239508A CN104239508A CN201410466552.1A CN201410466552A CN104239508A CN 104239508 A CN104239508 A CN 104239508A CN 201410466552 A CN201410466552 A CN 201410466552A CN 104239508 A CN104239508 A CN 104239508A
- Authority
- CN
- China
- Prior art keywords
- data storage
- query
- sql statement
- data
- query result
- 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.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24553—Query execution of query operations
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Abstract
The invention provides a data query method and a data query device, wherein the method comprises the following steps that when variable names carried in structured query language (SQL) statements are received, query condition values which are inputted by a user according to the variable names are obtained; the variable names and the query condition values are bound; the data types of query results outputted by the execution of the SQL statements are prejudged; according to the prejudged data types, data storage variable arrays are assigned to the query results outputted by the execution of the SQL statements; the SQL statements are executed, the query results are stored into the assigned data storage variable arrays, and the query results stored in the data storage variable arrays are provided for an application. The invention solves the technical problem that under the condition of uncertain query conditions, the SQL method of binding variables cannot be adopted to query data, which causes the low security of data query and the low data processing efficiency of a system, and therefore the technical effect of effectively increasing data query security and data processing efficiency is achieved.
Description
Technical field
The present invention relates to technical field of data processing, particularly a kind of data enquire method and device.
Background technology
Oracle database is one of database of current main-stream, and at present, many enterprises adopt oracle database to carry out the storage of business data.In the process using application software, frequent needs carry out the inquiry of flexible condition, such as: an inquiry may exist multinomial querying condition, but and the querying condition of not all is all necessary, user often only needs some of querying condition just can meet search request when in use.Therefore, need to inquire about flexibly, with the dimension and the granularity that allow user flexibility to hold inquiry, thus the more convenient result inquired rapidly needed for user.
For above-mentioned Flexible Query, usually can directly realize in JAVA, but for the consideration designed system layer, Browser/Server Mode (Browser/Server, BS) usually the mode of storing process is adopted to realize by the access of database in system, that is, all data base logics all realize in storing process, and JAVA program is a Calling Stored Procedure, transmission input parameter, acquisition result in whole process.
But, in the process that reality uses, proceduring SQL (the Procedural Language/SQL of the storing process use of oracle database, PL/SQL) local dynamic, structured query language (the Structured Query Language in language, SQL) when querying condition uses binding variable, the querying condition fixed can only be supported.If querying condition is not fixed, but need to use SQL, so the mode of unbundling variable is used to inquire about with regard to needing, but, there is following two problems in the SQL query mode of unbundling variable: 1) can cause a large amount of hard parsings because of sharing SQL, cause system overhead to increase, treatment effeciency declines; 2) owing to directly variate-value being write in SQL statement text in unbundling variable mode, there is SQL and introduce problem, easily occur security risk.
Therefore, in the unfixed situation of querying condition, how to use the mode of binding variable to carry out SQL data query and seem particularly important.
Summary of the invention
Embodiments provide a kind of data enquire method, in the unfixed situation of querying condition, adopt the SQL mode of binding variable to carry out the object of data query to reach, wherein, the method comprises:
When carrying name variable in the Structured Query Language (SQL) SQL statement received, obtain the query condition value that user inputs according to described name variable;
Described name variable and described query condition value are bound;
SQL statement described in anticipation performs the data type of the Query Result exported;
Data storage of variables array is distributed according to the Query Result that the data type of anticipation is described SQL statement execution output;
Perform described SQL statement, described SQL statement is performed the Query Result exported and is stored in the data storage of variables array of distribution;
The Query Result stored in described data storage of variables array is supplied to application program.
In one embodiment, be that described SQL statement performs the Query Result distribution data storage of variables array exported according to the data type of anticipation, comprise:
When the Query Result that described SQL statement performs output comprises multiple output field, determining the data type of each output field in described multiple output field, is the element that each field distributes in corresponding data storage of variables array respectively according to established data type.
Call predetermined bound functions, the element in output field and data storage of variables array is carried out man-to-man binding.
In one embodiment, perform described SQL statement, described SQL statement performed the Query Result exported and is stored in the data storage of variables array of distribution, comprising:
For described SQL statement distributes vernier;
According to the vernier distributed, described SQL statement is performed the Query Result exported and be stored in one by one in element corresponding to the data storage of variables array of distribution.
In one embodiment, after the Query Result stored in described data storage of variables array is supplied to application program, described method also comprises:
Be released to the vernier that described SQL statement is distributed, and the resource shared by described data storage of variables array.
In one embodiment, described SQL statement is performed the Query Result exported and is stored in the data storage of variables array of distribution, comprising:
Described Article 1 record, on the Article 1 record of described Query Result, is stored in described data storage of variables array by setting vernier;
Moving cursor, on next record, determines whether there is record;
If there is record, then current record is stored in element corresponding to described data storage of variables array, until vernier moves to the last item record.
In one embodiment, the name variable carried in described SQL statement, and the query condition value of user's input is variable.
The embodiment of the present invention additionally provides a kind of data query arrangement, and in the unfixed situation of querying condition, adopt the SQL mode of binding variable to carry out the object of data query to reach, wherein, this device comprises:
Acquisition module, during for carrying name variable in the Structured Query Language (SQL) SQL statement received, obtains the query condition value that user inputs according to described name variable;
Binding module, for binding described name variable and described query condition value;
Distribution module, performs the data type of the Query Result exported for SQL statement described in anticipation, and is that described SQL statement performs the Query Result distribution data storage of variables array exported according to the data type of anticipation;
Execution module, for performing described SQL statement, performing described SQL statement the Query Result exported and being stored in the data storage of variables array of distribution;
There is provided module, for the Query Result stored in described data storage of variables array is supplied to application program.
In one embodiment, described distribution module comprises:
Anticipation unit, performs the data type of the Query Result exported for SQL statement described in anticipation;
Elemental partition unit, for according to described anticipation unit established data type being the element that each field distributes in corresponding data storage of variables array respectively;
Binding unit, for after according to established data type being the element that distributes respectively in corresponding data storage of variables array of each field, calling predetermined bound functions, the element in output field and data storage of variables array is carried out man-to-man binding.
In one embodiment, described execution module comprises:
Vernier allocation units, for distributing vernier for described SQL statement;
Storage unit, for according to the vernier distributed, performs the Query Result exported and is stored in one by one in element corresponding to the data storage of variables array of distribution by described SQL statement.
In one embodiment, said apparatus also comprises:
Release module, after the Query Result stored in described data storage of variables array being supplied to application program in described providing unit, is released to the vernier that described SQL statement is distributed, and the resource shared by described data storage of variables array.
In one embodiment, module is provided to comprise described in:
Setup unit, for setting the Article 1 record of vernier at described Query Result, is stored to described Article 1 record in described data storage of variables array;
Mobile unit, moving cursor, on next record, determines whether there is record;
Storage unit, for determining to deposit in case of recording, is being stored in element corresponding to described data storage of variables array, until vernier moves to the last item record by current record.
In embodiments of the present invention, in the above-described embodiments, when carrying name variable in the SQL statement determining to receive, obtain the query condition value that user inputs according to this name variable, both are bound, then anticipation SQL statement performs the data type of the Query Result exported, be that Output rusults distributes data storage of variables array according to this data type, final execution SQL statement, the Query Result of output is stored in the data storage of variables array of distribution, to complete whole data query process.By the way, first variable and variate-value are bound, the data type of the Query Result exported after then determining to perform SQL statement, so that for it distributes suitable storage space, thus achieve and the SQL mode of binding variable also can be adopted to carry out data query in the unfixed situation of querying condition, to solve in prior art in the uncertain situation of querying condition, the SQL mode of binding variable cannot be adopted to carry out data query and the not high technical matters of the security of data query that causes, reach the technique effect effectively improving data query security.
Accompanying drawing explanation
Accompanying drawing described herein is used to provide a further understanding of the present invention, forms a application's part, does not form limitation of the invention.In the accompanying drawings:
Fig. 1 is the method flow diagram of the data enquire method of the embodiment of the present invention;
Fig. 2 is the structured flowchart of the data query arrangement based on oracle database of the embodiment of the present invention;
Fig. 3 is the structured flowchart of the query unit of the embodiment of the present invention;
Fig. 4 is the structured flowchart of the data processing unit of the application program end of the embodiment of the present invention;
Fig. 5 is the method flow diagram of the data enquire method based on oracle database of the embodiment of the present invention;
Fig. 6 is the structured flowchart of the data query arrangement of the embodiment of the present invention.
Embodiment
For making the object, technical solutions and advantages of the present invention clearly understand, below in conjunction with embodiment and accompanying drawing, the present invention is described in further details.At this, exemplary embodiment of the present invention and illustrating for explaining the present invention, but not as a limitation of the invention.
Inventor considers in prior art, and the inquiry for indefinite querying condition exists the not high technical matters of security.But the generation of this problem is mainly because can only adopt unbundling variable mode to indefinite querying condition at present.To this, propose a kind of data enquire method in this example to solve the problem, as shown in Figure 1, the method comprises the following steps:
Step 101: when carrying name variable in the Structured Query Language (SQL) SQL statement received, obtains the query condition value that user inputs according to described name variable;
Step 102: described name variable and described query condition value are bound;
Step 103: SQL statement described in anticipation performs the data type of the Query Result exported;
Step 104: distribute data storage of variables array according to the Query Result that the data type of anticipation is described SQL statement execution output;
Step 105: perform described SQL statement, performs described SQL statement the Query Result exported and is stored in the data storage of variables array of distribution;
Step 106: the Query Result stored in described data storage of variables array is supplied to application program.
In the above-described embodiments, when carrying name variable in the SQL statement determining to receive, obtain the query condition value that user inputs according to this name variable, both are bound, then anticipation SQL statement performs the data type of the Query Result exported, be that Output rusults distributes data storage of variables array according to this data type, final execution SQL statement, the Query Result of output is stored in the data storage of variables array of distribution, and the Query Result be stored in data storage of variables array is supplied to application program, to complete whole data query process.By the way, first variable and variate-value are bound, the data type of the Query Result exported after then determining to perform SQL statement, so that for it distributes suitable storage space, thus achieve and the SQL mode of binding variable also can be adopted to carry out data query in the unfixed situation of querying condition, to solve in prior art in the uncertain situation of querying condition, the SQL mode of binding variable cannot be adopted to carry out data query and the not high technical matters of the security of data query that causes, reach the technique effect effectively improving data query security.
In the process implemented, when performing above-mentioned steps 101 and obtaining the name variable in SQL statement, first can judge whether SQL carries variable, the process of carrying out follow-up binding is only just needed when SQL statement has variable, otherwise do not need to carry out follow-up binding procedure, in one embodiment, the name variable in SQL statement is obtained in the following manner: receive described SQL statement; Judge whether carry variable in described SQL statement; If had, then obtain the name variable in described SQL statement.By the way, can effectively avoid unnecessary operational processes, save query time.
Distributing in the process of data storage of variables array for Output rusults, comprise multiple field in data storage of variables array or comprise multiple element, often kind of data type has the field corresponding with it, a record in the corresponding array of each output field, therefore can be tied to field in corresponding field value according to the type of output field.
Concrete, after distribution data storage of variables array, also need to bind the element in output field and data storage of variables array, suitable DBMS_SQL.DEFINE_COLUMN method can be called according to the type of data output field and data storage of variables array are bound, if there is multiple field, repeatedly bind.Namely, be each output field in described multiple output field, after distributing the element in a data storage of variables array respectively, described method also comprises: call predetermined binding method, and the element in output field and data storage of variables array is carried out man-to-man binding.
In the process that reality performs, in order to Query Result being stored in data storage of variables array, also need for it distributes vernier, the storage of Query Result is realized by vernier, concrete, perform described SQL statement, described SQL statement is performed the Query Result exported and is stored in the data storage of variables array of distribution, can comprise: for described SQL statement distributes vernier; According to the vernier distributed, described SQL statement is performed the Query Result exported and be stored in one by one in element corresponding to the data storage of variables array of distribution.
Because for Query Result is assigned with array and vernier, these resources just do not have existent value after Query Result is supplied to application program, therefore, in order to saving resource, after the Query Result stored in described data storage of variables array is supplied to application program, can also comprise: be released to the vernier that described SQL statement is distributed, with the resource shared by described data storage of variables array, thus make this part resource may be used for other place, to realize effective utilization of resource, avoid irrational taking.
Being stored in performing the Query Result exported after described SQL statement in the process in the data storage of variables array of distribution, can be adopt the mode itemize of moving cursor to store, specifically: perform described SQL statement and obtain Query Result; Described Article 1 record, on the Article 1 record of Query Result, is stored in described data storage of variables array by setting vernier; Moving cursor is on next record, and moving cursor, on next record, determines whether there is record; If there is record, then current record is stored in element corresponding to described data storage of variables array, until vernier moves to the last item record.But it should be noted that, if consider disposable all fields all to be taken out, then storage resources may be caused not enough owing to recording too many, the storage of data can be realized by the combination of the data processing unit of the data processing unit of database side and application program end in the process realized, application program is only had to initiate request, wish to obtain data, just can provide a record to application program, and not together all records are all supplied to application program.
In each embodiment above-mentioned, the query condition value of the name variable carried in SQL statement and user's input is variable, that is, can input according to demand and select.
Below in conjunction with a specific embodiment, above-mentioned data enquire method is specifically described, but it should be noted that this specific embodiment is only to better the present invention is described, do not form inappropriate limitation of the present invention.
In this example, provide a kind of data query arrangement based on oracle database, can based on oracle database carry out safety flexibly, support indefinite querying condition, the data query of binding variable form.As shown in Figure 2, this device comprises: the data processing unit 05 of SQL receiving element 01, variable receiving element 02, query unit 03, data outputting unit 04, application program end, the inquiry closing unit 06 of application program end, be specifically described unit in this structure below:
1) SQL receiving element 01, for receiving SQL statement (this SQL statement can be character string) from application memory process or application program, namely, usually name variable is comprised in SQL statement, by application memory invocation of procedure SQL receiving element 01, also can call SQL receiving element 01 by application program (such as JAVA program).SQL receiving element 01 is deployed in Oracle database service device, and this Oracle database service device can be the server of the operating systems such as Windows, Unix, Linux.
2) variable receiving element 02, for receiving variate-value (namely from application memory process or application program, the query condition value of user's input), quantity for variate-value can be any number of, as long as be no more than the restriction of Oracle system itself, can by call stored procedure SQL receiving element 02, also can by application call SQL receiving element 02.Variable receiving element 02 is also deployed in Oracle database service device.
This unit comprises the method for reseptance that a variable array Variable_Array and group supports the variable of various data type, and wherein, the structure of variable array VariableArray can be as shown in table 1:
Table 1
Wherein, a record in each variable (name variable) corresponding array, is kept at the variate-value of variable in corresponding variate-value field according to data type of a variable.
Method of reseptance for above-mentioned variable mainly contains following several:
SetVariableNumber(VariableName?VARCHAR2,VariableValue?NUMBER)
SetVariableVarchar2(VariableName?VARCHAR2,VariableValue?VARCHAR2)
SetVariableDate(VariableName?VARCHAR2,VariableValue?DATE)
SetVariableRowid(VariableName?VARCHAR2,VariableValue?ROWID)
3) query unit 03, for calling Oracle function to resolve SQL, name variable in the variate-value of reception and SQL statement is bound, and obtain the output field of SQL statement, then the data type of output field is automatically identified, the data storage of variables of output field and respective type is bound, then performs SQL statement, and return vernier ID.As shown in Figure 3, query unit 03 comprises SQL parsing subelement 0301, Variable-Bindings subelement 0302, exports data definition subelement 0303, performs SQL subelement 0304.Query unit 03 also can be deployed in Oracle database service device, wherein:
SQL resolves subelement 0301, and open vernier for calling Oracle function and resolve SQL statement, this subelement mainly calls DBMS_SQL.OPEN_CURSOR method and DBMS_SQL.PARSE method, finally returns vernier ID;
Variable-Bindings subelement 0302, binds the name variable in the variate-value of reception and SQL statement for calling Oracle function, if there is multiple variable just to bind repeatedly; This binding procedure mainly for each record in variable array Variable_Array, that is, according to data type of a variable, calls suitable DBMS_SQL.BIND_VARIABLE method and is bound by the name variable in variate-value and SQL statement;
Export data definition subelement 0303, for calling Oracle function to obtain the information of all output fields, the mainly data type of output field, then according to the data type of each output field determined, the element in the data storage of variables array of output field and respective type is bound, if multiple output field just binding is repeatedly.This subelement mainly calls DBMS_SQL.DESCRIBE_COLUMNS to obtain field name, the information such as data type and field length of output field, and for the data type of each field, the element in itself and data storage of variables array Column_Array is bound, the structure of data storage of variables array can be as shown in table 2:
Table 2
Wherein, a record in the corresponding array of each output field, output field is tied in corresponding field value by the data type according to output field.
Perform SQL subelement 0304, perform SQL statement for calling Oracle function, this unit is mainly used in externally providing EXECUTE method, can realize by calling DBMS_SQL.EXECUTE method.
4) data outputting unit 04 of database side, for moving cursor, taken out by Query Result a record, each output field in this record deposited to the data storage of variables of its binding, this record passes to the data processing unit 05 of application program end the most at last.The data outputting unit 04 of database side is also deployed in Oracle database service device.This unit may be used for outwards providing FETCH_ROWS method, mainly vernier can be specified to next record by calling DBMS_SQL.FETCH_ROWS to move, and for each output field, DBMS_SQL.COLUMN_VALUE method unpack field value is called according to the field type of its correspondence, be saved in the respective field of respective record in data storage of variables array Column_Array, data storage of variables array Column_Array passes to the data processing unit 05 of application program end the most at last.
5) data processing unit 05 of application program end, for taking out Query Result data from the data outputting unit 04 of database side, then passes to JAVA application program.As shown in Figure 4, the data processing unit 05 of application program end comprises reading database data sub-element 0501, data spread out of subelement 0502.The data processing unit 05 of application program end is disposed on the application server, wherein:
Reading database data sub-element 0501, for reading the data processing unit 05 of a Query Result to application program end from oracle database, this subelement mainly with the FETCH_ROWS method of the data outputting unit 04 of FETCH_ROWS method call database side, and will record data taking-up from data storage of variables array Column_Array;
Data spread out of subelement 0502, for the output field data of current record in query structure are passed to JAVA application program, this subelement mainly provides one group of data removing method for JAVA application call, and the data removing method provided mainly contains following several:
getString
getInt
getFloat
getDate。
6) the inquiry closing unit 06 of application program end, for closing the database cursor that query unit 03 is opened, releasing resource, the inquiry closing unit 06 of application program end is disposed on the application server, this subelement is present in JAVA class OracleFlexibleQuery, be mainly used in calling DBMS_SQL.CLOSE_CURSOR by query unit 03 and close appointment vernier, above-mentioned application program can be JAVA program etc.
Based on above-mentioned data query arrangement, additionally provide a kind of data enquire method based on oracle database in this example, as shown in Figure 5, comprise the following steps:
Step 100:SQL receiving element 01 receives the SQL statement of application memory process or application passes, the Oracle storing process of JAVA application call application system itself, the query condition value that user inputs is passed to application memory process, application memory process generates the SQL statement character string including name variable according to querying condition, this character string is passed to SQL receiving element 01 and processes by the SetSQL method then calling SQL receiving element 01.In the concrete process implemented, also directly SQL statement character string can be sent to SQL receiving element 01 by JAVA application program.Application memory process need follow certain standard, namely needs the vernier ID obtained in subsequent step to return to JAVA application program.
Step 200: judge whether have variable in SQL statement, if had, then variable receiving element 02 receives name variable needed for the SQL statement of application memory process or application passes and variate-value (i.e. the querying condition of user's input).In this process, variable receiving element 02 can provide the variable transferring method (such as SetVariableNumber, SetVariableVarchar2 etc.) of numerous types of data so that the data of various data type can be transmitted.If there is multiple variable to need to spread out of, so just repeatedly transmit.In the concrete process implemented, also directly directly variable can be passed to variable receiving element 02 by JAVA application program and be stored in variable array VariableArray.
Step 300: the query statement that query unit 03 receives application memory process or application program is inquired about, can send by application memory process the EXECUTE method that query statement calls query unit 03 to inquire about, also directly can send query statement by JAVA application program.Concrete, EXECUTE method can comprise the following steps:
Sub-step 301: first the SQL parsing subelement 0301 of query unit 03 calls DBMS_SQL.OPEN_CURSOR method and open database cursor, then calls DBMS_SQL.PARSE method and resolves SQL, if SQL statement is wrong, then return miscue.
Sub-step 302: if having variable in SQL statement, then the Variable-Bindings subelement 0302 of query unit 03 calls suitable DBMS_SQL.bind_variable method according to data type of a variable, name variable in the variate-value of reception and SQL statement is bound, if there is multiple variable, repeatedly bind repeatedly, if binding procedure is made mistakes, then return miscue.
Sub-step 303: the output data definition subelement 0303 of query unit 03 call DBMS_SQL.describe_columns method obtain all inquiries record a demerit in the information of each output field, the mainly data type of each output field.
Sub-step 304: the output data definition subelement 0303 of query unit 03 calls suitable DBMS_SQL.DEFINE_COLUMN method according to data type and the data storage of variables of output field and respective type bound, if there is multiple output field, then carry out repeatedly bindings, become a shareholder and to make mistakes in binding procedure, then return miscue.
Sub-step 305: query unit 03 performs SQL subelement 0304 and calls DBMS_SQL.EXECUTE execution SQL statement, if made mistakes, then returns miscue.
Step 400: the data processing unit 05 of application program end receives the instruction of JAVA application program, vernier is moved to next record, mainly comprises the following steps:
Sub-step 401: the reading database data sub-element 0501 of the data processing unit 05 of application program end receives the instruction of JAVA application program, by FETCH_ROWS method, vernier can be moved to next record.
Sub-step 402: the FETCH_ROWS method of the reading database data sub-element 0501 visit data output unit 04 of the data processing unit 05 of application program end, data outputting unit 04 calls next record in DBMS_SQL.FETCH_ROWS method moving cursor query structure.
Sub-step 403: judge currently whether have record, if had, then note has record mark; If there is no record, then record no record mark.
Sub-step 404: if there is record, the reading database data sub-element 0501 of the data processing unit 05 of application program end calls suitable DBMS_SQL.COLUMN_VALUE method according to the data type of each output field, after the data of each output field are taken out, leave in the data storage of variables of binding in query unit 03, if there is multiple output field, then repeatedly deposit.
Sub-step 405: if there is record, then data outputting unit 04 returns record mark to the reading database data sub-element 0501 of the data processing unit 05 of application program end, completes the deposit data of whole record, if there is no record, then return no record mark.
Sub-step 406: the reading database data sub-element 0501 of the data processing unit 05 of application program end, is returned with or without record mark to JAVA application program by FETCH_ROWS method.
Step 500: the data of the data processing unit 05 of application program end spread out of the access that subelement 0502 receives JAVA application program, by data removing method (getString, getDate etc.), Query Result are supplied to JAVA application program.If there is multiple output field, then transmit repeatedly.The data of the data processing unit 05 of application program end spread out of subelement 0502 provides the data of numerous types of data to spread out of method.
Step 600:JAVA application program carries out subsequent treatment to the Query Result data obtained.
Step 700: if need to process next record, then get back to step 500 and continue to perform.
Step 800: if do not need reprocessing record, then the inquiry closing unit 06 of application program end receives JAVA application program instructions, closes query script, releasing resource.
In this example, owing to employing binding variable, avoid string-concatenation in SQL statement, thus reach the object preventing malicious user from rewriting SQL logic, SQL can be avoided to introduce the generation of problem, improve security of system, reduce the risk of data query, further owing to have employed the technology of binding variable, database only can generate limited different SQL statement, decreases Oracle SQL and firmly resolves, and can realize dynamic sql binding variable when indefinite querying condition, reduce system overhead, improve the whole efficiency of system.Owing to encapsulating the DBMS SQL bag of Oracle, developer can wrap without the need to understanding complicated DBMS SQL, effectively can reduce the difficulty that not fixed condition Flexible Query realizes, increase work efficiency.
Based on same inventive concept, additionally provide a kind of data query arrangement in the embodiment of the present invention, as described in the following examples.The principle of dealing with problems due to data query arrangement is similar to data enquire method, and therefore the enforcement of data query arrangement see the enforcement of data enquire method, can repeat part and repeat no more.Following used, term " unit " or " module " can realize the software of predetermined function and/or the combination of hardware.Although the device described by following examples preferably realizes with software, hardware, or the realization of the combination of software and hardware also may and conceived.Fig. 6 is a kind of structured flowchart of the data query arrangement of the embodiment of the present invention, as shown in Figure 6, comprise: acquisition module 601, binding module 602, anticipation module 603, distribution module 604, execution module 605 and module 606 is provided, below this structure is described.
Acquisition module 601, during for carrying name variable in the Structured Query Language (SQL) SQL statement received, obtains the query condition value that user inputs according to described name variable;
Binding module 602, for binding described name variable and described query condition value;
Distribution module 603, performs the data type of the Query Result exported for SQL statement described in anticipation, and is that described SQL statement performs the Query Result distribution data storage of variables array exported according to the data type of anticipation;
Execution module 604, for performing described SQL statement, performing described SQL statement the Query Result exported and being stored in the data storage of variables array of distribution;
There is provided module 605, for the Query Result stored in described data storage of variables array is supplied to application program.
In one embodiment, distribution module 603 comprises: anticipation unit, performs the data type of the Query Result exported specifically for SQL statement described in anticipation; Elemental partition unit, specifically for according to anticipation unit established data type being the element that each field distributes in corresponding data storage of variables array respectively; Binding unit, for after according to established data type being the element that distributes respectively in corresponding data storage of variables array of each field, calling predetermined bound functions, the element in output field and data storage of variables array is carried out man-to-man binding.
In one embodiment, execution module 604 comprises: vernier allocation units, for distributing vernier for described SQL statement; Storage unit, for according to the vernier distributed, performs the Query Result exported and is stored in one by one in element corresponding to the data storage of variables array of distribution by described SQL statement.
In one embodiment, this device can also comprise: release module, after the Query Result stored in described data storage of variables array being supplied to application program in described providing unit, be released to the vernier that described SQL statement is distributed, and the resource shared by described data storage of variables array.
In one embodiment, providing module 605 to comprise: setup unit, for setting the Article 1 record of vernier at described Query Result, described Article 1 record being stored in described data storage of variables array; Mobile unit, moving cursor, on next record, determines whether there is record; Storage unit, for determining to deposit in case of recording, is being stored in element corresponding to described data storage of variables array, until vernier moves to the last item record by current record.
In another embodiment, additionally provide a kind of software, this software is for performing the technical scheme described in above-described embodiment and preferred implementation.
In another embodiment, additionally provide a kind of storage medium, store above-mentioned software in this storage medium, this storage medium includes but not limited to: CD, floppy disk, hard disk, scratch pad memory etc.
From above description, can find out, the embodiment of the present invention achieves following technique effect: when carrying name variable in the SQL statement determining to receive, obtain the query condition value that user exports according to this name variable, both are bound, then anticipation SQL statement performs the data type of the Query Result exported, be that input results distributes data storage of variables array according to this data type, final execution SQL statement, the Query Result of output is stored in the data storage of variables array of distribution, to complete whole data query process.By the way, first variable and variate-value are bound, the data type of the Query Result exported after then determining to perform SQL statement, so that for it distributes suitable storage space, thus achieve and the SQL mode of binding variable also can be adopted to carry out data query in the unfixed situation of querying condition, to solve in prior art in the uncertain situation of querying condition, the SQL mode of binding variable cannot be adopted to carry out data query and the technical matters that the security of data query is not high and system data treatment effeciency is low that causes, reach the technique effect effectively improving data query security and improve data-handling efficiency.
Obviously, those skilled in the art should be understood that, each module of the above-mentioned embodiment of the present invention or each step can realize with general calculation element, they can concentrate on single calculation element, or be distributed on network that multiple calculation element forms, alternatively, they can realize with the executable program code of calculation element, thus, they can be stored and be performed by calculation element in the storage device, and in some cases, step shown or described by can performing with the order be different from herein, or they are made into each integrated circuit modules respectively, or the multiple module in them or step are made into single integrated circuit module to realize.Like this, the embodiment of the present invention is not restricted to any specific hardware and software combination.
The foregoing is only the preferred embodiments of the present invention, be not limited to the present invention, for a person skilled in the art, the embodiment of the present invention can have various modifications and variations.Within the spirit and principles in the present invention all, any amendment done, equivalent replacement, improvement etc., all should be included within protection scope of the present invention.
Claims (11)
1. a data enquire method, is characterized in that, comprising:
When carrying name variable in the Structured Query Language (SQL) SQL statement received, obtain the query condition value that user inputs according to described name variable;
Described name variable and described query condition value are bound;
SQL statement described in anticipation performs the data type of the Query Result exported;
According to the data type of anticipation, distribute data storage of variables array for described SQL statement performs the Query Result exported;
Perform described SQL statement, described SQL statement is performed the Query Result exported and is stored in the data storage of variables array of distribution;
The Query Result stored in described data storage of variables array is supplied to application program.
2. the method for claim 1, is characterized in that, according to the data type of anticipation, distributes data storage of variables array, comprising for described SQL statement performs the Query Result exported:
When the Query Result that described SQL statement performs output comprises multiple output field, determining the data type of each output field in described multiple output field, is the element that each field distributes in corresponding data storage of variables array respectively according to established data type;
Call predetermined bound functions, the element in output field and data storage of variables array is carried out man-to-man binding.
3. method as claimed in claim 2, is characterized in that, perform described SQL statement, described SQL statement is performed the Query Result exported and is stored in the data storage of variables array of distribution, comprising:
For described SQL statement distributes vernier;
According to the vernier distributed, described SQL statement is performed the Query Result exported and be stored in one by one in element corresponding to the data storage of variables array of distribution.
4. method as claimed in claim 3, it is characterized in that, after the Query Result stored in described data storage of variables array is supplied to application program, described method also comprises:
Be released to the vernier of described SQL statement distribution and the resource shared by described data storage of variables array.
5. the method for claim 1, is characterized in that, described SQL statement is performed the Query Result exported and is stored in the data storage of variables array of distribution, comprising:
Described Article 1 record, on the Article 1 record of described Query Result, is stored in described data storage of variables array by setting vernier;
Moving cursor, on next record, determines whether there is record;
If there is record, then current record is stored in element corresponding to described data storage of variables array, until vernier moves to the last item record.
6. the method according to any one of claim 1 to 5, is characterized in that, the name variable carried in described SQL statement, and the query condition value of user's input is variable.
7. a data query arrangement, is characterized in that, comprising:
Acquisition module, during for carrying name variable in the Structured Query Language (SQL) SQL statement received, obtains the query condition value that user inputs according to described name variable;
Binding module, for binding described name variable and described query condition value;
Distribution module, performs the data type of the Query Result exported for SQL statement described in anticipation, and is that described SQL statement performs the Query Result distribution data storage of variables array exported according to the data type of anticipation;
Execution module, for performing described SQL statement, performing described SQL statement the Query Result exported and being stored in the data storage of variables array of distribution;
There is provided module, for the Query Result stored in described data storage of variables array is supplied to application program.
8. device as claimed in claim 7, it is characterized in that, described distribution module comprises:
Anticipation unit, performs the data type of the Query Result exported for SQL statement described in anticipation;
Elemental partition unit, for according to described anticipation unit established data type being the element that each field distributes in corresponding data storage of variables array respectively;
Binding unit, for after according to established data type being the element that distributes respectively in corresponding data storage of variables array of each field, calling predetermined bound functions, the element in output field and data storage of variables array is carried out man-to-man binding.
9. device as claimed in claim 8, it is characterized in that, described execution module comprises:
Vernier allocation units, for distributing vernier for described SQL statement;
Storage unit, for according to the vernier distributed, performs the Query Result exported and is stored in one by one in element corresponding to the data storage of variables array of distribution by described SQL statement.
10. device as claimed in claim 9, is characterized in that, also comprise:
Release module, after the Query Result stored in described data storage of variables array being supplied to application program in described providing unit, is released to the vernier that described SQL statement is distributed, and the resource shared by described data storage of variables array.
11. devices according to any one of claim 7 to 10, is characterized in that, described in provide module to comprise:
Setup unit, for setting the Article 1 record of vernier at described Query Result, is stored to described Article 1 record in described data storage of variables array;
Mobile unit, moving cursor, on next record, determines whether there is record;
Storage unit, for determining to deposit in case of recording, is being stored in element corresponding to described data storage of variables array, until vernier moves to the last item record by current record.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410466552.1A CN104239508B (en) | 2014-09-12 | 2014-09-12 | Data query method and data query device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410466552.1A CN104239508B (en) | 2014-09-12 | 2014-09-12 | Data query method and data query device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN104239508A true CN104239508A (en) | 2014-12-24 |
| CN104239508B CN104239508B (en) | 2017-05-10 |
Family
ID=52227567
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201410466552.1A Active CN104239508B (en) | 2014-09-12 | 2014-09-12 | Data query method and data query device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104239508B (en) |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105447200A (en) * | 2015-12-30 | 2016-03-30 | 金蝶软件（中国）有限公司 | Data processing method and data processing apparatus |
| CN106610973A (en) * | 2015-10-21 | 2017-05-03 | 北京国双科技有限公司 | Television data query method and device |
| CN107038222A (en) * | 2017-03-24 | 2017-08-11 | 福建天泉教育科技有限公司 | Database caches implementation method and its system |
| CN108073401A (en) * | 2017-12-29 | 2018-05-25 | 广州市玄武无线科技股份有限公司 | The execution method, apparatus and electronic equipment of a kind of service logic |
| CN108121733A (en) * | 2016-11-29 | 2018-06-05 | 北京国双科技有限公司 | The querying method and device of a kind of data |
| CN111666295A (en) * | 2019-03-05 | 2020-09-15 | 深圳市天软科技开发有限公司 | Data extraction method, terminal device and computer-readable storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101004683A (en) * | 2007-01-31 | 2007-07-25 | 华为技术有限公司 | Method and device for accessing database |
| CN102354318A (en) * | 2011-09-22 | 2012-02-15 | 用友软件股份有限公司 | Device and method for reducing ad hoc query languages in database system |
| CN102495882A (en) * | 2011-12-07 | 2012-06-13 | 方正国际软件有限公司 | System and method for binding page object and database field in Java environment |
| CN103914290A (en) * | 2012-12-31 | 2014-07-09 | 杭州勒卡斯广告策划有限公司 | Operating command processing method and device |
-
2014
- 2014-09-12 CN CN201410466552.1A patent/CN104239508B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101004683A (en) * | 2007-01-31 | 2007-07-25 | 华为技术有限公司 | Method and device for accessing database |
| CN102354318A (en) * | 2011-09-22 | 2012-02-15 | 用友软件股份有限公司 | Device and method for reducing ad hoc query languages in database system |
| CN102495882A (en) * | 2011-12-07 | 2012-06-13 | 方正国际软件有限公司 | System and method for binding page object and database field in Java environment |
| CN103914290A (en) * | 2012-12-31 | 2014-07-09 | 杭州勒卡斯广告策划有限公司 | Operating command processing method and device |
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106610973A (en) * | 2015-10-21 | 2017-05-03 | 北京国双科技有限公司 | Television data query method and device |
| CN105447200A (en) * | 2015-12-30 | 2016-03-30 | 金蝶软件（中国）有限公司 | Data processing method and data processing apparatus |
| CN108121733A (en) * | 2016-11-29 | 2018-06-05 | 北京国双科技有限公司 | The querying method and device of a kind of data |
| CN107038222A (en) * | 2017-03-24 | 2017-08-11 | 福建天泉教育科技有限公司 | Database caches implementation method and its system |
| CN107038222B (en) * | 2017-03-24 | 2020-02-28 | 福建天泉教育科技有限公司 | Database cache implementation method and system |
| CN108073401A (en) * | 2017-12-29 | 2018-05-25 | 广州市玄武无线科技股份有限公司 | The execution method, apparatus and electronic equipment of a kind of service logic |
| CN108073401B (en) * | 2017-12-29 | 2020-09-15 | 广州市玄武无线科技股份有限公司 | Method and device for executing business logic and electronic equipment |
| CN111666295A (en) * | 2019-03-05 | 2020-09-15 | 深圳市天软科技开发有限公司 | Data extraction method, terminal device and computer-readable storage medium |
| CN111666295B (en) * | 2019-03-05 | 2023-12-26 | 深圳市天软科技开发有限公司 | Data extraction method, terminal device and computer readable storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN104239508B (en) | 2017-05-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11599509B2 (en) | Parallel access to data in a distributed file system | |
| US10831562B2 (en) | Method and system for operating a data center by reducing an amount of data to be processed | |
| US10853356B1 (en) | Persistent metadata catalog | |
| CN104239508A (en) | Data query method and data query device | |
| EP3915022A1 (en) | Transformation configuration in instance data replication with bi-directional replication support | |
| CN114586010B (en) | On-demand execution of object filtering code in output path of object store service | |
| US11669599B2 (en) | Systems and methods for software license management | |
| CN103209223A (en) | Distributed application conversation information sharing method and system and application server | |
| US20140324917A1 (en) | Reclamation of empty pages in database tables | |
| EP2778968B1 (en) | Mobile telecommunication device remote access to cloud-based or virtualized database systems | |
| US20070011274A1 (en) | Data transfer in a multi-environment document management system access | |
| CN109726004A (en) | A kind of data processing method and device | |
| WO2016023372A1 (en) | Data storage processing method and device | |
| CN103501341A (en) | Method and device for establishing Web service | |
| CN105653566B (en) | A kind of method and device for realizing database write access | |
| GB2520515A (en) | Publish and subscribe broker with multiple orthogonal topic trees | |
| US10114864B1 (en) | List element query support and processing | |
| US9172729B2 (en) | Managing message distribution in a networked environment | |
| US10120897B2 (en) | Interception of database queries for delegation to an in memory data grid | |
| CN105677692A (en) | Information system and method for providing information inquiry in real time | |
| WO2022057698A1 (en) | Efficient bulk loading multiple rows or partitions for single target table | |
| Luu et al. | Spark Streaming | |
| US8838634B1 (en) | Custom user parallel data import and export | |
| CN112650570A (en) | Dynamically expandable distributed crawler system, data processing method and device | |
| Beckner et al. | Solution: Receiving 834 Data |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |