仅从具有多个过滤条件的数据库中获取选定的列 spring boot 2 JPA [英] Get only selected columns from DB with multiple filtering criteria spring boot 2 JPA
问题描述
我正在尝试创建一个 spring boot 2 web 应用程序，它将根据传递给它的过滤条件从数据库中获取数据，但只会获取某些列.
I'm trying to create a spring boot 2 web application which will fetch data from the db based on the filtering criteria passed to it, but will only fetch certain columns.
这是我的员工课程:
@Entity
@Table("table=emplooyee")
class Employee{
@column("name="fname")
String fname;
@column("name="lname")
String lname;
@column("name="phoneNo")
String phoneNo;
@column("name="address")
String address;
}
我的实体和数据库中还有 25 个这样的字段.
There are 25 more such fields in my entity and in the db.
从前端，用户应该能够选择过滤条件，例如:fname、lname、phoneNo、address 等.他可以指定任何组合，如 fname 和 phoneNo，或 lname 和 address，也可以不指定任何组合我必须做一个选择*.在某种程度上，我想要多个过滤条件.我希望这些过滤器可以作为来自前端的请求参数.
From the front-end the user should be able to choose a filtering criteria such as: fname, lname, phoneNo, address etc. He may specify any combination like fname and phoneNo, or lname and address or may not specify anything in which I have to do a select *. In a way, I want multiple filtering criteria. I expect these filters to come as request parameters from the front end.
我的仓库是:
public interface EmployeeRepository extends JpaRepository<Employee,Long>, JpaSpecificationExecutor<Employee>{
}
到目前为止，我已经研究了 规格很酷.
So far, I've looked into specifications which is pretty cool.
所以我创建了一个规范，
So I created a specification,
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
public class EmployeeSpecs {
public static Specification<Employee> hasFname(String fname){
return new Specification<Employee>() {
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
return criteriaBuilder.equal(root.get("fname"),fname);
}
};
}
public static Specification<Employee> hasLname(String lname){
return new Specification<Employee>() {
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
return criteriaBuilder.equal(root.get("lname"), lname);
}
};
}
public static Specification<Employee> hasAddress(String address){
return new Specification<Employee>() {
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
return criteriaBuilder.equal(root.get("address"), address);
}
};
}
public static Specification<Employee> hasPhone(String phone){
return new Specification<Employee>() {
@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
return criteriaBuilder.equal(root.get("phone"), phone);
}
};
}
}
现在，根据我的服务，我打算:
Now, from my service, I plan to do:
this.employeeRepository.findAll(EmployeeSpecs.hasFName(requestParameterFname).and(EmployeeSpecs.hasLName(requestParameterLname))).forEach(e->list.add(e));
但是，这将获取我数据库中的所有 25 列.我的前端应用程序有 6 个页面，每个页面都需要显示不同的列，但是这些规范的组合作为 where 子句.
However, this would fetch all the 25 columns in my db. My front end application has 6 pages, each requiring different columns to be displayed but a combination of these specifications as where clauses.
我尝试研究 projection，但发现目前 SpringBoot 不支持 Specification with Projection.
I tried looking into the concept of projection, but figured out that currently SpringBoot does not support Specification with Projection.
有没有办法只获取选定的列并有多个过滤条件?关于能够将传递的请求参数动态附加到我的查询并仅获取相关字段的任何想法?
Is there a way to get only selected columns and have multiple filtering criteria? Any thoughts on being able to dynamically append the passed request parameters to my query and fetching only relevant fields?
我是否应该创建单独的实体，以便只从我的存储库中获取这些字段，然后每次都为每个字段创建一个新规范?这会不会创建太多不必要的实体和规范文件?
Should I create separate entities so that I get only those fields from my repository and then a new specification for each of those each time? Won't this create too many unnecessary entities and specification files?
我能想到的另一种方式是，我必须手动提取这些列.这听起来很愚蠢，因为我已经知道我需要从数据库中执行select column1, column2, column3 where condition1 = true and condition2= true" 但我仍在执行 select*.
The other way I can think of is that, I'll have to manually extract those columns. This would sound stupid as I already know that I need to do a 'select column1, column2, column3 from db where condition1 = true and condition2= true' but I'm still doing a select *.
谁能指导一下在这种情况下采取的最佳方法是什么?实现这一目标的最干净的方法是什么?我应该像原生查询一样手动编写查询吗?
Can anyone please guide on what's the best approach to take in this case? What would look the most clean way of achieving this? Should I be writing a query manually, like a native query?
简而言之，我想要以下内容:
In a nutshell, I want the following:
- 多个过滤条件 - 任何可能的组合，即.要传递给我的 sql select 语句的where"子句的多个条件.
- 仅选定的列，而不是全部 - 但不同的用例需要不同的列.
推荐答案
Spring Data 没有任何特殊功能或这个.因此，您需要创建自定义方法，将 Specification 中的 Predicate 与选择列表结合起来.
Spring Data doesn't have any special feature or this. So you would need to create a custom method, where you combine the Predicate from the Specification with a selection list.
自定义方法可能看起来像这样:
The custom method might look somewhat like this:
Employee findBySpecAndColumns(Specification spec, List<String> columns) {
// create select list as described here, but from the list of columns or whatever you use to specify which columns you want to select: https://www.objectdb.com/java/jpa/query/jpql/select#SELECT_in_Criteria_Queries
// use spec.toPredicate(...) to create the where clause
// execute the query.
// transform the result to the form you need/want.
}
另见:如何使用 Criteria API 指定选择列表.
我想知道，这是否值得付出努力.我希望为要在单个页面上显示的数据选择 25 列可能与从同一个表中选择 4 列没有太大区别.
I wonder though, if this is worth the effort. I'd expect that selecting 25 columns for data to be displayed on a single page probable doesn't make much difference from selecting 4 columns from the same table.
这篇关于仅从具有多个过滤条件的数据库中获取选定的列 spring boot 2 JPA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
