Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "how SpringDataJpa uses union multi-table paging condition query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

How to use union multi-table paging condition query

The current project has a global search function, which needs to involve 7-8 master tables. Because the current project uses Jpa, I decided to use union to query in the way of native sql. Because I am not familiar with this framework, I have been cheated for a long time.

Conditions

1. In the parameter list, use @ Param annotation (for example: @ Param ("userId") Long userId)

2. In sql, you should inject parameters in the way of: userId (for example: person.user_id =: userId), or use? 1? 2 to inject parameters according to the order of parameters. I use the first method.

3. Sql should be written in the value of @ Query, and set nativeQuery = true

4. If you want to set a value that may be empty, for example: there is a classification, you need to query as a condition, but this value may be empty, so you need to judge in sql. If it is empty, you will not filter. The syntax is actually the same as judging in sql.

Where IF (: category is not null,a.category =: category is not null,1=1) pagination

1. The parameter countQuery must be added for paging, and the above sql price can be count.

2. Pass Pageable instead of sort, otherwise an error will be reported and no solution has been found for the time being.

For example, select * from an is your sql

Then the paging should be

Select * from (select * from a) b order by? # {# pageable}

There is no need to add order by in countQuery.

3. Page is returned, and the order of content encapsulation in it is the order found by your sql.

Stepped on the pit

1. For a multi-table union query, the query fields should be the same. For example, the first user table must query id,name,sex, and the second address table must also query id,name,sex. If there is no such field, the fixed value will be encapsulated and can be returned as an empty string.

2. When entering pageable? # {# pageable} must be written in this way.

Share a few mysql grammars used

1. Determine whether it is empty, not empty query time between the incoming time of the data

IF (: startTime is not null and: endTime is not null,proc.create_time between: startTime and: endTime,1=1)

Here I judge whether the end time is empty in service. If the end time is empty, the start time is not empty to query the data of one year by default.

2. Fuzzy query according to name

Proc.content LIKE CONCAT ('%',: content,'%')

3. Finally, the types are screened according to the total data.

IF (: category is not null,a.category =: category is not null,1=1)

A brief summary:

1. In fact, a very simple paging condition query, because not familiar with the syntax of jpa, resulting in a lot of time wasted, hoping to help more people.

2, temporarily do not know how to sort, whether it is passed into sort in pageable, or in the final sorting of sql will report an error, if a boss knows, you can click.

Jpa executes native sql union bug solution @ Query (value = "SELECT * FROM count_entity where id=-1111 union (SELECT * FROM count_entity where video_name =? 1 AND platform = 'Tencent Video' ORDER BY click_date DESC LIMIT? 2)" + "UNION" + "(SELECT * FROM count_entity where video_name =? 1 AND platform = 'iqiyi' ORDER BY click_date DESC LIMIT? 2)" + "UNION" + "(SELECT * FROM count_entity where video_name =? 1) AND platform = 'mango TV' ORDER BY click_date DESC LIMIT? 2) " NativeQuery = true)

You need to connect an empty data query in front of union because the first query of union cannot use ()

This is the end of the content of "how SpringDataJpa uses union multi-table paging condition query". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.