春季启动，执行自定义查询 [英] Spring boot, execute custom query

问题描述

我是Web开发的新手，我做了一些示例，例如从mysql db获取数据并在一个jsp页面中显示它们。（使用CRUDRepository）
，但那样，我们只能显示一个表数据。
如果要显示合并两个表数据应该怎么做。

Im newbie to web development,and I did some examples like get data from mysql db and show them in a jsp pages.(use CRUDRepository ) but in that way we can only show only one table data. what should we do if we want to show combine two table data.

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.lastname like ?1%")
  List<User> findByAndSort(String lastname, Sort sort);

  @Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
  List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
}

---

如果可以的话复杂的查询（如三个表或更多表），
我们应该根据查询列创建一个新的实体类吗？
再一次是有效的，因为实际上没有这样的表。

---

if we can put that complicated query (like three tables or more) here, should we create a new entity class according to query coloumns ?? then again is that work because actually there isn't any table like that.

推荐答案

以获取更复杂的数据从数据库中，您可以使用投影，例如：

To get more complex data from DB you can use projections, for example:

public interface UserProjection {
    Long getId();
    Long getFirstNameLen();
}

@Query("select u.id as id, LENGTH(u.firstName) as firstNameLen from User u where u.lastname like ?1%")
List<UserProjection> getProjections(String lastName, Sort sort);

请注意，查询中应使用与投影中的getter匹配的别名（ ... as firstNameLen -> getFirstNameLen（））

Note that you should use aliases in the query that must match with getters in the projection (... as firstNameLen -> getFirstNameLen())

如果您的某个实体具有某些关联，例如：

If you have an entity with some associations, for example:

@Entity
public class User {
    //...
    @OneToMany
    private List<Role> roles;
}

然后，即使不使用存储库方法也可以获取用户及其角色数据任何投影，仅针对主要实体（ User ）。然后Spring自己完成其余工作：

then you can use repository method to get the users and their roles data even without any projection, just for the main entity (User). Then Spring does the rest of the work itself:

@EntityGraph(attributePaths = "roles")
List<User> findByFirstNameContainingIgnoreCase(String firstName);

或与查询相同：

@Query("select distinct u from User u left join fetch u.roles where upper(p.firstName) like concat('%', upper(?1), '%')")
List<User> findWithQuery(String firstName);

在这种情况下，所有用户的角色列表将填充<$ c $中的数据c>角色表。

In this case all users will have their lists of roles are filled with data from the roles table.

（注意在查询中使用 distinct 来防止重复记录的结果，更多信息，请参见此处。）

(Note to use distinct in the query to prevent the result from duplicated records, more info see here.)

有用的资源：

Spring Data JPA-参考文档

• < a href = https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation rel = nofollow noreferrer>查询创建


• 存储库查询关键字


• 投影

• Query Creation
• Repository query keywords
• Projections

Spring Data JPA @Query定义中的SpEL支持

Hibernate ORM用户指南

• 协会


• HQL和JPQL

• Associations
• HQL and JPQL

JPQL语言参考

这篇关于春季启动，执行自定义查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！