在后台开发中,数据库中会建许多表,而基本上每个表需要写一个查询的接口,而且查询的条件各不相同,每个表都要一一去适配其查询参数,每次客户需要新的查询参数,都需要去适配一下,这就很麻烦了,我觉得有一个通用的接口能够查询所有的表是很必要的,省时省力。

在接口中,提供一个规范,让前端去传相应的查询对象与查询参数与查询方法(=likebetween等),进行数据库查询,以后如果有新的查询参数需要添加,压力直接给到前端,我就不需要做任何操作了。当然,也支持连表查询(所以我经常喷Mybatis Plus的编程式查询,表都不能连^^),不过需要@ManyToMany@OneToMany@ManyToOne@OneToOne用的很好。不然一些查询还是要自己手动去写接口

比如,有2个表,personhuman,其都有相同的nameage属性,humanperson是多对一的关系,其human中存在着person的id

前端提供查询参数:{"name": "Human", "query": [{"field": "personEntity.name", "value": "xixi", "type": "like"}, {"field": "name", "value": "haha", "type": "eq"}]}
Spring-Data-Jpa查询生成的sql

select h1_0.id,h1_0.age,h1_0.birth,h1_0.name,h1_0.person_id 
from human h1_0 join person p1_0 on p1_0.id=h1_0.person_id 
where p1_0.name like ? and h1_0.name=? offset ? rows fetch first ? rows only

就很舒服了

使用Specification来实现,我是很喜欢用QueryDSL的,但是IntelliJ IDEAmaven阉割了生命周期,对QueryDSL的支持不好

1、先编写查询参数的对象

class DynamicParam {
    // entity的名字
    var name: String = ""
    var query: List<DynamicQuery> = mutableListOf()
    var pageable: DynamicPageable = DynamicPageable()
}

class DynamicQuery {
    var field: String = ""
    var value: String = ""
    var valueList: List<String> = mutableListOf()
    // eq、like、between、startWith、endWith、gt、ge、lt、le、ne、greaterThan、lessThan、greaterThanOrEqualTo
    var type: String = "eq"
}


class DynamicSort {
    var field: String = ""
    var order: String = "asc"
}

class DynamicPageable {
    var page: Int = 0
    var size: Int = 20
    var sort: List<DynamicSort> = mutableListOf()
}

@Data
public class DynamicPageable {
    private Integer page = 0;
    private Integer size = 20;
    private List<DynamicSort> sort = new ArrayList<>();
}

@Data
public class DynamicSort {
    private String field = "";
    private String order = "asc";
}

@Data
public class DynamicQuery {
    private String field = "";
    private String value = "";
    private List<String> valueList = new ArrayList<>();
    // eq、like、between、startWith、endWith、gt、ge、lt、le、ne、greaterThan、lessThan、greaterThanOrEqualTo
    private String type = "eq";
}

@Data
public class DynamicParam {
    private String name = "";
    private List<DynamicQuery> query = new ArrayList<>();
    private DynamicPageable pageable = new DynamicPageable();
}


其参数即为DynamicParam对象

2、编写其Entity映射表

@Entity
@Table(name= "person")
class PersonEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Int? = null
    var name: String = ""
    var age: Int = 0
    var birth: LocalDateTime = LocalDateTime.now()

}
@Entity
@Table(name= "human")
class HumanEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Int? = null
    var name: String = ""
    var age: Int = 0
    var birth: LocalDateTime = LocalDateTime.now()
    @ManyToOne
    @JoinColumn(name = "person_id")
    var personEntity: PersonEntity? = null
}

3、表对应的Repository

interface PersonRepository: JpaRepository<PersonEntity, Int>, JpaSpecificationExecutor<PersonEntity>
interface HumanRepository: JpaRepository<HumanEntity, Int>, JpaSpecificationExecutor<HumanEntity>

4、controller层

@RestController
@RequestMapping("/test")
class TestController(
    private val applicationContext: ApplicationContext
) {
    // repository的前缀包名
    private val packageName = "me.kuku.test"

    @PostMapping
    fun test(@RequestBody dynamicParam: DynamicParam): Any {
        val sp = Specification<Any> { root, _, criteriaBuilder ->
            val predicates = mutableListOf<Predicate>()
            for (dynamicQuery in dynamicParam.query) {
                val field = dynamicQuery.field
                val fieldList = field.split(".")
                val firstField = fieldList[0]
                var path = root.get<Any>(firstField)
                fieldList.stream().skip(1).forEach {
                    path = path.get(it)
                }
                val type = dynamicQuery.type
                val value = dynamicQuery.value
                val predicate = when (type) {
                    "eq" -> criteriaBuilder.equal(path, value)
                    "like" -> criteriaBuilder.like(path as Expression<String>, "%$value%")
                    "between" -> {
                        val valueList = dynamicQuery.valueList
                        if (valueList.size != 2) error("参数错误,类型为between,valueList数组必须是2个")
                        val first = valueList[0]
                        val second = valueList[1]
                        convert(criteriaBuilder, path, first, second)
                    }
                    "startWith" -> criteriaBuilder.like(path as Expression<String>, "%$value")
                    "endWith" -> criteriaBuilder.like(path as Expression<String>, "$value%")
                    "gt" -> criteriaBuilder.gt(path as Expression<out Number>, value.toInt())
                    "ge" -> criteriaBuilder.ge(path as Expression<out Number>, value.toInt())
                    "lt" -> criteriaBuilder.lt(path as Expression<out Number>, value.toInt())
                    "le" -> criteriaBuilder.le(path as Expression<out Number>, value.toInt())
                    "ne" -> criteriaBuilder.notEqual(path, value)
                    "greaterThan" -> criteriaBuilder.greaterThan(path as Expression<String>, value)
                    "lessThan" -> criteriaBuilder.lessThan(path as Expression<String>, value)
                    "greaterThanOrEqualTo" -> criteriaBuilder.greaterThanOrEqualTo(path as Expression<String>, value)
                    "lessThanOrEqualTo" -> criteriaBuilder.lessThanOrEqualTo(path as Expression<String>, value)
                    else -> continue
                }
                predicate?.let {
                    predicates.add(it)
                }
            }
            criteriaBuilder.and(*predicates.toTypedArray())
        }
        val dynamicPageable = dynamicParam.pageable
        val page = dynamicPageable.page
        val size = dynamicPageable.size
        val sortList = dynamicPageable.sort
        val sortOrderList = mutableListOf<Sort.Order>()
        for (dynamicSort in sortList) {
            val order = dynamicSort.order
            val field = dynamicSort.field
            if (order.uppercase() == "asc") sortOrderList.add(Sort.Order.asc(field))
            else if (order.uppercase() == "desc") sortOrderList.add(Sort.Order.desc(field))
        }
        val pageable = PageRequest.of(page, size, Sort.by(sortOrderList))
        val name = dynamicParam.name
        val prefix = name.substring(0, 1).uppercase() + name.substring(1)
        val repositoryClass = Class.forName("$packageName.pojo.${prefix}Repository")
        val repository = applicationContext.getBean(repositoryClass)
        val method =
            repositoryClass.getMethod("findAll", Specification::class.java, Pageable::class.java)
        return method.invoke(repository, sp, pageable)
    }

    private fun convert(build: CriteriaBuilder, path: Path<*>, text1: String, text2: String): Predicate? {
        return when (path.javaType) {
            LocalDate::class.java -> {
                build.between(path as Expression<LocalDate>,
                    DateTimeFormatterUtils.parseToLocalDate(text1, "yyyy-MM-dd"),
                    DateTimeFormatterUtils.parseToLocalDate(text2, "yyyy-MM-dd")
                )
            }
            LocalDateTime::class.java -> {
                build.between(path as Expression<LocalDateTime>,
                    DateTimeFormatterUtils.parseToLocalDateTime(text1, "yyyy-MM-dd HH:mm:ss"),
                    DateTimeFormatterUtils.parseToLocalDateTime(text2, "yyyy-MM-dd HH:mm:ss")
                )
            }
            String::class.java -> {
                build.between(path as Expression<String>, text1, text2)
            }
            else -> null
        }
    }

}

@RestController
@RequestMapping("/api/general")
public class GeneralController {
    String packageName = "me.kuku";

    @PostMapping("query")
    public Object query(@RequestBody DynamicParam dynamicParam) throws Exception {
        Specification<Object> sp = (root, query, criteriaBuilder) -> {
            List<javax.persistence.criteria.Predicate> predicates = new ArrayList<>();
            for (DynamicQuery dynamicQuery : dynamicParam.getQuery()) {
                String field = dynamicQuery.getField();
                String[] fieldList = field.split("\\.");
                String firstField = fieldList[0];
                Path path = root.get(firstField);
                for (String it : Arrays.stream(fieldList).skip(1).collect(Collectors.toList())) {
                    path = path.get(it);
                }
                String type = dynamicQuery.getType();
                String value = dynamicQuery.getValue();
                javax.persistence.criteria.Predicate predicate = null;
                switch (type) {
                    case "eq": predicate = criteriaBuilder.equal(path, value); break;
                    case "like": predicate = criteriaBuilder.like(path, "%" + value + "%"); break;
                    case "between": {
                        List<String> valueList = dynamicQuery.getValueList();
                        if (valueList.size() != 2) throw new IllegalStateException("参数错误,类型为between,valueList数组必须是2个");
                        String first = valueList.get(0);
                        String second = valueList.get(1);
                        predicate = convert(criteriaBuilder, path, first, second); break;
                    }
                    case "startsWith": criteriaBuilder.like(path, "%" + value); break;
                    case "endsWith": criteriaBuilder.like(path, value + "%"); break;
                    case "gt": criteriaBuilder.gt(path, Integer.parseInt(value)); break;
                    case "ge": criteriaBuilder.ge(path, Integer.parseInt(value)); break;
                    case "lt": criteriaBuilder.lt(path, Integer.parseInt(value)); break;
                    case "le": criteriaBuilder.le(path, Integer.parseInt(value)); break;
                    case "ne": criteriaBuilder.notEqual(path, Integer.parseInt(value)); break;
                    case "greaterThan": criteriaBuilder.greaterThan(path, value); break;
                    case "lessThan": criteriaBuilder.lessThan(path, value); break;
                    case "greaterThanOrEqualTo": criteriaBuilder.greaterThanOrEqualTo(path, value); break;
                    case "lessThanOrEqualTo": criteriaBuilder.lessThanOrEqualTo(path, value); break;
                    default: continue;
                }
                predicates.add(predicate);
            }
            return criteriaBuilder.and(predicates.toArray(new javax.persistence.criteria.Predicate[0]));
        };
        DynamicPageable dynamicParamPageable = dynamicParam.getPageable();
        Integer page = dynamicParamPageable.getPage();
        Integer size = dynamicParamPageable.getSize();
        List<DynamicSort> sortList = dynamicParamPageable.getSort();
        List<Sort.Order> sortOrderList = new ArrayList<>();
        for (DynamicSort dynamicSort : sortList) {
            String order = dynamicSort.getOrder();
            String field = dynamicSort.getField();
            if ("asc".equals(order.toUpperCase())) sortOrderList.add(Sort.Order.asc(field));
            else if ("desc".equals(order.toUpperCase())) sortOrderList.add(Sort.Order.desc(field));
        }
        PageRequest pageable = PageRequest.of(page, size, Sort.by(sortOrderList));
        String name = dynamicParam.name;
        String prefix = name.substring(0, 1).toUpperCase() + name.substring(1);
        Class<?> repositoryClass = Class.forName(packageName + ".repository" + "." + prefix + "Repository");
        Object repository = applicationContext.getBean(repositoryClass);
        Method method = repositoryClass.getMethod("findAll", Specification.class, Pageable.class);
        return method.invoke(repository, sp, pageable);
    }

    private javax.persistence.criteria.Predicate convert(CriteriaBuilder build, Path path, String text1, String text2) {
        Class clazz = path.getJavaType();
        if (clazz == LocalDate.class) {
            return build.between(path, DateTimeFormatterUtils.parseToLocalDate(text1, "yyyy-MM-dd"),
                    DateTimeFormatterUtils.parseToLocalDate(text2, "yyyy-MM-dd"));
        } else if (clazz == LocalDateTime.class) {
            return build.between(path, DateTimeFormatterUtils.parseToLocalDateTime(text1, "yyyy-MM-dd HH:mm:ss"),
                    DateTimeFormatterUtils.parseToLocalDateTime(text2, "yyyy-MM-dd HH:mm:ss"));
        } else return null;
    }

}


完成

最后修改:2023 年 03 月 31 日
如果觉得我的文章对你有用,请随意赞赏