SpringBoot集成Bean Searcher实现各种复杂查询

青莳吖

已于 2023-04-27 16:06:36 修改

阅读量1.1k

点赞数

于 2023-01-05 17:39:52 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_44758923/article/details/128567423

版权

导入依赖

		<dependency>
            <groupId>com.ejlchina</groupId>
            <artifactId>bean-searcher-boot-starter</artifactId>
            <version>3.8.2</version>
        </dependency>

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

实体类

import com.ejlchina.searcher.bean.DbField;
import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

/**
 * @author qingshi
 * @date 2023/1/5 8:53
 * info:
 */
@Data
@Table
@Entity
public class Student implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @DbField("user_name")//如果实体类的字段和数据库不匹配，就需要通过这个注解指定一下
    @Column(name = "user_name",columnDefinition = "varchar(100) comment '姓名'") // 若实体属性和表字段名称一致时，可以不用加@Column注解
    private String name;

    @Column(name = "sex")
    private int sex;

    @Column(name = "grade")
    private String grade;

    @Column(name = "age",columnDefinition = "varchar(2) comment '性别'")
    private String age;
}

测试

	@Autowired
	private BeanSearcher beanSearcher;
	
    void getSearch(){
        Map<String, Object> params = MapUtils.builder()
                .page(0,10)                                    //第1页10条
                .orderBy(Student::getAge).desc()               // age 字段，降序
                .onlySelect(Student::getName,Student::getAge)  //只查询 age 与 name 字段
                .field(Student::getName).op("小红")             //查询 name 等于 小红 的用户
                .field(Student::getName).op(NotEmpty.class)    //查询 name 不为空的用户
                .field(Student::getName, "小红").op(Contain.class)    // 查询 name 中包含字符串 小红 的用户
                .build();
        SearchResult<Student> search = beanSearcher.search(Student.class, params);
        List<Student> dataList = search.getDataList();        //获取数据列表
        Number totalCount = search.getTotalCount();           //获取数据总数
        System.out.println(dataList);
        System.out.println(totalCount);
    }

将bean-searcher日志转换成sql

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqlParser {
    public static void main(String[] args) {
        String log = "2023-04-27 16:01:32.562 [http-nio-8080-exec-3] DEBUG cn.zhxu.bs.implement.DefaultSqlExecutor:249 - bean-searcher [2ms] sql: [select distinct CONCAT( LPAD( 0 + CAST(product.sequence_no AS CHAR), 3, '0' ), ' - ', IFNULL( IFNULL( product.production_name, product.foreign_name ), product.temporary_name)) c_32, COUNT(*) c_0, IF(!ISNULL( chapter.sequence_no ), CONCAT( FORMAT( MIN( chapter.sequence_no ), 0, '' ), '~', FORMAT( MAX( chapter.sequence_no ), 0 )),NULL) c_1, task_settle.unit_price c_2, task_settle.calc_count c_3, task_settle.calc_unit c_4, task_settle.result_price c_5 from mx_production_chapter_task task left join mx_production product on task.production_id = product.production_id left join mx_platform platform on product.platform_id = platform.platform_id left join mx_production_chapter chapter on task.chapter_id = chapter.chapter_id left join mx_stage stage on task.stage_id = stage.stage_id left join mx_task_kpi kpi on task.task_id = kpi.task_id LEFT JOIN mx_task_settle task_settle ON task.task_id = task_settle.task_id where (task.del_flag = 0 AND product.del_flag = 0 AND platform.del_flag = 0 AND chapter.del_flag = 0  AND CONCAT( LPAD( 0 + CAST(product.sequence_no AS CHAR), 3, '0' ), ' - ', IFNULL( IFNULL( product.production_name, product.foreign_name ), product.temporary_name)) IS NOT NULL) and ((stage.stage_name = ?) and (platform.platform_name = ?) and (task.task_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))) group by CONCAT( LPAD( 0 + CAST(product.sequence_no AS CHAR), 3, '0' ), ' - ', IFNULL( IFNULL( product.production_name, product.foreign_name ), product.temporary_name)) order by platform.sequence_no asc, product.sequence_no asc, chapter.sequence_no asc, stage.sequence_no asc, DATE_FORMAT(task.deadline,'%Y-%m-%d') asc] params: [监制, KW简转繁, 84897, 97440, 97453, 119437, 44131, 44144, 44157, 44170, 44183, 44196, 44209, 44222, 109852, 119536, 126714, 44136, 44149, 44162, 44175, 44188, 44201, 44214, 44227, 109857, 119541, 126719, 84798, 97370, 84803, 97375, 119445, 119450, 119367, 119372, 70491, 97764, 119133, 70496, 97769, 119138, 61149, 61154, 70543, 119510, 119523, 70548, 119528, 57660, 61071, 70425, 84947, 84960, 84973, 97305, 119172, 57665, 61076, 70430, 84952, 84965, 84978, 97310, 119177, 60980, 60993, 64330, 64343, 97409, 97422, 119406, 119419, 24820, 24846, 24859, 24872, 119549, 60985, 60998, 64335, 64348, 97414, 97427, 119411, 119424, 43988, 70380, 70393, 84811, 97383, 97396, 119094, 24825, 24851, 24864, 24877, 119554, 59027, 61045, 61058, 43993, 70385, 70398, 84816, 97388, 97401, 119099, 70367, 84692, 84705, 59032, 61050, 61063, 70372, 84697, 84710, 84892, 97435, 97448, 119432, 84822, 84835, 84848, 44129, 44142, 44155, 44168, 44181, 44194, 44207, 44220, 109850, 119534, 126712, 115549, 115562, 124197, 124210, 124223, 124236, 124249, 84796, 97368, 119066, 119079, 119378, 84932, 97472, 97485, 115536, 119443, 97498, 97511, 97524, 97537, 109785, 109798, 97782, 97795, 119209, 119222, 91259, 97355, 109824, 119365, 90532, 90544, 90556, 90568, 90580, 90592, 90604, 90616, 90628, 90640, 70489, 97762, 119131, 84984, 84997, 85010, 85023, 85036, 84903, 97459, 115523, 61147, 84864, 97329, 97342, 109837, 97710, 97723, 97736, 97749, 109811, 70541, 119508, 119521, 57658, 61069, 70423, 84945, 84958, 84971, 97303, 119170, 60978, 60991, 64328, 64341, 97407, 97420, 119404, 119417, 24818, 24844, 24857, 24870, 119547, 119183, 43986, 70378, 70391, 84809, 97381, 97394, 119092, 59025, 61043, 61056, 70365, 84690, 84703, 70606, 70619, 84890, 97433, 97446, 119430, 84877]";

        String pattern = ".*sql:\\s*(\\[.*\\])\\s*params:\\s*(\\[.*\\])";
        Pattern r = Pattern.compile(pattern);
        Matcher m = r.matcher(log);

        if (m.find()) {
            String sql = m.group(1);
            String paramsStr = m.group(2);

            String[] params = paramsStr.replaceAll("\\s", "").replaceAll("[\\[\\]]", "").split(",");
            String[] newParams = new String[params.length];
            for (int i = 0; i < params.length; i++) {
                if (params[i].matches("^\\d+$")) {
                    newParams[i] = params[i];
                } else {
                    newParams[i] = "\"" + params[i] + "\"";
                }
            }

            String sqlWithParams = sql;
            for (String param : newParams) {
                sqlWithParams = sqlWithParams.replaceFirst("\\?", param);
            }

            System.out.println(sqlWithParams);
        }
    }
}

官网地址：https://bs.zhxu.cn/