CN112307068A - Dynamic SQL query method - Google Patents
Dynamic SQL query method Download PDFInfo
- Publication number
- CN112307068A CN112307068A CN202011244954.9A CN202011244954A CN112307068A CN 112307068 A CN112307068 A CN 112307068A CN 202011244954 A CN202011244954 A CN 202011244954A CN 112307068 A CN112307068 A CN 112307068A
- Authority
- CN
- China
- Prior art keywords
- instance
- sql
- dynamic
- dynamic sql
- mappedstatementhandler
- 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.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 27
- 230000007246 mechanism Effects 0.000 claims description 5
- 230000004048 modification Effects 0.000 abstract description 2
- 238000012986 modification Methods 0.000 abstract description 2
- 238000005516 engineering process Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000008878 coupling Effects 0.000 description 1
- 238000010168 coupling process Methods 0.000 description 1
- 238000005859 coupling reaction Methods 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 238000013507 mapping Methods 0.000 description 1
- 230000002085 persistent effect Effects 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Images
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/23—Updating
-
- 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
-
- 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/24552—Database cache management
-
- 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/248—Presentation of query results
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention particularly relates to a dynamic SQL query method. The dynamic SQL query method utilizes a check module to check input parameters; caching executed dynamic SQL information through a caching module; updating the configuration of the dynamic SQL information by using an updating module; and finally, realizing the specific execution of the dynamic SQL information through an execution module. The dynamic SQL query method solves the problem that when the Mybatis in the Java Web project executes the SQL statement, the xml file corresponding to the SQL statement needs to be written in advance, and dynamic modification cannot be performed.
Description
Technical Field
The invention relates to the technical field of Java Web, in particular to a dynamic SQL query method.
Background
The Java Web is a technology for solving the technical sum of related Web Internet fields by using Java technology. The Java Web framework is different, but basically follows a specific path number, namely a Servlet or a Filter is used for intercepting requests, an MVC thought design framework is used, convention, XML or Annotation is used for realizing configuration, the characteristics of Java object-oriented are applied, the flow of the requests and the response is realized in an abstract way, and Jsp, Freemarker, Velocity and other views are supported.
Execution of SQL (Structured Query Language) statements in Java Web projects is often implemented by Spring and Mybatis. Spring is an open source code design level framework, which solves the loose coupling problem of the business logic layer and other layers, so that it applies the interface-oriented programming idea throughout the whole system.
Mybases as a Java-based persistent layer framework supports customized SQL, stored procedures and some advanced mappings. The corresponding SQL information is typically configured and mapped by configuring an xml file or using annotations provided by mybases, and then the corresponding SQL statements are executed by parsing the configuration information. However, the mapped SQL information cannot be dynamically modified, and thus the situation that some SQL statements need to be dynamically adjusted cannot be dealt with.
Based on the above problems, the present invention provides a dynamic SQL query method.
Disclosure of Invention
In order to make up for the defects of the prior art, the invention provides a simple and efficient dynamic SQL query method.
The invention is realized by the following technical scheme:
a dynamic SQL query method is characterized in that: the method comprises the following steps:
firstly, checking input parameters by using a checking module;
secondly, caching executed dynamic SQL information through a cache module;
thirdly, updating the configuration of the dynamic SQL information by using an updating module;
and fourthly, implementing specific execution of the dynamic SQL information query through an execution module.
The checking module is used for checking the legality of the input parameters and generating a MappedStatementHandler instance according to the parameters.
And the checking module receives the input parameters, and generates a MappedStatementHandler instance of Mybaits through the SQL query ID and the dynamic SQL information after checking that the input parameters are legal.
The input parameters comprise SQL query ID, dynamic SQL information and SQL parameters; and when the SQL query ID and the dynamic SQL information are both non-empty character strings, the input parameters are considered to be legal.
The SQL query ID is a string of specific characters and is used for distinguishing different SQL queries;
the dynamic SQL information is an SQL statement needing to be pre-compiled, and the specific content is the same as that of SQL information configured by Mybatis in an xml file form;
the SQL parameter is a parameter value corresponding to the replacement precompiled placeholder.
The cache module is responsible for caching and generating SQL query ID and dynamic SQL information corresponding to the MappedStatementHandler instance.
The MappedStatementHandler instance is generated by a checking module, and the caching mode is cached in a key-value form of Map;
the key is the SQL query ID corresponding to the MappedStatementHandler instance, and the vaule is the dynamic SQL information corresponding to the MappedStatementHandler instance.
The updating module updates the MappedStatementHandler instance, and the MappedStatementHandler instance checking module generates the MappedStatementHandler instance.
And when the cache module does not have the dynamic SQL information corresponding to the current MappedStatementHandler instance or the dynamic SQL information corresponding to the current MappedStatementHandler instance is different, executing the updating operation.
If the cache module does not have the dynamic SQL information corresponding to the current MappedStatementHandler instance, analyzing the dynamic SQL information, registering the MappedStatement instance in the Configuration instance, and enabling the MappedStatement instance to be responsible for executing the dynamic SQL information.
If the dynamic SQL information in the cache module is different from the dynamic SQL information corresponding to the current MappedStatementHandler instance, acquiring the corresponding MappedStatement instance from the Configuration instance through the SQL query ID of the MappedStatementHandler instance, and then updating the sqlSource attribute of the MappedStatement instance by using a Java reflection mechanism to ensure that new dynamic SQL information is executed.
The execution module executes the query of the dynamic SQL information through the SqlSessionsTemplate instance of Mybatis and returns the query result.
The invention has the beneficial effects that: the dynamic SQL query method solves the problem that when the Mybatis in the Java Web project executes the SQL statement, the xml file corresponding to the SQL statement needs to be written in advance, and dynamic modification cannot be performed.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to these drawings without creative efforts.
FIG. 1 is a schematic diagram of an illegal implementation architecture of dynamic SQL query.
Detailed Description
In order to make those skilled in the art better understand the technical solution of the present invention, the technical solution in the embodiment of the present invention will be clearly and completely described below with reference to the embodiment of the present invention. It is to be understood that the described embodiments are merely exemplary of the invention, and not restrictive of the full scope of the invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
The dynamic SQL query method comprises the following steps:
firstly, checking input parameters by using a checking module;
secondly, caching executed dynamic SQL information through a cache module;
thirdly, updating the configuration of the dynamic SQL information by using an updating module;
and fourthly, implementing specific execution of the dynamic SQL information query through an execution module.
The checking module is used for checking the legality of the input parameters and generating a MappedStatementHandler instance according to the parameters.
And the checking module receives the input parameters, and generates a MappedStatementHandler instance of Mybaits through the SQL query ID and the dynamic SQL information after checking that the input parameters are legal.
The input parameters comprise SQL query ID, dynamic SQL information and SQL parameters; and when the SQL query ID and the dynamic SQL information are both non-empty character strings, the input parameters are considered to be legal.
The SQL query ID is a string of specific characters and is used for distinguishing different SQL queries;
the dynamic SQL information is an SQL statement needing to be pre-compiled, and the specific content is the same as that of SQL information configured by Mybatis in an xml file form;
the SQL parameter is a parameter value corresponding to the replacement precompiled placeholder.
The cache module is responsible for caching and generating SQL query ID and dynamic SQL information corresponding to the MappedStatementHandler instance.
Key-value is a form of stored data in Key-value pairs, each Key corresponding to a unique value.
The MappedStatementHandler instance is generated by a checking module, and the caching mode is cached in a key (key) -value (value) form of Map;
the key is the SQL query ID corresponding to the MappedStatementHandler instance, and the vaule is the dynamic SQL information corresponding to the MappedStatementHandler instance.
The updating module updates the MappedStatementHandler instance, and the MappedStatementHandler instance checking module generates the MappedStatementHandler instance.
And when the cache module does not have the dynamic SQL information corresponding to the current MappedStatementHandler instance or the dynamic SQL information corresponding to the current MappedStatementHandler instance is different, executing the updating operation.
If the cache module does not have the dynamic SQL information corresponding to the current MappedStatementHandler instance, analyzing the dynamic SQL information, registering the MappedStatement instance in the Configuration instance, and enabling the MappedStatement instance to be responsible for executing the dynamic SQL information.
If the dynamic SQL information in the cache module is different from the dynamic SQL information corresponding to the current MappedStatementHandler instance, acquiring the corresponding MappedStatement instance from the Configuration instance through the SQL query ID of the MappedStatementHandler instance, and then updating the sqlSource attribute of the MappedStatement instance by using a Java reflection mechanism to ensure that new dynamic SQL information is executed.
The function of dynamically acquiring information and dynamically calling object methods is called the reflection mechanism of the Java language. Specifically, the Java reflection mechanism is in a running state, and can know all attributes and methods of any entity class; any method and property can be called for any object.
The execution module executes the query of the dynamic SQL information through the SqlSessionsTemplate instance of Mybatis and returns the query result.
Compared with the prior art, the dynamic SQL query method can realize query operation of the dynamic SQL statement, and solves the problem that the XML file corresponding to the SQL statement needs to be written in advance when the SQL statement is executed by the Mybatis in the Java Web project, and cannot be dynamically modified.
The above-described embodiment is only one specific embodiment of the present invention, and general changes and substitutions by those skilled in the art within the technical scope of the present invention are included in the protection scope of the present invention.
Claims (9)
1. A dynamic SQL query method is characterized in that: the method comprises the following steps:
firstly, checking input parameters by using a checking module;
secondly, caching executed dynamic SQL information through a cache module;
thirdly, updating the configuration of the dynamic SQL information by using an updating module;
and fourthly, implementing specific execution of the dynamic SQL information query through an execution module.
2. The dynamic SQL query method of claim 1, wherein: the checking module is used for checking the legality of the input parameters and generating a MappedStatementHandler instance according to the parameters.
3. The dynamic SQL query method of claim 2, wherein: and the checking module receives the input parameters, and generates a MappedStatementHandler instance of Mybaits through the SQL query ID and the dynamic SQL information after checking that the input parameters are legal.
4. The dynamic SQL query method of claim 3, wherein: the input parameters comprise SQL query ID, dynamic SQL information and SQL parameters; and when the SQL query ID and the dynamic SQL information are both non-empty character strings, the input parameters are considered to be legal.
5. The dynamic SQL query method according to claim 4, wherein: the SQL query ID is a string of specific characters and is used for distinguishing different SQL queries;
the dynamic SQL information is an SQL statement needing to be pre-compiled, and the specific content is the same as that of SQL information configured by Mybatis in an xml file form;
the SQL parameter is a parameter value corresponding to the replacement precompiled placeholder.
6. The dynamic SQL query method of claim 3, wherein: the cache module is responsible for caching and generating SQL query ID and dynamic SQL information corresponding to the MappedStatementHandler instance.
7. The dynamic SQL query method of claim 6, wherein: the MappedStatementHandler instance is generated by a checking module, and the caching mode is cached in a key-value form of Map;
the key is the SQL query ID corresponding to the MappedStatementHandler instance, and the vaule is the dynamic SQL information corresponding to the MappedStatementHandler instance.
8. The dynamic SQL query method according to claim 6 or 7, characterized in that: the updating module updates a MappedStatementHandler instance, and the MappedStatementHandler instance checking module generates;
when the cache module does not have the dynamic SQL information corresponding to the current MappedStatementHandler instance or the dynamic SQL information corresponding to the current MappedStatementHandler instance is different, executing an updating operation;
if the cache module does not have the dynamic SQL information corresponding to the current MappedStatementHandler instance, analyzing the dynamic SQL information, registering the MappedStatement instance in the Configuration instance, and enabling the MappedStatement instance to be responsible for executing the dynamic SQL information;
if the dynamic SQL information in the cache module is different from the dynamic SQL information corresponding to the current MappedStatementHandler instance, acquiring the corresponding MappedStatement instance from the Configuration instance through the SQL query ID of the MappedStatementHandler instance, and then updating the sqlSource attribute of the MappedStatement instance by using a Java reflection mechanism to ensure that new dynamic SQL information is executed.
9. The dynamic SQL query method of claim 8, wherein: the execution module executes the query of the dynamic SQL information through the SqlSessionsTemplate instance of Mybatis and returns the query result.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011244954.9A CN112307068A (en) | 2020-11-10 | 2020-11-10 | Dynamic SQL query method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011244954.9A CN112307068A (en) | 2020-11-10 | 2020-11-10 | Dynamic SQL query method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112307068A true CN112307068A (en) | 2021-02-02 |
Family
ID=74325557
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011244954.9A Pending CN112307068A (en) | 2020-11-10 | 2020-11-10 | Dynamic SQL query method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112307068A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113392122A (en) * | 2021-06-09 | 2021-09-14 | 北京同创永益科技发展有限公司 | Method for separating and operating HQL based on Mybatis framework |
| CN114564231A (en) * | 2022-02-28 | 2022-05-31 | 北京达佳互联信息技术有限公司 | Data query method, data query device, electronic equipment, medium and program product |
| CN114756554A (en) * | 2022-06-13 | 2022-07-15 | 中建电子商务有限责任公司 | Data query processing method based on MyBatis framework |
Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120254178A1 (en) * | 2011-03-29 | 2012-10-04 | Sudarshan Srinivasa Murthy | System and method for processing an sql query made against a relational database |
| CN106874364A (en) * | 2016-12-30 | 2017-06-20 | 厦门南讯软件科技有限公司 | Mobilism SQL query method based on XML configurations |
| CN107038222A (en) * | 2017-03-24 | 2017-08-11 | 福建天泉教育科技有限公司 | Database caches implementation method and its system |
| CN108363746A (en) * | 2018-01-26 | 2018-08-03 | 福建星瑞格软件有限公司 | A kind of unified SQL query system for supporting multi-source heterogeneous data |
| CN109947788A (en) * | 2017-10-30 | 2019-06-28 | 北京京东尚科信息技术有限公司 | Data query method and apparatus |
| CN110019350A (en) * | 2017-07-28 | 2019-07-16 | 北京京东尚科信息技术有限公司 | Data query method and apparatus based on configuration information |
| CN110134705A (en) * | 2018-02-09 | 2019-08-16 | 中国移动通信集团有限公司 | A kind of data query method, cache server and terminal |
| CN110297843A (en) * | 2019-07-02 | 2019-10-01 | 山东浪潮通软信息科技有限公司 | Data query method and system, terminal for B/S system |
| CN111259042A (en) * | 2020-01-08 | 2020-06-09 | 智业软件股份有限公司 | Dynamic query method and system |
| CN111444543A (en) * | 2020-04-02 | 2020-07-24 | 深圳市康拓普信息技术有限公司 | Data authority management method and system |
-
2020
- 2020-11-10 CN CN202011244954.9A patent/CN112307068A/en active Pending
Patent Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120254178A1 (en) * | 2011-03-29 | 2012-10-04 | Sudarshan Srinivasa Murthy | System and method for processing an sql query made against a relational database |
| CN106874364A (en) * | 2016-12-30 | 2017-06-20 | 厦门南讯软件科技有限公司 | Mobilism SQL query method based on XML configurations |
| CN107038222A (en) * | 2017-03-24 | 2017-08-11 | 福建天泉教育科技有限公司 | Database caches implementation method and its system |
| CN110019350A (en) * | 2017-07-28 | 2019-07-16 | 北京京东尚科信息技术有限公司 | Data query method and apparatus based on configuration information |
| CN109947788A (en) * | 2017-10-30 | 2019-06-28 | 北京京东尚科信息技术有限公司 | Data query method and apparatus |
| CN108363746A (en) * | 2018-01-26 | 2018-08-03 | 福建星瑞格软件有限公司 | A kind of unified SQL query system for supporting multi-source heterogeneous data |
| CN110134705A (en) * | 2018-02-09 | 2019-08-16 | 中国移动通信集团有限公司 | A kind of data query method, cache server and terminal |
| CN110297843A (en) * | 2019-07-02 | 2019-10-01 | 山东浪潮通软信息科技有限公司 | Data query method and system, terminal for B/S system |
| CN111259042A (en) * | 2020-01-08 | 2020-06-09 | 智业软件股份有限公司 | Dynamic query method and system |
| CN111444543A (en) * | 2020-04-02 | 2020-07-24 | 深圳市康拓普信息技术有限公司 | Data authority management method and system |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113392122A (en) * | 2021-06-09 | 2021-09-14 | 北京同创永益科技发展有限公司 | Method for separating and operating HQL based on Mybatis framework |
| CN113392122B (en) * | 2021-06-09 | 2022-03-11 | 北京同创永益科技发展有限公司 | Method for separating and operating HQL based on Mybatis framework |
| CN114564231A (en) * | 2022-02-28 | 2022-05-31 | 北京达佳互联信息技术有限公司 | Data query method, data query device, electronic equipment, medium and program product |
| CN114756554A (en) * | 2022-06-13 | 2022-07-15 | 中建电子商务有限责任公司 | Data query processing method based on MyBatis framework |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112307068A (en) | Dynamic SQL query method | |
| US10095758B2 (en) | SQL extended with transient fields for calculation expressions in enhanced data models | |
| US9619552B2 (en) | Core data services extensibility for entity-relationship models | |
| WO2019210758A1 (en) | Data protection method and device and storage medium | |
| US9442977B2 (en) | Database language extended to accommodate entity-relationship models | |
| US9430523B2 (en) | Entity-relationship model extensions using annotations | |
| US10048949B2 (en) | Methods and systems for providing a user interface | |
| US9176801B2 (en) | Advanced data models containing declarative and programmatic constraints | |
| US9639572B2 (en) | SQL enhancements simplifying database querying | |
| US9354948B2 (en) | Data models containing host language embedded constraints | |
| CN114096956A (en) | Method and device for representing database operation layer | |
| US10083016B1 (en) | Procedurally specifying calculated database fields, and populating them | |
| US10789362B2 (en) | Taint analysis with access paths | |
| US9471546B2 (en) | System for generating reconfigurable web application | |
| US9773028B2 (en) | Manipulating non-schema attributes for objects in a schema based directory | |
| CN105204863B (en) | Unlawful data auditing system | |
| WO2008099393A2 (en) | Service provisioning system | |
| US12079623B2 (en) | Consolidation spaces providing access to multiple instances of application content | |
| US20230418680A1 (en) | Selective landscape element grouping facilitating landscape element consolidation or content installation | |
| US10908924B2 (en) | System and methods for loading objects from hash chains | |
| US11947677B2 (en) | Dynamic taint tracking in abstract syntax tree interpreters | |
| US20130019229A1 (en) | Embedding of a Non-Imperative Programming Language for Data Transformation Operations within an Imperative Programming Language | |
| US10719424B1 (en) | Compositional string analysis | |
| US8607201B2 (en) | Augmenting visualization of a call stack | |
| TWI620134B (en) | Integration device and integration method thereof |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210202 |
|
| RJ01 | Rejection of invention patent application after publication |