spring JPA 处理字段名动态变化的条件查询

业务需求

有工厂设备信息表 machine_info ,有字段 machine_name, factory_name, machine_code, machine_location 等，现要求页面上根据以上的某一个条件过滤出对应的设备并展示.

程序实现

后端查询是个很简单的sql语句: select * from machine_info where machine_name like xxx. 当然也可能是 factory_name like xxx,根据上面的要求，使用JPA做orm有三种方式来实现

• 第一个，来一种情况加一个sql，并在程序中用swith case做判断来实现(框架做了防SQL注入，这种写sql的方式不能实现动态字段填入，会报访问安全性的问题)

// 在MachineRepository中写N个查询语句，serviceImpl写方法根据需求调用

// -----------repository------------//
@Query(
	value = "select * from machine_info where machine_name like CONCAT('%', ?1, '%')",
	nativeQuery = true,
	countQuery = "select count(*) from machine_info where machine_name like CONCAT('%', ?1, '%')"
)
Page<MachineInfoEntity> getByMachineName(String name, Pageable pageable);

@Query(
	value = "select * from machine_info where machine_name like CONCAT('%', ?1, '%')",
	nativeQuery = true,
	countQuery = "select count(*) from machine_info where factory_name like CONCAT('%', ?1, '%')"
)
Page<MachineInfoEntity> getByFactoryName(String name, Pageable pageable);
...

// ----------- serviceImpl ---------------
public List<MachineInfoEntity> filterByFieldLike(String fieldName, String fieldValue, Pageable pageable) {
	siwch(fieldName) {
		case "machineName":
			repo.getByMachineName(fieldValue, pageable);
			break;
		case "factoryName":
			repo.getByFactoryName(fieldValue, pageable);
			...
	}
}

• 第二个，手动硬编码sql

// serviceImpl
import javax.persistence.EntityManager;

public List<MachineInfoEntity> filterByFieldLike(String fieldName, String fieldValue, Pageable pageable) {
	String sql = "select * from machine_info where " + fieleName + " like " + " %" + fieldValue + "%";
 List ret = entityManager.createNativeQuery(sql).getResultList();
 ...
}

• 第三个，实现条件查询接口（推荐）

// -------------repository--------------------
public interface MachineInfoRepository extends JpaRepository<MachineInfoEntity, Integer>, JpaSpecificationExecutor<MachineInfoEntity> {
}

// -------------spefication-------------
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;

/**
 * title: MachineInfoSpecification
 * projectName: test
 * author: wind
 * date: 2022-02-08 16:03
 * description: machine info spec
 **/
public class MachineInfoSpecification implements Specification<MachineInfoEntity> {

    private String randomColumnName;
    private String valueToSearchFor;

    public MachineInfoSpecification(String randomColumnName, String valueToSearchFor) {
        this.randomColumnName = randomColumnName;
        this.valueToSearchFor = valueToSearchFor;
    }

    /**
     * Creates a WHERE clause for a query of the referenced entity in form of a {@link Predicate} for the given
     * {@link Root} and {@link CriteriaQuery}.
     *
     * @param root            must not be {@literal null}.
     * @param query           must not be {@literal null}.
     * @param builder         must not be {@literal null}.
     * @return a {@link Predicate}, may be {@literal null}.
     */
    @Override
    public Predicate toPredicate(Root<MachineInfoEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        List<Predicate> predicateList = new ArrayList<>();

        predicateList.add(builder.like(root.<String>get(this.randomColumnName), "%" + this.valueToSearchFor + "%"));
        return builder.and(predicateList.toArray(new Predicate[predicateList.size()]));
    }
}


// -------------serviceImpl-------------
public List<MachineInfoEntity> filterByFieldLike(String fieldName, String fieldValue, Pageable pageable) {
	MachineInfoSpecification machineInfoSpecification = new MachineInfoSpecification(fieldName, fieldValue);
    Page<MachineInfoEntity> pageInfo = repo.findAll(machineInfoSpecification, pageable);
    ...
}
        

后记

第一种方案实现思路和编码都很简单，但后续维护成本会比较高，如果条件有变更，需要修改代码。另外，这些sql语句处理一个提交不一样，其它都是一样的，看起来重复度会很高
第二种方案查询返回结果是List，里面每个元素是一个Object数组，访问字段很不方便，另外，分页也是个问题
第三种方案代码重复度小，能适应后续条件的变更，只是编写的时候程序会相对复杂

参考

https://blog.csdn.net/qq_37840993/article/details/107562079
https://stackoverflow.com/questions/47407043/spring-data-jpa-pass-column-name-and-value-as-parameters
https://segmentfault.com/a/1190000037755804