CN103744891B - A kind of data query method and system - Google Patents
A kind of data query method and system Download PDFInfo
- Publication number
- CN103744891B CN103744891B CN201310718464.1A CN201310718464A CN103744891B CN 103744891 B CN103744891 B CN 103744891B CN 201310718464 A CN201310718464 A CN 201310718464A CN 103744891 B CN103744891 B CN 103744891B
- Authority
- CN
- China
- Prior art keywords
- query
- querying condition
- inquiry
- parameter
- condition
- 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/242—Query formulation
- G06F16/2433—Query languages
Abstract
The invention provides a kind of data query method and system, the problem of to solve to inquire about difficult and less efficient in available data inquiry.Described method includes：Receive in data inquiry request, the data inquiry request and carry query argument；Preset structured query sentence SQL is matched according to the query argument；The structured query sentence SQL is replaced using regular expression, the engine parameters of an inquiry query argument are generated；The engine parameters are sent into Report Engine to obtain the data result of inquiry.An engine parameters can be automatically generated to all querying conditions of request to inquire about, easy to use, search efficiency is higher.
Description
Technical field
The present invention relates to technical field of data processing, more particularly to a kind of data query method and system.
Background technology
JasperReport is a powerful, flexible report-building tool, can show abundant content of pages, and will
Be converted into portable document format (Portable Document Format, PDF), HTML (Hyper Text
Mark-up Language, HTML) form, or extensible markup language (Extensible Markup Language, XML)
Form.The storehouse is write as by Java completely, can be used in various java applications, including Java 2 Platform Enterprise Edition (Java
2Platform Enterprise Edition, J2EE), dynamic content is generated in internet (Web) application program.
JasperReport increases income, the also one related engineering IReport that increases income, and this is one patterned auxiliary
Assistant engineer has, and because JasperReport provide only workable class libraries and does not provide more preferable developing instrument, IReport's goes out
Now solve this problem.They, which are used cooperatively, will greatly improve efficiency.
JasperReport can be as Report Engine, and parameter (Parameters) is pair for passing to form filling operation
As quoting, i.e., parameter is mainly acted on is transmitted to Report Engine those data that can not be obtained from report data source.For example, handle
The user name for performing form filling process passes to Report Engine, if allowing user name to be shown on form or in form
It is dynamic on title (title) to change user name, it can be transmitted to Report Engine in the form of parameter.
But, JasperReport is in SQL (Structured Query Language, SQL) sentence
In two kinds of parameter P { } and P are only provided！{ }, and Report Engine handles $ P first！{ } parameter is quoted, by using theirs
Value obtains most important SQL query, when having parsed $ P！After { }, Report Engine just can be by remaining common $ P { } parameter
Reference, which passes to General Parameters, i.e. Report Engine, can carry out nested query.
Due to needing according to $ P { } or $ P！{ } parameter could carry out data query by JsperReport, therefore by SQL
Sentence composition above-mentioned parameter form is comparatively laborious, is often only capable of unconditionally being inquired about, and to carry out condition query, also only needs
Simple condition query is carried out using multiple parameters, using very inconvenient, and influence search efficiency.
The content of the invention
The invention provides a kind of data query method and system, to solve to inquire about difficult and efficiency in available data inquiry
Relatively low the problem of.
In order to solve the above problems, the embodiment of the invention discloses a kind of data query method, including：
Receive in data inquiry request, the data inquiry request and carry query argument；
Preset structured query sentence SQL is matched according to the query argument；
The structured query sentence SQL is replaced using regular expression, the inquiry inquiry ginseng is generated
Several engine parameters；
The engine parameters are sent into Report Engine to obtain the data result of inquiry.
It is described that preset structured query sentence is matched according to the query argument in an alternative embodiment of the invention
SQL, including：At least one statement parameter is obtained from the query argument；Each statement parameter is matched respectively,
It is determined that the structured query sentence SQL inquired about parameter name in the query argument.
In an alternative embodiment of the invention, the use regular expression is carried out to the structured query sentence SQL
Replace, the engine parameters of the generation inquiry query argument, including：The structured query sentence is obtained using regular expression
Querying condition in SQL, and the querying condition is replaced according to the parameter name and parameter value in the query argument；According to
The engine parameters of an inquiry query argument are generated according to the querying condition after replacement.
In an alternative embodiment of the invention, the use regular expression is obtained in the structured query sentence SQL
Querying condition, and the querying condition is replaced according to the parameter name and parameter value in the query argument, including：Adopt
Each structured query sentence SQL form is matched with regular expression, respectively from the structuralized query language
Querying condition is obtained in sentence SQL；The parameter names to be inquired about of structured query sentence SQL are determined, the parameter name is obtained
The corresponding parameter value in the query argument；The querying condition is replaced using the parameter value.
In an alternative embodiment of the invention, the query argument includes at least one parameter statement；The foundation is replaced
Querying condition after changing generates the engine parameters of an inquiry query argument, including：Obtain looking into after at least one replacement
Inquiry condition, each querying condition is carried out the engine parameters of one inquiry query argument of assembling generation.
In an alternative embodiment of the invention, the use regular expression is obtained in the structured query sentence SQL
Querying condition before, in addition to：Whether judge the parameter value is empty；When the parameter value is not space-time, perform to described
The step of querying condition is replaced；When the parameter value is space-time, the querying condition is replaced into preset empty condition.
In an alternative embodiment of the invention, containing at least one of following in the structured query sentence SQL：Each inquiry
The inquiry relation identity of condition, the interval condition of each querying condition, database transfer function and other be mingled with condition.
The embodiment of the present invention additionally provides a kind of data query system, including：
Receiving module, query argument is carried for receiving in data inquiry request, the data inquiry request；
Matching module, for matching preset structured query sentence SQL according to the query argument；
Replacement module, for being replaced using regular expression to the structured query sentence SQL, generation one is looked into
Ask the engine parameters of the query argument；
Enquiry module, for the engine parameters to be sent into Report Engine to obtain the data result of inquiry.
In an alternative embodiment of the invention, the matching module, including：Acquisition submodule, for joining from the inquiry
At least one statement parameter is obtained in number；Matched sub-block, for being matched respectively to each statement parameter, it is determined that pair
The structured query sentence SQL that parameter name is inquired about in the query argument.
In an alternative embodiment of the invention, the replacement module, including：Submodule is replaced, for using regular expressions
Formula obtains the querying condition in the structured query sentence SQL, and according to the parameter name and parameter value in the query argument
The querying condition is replaced；Submodule is generated, is looked into for being generated according to the querying condition after replacing described in an inquiry
Ask the engine parameters of parameter.
In an alternative embodiment of the invention, the replacement submodule, for using regular expression to each knot
Structure query statement SQL form is matched, and obtains querying condition from the structured query sentence SQL respectively；It is determined that
The parameter names to be inquired about of structured query sentence SQL, obtain parameter name corresponding ginseng in the query argument
Numerical value；The querying condition is replaced using the parameter value.
In an alternative embodiment of the invention, the query argument includes at least one parameter statement；Generation
Module, for obtaining the querying condition after at least one replacement, each querying condition is carried out to look into described in one inquiry of assembling generation
Ask the engine parameters of parameter.
In an alternative embodiment of the invention, the replacement module, in addition to：Parameter value judging submodule, for judging
Whether the parameter value is empty；When the parameter value is not space-time, triggering replaces submodule execution and the querying condition is replaced
Change；When the parameter value is space-time, triggering replaces submodule and the querying condition is replaced into preset empty condition.
In an alternative embodiment of the invention, containing at least one of following in the structured query sentence SQL：Each inquiry
The inquiry relation identity of condition, the interval condition of each querying condition, database transfer function and other be mingled with condition.
Compared with prior art, the present invention includes advantages below：
The present invention can match preset SQL statement according to the query argument in the data inquiry request of reception, then adopt
SQL statement SQL is replaced with regular expression, the engine parameters of the generation inquiry query argument, then by the engine
Parameter is sent to Report Engine to obtain the data result of inquiry.So as to which all querying conditions to request can automatically generate one
Individual engine parameters are inquired about, easy to use, and search efficiency is higher.
Brief description of the drawings
Fig. 1 is the data query method flow diagram that the embodiment of the present invention one is provided；
Fig. 2 is the data query method flow diagram that the embodiment of the present invention two is provided
Fig. 3 is the data query schematic diagram that the embodiment of the present invention two is provided；
Fig. 4 is the data query system structure chart that the embodiment of the present invention three is provided；
Fig. 5 is the data query system alternative construction figure that the embodiment of the present invention three is provided.
Embodiment
In order to facilitate the understanding of the purposes, features and advantages of the present invention, it is below in conjunction with the accompanying drawings and specific real
Applying mode, the present invention is further detailed explanation.
The embodiment of the present invention provides a kind of data query method, can join according to the inquiry in the data inquiry request of reception
The preset SQL statement of number matching, is then replaced using regular expression to SQL statement SQL, the generation inquiry inquiry ginseng
Then the engine parameters are sent to Report Engine to obtain the data result of inquiry by several engine parameters.So as to request
All querying conditions can automatically generate an engine parameters and be inquired about, and easy to use, search efficiency is higher.
Embodiment one
Reference picture 1, gives the data query method flow diagram of the offer of the embodiment of the present invention one.
Step 101, data inquiry request is received.
User can dynamically show the related information of inquiry, Yong Huke when carrying out data query in the form operation page
To input the relevant information to be inquired about, such as to inquire about which parameter, and the parameter parameter value etc. so as to carry out condition query,
Then click on and inquired about.
Taken in the information generation data inquiry request that the form operation page can be inputted according to user, the data inquiry request
With query argument, condition query can be carried out by the query argument.Then the form operation page sends out data inquiry request
Data query system is given, data query system obtains query argument from data inquiry request.
Step 102, preset SQL statement is matched according to the query argument.
The embodiment of the present invention has been pre-configured with a variety of SQL statements, the condition query for carrying out diversified forms, each SQL
The querying condition inquired about at least one parameter name is all configured with sentence.
So as to which after the query argument of page transmission is received, SQL statement can be matched according to the query argument.For example should
Configured in query argument by the parameter name to be inquired about, so as to match SQL statement according to the parameter name, and for example the query argument has been
Identify the SQL statement to be matched.
Step 103, the SQL statement is replaced using regular expression, generates an inquiry query argument
Engine parameters.
The embodiment of the present invention has been pre-configured with regular expression, wherein, regular expression can be used as to string operation
A kind of logical formula, exactly with the combination of some specific characters and these specific characters defined in advance, constitute one
" regular character string ", this " regular character string " is used for expressing a kind of filter logic to character string.
Desired specific part can be obtained from character string by regular expression, therefore regular expression can be used
SQL statement is filtered to obtain querying condition, and then the querying condition is replaced, generation one can be used
JasperReport carries out the engine parameters of data base querying to query argument.
Step 104, the engine parameters are sent into Report Engine to obtain the data result of inquiry.
Engine parameters are sent to Report Engine by data query system, and Report Engine inquires about data using the engine parameters
Storehouse, and then the data that database is returned are received, then data query system is fed back to, data query system is by data feedback to use
Family, is such as shown in the form operation page.
In summary, preset SQL statement is matched according to the query argument in the data inquiry request received, is then used
Regular expression is replaced to SQL statement SQL, the engine parameters of the generation inquiry query argument, then joins the engine
Number is sent to Report Engine to obtain the data result of inquiry.So as to which all querying conditions to request can automatically generate one
Engine parameters are inquired about, easy to use, and search efficiency is higher.
Embodiment two
On the basis of above-described embodiment, the querying method of the present embodiment data discussed in further detail.
Reference picture 2, gives the data query method flow diagram of the offer of the embodiment of the present invention two.
Step 201, data inquiry request is received, query argument is obtained from inquiry request.
In the present embodiment, above-mentioned steps 101 match preset SQL statement according to the query argument and further comprise step
202 and step 203.
Step 202, at least one statement parameter is obtained from the query argument.
Step 203, each statement parameter is matched respectively, it is determined that being carried out to parameter name in the query argument
The SQL statement of inquiry.
Query argument includes parameter name, parameter value and statement parameter in the embodiment of the present invention, wherein, statement parameter is used for
Obtain the SQL statement inquired about the parameter name and parameter value.Also, due to can look into multiple queries condition simultaneously
Ask, therefore query argument includes at least one set of parameter group being made up of parameter name, parameter value and statement parameter.
So as to obtain at least one statement parameter from query argument, then each statement parameter is carried out respectively
Match somebody with somebody, obtain the SQL statement matched with the statement parameter, the SQL statement is used for the ginseng to the corresponding parameter value of the statement parameter
It is several to be inquired about.
For example, one group of parameter group in query argument, parameter is entitled：String name=" a.clumn1 "；Parameter value
For：String value=" 6 "；Parameter statement is sql=ProcessorUtil.buildSql (name, value, sql), is adopted
With above-mentioned parameter group have the replacement of the usual terms of value.
Corresponding matching to the SQL statement of the usual terms for having value be：String sql=" select*from table
A where@a.clumn1=#a.clumn1#@".
Step 204, whether judge the SQL statement is empty.
After SQL statement is matched, whether be empty, so that determine whether can be by the SQL languages if also judging the SQL statement
Sentence carries out the inquiry of data.
If so, i.e. described SQL statement is sky, then step 205 is performed；If it is not, i.e. described SQL statement is not sky, then perform
Step 206.
Step 205, null character string is returned.
If SQL statement is sky, data can not be inquired using the SQL statement, therefore NUL can be returned to the page
String,
It can also further point out inquire about or inquire about less than data etc..
Step 206, whether judge the parameter value is empty.
Get after the querying condition in SQL statement, it can be determined that whether the parameter value in query argument is empty.
If so, i.e. described parameter value is sky, step 207 is performed；If it is not, i.e. described parameter value is not sky, then step is performed
208。
Step 207, the querying condition is replaced into preset empty condition.
Step 208, the querying condition in the SQL statement is obtained using regular expression.
If SQL statement is not sky, the querying condition containing this inquiry in SQL statement carries out database according to the condition
Inquiry, but be due to that JasperReport can not be parsed directly to the SQL statement, therefore to carry out SQL statement progress
Conversion, then will obtain the querying condition in SQL statement, subsequently being built according to the querying condition can be by using regular expression
The engine parameters of JasperReport identifications.
In an alternative embodiment of the invention, the querying condition in the SQL statement is obtained using regular expression, including：
The form of each SQL statement is matched using regular expression, querying condition is obtained from the SQL statement respectively.
Desired specific part, such as querying condition, therefore canonical table can be obtained from character string by regular expression
It is configurable to up to formula by the position where the form determination wherein querying condition of SQL statement, so as to obtain querying condition.
By taking the SQL statement of the above-mentioned usual terms for having value as an example, the parameter value of each querying condition is surrounded using " # "
Come, such as：#a.clumn1#；Each querying condition uses "@" symbol to be surrounded, such as：@a.clumn1=#a.clumn1#@.
When being matched using regular expression to it, spcial character " " that can be first inside processing parameter title,
Then the use of #param.name# replacement values is not empty parameter, and use regular expression@[^@] *param.name
[^@]*(#param.name#)[^@]*@matches whole querying condition, and removes "@" symbol on both sides, so as to be accorded with by "@"
It is a.clumn1=#a.clumn1# that number can determine the querying condition in the SQL statement.
Wherein, due to character as " " generally has a particular meaning in regular expression, and can be frequent in SQL statement
Attaching relation is represented using character " ", such as what a.name was represented is exactly table a name fields, therefore in order to prevent conflict,
Need to carry out escape processing to it.
Therefore, if parameter value in above-mentioned steps 207 in query argument is sky, querying condition is replaced into and pre- is empty bar
Part, such as 1=1.
Determine after querying condition, if the parameter value in the query argument is sky, use regular expression@[^@] *
param.name[^@]*(#param.name#)[^@]*Querying condition in@matching SQL statements, then puts querying condition
Change into " 1=1 ".
Step 209, the querying condition is replaced according to the parameter name and parameter value in the query argument.
In an alternative embodiment of the invention, according to the parameter name and parameter value in the query argument to the inquiry bar
Part is replaced, including：The parameter name to be inquired about of SQL statement is determined, the parameter name is obtained right in the query argument
The parameter value answered；The querying condition is replaced using the parameter value.
If the parameter value in query argument is not sky, querying condition can be replaced using the parameter, due to SQL
Being potentially included in sentence may also be comprising multiple parameters name in multiple queries condition, each querying condition, therefore is looked into matching
After inquiry condition, the parameters to be inquired about in querying condition name is also predefined, each parameter name is then obtained successively and is existed
Corresponding parameter value in the query argument, is then replaced using the parameter value to corresponding parameter value in querying condition.
Querying condition after then upper example is replaced is a.clumn1=6, and the replacement result of correspondence SQL statement is select*
From table a where a.clumn1=6.
Step 210, the engine parameters of an inquiry query argument are generated according to the querying condition after replacing.
Then the engine parameters of an inquiry query argument are generated according to the querying condition after replacing, i.e., by SQL languages
The engine parameters that can recognize of replacements result composition JasperReport of sentence, such as by by the replacement result of SQL statement
RelationSql is with $ P！The incoming Report Engine of form of { relationSql }.
In an alternative embodiment of the invention, the querying condition according to after replacing generates the inquiry inquiry ginseng
Several engine parameters, including：The querying condition after at least one replacement is obtained, each querying condition is carried out into assembling generation one looks into
Ask the engine parameters of the query argument.
The embodiment of the present invention causes Query Result more accurate or scope more to be rapidly performed by data query
Greatly, therefore query argument includes the SQL statement that at least first time data query of parameter group, i.e., one is used and may obtain multiple
Querying condition, then obtain after the querying condition after at least one replacement, can be by the assembling of the querying condition after each replacement generation one
The engine parameters of the individual inquiry query argument, wherein having certain relation, such as AND, OR between each query statement.It is complicated
SQL statement querying condition more than one sometimes, and set a lot, also a lot of other is mingled with condition, sequence, outer connection
All kinds of conditions such as interior connection combination, therefore many complicated conditions will be correctly spliced in different conditional statements,
Then an engine parameters are constituted to be inquired about.
Prior art will assemble SQL statement when carrying out data query according to querying condition, realize above-mentioned a variety of passes
System must just increase the relation between querying condition, it is also possible to there is the sequential organization of priority, such as i.e. with () certain partial condition
Bracket to change the priority of the condition, realize extremely complex, or even can not realize, therefore the correctness of SQL statement splicing
Do not know where to begin.And also there is interval condition, database transfer function etc., disparate databases transfer function is different, therefore number
According to storehouse transfer function in configuration process for different databases must IF ... ELSE, integrate that to write code very multiple
It is miscellaneous, and the database of UNKNOWN TYPE is also needed to modify.Therefore prior art can only realize the form of simple queries condition,
Or the form without querying condition, then most function has no idea to realize at all for being applied to enterprise.
In the embodiment of the present invention, containing at least one of following in SQL statement：It is the inquiry relation identity of each querying condition, each
The interval condition of querying condition, database transfer function and other be mingled with condition.
Wherein, the inquiry relation identity of each querying condition is used to representing inquiry relation between each querying condition, such as AND,
OR etc..The interval condition of each querying condition is used for the interval condition for representing querying condition, wherein, interval condition divides a lot of kinds again,
Time interval, digital interval etc., and interval literary style is different, some use BETWEEN ... AND, some uses>=<
Number combination.Database transfer function refers to the function used in inquired about database.Other, which are mingled with condition, includes sequence,
All kinds of conditions such as connection combination in outer connection.
Contained all kinds of conditions, transfer function etc. can be identified in query argument in above-mentioned SQL statement, such as language
The contents such as the relation of each sentence can be identified in sentence parameter.
For example, when carrying out data query, may require to contain each querying condition in SQL statement in query argument simultaneously
Inquiry relation, interval condition, database transfer function and parameter value are the contents such as sky, then correspond to the SQL statement replaced and obtained
Can be：Elect*from table a where a.clumn1=6and1=1and to_char (a.date, ' yyyy')
=' 20130219'and 1=1or a.date2between to_date (' 20130219') and to_date ('
20130220')or a.date3>=to_date (' 20130219') or a.date4<=to_date (' 20130219') or
1=1or a.date6>=to_date (' 20130219') and a.date6<=to_date (' 20130220') or
a.date7>=to_date (' 20130219') and 1=1or 1=1and a.date8<=to_date (' 20130220')
Or 1=1and 1=1.Obtained SQL statement will be replaced again with $ P！The incoming Report Engine of form of { relationSql }.
Step 211, the engine parameters are sent into Report Engine to obtain the data result of inquiry.
Based on above-described embodiment, it is known that when carrying out data query, the processing procedure between each system is as shown in Figure 3.
Form runs the page after user's input is obtained, and constitutes data inquiry request and is sent to data query system, data
Inquiry system obtains query argument according to the data inquiry request, and then the SQL stored in system is replaced, regeneration report
Then engine parameters are sent to Report Engine, Report Engine is according to the engine parameters by the engine parameters that table engine can be recognized
Database is inquired about, the data that database is returned is obtained, then looks into data feedback by data to data query system
Inquiry system is by the data feedback to report page to feed back to user.So as on the basis of original SQL statement is not changed,
The dynamic constituent for replacing SQL, realizes that Report Engine is inquired about data side.
In summary, the embodiment of the present invention can in dynamic condition query realize it is simple, it is not necessary to record excessive mark
Know attribute, and various databases are can be suitably used on the premise of code is not changed, with versatility.
Secondly, various complex query conditions can easily be realized, such as querying condition group, packet, sequence etc., and can open up
Reveal purpose of design, check SQL source codes, convenient test.And it need to only transmit an engine parameters and carry out data query, make
Obtain data query more simple, convenient, improve the search efficiency of data, inquire about the comprehensive and accuracy of data.
Embodiment three
On the basis of above-described embodiment, the present embodiment additionally provides a kind of data query system.
Reference picture 4, gives the data query system structure chart of the offer of the embodiment of the present invention three.
The data query system includes：Receiving module 401, matching module 402, replacement module 403 and enquiry module 404.
Wherein, receiving module 401, inquiry ginseng is carried for receiving in data inquiry request, the data inquiry request
Number；Matching module 402, for matching preset SQL statement according to the query argument；Replacement module 403, for using canonical
Expression formula is replaced to SQL statement, generates the engine parameters of an inquiry query argument；Enquiry module 404, for inciting somebody to action
The engine parameters are sent to Report Engine to obtain the data result of inquiry.
In summary, preset SQL statement is matched according to the query argument in the data inquiry request received, is then used
Regular expression is replaced to SQL statement SQL, the engine parameters of the generation inquiry query argument, then joins the engine
Number is sent to Report Engine to obtain the data result of inquiry.So as to which all querying conditions to request can automatically generate one
Engine parameters are inquired about, easy to use, and search efficiency is higher.
Reference picture 5, gives the data query system alternative construction figure of the offer of the embodiment of the present invention three.
In an alternative embodiment of the invention, matching module 402, including：Acquisition submodule 4021, matched sub-block
4022。
Wherein, acquisition submodule 4021, for obtaining at least one statement parameter from the query argument；Match submodule
Block 4022, for being matched respectively to each statement parameter, it is determined that inquiring about parameter name in the query argument
SQL statement.
In an alternative embodiment of the invention, the replacement module 403, including：Parameter value judging submodule 4031, replacement
Submodule 4032 and generation submodule 4033.
Wherein, submodule 4032 is replaced, for obtaining the querying condition in the SQL statement using regular expression, and
The querying condition is replaced according to the parameter name and parameter value in the query argument；Submodule 4033 is generated, is used for
The engine parameters of an inquiry query argument are generated according to the querying condition after replacing.Parameter value judging submodule 4031,
For judging whether the parameter value is empty；When the parameter value is not space-time, triggering is replaced submodule and performed to the inquiry
The replacement of condition；When the parameter value is space-time, triggering replaces submodule and the querying condition is replaced into preset empty condition.
In an alternative embodiment of the invention, the replacement submodule 4032, for using regular expression to each institute
The form for stating SQL statement is matched, and obtains querying condition from the SQL statement respectively；Determine that the SQL statement to be looked into
The parameter name of inquiry, obtains parameter name corresponding parameter value in the query argument；Looked into using the parameter value described
Inquiry condition is replaced.
In an alternative embodiment of the invention, the query argument includes at least one parameter statement；Generation
Module 4033, for obtaining the querying condition after at least one replacement, one inquiry institute of assembling generation is carried out by each querying condition
State the engine parameters of query argument.
In an alternative embodiment of the invention, containing at least one of following in SQL statement：The inquiry relation of each querying condition
Mark, the interval condition of each querying condition, database transfer function and other be mingled with condition.
In summary, the embodiment of the present invention can in dynamic condition query realize it is simple, it is not necessary to record excessive mark
Know attribute, and various databases are can be suitably used on the premise of code is not changed, with versatility.
Secondly, various complex query conditions can easily be realized, such as querying condition group, packet, sequence etc., and can open up
Reveal purpose of design, check SQL source codes, convenient test.And it need to only transmit an engine parameters and carry out data query, make
Obtain data query more simple, convenient, improve the search efficiency of data, inquire about the comprehensive and accuracy of data.
For system embodiment, because it is substantially similar to embodiment of the method, so description is fairly simple, it is related
Part illustrates referring to the part of embodiment of the method.
Each embodiment in this specification is described by the way of progressive, what each embodiment was stressed be with
Between the difference of other embodiment, each embodiment identical similar part mutually referring to.
The present invention can be described in the general context of computer executable instructions, such as program
Module.Usually, program module includes performing particular task or realizes routine, program, object, the group of particular abstract data type
Part, data structure etc..The present invention can also be put into practice in a distributed computing environment, in these DCEs, by
Remote processing devices connected by communication network perform task.In a distributed computing environment, program module can be with
Positioned at including in the local and remote computer-readable storage medium including storage device.
Finally, in addition it is also necessary to explanation, herein, such as first and second or the like relational terms be used merely to by
One entity or operation make a distinction with another entity or operation, and not necessarily require or imply these entities or operation
Between there is any this actual relation or order.Moreover, term " comprising ", "comprising" or its any other variant meaning
Covering including for nonexcludability, so that process, method, commodity or equipment including a series of key elements not only include that
A little key elements, but also other key elements including being not expressly set out, or also include be this process, method, commodity or
The intrinsic key element of equipment.In the absence of more restrictions, the key element limited by sentence "including a ...", is not arranged
Except also there is other identical element in the process including the key element, method, commodity or equipment.
Above to a kind of data query method and system provided by the present invention, it is described in detail, it is used herein
Specific case is set forth to the principle and embodiment of the present invention, and the explanation of above example is only intended to help and understands
The method and its core concept of the present invention；Simultaneously for those of ordinary skill in the art, according to the thought of the present invention, in tool
It will change in body embodiment and application, in summary, this specification content should not be construed as to the present invention
Limitation.
Claims (12)
1. a kind of data query method, it is characterised in that including：
Receive in data inquiry request, the data inquiry request and carry query argument；
Preset structured query sentence SQL is matched according to the query argument, including：
At least one statement parameter is obtained from the query argument；
Each statement parameter is matched respectively, it is determined that the structuring inquired about parameter name in the query argument
Query statement SQL；
The structured query sentence SQL is replaced using regular expression, one is generated and inquires about the query argument
Engine parameters；
The engine parameters are sent to Report Engine JasperReport to obtain the data result of inquiry.
2. according to the method described in claim 1, it is characterised in that the use regular expression is to the structuralized query language
Sentence SQL is replaced, the engine parameters of the generation inquiry query argument, including：
Querying condition in the structured query sentence SQL is obtained using regular expression, and according in the query argument
Parameter name and parameter value the querying condition is replaced；
The engine parameters of an inquiry query argument are generated according to the querying condition after replacing.
3. method according to claim 2, it is characterised in that the use regular expression obtains the structuralized query
Querying condition in sentence SQL, and the querying condition is replaced according to the parameter name and parameter value in the query argument
Change, including：
Each structured query sentence SQL form is matched using regular expression, respectively from the structuring
Querying condition is obtained in query statement SQL；
The parameter names to be inquired about of structured query sentence SQL are determined, the parameter name are obtained in the query argument
Corresponding parameter value；
The querying condition is replaced using the parameter value.
4. method according to claim 2, it is characterised in that the query argument includes at least one parameter statement；
The querying condition according to after replacing generates the engine parameters of an inquiry query argument, including：
The querying condition after at least one replacement is obtained, each querying condition is subjected to one inquiry query argument of assembling generation
Engine parameters.
5. method according to claim 2, it is characterised in that the use regular expression obtains the structuralized query
Before querying condition in sentence SQL, in addition to：
Whether judge the parameter value is empty；
When the parameter value is not space-time, the step of execution is replaced to the querying condition；
When the parameter value is space-time, the querying condition is replaced into preset empty condition.
6. according to any described method of claim 1 to 5, it is characterised in that contain in the structured query sentence SQL with
It is at least one of lower：Each querying condition inquiry relation identity, the interval condition of each querying condition, database transfer function and other
It is mingled with condition, described other, which are mingled with condition, includes connection combination in sequence, outer connection.
7. a kind of data query system, it is characterised in that including：
Receiving module, query argument is carried for receiving in data inquiry request, the data inquiry request；
Matching module, for matching preset structured query sentence SQL according to the query argument, including：
Acquisition submodule, for obtaining at least one statement parameter from the query argument；
Matched sub-block, for being matched respectively to each statement parameter, it is determined that to parameter name in the query argument
The structured query sentence SQL inquired about；
Replacement module, for being replaced using regular expression to the structured query sentence SQL, generates an inquiry institute
State the engine parameters of query argument；
Enquiry module, for the engine parameters to be sent into Report Engine JasperReport to obtain the data knot of inquiry
Really.
8. system according to claim 7, it is characterised in that the replacement module, including：
Submodule is replaced, for obtaining the querying condition in the structured query sentence SQL, and foundation using regular expression
Parameter name and parameter value in the query argument are replaced to the querying condition；
Submodule is generated, the engine parameters for generating an inquiry query argument according to the querying condition after replacing.
9. system according to claim 8, it is characterised in that：
The replacement submodule, for the form progress using regular expression to each structured query sentence SQL
Match somebody with somebody, obtain querying condition from the structured query sentence SQL respectively；Determine that the structured query sentence SQL to be looked into
The parameter name of inquiry, obtains parameter name corresponding parameter value in the query argument；Looked into using the parameter value described
Inquiry condition is replaced.
10. system according to claim 8, it is characterised in that the query argument includes at least one parameter statement；
The generation submodule, for obtaining the querying condition after at least one replacement, assembling generation is carried out by each querying condition
The engine parameters of one inquiry query argument.
11. system according to claim 8, it is characterised in that the replacement module, in addition to：
Parameter value judging submodule, for judging whether the parameter value is empty；When the parameter value is not space-time, triggering is replaced
Submodule performs the replacement to the querying condition；When the parameter value is space-time, triggering replaces submodule by the inquiry bar
Part is replaced into preset empty condition.
12. according to any described system of claim 7 to 11, it is characterised in that contain in the structured query sentence SQL
At least one of below：Each querying condition inquiry relation identity, the interval condition of each querying condition, database transfer function and its
He is mingled with condition, and described other, which are mingled with condition, includes connection combination in sequence, outer connection.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310718464.1A CN103744891B (en) | 2013-12-23 | 2013-12-23 | A kind of data query method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201310718464.1A CN103744891B (en) | 2013-12-23 | 2013-12-23 | A kind of data query method and system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN103744891A CN103744891A (en) | 2014-04-23 |
| CN103744891B true CN103744891B (en) | 2017-10-10 |
Family
ID=50501909
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201310718464.1A Active CN103744891B (en) | 2013-12-23 | 2013-12-23 | A kind of data query method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103744891B (en) |
Families Citing this family (22)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104536987B (en) * | 2014-12-08 | 2017-12-05 | 联动优势电子商务有限公司 | A kind of method and device for inquiring about data |
| CN104809254A (en) * | 2015-05-19 | 2015-07-29 | 郑州悉知信息技术有限公司 | Data query method and device |
| CN106611031A (en) * | 2015-10-27 | 2017-05-03 | 北京国双科技有限公司 | Data query method and device |
| CN106708864A (en) * | 2015-11-13 | 2017-05-24 | 北京国双科技有限公司 | Query framework-based data query method and apparatus |
| CN105824637B (en) * | 2016-03-17 | 2019-03-12 | 中国银行股份有限公司 | Method for inquiring customization and system |
| CN106708917B (en) * | 2016-06-30 | 2019-03-15 | 腾讯科技（深圳）有限公司 | A kind of data processing method, device and OLAP system |
| CN106777076B (en) * | 2016-12-13 | 2020-05-05 | 广东金赋科技股份有限公司 | SQL limited query clause generation method and combinational logic filter |
| CN108255882A (en) * | 2016-12-29 | 2018-07-06 | 北京国双科技有限公司 | Data query method and device |
| CN106991276B (en) * | 2017-03-17 | 2020-01-21 | 浙江大学 | Data interface dynamic generation method based on openEHR template |
| CN107256249A (en) * | 2017-06-08 | 2017-10-17 | 北京小度信息科技有限公司 | A kind of data base query method, device, electronic equipment and computer-readable storage medium |
| CN107463662B (en) * | 2017-07-31 | 2019-12-10 | 拉卡拉支付股份有限公司 | Dynamic Sql query method and device |
| CN107480280A (en) * | 2017-08-22 | 2017-12-15 | 金蝶软件（中国）有限公司 | The method and relevant device of a kind of data processing |
| CN107609109A (en) * | 2017-09-13 | 2018-01-19 | 山东浪潮云服务信息科技有限公司 | A kind of report form generation method, device and system |
| CN110019306B (en) * | 2017-12-27 | 2021-08-31 | 航天信息股份有限公司 | SQL statement searching method and system based on XML format file |
| CN108388589B (en) * | 2018-01-30 | 2022-09-16 | 福建星瑞格软件有限公司 | Device for automatically generating sql query statement of database |
| CN108460092B (en) * | 2018-01-30 | 2022-09-16 | 福建星瑞格软件有限公司 | Automatic generation method and system for sql query statement containing database built-in function |
| CN109063136A (en) * | 2018-08-03 | 2018-12-21 | 北京大米未来科技有限公司 | Non-relational database inquiry system and method |
| CN109542931A (en) * | 2018-11-23 | 2019-03-29 | 四川长虹电器股份有限公司 | A method of the accurate search efficiency of sql database is improved based on regular expression |
| CN110232178A (en) * | 2019-05-29 | 2019-09-13 | 北京中亦安图科技股份有限公司 | Report generation method and device |
| CN112988781A (en) * | 2021-02-02 | 2021-06-18 | 北京金山云网络技术有限公司 | Data query method and device, electronic equipment and computer readable storage medium |
| CN113064914A (en) * | 2021-04-22 | 2021-07-02 | 中国工商银行股份有限公司 | Data extraction method and device |
| CN113342821B (en) * | 2021-06-29 | 2023-04-07 | 深圳前海微众银行股份有限公司 | Report configuration method, device, equipment and computer storage medium |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101021875A (en) * | 2007-03-22 | 2007-08-22 | 金蝶软件（中国）有限公司 | Object-oriented data bank access method and system |
| CN101645074A (en) * | 2009-09-07 | 2010-02-10 | 浪潮集团山东通用软件有限公司 | Method for accessing relational databases of different types |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8447771B2 (en) * | 2006-11-20 | 2013-05-21 | Oracle International Corporation | Query generation |
-
2013
- 2013-12-23 CN CN201310718464.1A patent/CN103744891B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101021875A (en) * | 2007-03-22 | 2007-08-22 | 金蝶软件（中国）有限公司 | Object-oriented data bank access method and system |
| CN101645074A (en) * | 2009-09-07 | 2010-02-10 | 浪潮集团山东通用软件有限公司 | Method for accessing relational databases of different types |
Non-Patent Citations (2)
| Title |
|---|
| ASP进行动态和参数化数据查询;王广柱 等;《微电子学与计算机》;20000229(第2期);第22-24页 * |
| SQL参数化查询详解;王小鉴;《电脑编程技巧与维》;20110131(第1期);第42-50页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN103744891A (en) | 2014-04-23 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103744891B (en) | A kind of data query method and system | |
| US8140556B2 (en) | Techniques for automated generation of queries for querying ontologies | |
| Lanthaler et al. | A semantic description language for restful data services to combat semaphobia | |
| Bowers et al. | An ontology-driven framework for data transformation in scientific workflows | |
| Omelayenko | RDFT: A mapping meta-ontology for business integration | |
| US7194485B2 (en) | Mapping XML schema components to qualified java components | |
| CN102609451B (en) | SQL (structured query language) query plan generation method oriented to streaming data processing | |
| US8103705B2 (en) | System and method for storing text annotations with associated type information in a structured data store | |
| US8868648B2 (en) | Accessing open data using business intelligence tools | |
| US8983931B2 (en) | Index-based evaluation of path-based queries | |
| US20100299327A1 (en) | Generating Structured Query Language/Extensible Markup Language (SQL/XML) Statements | |
| US20060015843A1 (en) | Semantic system for integrating software components | |
| KR20080019439A (en) | System and method for knowledge extension and inference service based on dbms | |
| CN110222110A (en) | A kind of resource description framework data conversion storage integral method based on ETL tool | |
| CN109656951A (en) | Method and inquiry system based on expression formula inquiry data | |
| Patrascoiu | Mapping EDOC to web services using YATL | |
| US9846573B2 (en) | Generating a logical representation from a physical flow | |
| Song et al. | An Automated Generation of XForms Interfaces for Web Servic | |
| CN101593103B (en) | System and method for simultaneously realizing relation perdurability and semantic network style access | |
| Gernhardt et al. | Knowledge-based production planning within the reference planning process supporting manufacturing change management | |
| Fisher et al. | Use of OWL and SWRL for Semantic Relational Database Translation. | |
| Chang et al. | Supporting unified interface to wrapper generator in Integrated Information Retrieval | |
| Comito et al. | XML data integration in OGSA grids | |
| Jaglan et al. | LOD: Linking and querying shared data on web | |
| CN105574016A (en) | Method for half-structured Web information extraction technology |
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 |