I Was Tormented by List Queries for 7 Years, Until One Day I Realized It's Not an ORM Problem!

Author: 遇见代码 Tags: Spring Boot, GraphQL, Java

A backend engineer's confession: Why you write 100 lines of Java code, when you're really only doing one thing — assembling a few parameters passed from the frontend into a single SQL statement.

---

A One-Page Requirement

Product manager Xiao Wang sent me a prototype. A very ordinary backend admin page:

• Paginated table display
• Four filter conditions at the top: name, age range, department, entry date
• Sortable by any column
• Footer statistics: total count, average age

"This is simple, right? Can it go live this afternoon?" he asked.

I said: "Yes."

Then I silently opened IDEA and started coding.

---

If You Also Use MyBatis, Here's What You'll Do in the Next 30 Minutes

// First, you need to write a dynamic SQL
<select id="searchUsers" resultType="UserVO">
    SELECT u.*, d.name as deptName
    FROM user u LEFT JOIN dept d ON u.dept_id = d.id
    <where>
        <if test="name != null and name != ''">
            AND u.name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="ageMin != null">
            AND u.age >= #{ageMin}
        </if>
        <if test="ageMax != null">
            AND u.age &lt;= #{ageMax}
        </if>
        <if test="dept != null and dept != ''">
            AND d.name = #{dept}
        </if>
        <if test="entryDateStart != null">
            AND u.entry_date >= #{entryDateStart}
        </if>
        <if test="entryDateEnd != null">
            AND u.entry_date &lt;= #{entryDateEnd}
        </if>
    </where>
    <if test="sortField != null and sortOrder != null">
        ORDER BY ${sortField} ${sortOrder}
    </if>
    LIMIT #{offset}, #{size}
</select>

Not done yet. You also need a count SQL, a Controller method to parse parameters, a Service layer for pagination calculation, a Mapper interface, a VO class specifically to hold query results — and you must be careful about SQL injection with ${}.

The requirement only mentioned four filter conditions, and the code is already nearly 100 lines.

Then Xiao Wang says: "Oh right, change the department filter to support multi-select. Also, add a gender filter."

You take a deep breath and keep writing.

This isn't MyBatis's problem — switching to JPA won't make it any easier:

Specification<User> spec = (root, query, cb) -> {
    List<Predicate> predicates = new ArrayList<>();
    // Join: to query department name, first JOIN the department table
    Join<User, Department> deptJoin = root.join("dept", JoinType.LEFT);

    if (StringUtils.isNotBlank(name)) {
        predicates.add(cb.like(root.get("name"), "%" + name + "%"));
    }
    if (ageMin != null) {
        predicates.add(cb.ge(root.get("age"), ageMin));
    }
    if (ageMax != null) {
        predicates.add(cb.le(root.get("age"), ageMax));
    }
    if (StringUtils.isNotBlank(dept)) {
        predicates.add(cb.equal(deptJoin.get("name"), dept));
    }
    // ... the same if hell, just with different syntax
    return cb.and(predicates.toArray(new Predicate[0]));
};
// Besides this, there's sorting, pagination, total count, field statistics — each one requires you to keep piling up code...

MyBatis writes <if> in XML, JPA assembles Predicate in Java. The essence hasn't changed — you are still manually translating parameters into query conditions.

The only reason this problem exists is that you've mixed "declaration" and "execution" together.

---

A Different Mindset

Let's look at this problem from a different angle.

The database tables are already defined. What the frontend wants to query is up to the user.

So why does the translation work in the middle — converting HTTP parameters into SQL — require you to write if-else line by line?

Is it possible to have a smart middle layer handle this? It knows:

• What fields your entity has → this is the search boundary
• What parameters the frontend passed → this is the search intent
• Combine the two → generate SQL

This idea isn't my invention. In the API world, it has a thunderous name:

GraphQL — the client specifies what fields it wants, the server returns those fields. One request replaces multiple REST calls.

So in the REST API list query domain, can there be something similar?

Dimension	GraphQL	Bean Searcher
Domain	API data query	Database list retrieval
What the client controls	Which fields to return	Which fields to return + what to filter by + what to sort by + how much to paginate
Protocol	POST + GraphQL body	Standard HTTP parameters (GET/POST both work)
Core idea	Declare what data you want	Declare search boundaries, parameter-driven queries
Integration cost	Modify API layer, add Schema	One dependency, zero code changes

In one sentence: GraphQL lets the frontend freely control returned data in one request; Bean Searcher lets the frontend freely control filtering, sorting, pagination, and statistics in one request — using REST's most familiar URL parameter approach.

---

The REST Version of GraphQL for List Retrieval

You only need to define one entity:

@SearchBean(tables = "user u, dept d", where = "u.dept_id = d.id", autoMapTo = "u")
public class UserVO {
    private Long id;
    private String name;
    private Integer age;
    private String gender;
    @DbField("d.name")
    private String deptName;
    private LocalDate entryDate;
    // getters & setters...
}

Then, the entire search endpoint is one line of code:

@GetMapping("/user/search")
public SearchResult<UserVO> search() {
    return beanSearcher.search(UserVO.class, UserVO::getAge);
}

The frontend directly sends a GET request:

# Query users with surname Zhang, sorted by age
GET /user/search? name=张 & name-op=sw & sort=age & order=desc

The data returned by this one line of code looks like this:

{
  "dataList": [
    { "id": 1, "name": "张三", "age": 25, "deptName": "技术部", "entryDate": "2023-03-01" },
    { "id": 2, "name": "张小明", "age": 28, "deptName": "产品部", "entryDate": "2022-11-15" }
  ],
  "totalCount": 47,
  "summaries": [ 1350 ]
}

Pagination, joins, multi-condition filtering, sorting, statistics — all handled by one endpoint. No XML. No if-else. No VO conversion code.

This is Bean Searcher — a framework I've used for three years and can't help but recommend to every backend engineer.

---

What It Actually Does

Let me explain its core principle in one sentence, because once you understand this, you'll understand why it can save 90% of the code:

The entity class declares the search boundary, HTTP parameters drive the query logic.

	Traditional approach (MyBatis / JPA)	Bean Searcher
How filter conditions are defined	Write <if> in XML / assemble QueryWrapper in Java	Parameter names directly map to field names
Adding a new filter condition	Modify XML / modify Java code → recompile and deploy	Frontend directly passes new parameters, zero backend changes
Multi-table joins	Hand-write JOIN SQL	Entity class declares join relationships
Return results	Need a VO conversion layer	SearchBean is the VO
Security	Write your own validation	Anti-injection, anti-large-page, anti-deep-offset, all enabled by default

An analogy: The traditional way is "imperative" — you tell the framework what to do step by step. Bean Searcher is "declarative" — you declare "what can be searched" (entity defines boundaries), then the frontend expresses "what it wants to search" through parameters (driving query logic).

You're not writing queries. You're declaring search boundaries.

---

A Question That Will Be Asked

"Won't this let the frontend pass too many parameters?"

I've been asked this question countless times. The answer is: How many parameters the frontend passes is only related to the complexity of the product requirements, and has nothing to do with what framework the backend uses.

If the product only needs a single fuzzy search box, the frontend only passes ?name=张, no need for name-op and name-ic. You can even use it with zero annotations — a plain POJO, field names default-mapped to database column names (camelCase to snake_case).

Remember one thing: Annotations are for constraining and fine-grained control, not mandatory. A single-table entity needs no annotations at all — it's inherently searchable.

---

Is It an Enemy of MyBatis/JPA?

Absolutely not.

MyBatis handles CRUD, Bean Searcher handles list queries. Each does its own job, coexisting harmoniously.

Just as GraphQL wasn't born to replace REST — it just makes API queries more flexible. Bean Searcher isn't meant to replace MyBatis — it just makes list retrieval no longer painful.

	When to use
MyBatis / JPA	CRUD, transactional operations, complex business logic
Bean Searcher	Backend admin lists, data export, report queries, any "multi-condition dynamic filtering" scenario
Their relationship	Complementary, not a replacement. Just add one dependency.

---

Real Project Experience

I've deeply used Bean Searcher in over a dozen projects (Spring Boot 2/3/4 and Solon 3/4 respectively). The biggest feeling isn't "less code" — it's that the way of thinking changed.

Before, when receiving a list query requirement, my mind would think "how to write this SQL, how to assemble parameters, how to handle sorting, what object to pass for pagination."

Now, when receiving a list query requirement, my mind thinks "what fields does this page need, which tables do they come from, which fields allow frontend filtering."

You are no longer an SQL assembler. You are a domain modeler.

And when you share this experience with colleagues, their first reaction is usually: "Isn't this just... the Java backend version of GraphQL?"

"Yes."

---

Give It a Try

If you've read this far and realize the pain points described above are what you experience every day — you should give it a try.

No need to refactor the project, no need to replace the ORM, no need to change any architecture. It's a completely non-intrusive framework that can coexist with MyBatis/JPA/Spring Data JDBC.

• 📖 Full Documentation
• 🖥 Online Demo (Zero Deployment Experience)
• ⭐ GitHub ｜ Gitee

If you think this thing really solves your pain points, give it a Star, and let more Java engineers tormented by list queries see it.

After all — spending your life writing if-else is not as good as spending it on more valuable things.