How to quickly implement custom SQL paging? If not, I will teach you hand in hand.

Make writing a habit together! This is the 15th day of my participation in the “Gold Digging Day New Plan · April More text Challenge”. Click here for more details.

1. Preface 🔥

👨🎓 Author: Bug Bacteria

✏️ blog: CSDN, Nuggets, etc

💌 public account: Magic House of the Circle of the Apes

🚫 special statement: original is not easy, reprint please attach the original source link and this article statement, thank you for your cooperation.

🙏 Copyright notice: part of the text or pictures in the article may come from the Internet or Baidu Encyclopedia, if there is infringement, please contact bug bacteria processing.

Hello, little friends, I am bug bacteria 👀, unconsciously on a year and April, is a good season for outing. But the epidemic in Shanghai, in addition to telecommuting at home is to watch technology incidentally volume some articles. This is not another article activity in April, push yourself, do some content output every day, develop the habit of writing, in the future you will come back to thank yourself for your perseverance to have the brilliant achievements at this moment.

In the process of reviewing articles, if you think the articles are helpful to you at all, please don’t be too mean with your likes and bravely light up the articles 👍. Your likes (collect ⭐️+ pay attention to 👨 port + message board) are the best encouragement and support for bugs on my creation path. Time does not abandon 🏃🏻♀️, nuggets stop 💕, cheer up 🏻

2. Environment description 🔥

** SpringBoot 2.3.1 + Mybatis -plus3.2

3. Abstract 🔥

In daily development, it is inevitable to encounter complex business logic, so it is inevitable that some scenarios will be realized by handwritten SQL, that is, SQL associated query of multiple tables, there will be many cases, encounter paging scenarios? So how does paging work for custom SQL?

. .

Now, students present, I would like to invite you to speak.

A student could not bear to answer: “since all handwritten SQL, that simply put the pagination also in SQL implementation is not good, anyway mysql, Oracle and other databases support pagination syntax, is not it?”

Bug bacteria: “do not refute any ideas, but what you said is not the most convenient, there are other students have the way to achieve?” .

B students slowly raise their hands and say :” yes, manual paging, first check out the whole data and then according to the paging parameters manual points.”

Bug fungus: “Wonderful! Wonderful! Take it away. Next…”

Bug bacteria: “suddenly no students speak, is not scared by me, not it ~~~”.

. .

On the field past ten minutes, everyone is the lower the head to check information in abundance, that good, in fact you ask me good. I will not not teach you, do not understand ah, this is the fine traditional virtue of China.

4. Implementation scheme 🔥

In fact, we do not need to repeat the wheel, someone else MP provides a page plug-in to support custom SQL pages, but usually are rarely involved in complex SQL pages, so ordinary people may not understand, today I want to teach you is through MP to achieve, super convenient! Don’t beat me up with you…

Here are the implementation steps:

1. Pom introduces Mybatis- Plus dependency

Introduce mp dependencies in your project pom.xml, ignore them if you already have one.

<! --mybatis-plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> The < version > 3.2.0 < / version > < / dependency >Copy the code

Create MybatisPlusConfig config class

To create the MybatisPlusConfig configuration class, configure the paging plug-in.

/ * * @ * * * Configuration page plugin Author luoYong * @ Date and 2021-08-05 * / @ EnableTransactionManagement @ Configuration @MapperScan("com.example.review.dao") public class PageBeanConfig { @Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); / / set the request page after page is greater than the maximum operation, true transferred back to the home page, the default false false continue to request / / paginationInterceptor setOverflow (false); / / set the biggest single page limit an amount, the default 500-1 is not restricted. / / paginationInterceptor setLimit (500); return paginationInterceptor; }}Copy the code

3. Specific paging implementation

Here I will demonstrate in accordance with the example, anyway, students who need to learn can refer to it, the writing method is consistent.

XxxServiceImpl layer:

Define a query interface, and the first parameter must be the paging parameter. Then you need to manually create a Page(Long Current, long Size) class where the first parameter of the Page class is the current Page number and the second parameter is the Page size; The second is that the page page parameter should be placed first in your page method, followed by your other parameters, so that a page is good.

Specific examples are as follows:

2. Dao Layer:

The Dao layer defines the interface. The first Page parameter is Page, followed by other parameters in your SQL query, and the return type should also be Page object.

Specific examples are as follows:

Specific Ipage source we can also look at:

3. Test:

Check whether the page page parameter is set to the upper page parameter. See the following for details:

Then the breakpoint opens, waits for execution to complete, and we can look at the execution SQL printed by the console. You can obviously see the limit statement that automatically concatenates mysql at the end of the SQL execution. And the page page parameter is what we set.

4, Check swagger interface

Check the return of the interface to prove that the paging function is correct, so please rest assured to eat.

A classmate: “or the method that bug fungus teaches is practical, praise!” .

Student B :”” Much more convenient than my manual paging method, but also save trouble, love love.”

Bug bacteria old face a red, lift the door and go…

. .

Ok, that’s it. If you want to learn more, you can read my previous recommendations. Accumulate a little weird knowledge every day, and over a long period of time, you will definitely become a respected leader. Well, I’ll see you next time

Five, the previous recommendation 🔥

How to use Mybatis- Plus to automatically fill field contents? If not, I’ll teach you.
How to configure header request information in Swagger2? (If not, I’ll teach you by hand)
Springboot series (16) : Integrated easyPOI to realize word template circulation export multiple data
Springboot series (16) : Integrated easyPOI word template image export
Springboot series (16) : integrated easyPOI to achieve excel multi-sheet import
Springboot series (16) : integrated easyPOI to achieve excel multi-sheet export

Six, the end of the article 🔥

If you want to learn more, you can pay attention to the bug bug column “SpringBoot Zero-based Introduction”, from scratch, from zero to one! Hope I can help you.

I am bug fungus, a want to go 👣 out of the mountain to change the fate of the program ape. The next road is still very long, waiting for us to break through, to challenge. Come on, friends, let’s come on! Fighting for the future!

Finally, I like to send you two words, and you share!

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

☘️ Be who you want to be, there is no time limit, you can start whenever you want,

🍀 You can change from now on, you can also stay the same, this thing, there are no rules to speak of, you can live the most wonderful yourself.