java使用JPA编写自定义SQL查询

Question

我有一个数据库实体的JPA存储库，如下所示：

@Repository
public interface StudentRepository
    extends  JpaRepository<Student, String>  {
}

Student实体如下：

@Entity
@Table(name = "student")
public class Student implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  @GeneratedValue(generator = "custom-uuid")
  @GenericGenerator(name = "custom-uuid",
      strategy = "generator.CustomUUIDGenerator")
  private String id;

  @Column(name = "roll_number")
  private String rollNumber;

  @Column(name = "name")
  private String name;

  @LastModifiedDate
  @Column(name = "last_modified_date")
  @JsonIgnore
  private Instant lastModifiedDate = Instant.now();

现在，我想使用JPA编写以下SQL查询，如下所示：

SELECT id, roll_number from  student where id=<id> order by last_modified_date desc;

作为JPA/Hibernate的新手，我不知道如何使用JPA/Hibernate实现这一点

有人能帮忙吗

Answer 1

你可以这样写：

String hql = "SELECT s.id, s.rollNumber FROM Student s WHERE s.id = :id ORDER BY s.lastModifiedDate DESC";
query.setParameter("id", 10);
Query query = session.createQuery(hql);
List results = query.list();

参考：https://www.tutorialspoint.com/jpa/jpa_jpql.htm

Answer 2

在存储库中创建新方法

@Repository
public interface StudentRepository extends JpaRepository<Student, String> {

    /**
     * List all student by criterias.
     *
     * @return
     */
    @Query(value = "SELECT id, roll_number from student where id=?1 order by last_modified_date desc;", nativeQuery = true)
    List<Student> listStudent(String id);

}

参考文件：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query