Writing code like this improves efficiency by 100 times compared to directly using MyBatis
Tags: java, orm, beansearcher
Published: 2024-06-19 15:34:03

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)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/igfws29nko5iaka1feru.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/djemgtg3uavg2b3fe7gy.png)

## 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:

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

* Gradle:

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

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

```java
@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

```java
@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:

```json
{
    "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`)

```json
{
    "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 of`GreateThan`) 

### (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 of`LessThan`) 

### (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**?

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lwm19dm3ycuyzxdvytr4.png)

## 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:

```java
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:

```java
@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:

```java
@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:

```java
@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:

```java
@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:

```java
@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:

```java
@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):

```java
@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;
    // ...
}
```

3. 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:

```java
@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](https://github.com/troyzhxu/bean-searcher)** and **[Gitee](https://gitee.com/troyzhxu/bean-searcher)**.

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