CN104239508B - Data query method and data query device - Google Patents
Data query method and data query device Download PDFInfo
- Publication number
- CN104239508B CN104239508B CN201410466552.1A CN201410466552A CN104239508B CN 104239508 B CN104239508 B CN 104239508B CN 201410466552 A CN201410466552 A CN 201410466552A CN 104239508 B CN104239508 B CN 104239508B
- Authority
- CN
- China
- Prior art keywords
- data
- query
- sql statement
- data storage
- variable
- 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; 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
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 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, more particularly to a kind of data query method and apparatus.
Background technology
Oracle database is one of data base of current main-stream, and at present, many enterprises are carried out using oracle database
The storage of business data.During using application software, it is often necessary to carry out the inquiry of flexible condition, such as：One is looked into
Inquiry there may be multinomial querying condition, but not all of querying condition is all necessary, and user is when in use often only
Some of querying conditions are needed just to meet search request.Accordingly, it would be desirable to flexible inquire about, to allow user flexibility handle
The dimension and granularity of inquiry are held, so as to the more convenient result rapidly inquired needed for user.
For above-mentioned Flexible Query, can be directly realized by generally in JAVA, but for designing to system layer
Consider, generally the access to data base is adopted in Browser/Server Mode (Browser/Server, BS) system and stored
The mode of journey is realized, i.e. all of data base logic realizes that all in storing process JAVA programs are only called in whole process
Storing process, transmission |input paramete, acquisition result.
However, during actually used, the proceduring SQL that the storing process of oracle database is used
Local dynamic, structured query language (Structured in (Procedural Language/SQL, PL/SQL) language
Query Language, SQL) when querying condition uses binding variable, fixed querying condition can only be supported.If inquiry bar
Part is not fixed, but needs to use SQL, then be accomplished by being inquired about using the mode of unbundling variable, however, unbundling variable
SQL query mode there are problems that following two：1) substantial amounts of hard parsing can be caused because of SQL cannot be shared, causes system
Expense increases, and treatment effeciency declines；2) due to directly variate-value being write in SQL statement text in unbundling variable mode, deposit
Problem is introduced in SQL, security risk easily occurs.
Therefore, in the case of querying condition is unfixed, how SQL data queries to be carried out using the mode of binding variable and is shown
Obtain particularly important.
The content of the invention
A kind of data query method is embodiments provided, is adopted in the case of querying condition is unfixed with reaching
The SQL modes of binding variable carry out the purpose of data query, wherein, the method includes：
When name variable is carried in the SQL SQL statement for receiving, obtain user according to the variable name
Claim the query condition value of input；
The name variable is bound with the query condition value；
SQL statement described in anticipation performs the data type of the Query Result of output；
It is the Query Result distribution data storage variable number that the SQL statement performs output according to the data type of anticipation
Group；
The SQL statement is performed, the data storage that the Query Result that the SQL statement performs output is stored in distribution is become
In amount array；
The Query Result stored in the data storage variable array is supplied to into application program.
In one embodiment, according to the Query Result distribution that the data type of anticipation is the SQL statement execution output
Data storage variable array, including：
When the Query Result that output is performed in the SQL statement includes multiple output fields, the plurality of output is determined
The data type of each output field in field, is respectively allocated corresponding data for each field according to the data type for determining and deposits
Element in storage variable array.
Predetermined bound functions are called, output field is carried out into man-to-man tying up with the element in data storage variable array
It is fixed.
In one embodiment, the SQL statement is performed, the Query Result that the SQL statement performs output is stored in
In the data storage variable array of distribution, including：
Distribute vernier for the SQL statement；
According to the vernier of distribution, the data that the Query Result that the SQL statement performs output is stored in distribution one by one are deposited
In the corresponding element of storage variable array.
In one embodiment, the Query Result stored in the data storage variable array is being supplied to into application program
Afterwards, methods described also includes：
It is released to the vernier of the SQL statement distribution, and the resource shared by the data storage variable array.
In one embodiment, the data storage that the Query Result that the SQL statement performs output is stored in distribution is become
In amount array, including：
First record storage is deposited on first record of the Query Result by setting vernier to the data
In storage variable array；
Moving cursor is to next record, it is determined whether there is record；
If there is record, then by current record storage to the corresponding element of the data storage variable array, directly
The last item record is moved to vernier.
In one embodiment, the name variable for carrying in the SQL statement, and the query condition value of user input be can
Become.
The embodiment of the present invention additionally provides a kind of data query arrangement, is adopted in the case of querying condition is unfixed with reaching
The purpose of data query is carried out with the SQL modes of binding variable, wherein, the device includes：
Acquisition module, during for carrying name variable in the SQL SQL statement for receiving, obtains user's root
According to the query condition value that the name variable is input into；
Binding module, for the name variable is bound with the query condition value；
Distribute module, performs the data type of the Query Result of output for SQL statement described in anticipation, and according to anticipation
Data type is the Query Result distribution data storage variable array that the SQL statement performs output；
Performing module, for performing the SQL statement, the Query Result that the SQL statement performs output is stored in point
In the data storage variable array matched somebody with somebody；
Module is provided, for the Query Result stored in the data storage variable array is supplied to application program.
In one embodiment, the distribute module includes：
Anticipation unit, performs the data type of the Query Result of output for SQL statement described in anticipation；
Elemental partition unit, the data type for being determined according to the anticipation unit are respectively allocated correspondence for each field
Data storage variable array in element；
Binding unit, for being respectively allocated corresponding data storage variable for each field according to the data type for determining
After element in array, predetermined bound functions are called, output field is carried out with the element in data storage variable array
Man-to-man binding.
In one embodiment, the performing module includes：
Vernier allocation unit, for distributing vernier for the SQL statement；
Memory element, for the vernier according to distribution, the Query Result that the SQL statement performs output is stored in one by one
In the corresponding element of data storage variable array of distribution.
In one embodiment, said apparatus also include：
Release module, for providing the Query Result stored in the data storage variable array in the offer unit
After to application program, the vernier of the SQL statement distribution, and the money shared by the data storage variable array are released to
Source.
In one embodiment, the offer module includes：
Setup unit, for setting first record of the vernier in the Query Result, first record is deposited
Store up into the data storage variable array；
Mobile unit, on moving cursor to next record, it is determined whether there is record；
Memory element, for it is determined that deposit in case of recording, current record storage being become to the data storage
In the corresponding element of amount array, until vernier is moved to the last item record.
In embodiments of the present invention, in the above-described embodiments, it is determined that carrying name variable in the SQL statement that receives
In the case of, query condition value of the user according to the name variable input is obtained, both is bound, then anticipation SQL languages
Sentence performs the data type of the Query Result of output, is that output result distributes data storage variable array according to the data type,
It is final to perform SQL statement, the Query Result of output is stored into the data storage variable array of distribution, to complete whole data
Query script.By the way, first variable and variate-value are bound, it is then determined that what is exported after performing SQL statement looks into
The data type of result is ask, to distribute suitable memory space for which, it is achieved thereby that in the unfixed situation of querying condition
The SQL modes of binding variable can also be adopted down to carry out data query, solved uncertain in querying condition in prior art
In the case of, it is impossible to data query is carried out using the SQL modes of binding variable and the not high skill of the safety of caused data query
Art problem, has reached the technique effect for effectively improving data query safety.
Description of the drawings
Accompanying drawing described herein is used for providing a further understanding of the present invention, constitutes the part of the application, not
Constitute limitation of the invention.In the accompanying drawings：
Fig. 1 is the method flow diagram of the data query 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 at the application program end of the embodiment of the present invention；
Fig. 5 is the method flow diagram of the data query 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.
Specific embodiment
It is to make the object, technical solutions and advantages of the present invention become more apparent, with reference to embodiment and accompanying drawing, right
The present invention is described in further details.Here, the exemplary embodiment of the present invention and its illustrate for explaining the present invention, but and
It is not as a limitation of the invention.
During inventor considers prior art, ask for the inquiry of indefinite querying condition has the not high technology of safety
Topic.However, the generation of this problem is primarily due to adopt unbundling variable mode to indefinite querying condition at present.It is right
This, proposes a kind of data query method to solve the above problems, in this example as shown in figure 1, the method is comprised the following steps：
Step 101：When name variable is carried in the SQL SQL statement for receiving, obtain user according to institute
State the query condition value of name variable input；
Step 102：The name variable is bound with the query condition value；
Step 103：SQL statement described in anticipation performs the data type of the Query Result of output；
Step 104：It is the Query Result distribution data storage that the SQL statement performs output according to the data type of anticipation
Variable array；
Step 105：The SQL statement is performed, the Query Result that the SQL statement performs output is stored in into the number of distribution
According in storage variable array；
Step 106：The Query Result stored in the data storage variable array is supplied to into application program.
In the above-described embodiments, in the case of it is determined that name variable is carried in the SQL statement that receives, obtain user
According to the query condition value of the name variable input, both are bound, then anticipation SQL statement performs the inquiry knot of output
The data type of fruit, is that output result distributes data storage variable array according to the data type, finally performs SQL statement, will
The Query Result of output is stored into the data storage variable array of distribution, and will be stored in looking in data storage variable array
Ask result and be supplied to application program, to complete whole data query process.By the way, first variable and variate-value are carried out
Binding, it is then determined that the data type of the Query Result exported after performing SQL statement, to store empty for its distribution is suitable
Between, it is achieved thereby that in the case of querying condition is unfixed, the SQL modes of binding variable can also be adopted to carry out data query,
Solve in prior art in the case of querying condition is uncertain, it is impossible to data are carried out using the SQL modes of binding variable and is looked into
Ask and the not high technical problem of the safety of caused data query, reached the technology effect for effectively improving data query safety
Really.
During enforcement, when execution above-mentioned steps 101 obtain the name variable in SQL statement, first can judge
Whether SQL carries variable, the process of the binding for just needing to carry out follow-up only when SQL statement has variable, otherwise not
Need to carry out follow-up binding procedure, in one embodiment, obtain the name variable in SQL statement in the following manner：Connect
Receive the SQL statement；Whether variable is carried in judging the SQL statement；If it has, then obtaining the change in the SQL statement
Amount title.By the way, unnecessary operation can be prevented effectively to process, query time is saved.
During for output result distribution data storage variable array, multiple words in data storage variable array, are included
Section or multiple elements are included, every kind of data type has corresponding field, in each output field correspondence array
One record, therefore field can be tied in corresponding field value according to the type of output field.
Specifically, after distribution data storage variable array, in addition it is also necessary to output field and data storage variable array
In element bound, can according to the type of data call suitable DBMS_SQL.DEFINE_COLUMN methods will export
Field and data storage variable array are bound, if multiple fields are then repeatedly bound.That is, for the plurality of defeated
Each output field gone out in field, after being respectively allocated the element in a data storage variable array, methods described is also wrapped
Include：Predetermined binding method is called, output field is carried out into man-to-man binding with the element in data storage variable array.
During actual execution, in order to Query Result is stored in data storage variable array, in addition it is also necessary to for which
Distribution vernier, realizes the storage of Query Result by vernier, specifically, performs the SQL statement, the SQL statement is performed
The Query Result of output is stored in the data storage variable array of distribution, can be included：Distribute vernier for the SQL statement；
According to the vernier of distribution, the Query Result that the SQL statement performs output is stored in into the data storage variable number of distribution one by one
In the corresponding element of group.
Because being assigned with array and vernier for Query Result, these resources after Query Result is supplied to application program just
Without existent value, therefore, for save resources, the Query Result stored in the data storage variable array is being supplied to
After application program, can also include：It is released to the vernier of the SQL statement distribution, and the data storage variable array institute
The resource of occupancy, so that this part resource can be used for other places, to realize the effectively utilizes of resource, it is to avoid unreasonable
Occupancy.
The Query Result exported after it will perform the SQL statement is stored in the mistake in the data storage variable array of distribution
Cheng Zhong, can record one by one to be stored by the way of moving cursor, specifically：Perform the SQL statement to be looked into
Ask result；First record storage is become on first record of Query Result by setting vernier to the data storage
In amount array；To next record, moving cursor is recorded moving cursor to next, it is determined whether there is record；If
There is record, then by current record storage to the corresponding element of the data storage variable array, be moved to up to vernier
The last item is recorded.It is important to note, however, that consider if disposably all fields are all taken out, because of record too
Cause storage resource not enough more, the data processing unit and application program of database side can be passed through during realization
Request is initiated in the storage for being implemented in combination in data of both data processing units at end, only application program, it is desirable to obtain data,
Just a record can be provided to application program, and all of record is all supplied to application program together not.
In above-mentioned each embodiment, the name variable carried in SQL statement and the query condition value of user input are can
Become, i.e. can be input into according to demand and select.
Above-mentioned data query method is specifically described below in conjunction with a specific embodiment, but is merited attention
, the specific embodiment do not constitute inappropriate limitation of the present invention merely to the present invention is better described.
In this example, there is provided a kind of data query arrangement based on oracle database, Oracle data can be based on
Storehouse carry out safely and flexibly, support indefinite querying condition, binding variable form data query.As shown in Fig. 2 the device bag
Include：At SQL receiving units 01, variable receiving unit 02, query unit 03, data outputting unit 04, the data at application program end
Reason unit 05, the inquiry closing unit 06 at application program end, are specifically described to unit in the structure below：
1) SQL receiving units 01, for SQL statement is received from application memory process or application program, (SQL statement can
Being character string), i.e. name variable is generally comprised in SQL statement, can be by application memory invocation of procedure SQL receiving unit
01, it is also possible to which SQL receiving units 01 are called by application program (such as JAVA programs).SQL receiving units 01 are deployed in Oracle
In database server, the Oracle database service device can be the service of the operating systems such as Windows, Unix, Linux
Device.
2) variable receiving unit 02, for variate-value (that is, user input is received from application memory process or application program
Query condition value), for the quantity of variate-value can be any number of, as long as the restriction of itself less than Oracle systems is
Can, can be by call stored procedure SQL receiving units 02, it is also possible to by application call SQL receiving units 02.Variable is received
Unit 02 is also deployed in Oracle database service device.
The recipient of the variable of unit comprising variable array Variable_Array and the various data types of one group of support
Method, wherein, the structure of variable array VariableArray can be as shown in table 1：
Table 1
Wherein, a record in each variable (name variable) correspondence array, according to data type of a variable by variable
Variate-value be stored in corresponding variable value field.
For the method for reseptance of above-mentioned variable mainly has 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, parses SQL for calling Oracle functions, by the variate-value and SQL statement that receive
Name variable is bound, and obtains the output field of SQL statement, then the data type of automatic identification output field, will be defeated
Go out the data storage Variable-Bindings of field and respective type, then perform SQL statement, and return vernier ID.As shown in figure 3, looking into
Ask unit 03 to define subelement 0303, perform comprising SQL parsing subelements 0301, Variable-Bindings subelement 0302, output data
SQL subelements 0304.Query unit 03 can also be deployed in Oracle database service device, wherein：
SQL parses subelement 0301, for calling Oracle functions to open vernier and parse SQL statement, subelement master
If calling DBMS_SQL.OPEN_CURSOR methods and DBMS_SQL.PARSE methods, vernier ID is finally returned that；
Variable-Bindings subelement 0302, for calling Oracle functions by the variable in the variate-value and SQL statement that receive
Title is bound, if multiple variables are just bound repeatedly；This binding procedure is primarily directed to variable array Variable_
Each record in Array, i.e. according to data type of a variable, call suitable DBMS_SQL.BIND_VARIABLE methods
Variate-value is bound with the name variable in SQL statement；
Output data defines subelement 0303, for calling Oracle functions to obtain the information of all output fields, main
If the data type of output field, then according to the data type of each output field for determining by output field and respective class
Element in the data storage variable array of type is bound, and is bound repeatedly if multiple output fields.The subelement is main
It is to call DBMS_SQL.DESCRIBE_COLUMNS to obtain field name, data type and the field length of output field
Etc. information, and which is carried out by the data type for each field with the element in data storage variable array Column_Array
Binding, the structure of data storage variable array can be as shown in table 2：
Table 2
Wherein, a record in each output field correspondence array, according to the data type of output field by output word
Section is tied in corresponding field value.
SQL subelements 0304 are performed, for calling Oracle function executing SQL statement, the unit to be mainly used in externally carrying
For EXECUTE methods, can be realized by calling DBMS_SQL.EXECUTE methods.
4) data outputting unit 04 of database side, for moving cursor, a record in Query Result is taken out, will
Each output field in the record is deposited into the data storage variable bound with which, and most the record is passed to and applies journey at last
The data processing unit 05 at sequence end.The data outputting unit 04 of database side is also deployed in Oracle database service device.Should
Unit can be used for being provided out FETCH_ROWS methods, mainly can be moved by calling DBMS_SQL.FETCH_ROWS
Specified vernier is recorded to next, and is directed to each output field, calls DBMS_ according to its corresponding field type
SQL.COLUMN_VALUE method unpack field values, are saved in respective record in data storage variable array Column_Array
In respective field, most data storage variable array Column_Array passes to the data processing unit 05 at application program end at last.
5) data processing unit 05 at application program end, for inquiry is taken out from the data outputting unit 04 of database side
Result data, is then passed to JAVA application programs.As shown in figure 4, the data processing unit 05 at application program end includes receive data
Subelement 0502 is spread out of according to database data subelement 0501, data.The data processing unit 05 at application program end is deployed in using clothes
On business device, wherein：
Reading database data sub-element 0501, for a Query Result is read from oracle database to applying journey
The data processing unit 05 at sequence end, the subelement are mainly with the data outputting unit of FETCH_ROWS method call database sides
04 FETCH_ROWS methods, and record data is taken out from data storage variable array Column_Array；
Data spread out of subelement 0502, for the output field data transfer of current record in query structure is answered to JAVA
With program, the subelement mainly provides one group of data removing method for JAVA application calls, there is provided data removing method
Mainly have following several：
getString
getInt
getFloat
getDate。
6) the inquiry closing unit 06 at application program end, for closing the database cursor of the opening of query unit 03, release
Resource, the inquiry closing unit 06 at application program end are disposed on the application server, and the subelement is present in JAVA classes
In OracleFlexibleQuery, be mainly used in DBMS_SQL.CLOSE_CURSOR being called by query unit 03 and refer to close
Determine vernier, above-mentioned application program can be JAVA programs etc..
Based on above-mentioned data query arrangement, a kind of data query side based on oracle database is additionally provided in this example
Method, as shown in figure 5, comprising the following steps：
Step 100：SQL receiving units 01 receive the SQL statement of application memory process or application passes, JAVA applications
The query condition value of user input is passed to application memory mistake by the Oracle storing processs of routine call application system itself
Journey, application memory process generate the SQL statement character string for including name variable according to querying condition, then call SQL to receive
The character string is passed to SQL receiving units 01 and is processed by the SetSQL methods of unit 01.During being embodied as,
SQL statement character string directly can be sent to by SQL receiving units 01 by JAVA application programs.Application memory process need to follow one
Calibration is accurate, that is, need the vernier ID that will be obtained in subsequent step to return to JAVA application programs.
Step 200：Whether variable is had in judging SQL statement, if it has, then variable receiving unit 02 receives application memory mistake
Name variable and variate-value (i.e. the querying condition of user input) needed for the SQL statement of journey or application passes.At this
During individual, variable receiving unit 02 can provide the variable transferring method of numerous types of data (for example
SetVariableNumber, SetVariableVarchar2 etc.) can be transmitted so as to the data of various data types.Such as
Fruit has multiple variables to need to spread out of, then just repeatedly transmitted.During being embodied as, it is also possible to directly should by JAVA
Variable is passed to variable receiving unit 02 directly with program to be stored in variable array VariableArray.
Step 300：Query unit 03 receives application memory process or the query statement of application program is inquired about, Ke Yiyou
Application memory process sends query statement and calls the EXECUTE methods of query unit 03 to be inquired about, it is also possible to applied by JAVA
Program directly transmits query statement.Specifically, EXECUTE methods may comprise steps of：
Sub-step 301：The SQL parsing subelements 0301 of query unit 03 call DBMS_SQL.OPEN_CURSOR side first
Method opens database cursor, then calls DBMS_SQL.PARSE methods parsing SQL, if SQL statement is wrong, returns mistake
Prompting.
Sub-step 302：If there is variable in SQL statement, the Variable-Bindings subelement 0302 of query unit 03 is according to change
The data type of amount calls suitable DBMS_SQL.bind_variable methods, by the variate-value and SQL statement that receive
Name variable is bound, if multiple variables then carry out repeatedly binding repeatedly, if binding procedure error, returns mistake
Prompting.
Sub-step 303：The output data of query unit 03 defines subelement 0303 and calls DBMS_SQL.describe_
Columns methods obtain the information of each output field during all inquiries are recorded a demerit, and are mainly the data type of each output field.
Sub-step 304：The output data of query unit 03 defines subelement 0303 and is called suitably according to data type
The data storage variable of output field and respective type is bound by DBMS_SQL.DEFINE_COLUMN methods, if many
Individual output field, then carry out multiple bindings, becomes a shareholder and malfunctions in binding procedure, then returns miscue.
Sub-step 305：Query unit 03 performs SQL subelements 0304 and calls DBMS_SQL.EXECUTE to perform SQL statement,
If error, returns miscue.
Step 400：The data processing unit 05 at application program end receives the instruction of JAVA application programs, and vernier is moved to
Next record, mainly includes the following steps that：
Sub-step 401：The reading database data sub-element 0501 of the data processing unit 05 at application program end is received
Vernier can be moved to next record by FETCH_ROWS methods by the instruction of JAVA application programs.
Sub-step 402：The reading database data sub-element 0501 of the data processing unit 05 at application program end accesses number
According to the FETCH_ROWS methods of output unit 04, data outputting unit 04 calls the movement trip of DBMS_SQL.FETCH_ROWS methods
Next record in mark query structure.
Sub-step 403：Whether judgement currently has record, if it has, then note has record mark；If do not recorded, remember
Record no record mark.
Sub-step 404：If record, the reading database data sub-element of the data processing unit 05 at application program end
0501 calls suitable DBMS_SQL.COLUMN_VALUE methods according to the data type of each output field, by each output field
Data take out after, be stored in the data storage variable bind in query unit 03, if multiple output fields, then carry out
Repeatedly deposit.
Sub-step 405：If record, then reading of the data outputting unit 04 to the data processing unit 05 at application program end
The data storage that the database data subelement 0501 that fetches data is returned to be had record mark, complete whole record, if do not recorded, returns
Return no record mark.
Sub-step 406：The reading database data sub-element 0501 of the data processing unit 05 at application program end, passes through
FETCH_ROWS methods are returned to JAVA application programs no record mark.
Step 500：The data of the data processing unit 05 at application program end spread out of subelement 0502 and receive JAVA application journeys
Query Result is supplied to JAVA application programs by data removing method (getString, getDate etc.) by the access of sequence.Such as
Fruit has multiple output fields, then transmit multiple.The data of the data processing unit 05 at application program end spread out of subelement 0502 and carry
Method is spread out of for the data of numerous types of data.
Step 600：JAVA application programs carry out subsequent treatment to the Query Result data for obtaining.
Step 700：Next record is processed if desired, then return to step 500 and continue executing with.
Step 800：If being not required to reprocessing record, the inquiry closing unit 06 at application program end receives JAVA application journeys
Sequence is instructed, and closes query script, discharges resource.
In this example, due to having used binding variable, it is to avoid by string-concatenation in SQL statement, so as to reaching
Prevent malicious user from rewriting the purpose of SQL logics, SQL can be avoided to introduce the generation of problem, security of system is improve, reduced
The risk of data query, further as a result of the technology of binding variable, data base can only generate limited different SQL
Sentence, reduces Oracle SQL and parses firmly, and dynamic sql binding variable can be realized in the case of indefinite querying condition, drop
Low overhead, improves the whole efficiency of system.As the DBMS SQL bags to Oracle are encapsulated, developer
Can effectively reduce the difficulty that fixed condition Flexible Query is not realized, improve work without the need for understanding complicated DBMS SQL bags
Efficiency.
Based on same inventive concept, a kind of data query arrangement in the embodiment of the present invention, is additionally provided, such as following enforcement
Example is described.Due to the principle of data query arrangement solve problem it is similar to data query method, therefore the reality of data query arrangement
The enforcement that may refer to data query method is applied, is repeated part and is repeated no more.It is used below, term " unit " or " mould
Block " can realize the combination of the software and/or hardware of predetermined function.Although the device described by following examples is preferably with soft
Part is realizing, but hardware, or the realization of the combination of software and hardware is also what is may and be contemplated.Fig. 6 is of the invention real
A kind of structured flowchart of the data query arrangement of example is applied, as shown in fig. 6, including：Acquisition module 601, binding module 602, anticipation
Module 603, distribute module 604, performing module 605 and offer module 606, are illustrated to the structure below.
Acquisition module 601, during for carrying name variable in the SQL SQL statement for receiving, obtaining and using
The query condition value that family is input into according to the name variable；
Binding module 602, for the name variable is bound with the query condition value；
Distribute module 603, performs the data type of the Query Result of output for SQL statement described in anticipation, and according to pre-
The data type sentenced is the Query Result distribution data storage variable array that the SQL statement performs output；
Performing module 604, for performing the SQL statement, the Query Result that the SQL statement performs output is stored in
In the data storage variable array of distribution；
Module 605 is provided, for the Query Result stored in the data storage variable array is supplied to application program.
In one embodiment, distribute module 603 includes：Anticipation unit, performs specifically for SQL statement described in anticipation defeated
The data type of the Query Result for going out；Elemental partition unit, the data type specifically for being determined according to anticipation unit are each
Field is respectively allocated the element in corresponding data storage variable array；Binding unit, for according to the data type for determining
After the element being respectively allocated in corresponding data storage variable array for each field, predetermined bound functions are called, will be defeated
Going out field carries out man-to-man binding with the element in data storage variable array.
In one embodiment, performing module 604 includes：Vernier allocation unit, for distributing vernier for the SQL statement；
The Query Result that the SQL statement performs output, for the vernier according to distribution, is stored in the number of distribution by memory element one by one
According in the corresponding element of storage variable array.
In one embodiment, the device can also include：Release module, for depositing the data in the offer unit
After the Query Result stored in storage variable array is supplied to application program, the vernier of the SQL statement distribution, and institute are released to
State the resource shared by data storage variable array.
In one embodiment, there is provided module 605 includes：Setup unit, for setting vernier in the Query Result
On first record, by first record storage to the data storage variable array；Mobile unit, moving cursor is extremely
On next record, it is determined whether there is record；Memory element, for it is determined that deposit in case of recording, by current note
Address book stored is into the corresponding element of the data storage variable array, until vernier is moved to the last item record.
In another embodiment, a kind of software is additionally provided, the software is used to perform above-described embodiment and preferred reality
Apply the technical scheme described in mode.
In another embodiment, a kind of storage medium is additionally provided, be stored with the storage medium above-mentioned software, should
Storage medium is included but is not limited to：CD, floppy disk, hard disk, scratch pad memory etc..
As can be seen from the above description, the embodiment of the present invention realizes following technique effect：It is determined that receiving
In the case of name variable is carried in SQL statement, query condition value of the user according to the name variable output is obtained, incite somebody to action both
Bound, then anticipation SQL statement performs the data type of the Query Result of output, be input results according to the data type
Distribution data storage variable array, finally performs SQL statement, the Query Result of output is stored to the data storage variable of distribution
In array, to complete whole data query process.By the way, first variable and variate-value are bound, it is then determined that
The data type of the Query Result exported after performing SQL statement, to distribute suitable memory space for which, it is achieved thereby that
The SQL modes of binding variable can also be adopted in the case of querying condition is unfixed to carry out data query, solve prior art
In in the case of querying condition is uncertain, it is impossible to data query is carried out and caused data using the SQL modes of binding variable
The safety of inquiry is not high and the low technical problem of system data treatment effeciency, has reached and has effectively improved data query safety
With the technique effect for improving data-handling efficiency.
Obviously, those skilled in the art should be understood that each module or each step of the above-mentioned embodiment of the present invention can be with
Realized with general computing device, they can be concentrated on single computing device, or be distributed in multiple computing devices
On the network for being constituted, alternatively, they can be realized with the executable program code of computing device, it is thus possible to by it
Store in the storage device by computing device performing, and in some cases, can be holding different from order herein
The shown or described step of row, or they are fabricated to each integrated circuit modules respectively, or will be multiple in them
Module or step are fabricated to single integrated circuit module to realize.So, the embodiment of the present invention is not restricted to any specific hard
Part and software are combined.
The preferred embodiments of the present invention are the foregoing is only, the present invention is not limited to, for the skill of this area
For art personnel, the embodiment of the present invention can have various modifications and variations.It is all within the spirit and principles in the present invention, made
Any modification, equivalent substitution and improvements etc., should be included within the scope of the present invention.
Claims (9)
1. a kind of data query method, it is characterised in that include：
When name variable is carried in the SQL SQL statement for receiving, obtain user defeated according to the name variable
The query condition value for entering；
The name variable is bound with the query condition value；
SQL statement described in anticipation performs the data type of the Query Result of output；
According to the data type of anticipation, it is that the SQL statement performs the Query Result distribution data storage variable array for exporting；
The SQL statement is performed, the Query Result that the SQL statement performs output is stored in into the data storage variable number of distribution
In group；
The Query Result stored in the data storage variable array is supplied to into application program；
Wherein, the data type according to anticipation, is that the SQL statement performs the Query Result distribution data storage variable number for exporting
Group, including：When the Query Result that output is performed in the SQL statement includes multiple output fields, the plurality of output is determined
The data type of each output field in field, is respectively allocated corresponding data for each field according to the data type for determining and deposits
Element in storage variable array；Predetermined bound functions are called, output field is entered with the element in data storage variable array
The man-to-man binding of row.
2. the method for claim 1, it is characterised in that perform the SQL statement, the SQL statement is performed into output
Query Result be stored in the data storage variable array of distribution, including：
Distribute vernier for the SQL statement；
According to the vernier of distribution, the data storage that the Query Result that the SQL statement performs output is stored in distribution one by one is become
In the corresponding element of amount array.
3. method as claimed in claim 2, it is characterised in that in the inquiry knot that will be stored in the data storage variable array
After fruit is supplied to application program, methods described also includes：
The resource being released to shared by the vernier and the data storage variable array of the SQL statement distribution.
4. the method for claim 1, it is characterised in that the Query Result that the SQL statement performs output is stored in
In the data storage variable array of distribution, including：
First record storage is become on first record of the Query Result by setting vernier to the data storage
In amount array；
Moving cursor is to next record, it is determined whether there is record；
If there is record, then by current record storage to the corresponding element of the data storage variable array, until trip
Mark is moved to the last item record.
5. the method as any one of Claims 1-4, it is characterised in that the variable name carried in the SQL statement
Claim, and the query condition value of user input is variable.
6. a kind of data query arrangement, it is characterised in that include：
Acquisition module, during for carrying name variable in the SQL SQL statement for receiving, obtains user according to institute
State the query condition value of name variable input；
Binding module, for the name variable is bound with the query condition value；
Distribute module, performs the data type of the Query Result of output for SQL statement described in anticipation, and according to the data of anticipation
Type is the Query Result distribution data storage variable array that the SQL statement performs output；
The Query Result that the SQL statement performs output, for performing the SQL statement, is stored in distribution by performing module
In data storage variable array；
Module is provided, for the Query Result stored in the data storage variable array is supplied to application program；
Wherein, the distribute module includes：
Anticipation unit, performs the data type of the Query Result of output for SQL statement described in anticipation；
Elemental partition unit, the data type for being determined according to the anticipation unit are respectively allocated corresponding number for each field
According to the element in storage variable array；
Binding unit, for being respectively allocated corresponding data storage variable array for each field according to the data type for determining
In element after, call predetermined bound functions, the element in output field and data storage variable array carried out a pair
One binding.
7. device as claimed in claim 6, it is characterised in that the performing module includes：
Vernier allocation unit, for distributing vernier for the SQL statement；
The Query Result that the SQL statement performs output, for the vernier according to distribution, is stored in distribution by memory element one by one
The corresponding element of data storage variable array in.
8. device as claimed in claim 7, it is characterised in that also include：
Release module, should for being supplied to the Query Result stored in the data storage variable array in the offer unit
After with program, the vernier of the SQL statement distribution, and the resource shared by the data storage variable array are released to.
9. the device as any one of claim 6 to 8, it is characterised in that the offer module includes：
Setup unit, for setting first record of the vernier in the Query Result, by first record storage extremely
In the data storage variable array；
Mobile unit, on moving cursor to next record, it is determined whether there is record；
Memory element, for it is determined that deposit in case of recording, by current record storage to the data storage variable number
In the corresponding element of group, until vernier is moved to the last item 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 CN104239508A (en) | 2014-12-24 |
| CN104239508B true 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) |
Families Citing this family (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106610973B (en) * | 2015-10-21 | 2020-09-04 | 北京国双科技有限公司 | Television data query method and device |
| CN105447200A (en) * | 2015-12-30 | 2016-03-30 | 金蝶软件（中国）有限公司 | Data processing method and data processing apparatus |
| CN108121733B (en) * | 2016-11-29 | 2021-10-15 | 北京国双科技有限公司 | Data query method and device |
| CN107038222B (en) * | 2017-03-24 | 2020-02-28 | 福建天泉教育科技有限公司 | Database cache implementation method and system |
| CN108073401B (en) * | 2017-12-29 | 2020-09-15 | 广州市玄武无线科技股份有限公司 | Method and device for executing business logic and electronic equipment |
| CN111666295B (en) * | 2019-03-05 | 2023-12-26 | 深圳市天软科技开发有限公司 | Data extraction method, terminal device and computer readable storage medium |
| CN114328572A (en) * | 2020-09-28 | 2022-04-12 | 北京鸿享技术服务有限公司 | Data query method, device, system and medium based on SQL parser |
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 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN104239508A (en) | 2014-12-24 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN104239508B (en) | Data query method and data query device | |
| US11210460B2 (en) | Templating process for a multi-page formatted document | |
| US10831562B2 (en) | Method and system for operating a data center by reducing an amount of data to be processed | |
| CN104471585B (en) | For data access control implementation based on the SQL optimization methods changed and equipment | |
| CN106980669B (en) | Data storage and acquisition method and device | |
| CN103905311B (en) | Flow table matching method and device and switch | |
| CN107395665A (en) | A kind of block chain service handling and business common recognition method and device | |
| CN108510082A (en) | The method and device that machine learning model is handled | |
| CN107040585A (en) | A kind of method and device of business verification | |
| CN109726004B (en) | Data processing method and device | |
| CN111383106B (en) | Credit processing method, device, equipment and system | |
| CN104423982B (en) | The processing method and processing equipment of request | |
| CN107665216A (en) | A kind of data bank access method and middleware | |
| CN107102896A (en) | A kind of operating method of multi-level buffer, device and electronic equipment | |
| US20170140160A1 (en) | System and method for creating, tracking, and maintaining big data use cases | |
| EP2965492B1 (en) | Selection of data storage settings for an application | |
| CN107113337A (en) | The network address translation | |
| CN107133243A (en) | A kind of data processing method and server | |
| CN111858611B (en) | Data access method, device, computer equipment and storage medium | |
| Ching et al. | An inventory model with returns and lateral transshipments | |
| CN109618003A (en) | A kind of servers' layout method, server and storage medium | |
| CN105224541B (en) | Uniqueness control method, information storage means and the device of data | |
| CN101807184A (en) | Method for searching character string with wildcard character and system thereof | |
| CN105550220B (en) | A kind of method and device of the access of heterogeneous system | |
| CN107786308B (en) | Data transmission method and terminal device |
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 |