Other Forms

In addition to the above multi-table associations, Bean Searcher also supports many complex SQL forms:

Select Subquery

@SearchBean(tables = "student s") 
public class Student {

    @DbField("s.name")
    private String name;

    // The total score of this student (Select subquery)
    @DbField("select sum(sc.score) from student_course sc where sc.student_id = s.id")
    private int totalScore;

    // ...
}

Where Subquery

@SearchBean(
    tables = "student s", 
    // Only query students whose average exam score is passing (Where subquery)
    where = "(select avg(sc.score) from student_course sc where sc.student_id = s.id) >= 60"
)
public class GoodStudent {

    @DbField("s.name")
    private String name;

    // ...
}

Distinct Deduplication

// Courses in which exams are taken
@SearchBean(
    tables = "student_course sc, course c", 
    where = "sc.course_id = c.id", 
    distinct = true                     // Deduplication
) 
public class ExamCourse {

    @DbField("c.id")
    private String courseId;

    @DbField("c.name")
    private String courseName;

    // ...
}

Group By and Aggregate Functions

@SearchBean(
    tables = "student_course sc", 
    groupBy = "sc.course_id"            // Group by course ID
) 
public class CourseScore {

    @DbField("sc.course_id")
    private long courseId;

    @DbField("sum(sc.score)")           // The total score of this course (Aggregate function: sum)
    private long totalScore;
    // ...
}

If there are fixed having conditions, they can be written here:

@SearchBean(
    tables = "student_course sc", 
    groupBy = "sc.course_id",           // Group by course ID
    having = "avg(sc.score) > 50"       // having condition  (since v3.8.0)
)

Field Alias (since v3.5.0)

By default, Bean Searcher generates a non-repeating alias for each field. Since v3.5.0, you can use the alias attribute of the @DbField annotation to manually specify an alias, for example:

@SearchBean(
    tables = "user u", 
    groupBy = "date"           // Group by alias
) 
public class UseData {

    @DbField("count(u.id)")
    private long count;        // The number of user registrations per day

    // Format the registration date to the day and specify the alias: date
    @DbField(value = "DATE_FORMAT(u.date_created, '%Y-%m-%d')", alias="date") 
    private String dateCreated;
    // ...
}

Note

Whether the alias can be used in the groupBy clause depends on the database being used. MySQL supports it, while Oracle may not.

Default Sorting (since v3.6.0)

Since v2.6.0, you can declare the default sorting rules in the entity class, for example:

@SearchBean(orderBy = "age desc, height asc")
public class User {
    // ...
}

When no sorting information is specified in the retrieval parameters, Bean Searcher will use the sorting information specified in the @SearchBean annotation for sorting.

Note

When the retrieval parameters also contain sorting information, the sorting information specified in the @SearchBean annotation may be overwritten. Refer to: Sorting Constraints.

See the Parameters > Sorting Parameters section.

Sorting Constraints (since v3.6.0)

Sometimes, we want the SQL generated by a retrieval entity class to be sorted only by a certain fixed field. That is, after we specify the Default Sorting, we do not want the retrieval parameters to modify it again. At this time, we can specify the sorting constraint type in the entity class:

@SearchBean(
    orderBy = "age desc",               // If this field is not specified, it means: disable sorting
    sortType = SortType.ONLY_ENTITY     // Specify that only the sorting information in the entity class will take effect, and the sorting information in the retrieval parameters will be ignored
)
public class User {
    // ...
}

Among them, SortType is an enumeration with three values:

• ONLY_ENTITY - Only use the sorting information in the entity class, and the sorting information in the retrieval parameters will be ignored.
• ALLOW_PARAM - Allow the sorting information in the retrieval parameters to overwrite the sorting information in the entity class.
• DEFAULT - (Default value) Depends on the Default Sorting Constraint of the retriever.

Configuring Default Sorting Constraints

The default sorting constraint is: ALLOW_PARAM, and you can also modify it.

SpringBoot / Grails

When using the bean-searcher-boot-starter dependency, you can configure it through the following key names:

Configuration Key Name	Meaning	Available Values	Default Value
bean-searcher.sql.default-mapping.sort-type	Default sorting constraint	ALLOW_PARAM, ONLY_ENTITY	ALLOW_PARAM

Non-Boot Spring Projects

<bean id="dbMapping" class="cn.zhxu.bs.implement.DefaultDbMapping">
    <property name="defaultSortType" /> 
        <util:constant static-field="cn.zhxu.bs.bean.SortType.ONLY_ENTITY"/>
    </property>
</bean>
<bean id="metaResolver" class="cn.zhxu.bs.implement.DefaultMetaResolver">
    <property name="dbMapping" ref="dbMapping" />
</bean>
<bean id="mapSearcher" class="cn.zhxu.bs.implement.DefaultMapSearcher">
    <!-- Other attribute configurations are omitted. The BeanSearcher retriever is configured in the same way. -->
    <property name="metaResolver" ref="metaResolver" />
</bean>

Other Frameworks

DefaultDbMapping dbMapping = new DefaultDbMapping();
dbMapping.setDefaultSortType(SortType.ONLY_ENTITY);               // Configure the default inheritance type here
MapSearcher mapSearcher = SearcherBuilder.mapSearcher()
        // Other configurations are omitted.
        .metaResolver(new DefaultMetaResolver(dbMapping))       // The BeanSearcher retriever is configured in the same way.
        .build();