Write custom SQL query using JPA

Question

I have a JPA repository for a DB entity as follows:

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

Student entity is as follows:

@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();

Now, I would like to write the following SQL query using JPA as follows:

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

Being new to JPA/Hibernate, I don't have an idea how to achieve this using JPA/Hibernate.

Could anyone please help here ?

Mayur Somani · Accepted Answer · 2020-11-01 12:18:25Z

1

You could write something like:

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();

Refer: https://www.tutorialspoint.com/jpa/jpa_jpql.htm

answered Nov 1, 2020 at 12:18

Mayur Somani

314 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vy Do · Accepted Answer · 2020-11-01 13:46:04Z

0

Create a new method in Repository

@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);

}

Reference document: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query

answered Nov 1, 2020 at 13:46

Vy Do

1

Collectives™ on Stack Overflow

Write custom SQL query using JPA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related