CN106250477A - A kind of paging method of point of table data - Google Patents

A kind of paging method of point of table data Download PDF

Info

Publication number
CN106250477A
CN106250477A CN201610617337.6A CN201610617337A CN106250477A CN 106250477 A CN106250477 A CN 106250477A CN 201610617337 A CN201610617337 A CN 201610617337A CN 106250477 A CN106250477 A CN 106250477A
Authority
CN
China
Prior art keywords
paging
point
strategy
data
sql
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Granted
Application number
CN201610617337.6A
Other languages
Chinese (zh)
Other versions
CN106250477B (en
Inventor
陈德胜
涂高元
邱志斌
陈雅贤
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
XIAMEN TIPRAY TECHNOLOGY Co Ltd
Original Assignee
XIAMEN TIPRAY TECHNOLOGY Co Ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by XIAMEN TIPRAY TECHNOLOGY Co Ltd filed Critical XIAMEN TIPRAY TECHNOLOGY Co Ltd
Priority to CN201610617337.6A priority Critical patent/CN106250477B/en
Publication of CN106250477A publication Critical patent/CN106250477A/en
Application granted granted Critical
Publication of CN106250477B publication Critical patent/CN106250477B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/27Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
    • G06F16/278Data partitioning, e.g. horizontal or vertical partitioning

Landscapes

  • Engineering & Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Theoretical Computer Science (AREA)
  • Computing Systems (AREA)
  • Data Mining & Analysis (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The present invention relates to the paging method of a kind of point of table data, the method need to provide a terminal, a server and a data base, and the method comprises the following steps: terminal sends pagination command;Server end, according to pagination command, obtains from configuration file and performs SQL;Server end obtains point table strategy of corresponding table according to performing SQL;Server end obtains corresponding paging strategy according to type of database;Server end is transformed performing SQL according to point table strategy and paging strategy, assembled query SQL, and obtains paged data according to query SQL.The present invention obtains paged data by configuring point table strategy and paging strategy from different point tables, facilitates the design and development of product;Present invention may determine which table has a point table, have which point table, that does not worries point table adds the impact on paging structure;This technology is the invention enables to disclosure satisfy that the paging demand of most of type of database;The present invention can carry out database paging inquiry operation effectively from point table.

Description

A kind of paging method of point of table data
Technical field
The present invention relates to database field, the paging method of a kind of point of table data.
Background technology
At present, along with developing rapidly of network technology, information data is more and more huger, how from the hugest data In the quickly inquiry of response user result is shown, become the key factor limiting research and development of products, in this big field Lower point of table paging technique of scape is arisen at the historic moment.
What is a point table, and when the data volume in a table is excessive, we can be according to certain rule, as ID hashes, by number According to dividing, it is then stored in the table that multiple structure is identical.
What is paging, and during data base reads, if data volume is relatively big, disposable reading total data can be greatly Consume the internal memory of computer, it will cause internal memory to overflow, and by consuming the longer time in data transmission procedure, be unfavorable for user Experience, therefore the data in data base are carried out Pagination Display, every one page the most only obtains a certain amount of data.
Owing to different data road types has different paging activity modes, code embeds opening of paging activity symbol Originating party formula creates no small burden to developer, especially in the case of point table, needs to inquire about each point of table, and Result is assembled, the most just can carry out paging, such embedded paging is developed, and exploitation and maintenance to developer will produce pole Greatly cost.
Existing scheme is the data only storing corresponding ID interval by limiting every point of table, according to ID when of paging query Different point tables is inquired about, and for different data bases, it is necessary in program code, embed different paging behaviour Accord with.
But, current data are the most complex, simply carry out paging according to ID and can not meet existing answering By scene, and, program code embeds paging activity symbol and does not possess versatility, such as the paging technique of Oracle and MySql The most variant, this has inconvenience for the exploitation of product.
In view of this, the present inventor's specialized designs paging method of a kind of point of table data, this case thus produces.
Summary of the invention
It is an object of the invention to provide the paging method of a kind of point of table data, to facilitate the design and development of product, nothing That need to worry point table adds the impact on paging structure, and makes it meet the paging demand of most of type of database, it is simple to number Operate according to storehouse paging query.
To achieve these goals, the technical solution used in the present invention is as follows:
The paging method of a kind of point of table data, described method need to provide a terminal, a server and a data base, described side Method comprises the following steps:
S01: terminal sends pagination command;
S02: server end, according to pagination command, obtains from configuration file and performs SQL;
S03: server end obtains point table strategy of corresponding table according to performing SQL;
S04: server end obtains corresponding paging strategy according to type of database;
S05: server end is transformed performing SQL according to point table strategy and paging strategy, assembled query SQL, and root Paged data is obtained according to query SQL.
Described step S05 is specifically, first determine whether whether pagination command can be directly targeted to individual table, if can be direct Navigate to individual table, then directly obtain corresponding paged data according to paging strategy;If desired multiple point of table merges inquiry, Then judging whether to need sequence, if need not sequence, then according to a point table strategy, first obtaining the Query Result number of every point of table, so Determine needs obtain paged data from which point table according to pagination command afterwards, and obtain paging from several points of corresponding tables Data, if desired sort, then need all data to meeting querying condition in every point of table to be ranked up, then further according to dividing Page order obtains paged data.
After using such scheme, the method have the advantages that
One, the present invention obtains paged data, with existing by configuring point table strategy and paging strategy from different point tables Embedded code formula paging have bigger difference, this difference to be very easy to the design and development of product;
Two, the present invention is by a point table strategy, it may be determined which table has a point table, which has and divides table, does not worries point The interpolation of the table impact on paging structure;
Three, the present invention is by the paging strategy differently configured to different type of database such as Oracle, Mysql so that This technology disclosure satisfy that the paging demand of most of type of database;
Four, the present invention can more simply have corresponding to different point table paging techniques, application point table strategy and paging strategy Effect ground carries out database paging inquiry operation from point table.
Accompanying drawing explanation
Accompanying drawing described herein is used for providing a further understanding of the present invention, constitutes the part of the present invention, this Bright schematic description and description is used for explaining the present invention, is not intended that inappropriate limitation of the present invention.In the accompanying drawings:
Fig. 1 is the inventive method schematic flow sheet.
Detailed description of the invention
In order to make the technical problem to be solved, technical scheme and beneficial effect clearer, clear, below tie Close drawings and Examples, the present invention is further elaborated.Should be appreciated that specific embodiment described herein is only used To explain the present invention, it is not intended to limit the present invention.
As it is shown in figure 1, the present invention provides the paging method of a kind of point of table data, described method need to provide a terminal, clothes Business device and a data base, said method comprising the steps of:
S01: terminal sends pagination command;Wherein, this pagination command can include data query conditions, the page number (page), Every page line number (row), sort field, sortord (ascending order, descending);
S02: server end, according to pagination command, obtains from configuration file and performs SQL;
S03: server end obtains point table strategy of corresponding table according to performing SQL;
S04: server end obtains corresponding paging strategy according to type of database;
S05: server end is transformed performing SQL according to point table strategy and paging strategy, assembled query SQL, and root Paged data is obtained according to query SQL.
In an embodiment of the present invention, it is provided that the paging method of a kind of point of table data, comprise the following steps:
S01: terminal sends pagination command;Wherein, this pagination command can include data query conditions, the page number (page), Every page line number (row), sort field, sortord (ascending order, descending);
S02: server end, according to pagination command, obtains from configuration file and performs SQL;
S03: server end obtains point table strategy of corresponding table according to performing SQL.Table strategy is divided mainly to include table name And point table rule (a point table rule defines according to project reality, and point table such as a monthly point table is regular) (table).And carry For the method obtaining point table performing SQL, the method is according to performing parameter such as: the parameter such as major key, time period determines a point table;
S04: server end obtains corresponding paging strategy according to type of database.Paging strategy mainly provides different pieces of information The paging method in storehouse, if the paging of Mysql is that the paging using keyword LIMIT, Oracle mainly uses ROWNUM, then root Paging keyword and assembled paging statement is added for performing SQL according to paging method;
S05: server end is transformed performing SQL according to point table strategy and paging strategy, assembled query SQL, and root Paged data is obtained according to query SQL.First, determine, according to point table strategy, the one or more points of tables that execution SQL will operate;Its Secondary, replace the table name (table) performed in SQL with point table, as multiple point of table need to be operated, then carry out with keyword UNION ALL The merging of result set, assembles new execution SQL;Again, according to the paging method of paging strategy for performing SQL assembly paging language Sentence;Finally, the execution SQL after assembly giving the database-driven of server, it is right that database-driven will be carried out according to execution SQL The data query answered.Assembled paging statement carries out paging query when, in order to improve search efficiency, need to order according to paging Making the scope to operation point table be controlled, detailed process is as follows: first, it is determined that whether pagination command can be directly targeted to list Open table, if individual table can be directly targeted to, then directly obtain corresponding paged data according to paging strategy;If desired multiple points Table merges inquiry, then judge whether to need sequence;If need not sequence, then according to a point table strategy, first obtain every point of table Query Result number, then determine needs obtain paged data from which point table according to pagination command, and from corresponding several Open and point table obtains paged data;If desired sort, then need all data to meeting querying condition in every point of table to carry out Sequence, then obtains paged data further according to pagination command.
The present invention improves search efficiency by the quantity limiting the inquiry data of each point of table, and principle is as follows: according to dividing Page order obtains the front n data after every table internal sort, wherein n=page*row;Then, grasp with the merging of database table Accord with " UNION ALL " to be merged by the Query Result of previous step, and the result set after being combined is ranked up;Finally, root The paged data after sequence is obtained according to pagination command.
The method have the advantages that
One, the present invention obtains paged data, with existing by configuring point table strategy and paging strategy from different point tables Embedded code formula paging have bigger difference, this difference to be very easy to the design and development of product;
Two, the present invention is by a point table strategy, it may be determined which table has a point table, which has and divides table, does not worries point The interpolation of the table impact on paging structure;
Three, the present invention is by the paging strategy differently configured to different type of database such as Oracle, Mysql so that This technology disclosure satisfy that the paging demand of most of type of database;
Four, the present invention can more simply have corresponding to different point table paging techniques, application point table strategy and paging strategy Effect ground carries out database paging inquiry operation from point table.
Described above illustrate and describes the preferred embodiments of the present invention, as previously mentioned, it should be understood that the present invention not office Be limited to form disclosed herein, be not to be taken as the eliminating to other embodiments, and can be used for other combinations various, amendment and Environment, and can be changed by above-mentioned teaching or the technology of association area or knowledge in invention contemplated scope described herein Dynamic.And the change that those skilled in the art are carried out and change are without departing from the spirit and scope of the present invention, the most all should be appended by the present invention In scope of the claims.

Claims (2)

1. the paging method of one kind of point of table data, it is characterised in that described method need to provide a terminal, a server and data Storehouse, said method comprising the steps of:
S01: terminal sends pagination command;
S02: server end, according to pagination command, obtains from configuration file and performs SQL;
S03: server end obtains point table strategy of corresponding table according to performing SQL;
S04: server end obtains corresponding paging strategy according to type of database;
S05: server end is transformed performing SQL according to point table strategy and paging strategy, assembled query SQL, and according to looking into Ask SQL and obtain paged data.
The paging method of a kind of point of table data the most according to claim 1, it is characterised in that: described step S05 specifically, First determine whether whether pagination command can be directly targeted to individual table, if individual table can be directly targeted to, then directly according to dividing Page strategy obtains corresponding paged data;If desired multiple point of table merges inquiry, then judge whether to need sequence, if being not required to Sort, then according to a point table strategy, first obtain the Query Result number of every point of table, then determine needs are from which according to pagination command Several points of tables obtain paged data, and obtains paged data from several points of corresponding tables, if desired sort, then need often Open and divide all data meeting querying condition in table to be ranked up, then obtain paged data further according to pagination command.
CN201610617337.6A 2016-07-29 2016-07-29 Paging method for data in sub-table Active CN106250477B (en)

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 true CN106250477A (en) 2016-12-21
CN106250477B 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)

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN107016115A (en) * 2017-04-18 2017-08-04 网易(杭州)网络有限公司 Data export method, device, computer-readable recording 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 (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN101216840A (en) * 2008-01-21 2008-07-09 金蝶软件(中国)有限公司 Data enquiry method and data enquiry system
US20100115000A1 (en) * 2008-10-30 2010-05-06 International Business Machines Corporation Journaling Database Changes Using a Bit Map for Zones Defined in Each Page
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

Patent Citations (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN101216840A (en) * 2008-01-21 2008-07-09 金蝶软件(中国)有限公司 Data enquiry method and data enquiry system
US20100115000A1 (en) * 2008-10-30 2010-05-06 International Business Machines Corporation Journaling Database Changes Using a Bit Map for Zones Defined in Each Page
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

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN107016115A (en) * 2017-04-18 2017-08-04 网易(杭州)网络有限公司 Data export method, device, computer-readable recording medium and electronic equipment
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

Also Published As

Publication number Publication date
CN106250477B (en) 2020-04-03

Similar Documents

Publication Publication Date Title
CN103020158A (en) Report form creation method, device and system
CN104021198A (en) Relational database information retrieval method and device based on ontology semantic index
CN103886022A (en) Paging-query querying device and method based on primary key fields
CN107526806A (en) The conversion method and device of data
CN108197187A (en) Optimization method, device, storage medium and the computer equipment of query statement
CN104615602A (en) Automatic generation device and method thereof for query interface
CN104866527A (en) Dynamic webpage template matching method and device
CN101727624A (en) Method for showing task list and system thereof
CN105573774A (en) Application parameter managing and configuring method
CN106250477A (en) A kind of paging method of point of table data
CN103488655A (en) Method and system for processing composite model data
CN103324764A (en) Web implementation of multi-condition random keyword multi-field fuzzy query method
CN106168949B (en) The method and device that database is split
CN105912723A (en) Storage method of custom field
CN103336765A (en) Markov matrix off-line correction method of text keywords
CN106919615A (en) Data access method and system
CN104731804A (en) General query framework establishing method and device
CN104199675A (en) System capable of quick system development based on database parameter utilization
CN106844377B (en) Processing method and device of multidimensional database
CN103646071A (en) Dynamic data search method
CN108268517B (en) Method and system for managing labels in database
CN104899045A (en) Universal software coding assistance system and method
CN111143356A (en) Report retrieval method and device
US11068521B2 (en) Method and apparatus for providing relation note using correlation
CN110851543A (en) Data modeling method, device, equipment and storage medium

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