CN106250477B - Paging method for data in sub-table - Google Patents
Paging method for data in sub-table Download PDFInfo
- Publication number
- CN106250477B CN106250477B CN201610617337.6A CN201610617337A CN106250477B CN 106250477 B CN106250477 B CN 106250477B CN 201610617337 A CN201610617337 A CN 201610617337A CN 106250477 B CN106250477 B CN 106250477B
- Authority
- CN
- China
- Prior art keywords
- paging
- sub
- strategy
- sql
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
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/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F16/278—Data partitioning, e.g. horizontal or vertical partitioning
Abstract
The invention relates to a paging method of tabulated data, which needs to provide a terminal, a server and a database, and comprises the following steps: the terminal sends out a paging command; the server side acquires the execution SQL from the configuration file according to the paging command; the server side acquires a sub-table strategy of the corresponding table according to the execution SQL; the server side obtains a corresponding paging strategy according to the type of the database; and the server side reforms the execution SQL according to the sub-table strategy and the paging strategy, assembles the query SQL and acquires paging data according to the query SQL. The invention acquires the paging data from different sub-tables by configuring the sub-table strategy and the paging strategy, thereby facilitating the design and development of products; the invention can determine which tables have sub-tables and which sub-tables, and does not worry about the influence of the addition of the sub-tables on the paging structure; the invention enables the technology to meet the paging requirements of most database types; the invention can effectively perform database paging query operation from the sub-tables.
Description
Technical Field
The invention relates to the field of databases, in particular to a paging method for tabulated data.
Background
At present, with the rapid development of network technology, information data is more and more huge, how to quickly respond to user query from the huge data and show results becomes an important factor limiting product research and development, and a sub-table paging technology is developed at the same time in this large scene.
In other words, when the amount of data in one table is too large, the data can be divided according to some rule, such as ID hashing, and then stored in multiple tables with the same structure.
What is paging is that in the database reading process, if the data volume is large, reading all data at one time will greatly consume the memory of the computer, which will cause memory overflow, and will consume a long time in the data transmission process, which is not good for the user experience, so that paging display is performed on the data in the database, and each page only acquires a certain amount of data.
Because different data path types have different paging operation modes, the development mode of embedding paging operator in codes generates a small burden for developers, and particularly under the condition of table division, each table division needs to be queried, results are assembled, and paging can be performed finally.
The existing scheme is that each sub-table is limited to only store data corresponding to an ID interval, query is carried out in different sub-tables according to IDs during paging query, and different paging operators must be embedded in program codes for different databases.
However, the current data are complex, simple paging according to ID cannot meet the existing application scenario, and embedding a paging operator in a program code has no generality, such as differences in paging technologies of Oracle and MySql, which is inconvenient for product development.
In view of the above, the present inventors have devised a method for paging table-divided data, and have resulted in the present invention.
Disclosure of Invention
The invention aims to provide a paging method of the sub-table data, which is convenient for the design and development of products, does not need to worry about the influence of the addition of the sub-table on paging structures, meets paging requirements of most database types and is convenient for paging query operation of the database.
In order to achieve the purpose, the technical scheme adopted by the invention is as follows:
a paging method for table data, which needs to provide a terminal, a server and a database, includes the following steps:
s01: the terminal sends out a paging command;
s02: the server side acquires the execution SQL from the configuration file according to the paging command;
s03: the server side acquires a sub-table strategy of the corresponding table according to the execution SQL;
s04: the server side obtains a corresponding paging strategy according to the type of the database;
s05: and the server side reforms the execution SQL according to the sub-table strategy and the paging strategy, assembles the query SQL and acquires paging data according to the query SQL.
The step S05 is specifically to first determine whether the paging command can be directly located in a single-sheet table, and if the paging command can be directly located in the single-sheet table, directly obtain corresponding paging data according to the paging policy; if a plurality of sub-tables are needed to be combined for query, whether the sorting is needed or not is judged, if the sorting is not needed, the query result number of each sub-table is firstly obtained according to the sub-table strategy, then the sub-page data which needs to be obtained from the sub-tables is determined according to the sub-page command, the sub-page data is obtained from the corresponding sub-tables, if the sorting is needed, all data which meet the query conditions in each sub-table needs to be sorted, and then the sub-page data is obtained according to the sub-page command.
After the scheme is adopted, the invention has the following advantages:
the invention acquires paging data from different sub-tables by configuring the sub-table strategy and the paging strategy, and has great difference with the prior embedded code type paging, and the difference greatly facilitates the design and development of products;
secondly, the invention can determine which tables have the branch tables and which branch tables through the table division strategy, and does not need to worry about the influence of the addition of the branch tables on the paging structure;
thirdly, different paging strategies are configured for different database types such as Oracle and Mysql, so that the technology can meet paging requirements of most database types;
the invention is corresponding to different sub-table paging technologies, and the sub-table strategy and the paging strategy can be applied to more simply and effectively perform database paging query operation from the sub-tables.
Drawings
The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and together with the description serve to explain the invention and not to limit the invention. In the drawings:
FIG. 1 is a schematic flow diagram of the process of the present invention.
Detailed Description
In order to make the technical problems, technical solutions and advantageous effects to be solved by the present invention clearer and clearer, the present invention is further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
As shown in fig. 1, the present invention provides a method for paging table-divided data, where the method needs to provide a terminal, a server and a database, and the method includes the following steps:
s01: the terminal sends out a paging command; the paging command may include a data query condition, a page number (page), a row per page (row), a sorting field, and a sorting manner (ascending order and descending order);
s02: the server side acquires the execution SQL from the configuration file according to the paging command;
s03: the server side acquires a sub-table strategy of the corresponding table according to the execution SQL;
s04: the server side obtains a corresponding paging strategy according to the type of the database;
s05: and the server side reforms the execution SQL according to the sub-table strategy and the paging strategy, assembles the query SQL and acquires paging data according to the query SQL.
In an embodiment of the present invention, a method for paging table-divided data is provided, which includes the following steps:
s01: the terminal sends out a paging command; the paging command may include a data query condition, a page number (page), a row per page (row), a sorting field, and a sorting manner (ascending order and descending order);
s02: the server side acquires the execution SQL from the configuration file according to the paging command;
s03: and the server side acquires the sub-table strategy of the corresponding table according to the execution SQL. The table division strategy mainly comprises a table name (table) and a table division rule (the table division rule is defined according to the actual condition of the item, such as the table division rule of one table per month). And provides a method for obtaining the sub-table for executing SQL, which comprises the following steps: determining a branch table by parameters such as a main key and a time period;
s04: and the server side acquires the corresponding paging strategy according to the database type. The paging strategy mainly provides paging methods of different databases, for example, Mysql paging uses keywords LIMIT, Oracle paging mainly uses ROWNUM, and then adds paging keywords and assembles paging statements for SQL execution according to the paging method;
s05: and the server side reforms the execution SQL according to the sub-table strategy and the paging strategy, assembles the query SQL and acquires paging data according to the query SQL. Firstly, determining one or more sub-tables to be operated for executing SQL according to a sub-table strategy; secondly, replacing a table name (table) in the execution SQL by the sub-table, and if a plurality of sub-tables need to be operated, merging the result set by using a keyword UNION ALL to assemble a new execution SQL; thirdly, according to the paging method of the paging strategy, SQL is executed to assemble paging statements; and finally, the assembled execution SQL is handed to a database driver of the server, and the database driver carries out corresponding data query according to the execution SQL. When the paging query is performed on the assembled paging statement, in order to improve the query efficiency, the range of the operation sub-table needs to be controlled according to the paging command, and the specific process is as follows: firstly, judging whether a paging command can be directly positioned to a single sheet table or not, and if the paging command can be directly positioned to the single sheet table, directly acquiring corresponding paging data according to a paging strategy; if a plurality of sub-tables are needed to be combined and inquired, judging whether the sorting is needed or not; if the sorting is not needed, firstly obtaining the query result number of each sub-table according to the sub-table strategy, then determining which sub-tables need to obtain the paging data according to the paging command, and obtaining the paging data from the corresponding sub-tables; if the sorting is needed, all the data meeting the query condition in each sub-table needs to be sorted, and then the paging data is obtained according to the paging command.
The invention improves the query efficiency by limiting the quantity of query data of each sub-table, and the principle is as follows: acquiring the first n pieces of data after internal sorting of each table according to the paging command, wherein n is page row; then, merging the query results of the previous step by using a merging operator 'UNION ALL' of the database table, and sequencing the merged result set; and finally, obtaining the sorted paging data according to the paging command.
The invention has the following advantages:
the invention acquires paging data from different sub-tables by configuring the sub-table strategy and the paging strategy, and has great difference with the prior embedded code type paging, and the difference greatly facilitates the design and development of products;
secondly, the invention can determine which tables have the branch tables and which branch tables through the table division strategy, and does not need to worry about the influence of the addition of the branch tables on the paging structure;
thirdly, different paging strategies are configured for different database types such as Oracle and Mysql, so that the technology can meet paging requirements of most database types;
the invention is corresponding to different sub-table paging technologies, and the sub-table strategy and the paging strategy can be applied to more simply and effectively perform database paging query operation from the sub-tables.
While the foregoing description shows and describes the preferred embodiments of the present invention, it is to be understood that the invention is not limited to the forms disclosed herein, but is not to be construed as excluding other embodiments and is capable of use in various other combinations, modifications, and environments and is capable of changes within the scope of the inventive concept as described herein, commensurate with the above teachings, or the skill or knowledge of the relevant art. And that modifications and variations may be effected by those skilled in the art without departing from the spirit and scope of the invention as defined by the appended claims.
Claims (1)
1. A method for paging table data, the method providing a terminal, a server and a database, the method comprising the steps of:
s01: the terminal sends out a paging command;
s02: the server side acquires the execution SQL from the configuration file according to the paging command;
s03: the server side acquires a sub-table strategy of the corresponding table according to the execution SQL;
s04: the server side obtains a corresponding paging strategy according to the type of the database;
s05: the server side reforms the execution SQL according to the sub-table strategy and the paging strategy, assembles the query SQL and obtains paging data according to the query SQL;
step S05 is to determine one or more sub-tables to be operated for executing SQL according to the sub-table policy; secondly, replacing a table name table in the execution SQL by the sub-table, and if a plurality of sub-tables need to be operated, merging the result set by using a keyword UNION ALL to assemble a new execution SQL; thirdly, according to the paging method of the paging strategy, SQL is executed to assemble paging statements; finally, the assembled execution SQL is handed to a database driver of a server, and the database driver carries out corresponding data query according to the execution SQL;
controlling the range of the operation sub-table according to the paging command, wherein the process is as follows: firstly, judging whether a paging command can be directly positioned to a single sheet table or not, and if the paging command can be directly positioned to the single sheet table, directly acquiring corresponding paging data according to a paging strategy; if a plurality of sub-tables are needed to be combined for query, whether the sorting is needed or not is judged, if the sorting is not needed, the query result number of each sub-table is firstly obtained according to the sub-table strategy, then the sub-page data which needs to be obtained from the sub-tables is determined according to the sub-page command, the sub-page data is obtained from the corresponding sub-tables, if the sorting is needed, all data which meet the query conditions in each sub-table needs to be sorted, and then the sub-page data is obtained according to the sub-page command.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610617337.6A CN106250477B (en) | 2016-07-29 | 2016-07-29 | Paging method for data in sub-table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610617337.6A CN106250477B (en) | 2016-07-29 | 2016-07-29 | Paging method for data in sub-table |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106250477A CN106250477A (en) | 2016-12-21 |
| CN106250477B true CN106250477B (en) | 2020-04-03 |
Family
ID=57606861
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610617337.6A Active CN106250477B (en) | 2016-07-29 | 2016-07-29 | Paging method for data in sub-table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106250477B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107016115B (en) * | 2017-04-18 | 2020-06-19 | 网易有道信息技术(杭州)有限公司 | Data export method and device, computer readable storage medium and electronic equipment |
| CN110019558A (en) * | 2017-12-27 | 2019-07-16 | 航天信息股份有限公司 | Data transmission method, device, computer readable storage medium and equipment based on Sqoop |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101216840A (en) * | 2008-01-21 | 2008-07-09 | 金蝶软件（中国）有限公司 | Data enquiry method and data enquiry system |
| CN102479234A (en) * | 2010-11-29 | 2012-05-30 | 深圳市金蝶中间件有限公司 | Database paging operation method and device |
| CN102880685A (en) * | 2012-09-13 | 2013-01-16 | 北京航空航天大学 | Method for interval and paging query of time-intensive B/S (Browser/Server) with large data size |
| CN104123340A (en) * | 2014-06-25 | 2014-10-29 | 世纪禾光科技发展（北京）有限公司 | Table-by-table and page-by-page query method and system for database |
| CN104657446A (en) * | 2015-02-04 | 2015-05-27 | 深圳市汇朗科技有限公司 | Combined statistical query method, combined statistical query device and combined statistical query system for secondary tables |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8200627B2 (en) * | 2008-10-30 | 2012-06-12 | International Business Machines Corporation | Journaling database changes using a bit map for zones defined in each page |
-
2016
- 2016-07-29 CN CN201610617337.6A patent/CN106250477B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101216840A (en) * | 2008-01-21 | 2008-07-09 | 金蝶软件（中国）有限公司 | Data enquiry method and data enquiry system |
| CN102479234A (en) * | 2010-11-29 | 2012-05-30 | 深圳市金蝶中间件有限公司 | Database paging operation method and device |
| CN102880685A (en) * | 2012-09-13 | 2013-01-16 | 北京航空航天大学 | Method for interval and paging query of time-intensive B/S (Browser/Server) with large data size |
| CN104123340A (en) * | 2014-06-25 | 2014-10-29 | 世纪禾光科技发展（北京）有限公司 | Table-by-table and page-by-page query method and system for database |
| CN104657446A (en) * | 2015-02-04 | 2015-05-27 | 深圳市汇朗科技有限公司 | Combined statistical query method, combined statistical query device and combined statistical query system for secondary tables |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106250477A (en) | 2016-12-21 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11042662B2 (en) | Data aggregation system for enabling query operations on restricted data that originates from multiple independent multiple sources | |
| CN106951430B (en) | Account table query method and device | |
| US8682875B2 (en) | Database statistics for optimization of database queries containing user-defined functions | |
| US20120330924A1 (en) | Method and system for querying an on demand database service | |
| CN107679071B (en) | Relational database-oriented general data service customized packaging method | |
| CN109815283B (en) | Heterogeneous data source visual query method | |
| CN104133772A (en) | Automatic test data generation method | |
| CN106156088B (en) | Index data processing method, data query method and device | |
| CN104965735A (en) | Apparatus for generating upgrade SQL script | |
| US10296505B2 (en) | Framework for joining datasets | |
| CN107015987B (en) | Method and equipment for updating and searching database | |
| CN106990970B (en) | MVC-based dynamic page generation method and system | |
| US20190188302A1 (en) | Group-by-time operations with returned time context | |
| CN104536987A (en) | Data query method and device | |
| CN106250477B (en) | Paging method for data in sub-table | |
| CN102426612A (en) | Condition object query method and system | |
| CN112883030A (en) | Data collection method and device, computer equipment and storage medium | |
| CN107798111B (en) | Method for exporting data in large batch in distributed environment | |
| CN101354723B (en) | Method and apparatus for implementing combined field | |
| CN110019306B (en) | SQL statement searching method and system based on XML format file | |
| CN113688602A (en) | Task processing method and device | |
| CN110347693A (en) | Method of data synchronization, device and storage medium | |
| CN115905298A (en) | Presto query engine based on http data source and netty | |
| WO2016201813A1 (en) | Dynamic layout method and system based on android | |
| CN105320763A (en) | Method and apparatus for importing contents of XML file into database |
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 |