从具有多个过滤条件的数据库中仅获取选定的列,使用 Spring Boot 2 和 JPA。

huangapple go评论78阅读模式
英文:

Get only selected columns from DB with multiple filtering criteria spring boot 2 JPA

问题

我试图创建一个Spring Boot 2 Web应用程序,它将根据传递的筛选条件从数据库中获取数据,但只获取特定的列。

这是我的雇员类:

@Entity
@Table(name = "employee")
class Employee {
    @Column(name = "fname")
    String fname;
    @Column(name = "lname")
    String lname;
    @Column(name = "phoneNo")
    String phoneNo;
    @Column(name = "address")
    String address;
}

在我的实体和数据库中还有另外25个字段。

从前端,用户应该能够选择筛选条件,例如:fname,lname,phoneNo,address等。他可以指定任何组合,如fname和phoneNo,或lname和address,或者可能不指定任何内容,在这种情况下我必须执行一个select *。以某种方式,我想要多个筛选条件。我预期这些筛选器将作为来自前端的请求参数。

我的Repository是:

public interface EmployeeRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {
}

到目前为止,我已经研究了specifications,这非常不错。

因此,我创建了一个规范(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);
            }
        };
    }
    // 其他规范方法类似...
}

现在,从我的Service中,我计划执行:

this.employeeRepository.findAll(EmployeeSpecs.hasFname(requestParameterFname).and(EmployeeSpecs.hasLname(requestParameterLname))).forEach(e -> list.add(e));

然而,这将从数据库中获取所有的25列。

我的前端应用程序有6个页面,每个页面都需要显示不同的列,但需要将这些规范组合为WHERE子句。

我尝试了解projection的概念,但发现当前Spring Boot不支持带有Projection的Specification。

有没有办法只获取选定的列并具有多个筛选条件?对于能否动态地将传递的请求参数附加到我的查询并仅获取相关字段,你有什么想法?

我是否应该创建单独的实体,以便从我的Repository中只获取那些字段,然后每次都为每个字段创建一个新的Specification?这样做会不会创建过多的不必要的实体和规范文件?

我能想到的另一种方法是,我将不得不手动提取那些列。这听起来很愚蠢,因为我已经知道我需要执行select column1, column2, column3 from db where condition1 = true and condition2 = true,但我仍然在执行select *

请问有人能够指导一下在这种情况下采取的最佳方法是什么?在此情况下,哪种方法看起来最清晰?我是否应该手动编写查询,比如本地查询?

简而言之,我想要以下内容:

  1. 多个筛选条件 - 可能的任何组合,即多个条件传递到SQL选择语句的WHERE子句。
  2. 仅选定的列,不是全部 - 但不同的用例需要不同的列。
英文:

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.

Here is my employee class:

@Entity
@Table(&quot;table=emplooyee&quot;)
class Employee{ 
         @column(&quot;name=&quot;fname&quot;)
         String fname;
         @column(&quot;name=&quot;lname&quot;)
         String lname;
         @column(&quot;name=&quot;phoneNo&quot;)
         String phoneNo;
         @column(&quot;name=&quot;address&quot;)
         String address;
     }

There are 25 more such fields in my entity and in the db.

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.

My repository is:

public interface EmployeeRepository extends JpaRepository&lt;Employee,Long&gt;, JpaSpecificationExecutor&lt;Employee&gt;{

}

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&lt;Employee&gt; hasFname(String fname){
		return new Specification&lt;Employee&gt;() {
			@Override
			public Predicate toPredicate(Root&lt;Employee&gt; root, CriteriaQuery&lt;?&gt; query, CriteriaBuilder criteriaBuilder) {
				return criteriaBuilder.equal(root.get(&quot;fname&quot;),fname);
			}
		};
	}
	public static Specification&lt;Employee&gt; hasLname(String lname){
		return new Specification&lt;Employee&gt;() {
			@Override
			public Predicate toPredicate(Root&lt;Employee&gt; root, CriteriaQuery&lt;?&gt; query, CriteriaBuilder criteriaBuilder) {
				return criteriaBuilder.equal(root.get(&quot;lname&quot;), lname);
			}
		};
	}
	public static Specification&lt;Employee&gt; hasAddress(String address){
		return new Specification&lt;Employee&gt;() {
			@Override
			public Predicate toPredicate(Root&lt;Employee&gt; root, CriteriaQuery&lt;?&gt; query, CriteriaBuilder criteriaBuilder) {
				return criteriaBuilder.equal(root.get(&quot;address&quot;), address);
			}
		};
	}
	public static Specification&lt;Employee&gt; hasPhone(String phone){
		return new Specification&lt;Employee&gt;() {
			@Override
			public Predicate toPredicate(Root&lt;Employee&gt; root, CriteriaQuery&lt;?&gt; query, CriteriaBuilder criteriaBuilder) {
				return criteriaBuilder.equal(root.get(&quot;phone&quot;), phone);
			}
		};
	}
}

Now, from my service, I plan to do:

this.employeeRepository.findAll(EmployeeSpecs.hasFName(requestParameterFname).and(EmployeeSpecs.hasLName(requestParameterLname))).forEach(e-&gt;list.add(e));

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.

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?

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&#39; 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:

  1. Multiple filtering criteria - any combination possible, ie. multiple conditions to be passed to the 'where' clause of my sql select statement.
  2. Only selected columns, not all - but different use cases require different columns.

答案1

得分: 1

Spring Data没有任何特殊功能或此类功能。因此,您需要创建一个自定义方法,在此方法中将Specification中的Predicate与选择列表结合起来。

自定义方法可能如下所示:

Employee findBySpecAndColumns(Specification spec, List<String> columns) {
    
    // 根据此处描述创建选择列表,但从列列表或您用于指定要选择的列的其他内容中获取:https://www.objectdb.com/java/jpa/query/jpql/select#SELECT_in_Criteria_Queries

    // 使用 spec.toPredicate(...) 创建 where 子句

    // 执行查询。

    // 将结果转换为您所需/希望的形式。
}

另请参阅:
如何使用Criteria API指定选择列表

不过,我在想这是否值得努力。我认为,对于要在单个页面上显示的数据选择 25 列与从同一表中选择 4 列可能并没有太大区别。

英文:

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&lt;String&gt; 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.
}

See also:
How to specify the select list using the Criteria API.

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.

答案2

得分: 1

你可以使用GraphQLQueryDSL

使用QueryDSL的示例:

QMenuItemRelation entity = new QMenuItemRelation("entity");
QMenuItem menuItem = new QMenuItem("menuItem");
QMenuItemRelationPrice menuItemRelationPrice = new QMenuItemRelationPrice("menuItemRelationPrice");
return queryFactory.select(Projections.constructor(
                MenuItemScalesExportDTO.class,
                entity.menuItem.id,
                entity.menuItem.name,
                entity.menuItem.barcode,
                entity.menuItem.unitType,
                menuItemRelationPrice.price))
        .from(entity)
        .where(entity.active.eq(true), entity.menu.id.eq(menuId), entity.menuItem.usedByScales.eq(true))
        .leftJoin(entity.menuItem, menuItem)
        .leftJoin(menuItemRelationPrice).on(entity.eq(menuItemRelationPrice.menuItemRelation))
        .orderBy(entity.id.desc())
        .fetch();

如果您希望使用getter/setter而不是构造函数进行映射,也可以使用Projections.bean。

DTO:

public class MenuItemScalesExportDTO implements Serializable {
    private UUID id;
    private String name;
    private String code;
    private String unit;
    private List<PriceDTO> price;
    private BigDecimal unitPrice;

    public MenuItemScalesExportDTO(UUID id, String name, String code, String unit, List<PriceDTO> price) {
        this.id = id;
        this.name = name;
        this.code = code;
        this.unit = unit;
        this.price = price;
    }
}

[1]: https://graphql.org
[2]: http://querydsl.com/

注意:上述代码是Java代码,仅供参考。如果您有其他问题或需要进一步的帮助,请随时提出。

英文:

You can use GraphQL or QueryDSL

Example using queryDSL

    QMenuItemRelation entity = new QMenuItemRelation(&quot;entity&quot;);
    QMenuItem menuItem = new QMenuItem(&quot;menuItem&quot;);
    QMenuItemRelationPrice menuItemRelationPrice = new QMenuItemRelationPrice(&quot;menuItemRelationPrice&quot;);
    return queryFactory.select(Projections.constructor(
                    MenuItemScalesExportDTO.class,
                    entity.menuItem.id,
                    entity.menuItem.name,
                    entity.menuItem.barcode,
                    entity.menuItem.unitType,
                    menuItemRelationPrice.price))
            .from(entity)
            .where(entity.active.eq(true), entity.menu.id.eq(menuId), entity.menuItem.usedByScales.eq(true))
            .leftJoin(entity.menuItem, menuItem)
            .leftJoin(menuItemRelationPrice).on(entity.eq(menuItemRelationPrice.menuItemRelation))
            .orderBy(entity.id.desc())
            .fetch();

You also can use Projections.bean if you want to map with getter/setter instead of constructor.

DTO

public class MenuItemScalesExportDTO implements Serializable {
private UUID id;
private String name;
private String code;
private String unit;
private List&lt;PriceDTO&gt; price;
private BigDecimal unitPrice;

public MenuItemScalesExportDTO(UUID id, String name, String code, String unit, List&lt;PriceDTO&gt; price) {
    this.id = id;
    this.name = name;
    this.code = code;
    this.unit = unit;
    this.price = price;
}

huangapple
  • 本文由 发表于 2020年10月12日 13:32:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/64312184.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定