For a Java backend programmer, MyBatis, Hibernate, Data Jdbc, and others are commonly used ORM frameworks. They are sometimes very useful, such as simple CRUD and excellent transaction support. But sometimes it can be very cumbersome to use, such as a common development requirement that we will talk about next. For this type of requirement, this article will provide a method that can improve development efficiency by at least 100 times compared to directly using these ORMs (without exaggeration).

Firstly, the database has two tables

User Table: (For simplicity, assume there are only 4 fields)

Role table: (For simplicity, assume there are only 2 fields)

Next, we need to implement a user query function

This query is a bit complex, and its requirements are as follows:

• Can be queried by the username field, with the following requirements:
  • Can be accurately matched (equal to a certain value)
  • Fully fuzzy matching (including given values)
  • Post fuzzy query (starting with...)
  • Pre fuzzy query (ending with...)
  • Can you specify whether the above four matches can ignore case
• Can be queried by the age field, with the following requirements:
  • Can be accurately matched (equal to a certain age)
  • Can be greater than matching (greater than a certain value)
  • Can be less than matching (less than a certain value)
  • Interval matching (within a certain range of intervals)
• Can be queried by roleId, with the requirement of precise matching
• Can be queried by 'userId', requirement: same as 'age' field
• You can specify which columns to output only (for example, only query the id and username columns)
• Support pagination (after each query, the page should display the total number of users who meet the conditions)
• When querying, you can choose to sort by any field such as id, username, age, etc

How should the backend interface be written?

Imagine, for this type of query, if the code in the backend interface is written directly using MyBatis, Hibernate or Data Jdbc, can it be completed within 100 lines of code ?

Anyway, I don't have the confidence. Forget it, I'll just be honest. How can I handle this kind of requirement with just one line of code on the backend? (Interested students can try MyBatis and compare it in the end)

Only one line of code is used to implement the above requirements

First of all, the key figure has appeared: Bean Searcher, which is a read-only ORM that focuses on advanced queries. For this type of list retrieval, whether it is simple or complex, it can be done in one line of code! And it is also very lightweight and has no third-party dependencies (can be used in the same project as any other ORM).

Assuming that the framework we are using in our project is Spring Boot (Of course, Bean Searcher does not have any special requirements for web frameworks, but it is more convenient to use in Spring Boot).

Add Dependency

• Maven:

<dependency>
    <groupId>cn.zhxu</groupId>
    <artifactId>bean-searcher-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>

• Gradle:

implementation 'cn.zhxu:bean-searcher-boot-starter:4.3.0'

Then write an entity class to carry the results of the query

@SearchBean(tables="user u, role r", where="u.role_id = r.id", autoMapTo="u") 
public class User {
    private Long id;        // User ID (u.id)
    private String name;    // User Name (u.name) 
    private int age;        // Age (u.age) 
    private int roleId;     // Role ID (u.role_id) 
    @DbField("r.name")      // Indicates that this attribute comes from the name field of the role table
    private String role;    // Role Name (r.name) 
    // Getter and Setter ...
}

Note: This entity class is mapped to two tables and can be directly returned to the front-end

Then we can write the user query interface

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private MapSearcher mapSearcher;  // injection searcher (provided by bean-searcher-boot-starter)

    @GetMapping("/index")
    public SearchResult<Map<String, Object>> index(HttpServletRequest request) {
        // Here we only write one line of code
        return mapSearcher.search(User.class, MapUtils.flat(request.getParameterMap()));
    }

}

The MapUtils in the above code is a tool provided by Bean Searcher, while MapUtils.flat(request. getParameterMap()) is only used to collect the request parameters passed from the front-end, and the rest is handed over to the MapSearcher.

Is that all? Let's test this interface and see the effect

(1) No parameter request

• GET /user/index
• Return result:

{
    "dataList": [      // User list, returns page 0 by default, with a default page size of 15 (configurable)
        { "id": 1, "name": "Jack", "age": 25, "roleId": 1, "role": "VIP" },
        { "id": 2, "name": "Tom", "age": 26, "roleId": 1, "role": "VIP" },
        ...
    ],
    "totalCount": 100  // Total number of users
}

(2) Paging request (page | size)

• GET /user/index? page=2 & size=10
• Return result: The structure is the same as (1) (only 10 items per page, with page 2)

The parameter names size and page can be customized, with page starting from 0 by default. They can also be customized and can be used in combination with other parameters.

(3) Data sorting (sort | order)

• GET /user/index? sort=age & order=desc
• Return result: The structure is the same as (1) (except that the dataList is output in descending order of the age field)

The parameter names sort and order are customizable and can be used in combination with other parameters.

(4) Specify (exclude) fields (onlySelect | selectExclude)

• GET /user/index? onlySelect=id,name,role
• GET /user/index? selectExclude=age,roleId
• Return result: (The list only contains three fields: id, name, and role)

{
    "dataList": [      // User list, returns page 0 by default (only containing id, name, role fields)
        { "id": 1, "name": "Jack", "role": "VIP" },
        { "id": 2, "name": "Tom", "role": "VIP" },
        ...
    ],
    "totalCount": 100  // Total number of users
}

The parameter names onlySelect and selectExclude are customizable and can be used in combination with other parameters.

(5) Field filtering (op = eq)

• GET /user/index? age=20
• GET /user/index? age=20 & age-op=eq
• GET /user/index? age-eq=20 Simplified writing, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with age=20)

The parameter age-op=eq represents the field operator of age, which is eq(abbreviation for Equal), indicating that the relationship between parameter age and parameter value 20 is Equal. Since Equal is a default relationship, age-op=eq can also be omitted.
The suffix -op for the parameter name age-op is customizable and can be used in combination with other field parameters and the parameters listed above (pagination, sorting, specified fields). The same applies to the field parameters listed below and will not be repeated.

(6) Field filtering (op = ne)

• GET /user/index? age=20 & age-op=ne
• GET /user/index? age-ne=20 Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with age != 20, where ne is an abbreviation for NotEqual).

(7) Field filtering (op = ge)

• GET /user/index? age=20 & age-op=ge
• GET /user/index? age-ge=20 Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with age >= 20, where ge is the abbreviation of GreateEqual)

(8) Field filtering (op = le)

• GET /user/index? age=20 & age-op=le
• GET /user/index? age-le=20 Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with age <= 20, where le is the abbreviation of LessEqual)

(9) Field filtering (op = gt)

• GET /user/index? age=20 & age-op=gt
• GET /user/index? age-gt=20 Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with age > 20, where gt is the abbreviation ofGreateThan)

(10) Field filtering (op = lt)

• GET /user/index? age=20 & age-op=lt
• GET /user/index? age-lt=20 Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with age < 20, where lt is the abbreviation ofLessThan)

(11) Field filtering (op = bt)

• GET /user/index? age-0=20 & age-1=30 & age-op=bt
• GET /user/index? age=[20,30] & age-op=bt (Simplified version，[20,30] requires UrlEncode, refer to the following text)
• GET /user/index? age-bt=[20,30] Simplify again, refer to：https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with 20 <= age <= 30, where bt is the abbreviation of Between)

The parameter age-0 = 20 indicates that the 0th parameter value of age is 20. The above-mentioned age=20 is actually a shortened form of age-0=20. Additionally, the hyphen - in the parameter names age-0 and age-1 can be customized.

(12) Field filtering (op = il)

• GET /user/index? age-0=20 & age-1=30 & age-2=40 & age-op=il
• GET /user/index? age=[20,30,40] & age-op=il (Simplified version，[20,30,40] requires UrlEncode, refer to the following text)
• GET /user/index? age-il=[20,30,40] Simplify again, refer to：https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with age in (20, 30, 40), where il is the abbreviation of InList)

(13) Field filtering (op = ct)

• GET /user/index? name=Jack & name-op=ct
• GET /user/index? name-ct=Jack Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with name contains Jack, where ct is the abbreviation of Contain)

(14) Field filtering (op = sw)

• GET /user/index? name=Jack & name-op=sw
• GET /user/index? name-sw=Jack Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with name staring with 'Jack', where sw is the abbreviation of StartWith)

(15) Field filtering (op = ew)

• GET /user/index? name=Jack & name-op=ew
• GET /user/index? name-ew=Jack Simplified version, reference: https://bs.zhxu.cn/guide/advance/filter.html#suffixopparamfilter
• Return result: The structure is the same as (1) (but only returns data with name ending with 'Jack', where ew is the abbreviation of EndWith)

(16) Ignoring case (ic = true)

• GET /user/index? name=Jack & name-ic=true
• Return result: The structure is the same as (1) (but only returns data with name equal with Jack(ignore case), where ic is the abbreviation of IgnoreCase)

The suffix -ic in the parameter name name-ic is customizable and can be used in combination with other parameters. For example, when retrieving a name equal to Jack, case is ignored, but it is also applicable when retrieving a name starting or ending with Jack, case is ignored.

More search methods are also supported, and we will not provide examples here. To learn more, please refer: https://bs.zhxu.cn/guide/param/field.html#%E5%AD%97%E6%AE%B5%E8%BF%90%E7%AE%97%E7%AC%A6

Of course, all of the above conditions can be combined.

Such as: Query name starting with 'Jack' (ignoring case), roleId=1, results sorted by id field, loading 10 entries per page, query page 2:

• GET /user/index? name=Jack & name-op=sw & name-ic=true & roleId=1 & sort=id & size=10 & page=2
• Return result: The structure is the same as (1)

In fact, Bean Searcher also supports more search methods (even customizable), so we won't list them all here.

OK， After seeing the effect, we have only written one line of code in the GET /user/index interface, which can support so many retrieval methods. Do you think that now you can write a single line of code can be equivalent to someone else's 100 lines?

Bean Searcher

In this example, we only used one retrieval method from the MapSearcher retriever provided by Bean Searcher, which actually has many other retrieval methods.

Retrieval methods

• searchCount(Class<T> beanClass, Map<String, Object> params) Query the total number of data under specified conditions
• searchSum(Class<T> beanClass, Map<String, Object> params, String field) Query the statistical value of a certain field under specified conditions
• searchSum(Class<T> beanClass, Map<String, Object> params, String[] fields) Query the statistical values of multiple fields under specified conditions
• search(Class<T> beanClass, Map<String, Object> params) Paging query List data and Total number of entries under specified conditions
• search(Class<T> beanClass, Map<String, Object> params, String[] summaryFields) Same as above + multi field statistics
• searchFirst(Class<T> beanClass, Map<String, Object> params) Query the first data under specified conditions
• searchList(Class<T> beanClass, Map<String, Object> params) Paging Query List data under specified conditions
• searchAll(Class<T> beanClass, Map<String, Object> params) Query a list of all data under specified conditions

MapSearcher and BeanSearcher

In addition, Bean Searcher provides not only a 'MapSearcher' retriever, but also a 'BeanSearcher' retriever, which also has all the methods of 'MapSearcher'. However, the single data it returns is not a 'Map', but a generic object.

Parameter construction tool

Additionally, if you are using Bean Searcher in a Service, using parameters of type Map<String, Object> directly may not be elegant. Therefore, Bean Searcher specifically provides a parameter construction tool.

For example, if the query name starts with 'Jack' (ignoring case) and roleId=1, and the result is sorted by the id field, load 10 entries per page, load page 2, and use a parameter builder, the code can be written as follows:

Map<String, Object> params = MapUtils.builder()
        .field(User::getName, "Jack").op(Operator.StartWith).ic()
        .field(User::getRoleId, 1)
        .orderBy(User::getId).asc()
        .page(2, 10)
        .build()
List<User> users = beanSearcher.searchList(User.class, params);

The BeanSearcher retriever and its' searchList (Class<T> beanClass, Map<String, Object> params) method are used here.

Operator constraints

As we saw earlier, Bean Searcher directly supports many retrieval methods for each field in the entity class.

But a classmate: Oh my! There are too many search methods, I don't need so many at all. My data volume is billions, and the fuzzy query method before the username field cannot utilize the index. What if my database crashes?

Easy to handle, Bean Searcher supports operator constraints, and the name field of the entity class only needs to be annotated:

@SearchBean(tables="user u, role r", where="u.role_id = r.id", autoMapTo="u") 
public class User {
    @DbField(onlyOn = {Equal.class, StartWith.class})
    private String name;
    // ...
}

By using the onlyOn attribute of @DbField, it is specified that the name field can only be used for Equal and StartWith operators, and other operators will be ignored directly.

The above code restricts name to only two operators. If it is stricter and only allows precise matching, there are actually two ways to write it.

(1) use operator constraints:

@SearchBean(tables="user u, role r", where="u.role_id = r.id", autoMapTo="u") 
public class User {
    @DbField(onlyOn = Equal.class)
    private String name;
    // ...
}

(2) Overwrite operator parameters in the method of Controller:

@GetMapping("/index")
public SearchResult<Map<String, Object>> index(HttpServletRequest request) {
    Map<String, Object> params = MapUtils.flatBuilder(request.getParameterMap())
        .field(User::getName).op(Operator.Equal)   // Overwrite the operator of the name field directly to Equal
        .build()
    return mapSearcher.search(User.class, params);
}

Conditional constraints

The student said also: Oh my! My data volume is still very large, and the age field has no index. I don't want it to participate in the where condition, otherwise it is likely to cause slow SQLs!

Don't worry, Bean Searcher also supports conditional constraints, making this field directly unavailable as a condition:

@SearchBean(tables="user u, role r", where="u.role_id = r.id", autoMapTo="u") 
public class User {
    @DbField(conditional = false)
    private int age;
    // ...
}

By using the conditional attribute of @DbField, the age field is directly not allowed to participate in the condition. No matter how the frontend passes the parameter, the Bean Searcher always ignores it.

Parameter filter

The student still said: Oh my! Oh my...

Don't be afraid Bean Searcher also supports configuring global parameter filters and can customize any parameter filtering rules. In the SpringBoot project, only one bean needs to be declared:

@Bean
public ParamFilter myParamFilter() {
    return new ParamFilter() {
        @Override
        public <T> Map<String, Object> doFilter(BeanMeta<T> beanMeta, Map<String, Object> paraMap) {
            // beanMeta is the meta information of the entity class being retrieved, and paraMap is the current retrieval parameters
            // TODO: Here you can write some custom parameter filtering rules
            return paraMap;      // Returns the filtered search parameters
        }
    };
}

Another classmate asked

Why are the parameters so strange? With so many parameters, is there any grudge against the front-end?

1. Whether the parameter name is strange or not depends on personal preference. If you don't like the hyphen -, the suffix op, or ic, you can completely customize it. Please refer to this document: https://bs.zhxu.cn/guide/param/field.html

2. The number of parameters is actually related to the complexity of the product requirements. If the requirements are very simple, then many parameters do not need to be sent from the front-end, and the back-end can simply plug them in. For example, if name only requires post fuzzy matching and age only requires interval matching, then it can:
   

@GetMapping("/index")
public SearchResult<Map<String, Object>> index(HttpServletRequest request) {
    Map<String, Object> params = MapUtils.flatBuilder(request.getParameterMap())
        .field(User::getName).op(Operator.StartWith)
        .field(User::getAge).op(Operator.Between)
        .build()
    return mapSearcher.search(User.class, params);
}

This way, the front-end does not need to sent the name-op and age-op parameters.

There is actually a simpler method, which is the operator constraint (when the constraint exists, the operator defaults to the first value specified in the onlyOn attribute, which can be omitted from the frontend):

@SearchBean(tables="user u, role r", where="u.role_id = r.id", autoMapTo="u") 
public class User {
    @DbField(onlyOn = Operator.StartWith)
    private String name;
    @DbField(onlyOn = Operator.Between)
    private String age;
    // ...
}

1. For multi valued parameter passing with op=bt/il, parameters can indeed be simplified, for example:

• Simplify age-0=20 & age-1=30 & age-op=bt to age=[20,30] & age-op=bt and further simplify it to age-bt=[20,30];
• Simplify age-0=20 & age-1=30 & age-2=40 & age-op=il to age=[20,30,40] & age-op=il and further simplify it to age-il=[20,30,40].

Simplification method: Just enable one configuration, please refer here:

• https://bs.zhxu.cn/guide/advance/filter.html#jsonarrayparamfilter

The input parameter is a request, but the Swagger document is not easy to render

In fact, the retriever of Bean Searcher only requires a parameter of type Map<String, Object>, and how this parameter is obtained is not directly related to Bean Searcher. The reason why I use request is because it makes the code look concise. If you like to declare parameters, you can write the code as follows:

@GetMapping("/index")
public SearchResult<Map<String, Object>> index(Integer page, Integer size, 
            String sort, String order, String name, Integer roleId,
            @RequestParam(value = "name-op", required = false) String name_op,
            @RequestParam(value = "name-ic", required = false) Boolean name_ic,
            @RequestParam(value = "age-0", required = false) Integer age_0,
            @RequestParam(value = "age-1", required = false) Integer age_1,
            @RequestParam(value = "age-op", required = false) String age_op) {
    Map<String, Object> params = MapUtils.builder()
        .field(Employee::getName, name).op(name_op).ic(name_ic)
        .field(Employee::getAge, age_0, age_1).op(age_op)
        .field(Employee::getRoleId, roleId)
        .orderBy(sort, order)
        .page(page, size)
        .build();
    return mapSearcher.search(User.class, params);
}

The relationship between field parameters is "and", what about "or"? And any combination of "or" and "and"?

As for "or", although there are not many usage scenarios, Bean Searcher still supports it (and it is very convenient and powerful). For more details, please refer:

• https://bs.zhxu.cn/guide/param/group.html

I won't repeat it here.

Are the values of parameters such as sort and onlySelect in the previous text the field names of the data table, and is there a risk of SQL injection?

You can completely be at ease on it. SQL injection, such a low-level error, was already avoided at the beginning of framework design. The values of parameters such as sort and onlySelect are all attribute names of the entity class (rather than fields in the data table). When the user passes a value that is not a certain attribute name, the framework will automatically ignore them, and there is no injection problem.

Not only that, Bean Searcher also comes with pagination protection function to ensure the security of your service, which can effectively block malicious large page requests from clients.

Has development efficiency really increased by 100 times?

From this example, it can be seen that the degree of efficiency improvement depends on the complexity of the retrieval requirements. The more complex the demand, the higher the efficiency improvement. Conversely, the lower the efficiency improvement. If the demand is super complex, it is possible to increase it by 1000 times.
But even if we don't have such complex requirements in our daily development, and the development efficiency only improves by 3 to 5 times, is that still very impressive?

Conclusion

This article introduces the powerful capabilities of Bean Searcher in the field of complex list retrieval. The reason why it can greatly improve the development efficiency of such requirements is fundamentally attributed to its original dynamic field operator and multi table mapping mechanism, which is not available in traditional ORM frameworks. However, due to space limitations, its characteristics cannot be fully described in this article, for example, it also:

• Support aggregation queries
• Support Select|Where|From sub queries
• Support entity class embedding parameters
• Support parameter grouping and logic optimization
• Support Field Converter
• Support Sql interceptor
• Support Database Dialect extension
• Support multiple datasources
• Support custom operators
• Support custom annotations
• And so on..

To learn more, pleast star it on Github and Gitee.

Detailed documentation: https://bs.zhxu.cn