Menu

ways to retrieve data from the database

ways to retrieve data from the database

In JPA, there are several ways to retrieve data from the database. These methods can be categorized based on the type of queries they use, such as JPQL (Java Persistence Query Language), Criteria API, and native SQL queries. Each method provides a way to query the database and map the results to entity objects.

ways to retrieve data from the database

1.  JPQL (Java Persistence Query Language) Queries

JPQL is a platform-independent object-oriented query language defined as part of the JPA specification. It uses entity names instead of table names and properties instead of columns.

  • Creating a Query :
    • createQuery(String qlString): Used to create a JPQL query.

Example
String jpql = "SELECT s FROM Student s WHERE s.age > :age";
  TypedQuery<Student> query = entityManager.createQuery(jpql, Student.class);
  query.setParameter("age", 18);
  List<Student> students = query.getResultList();

2.  Criteria API Queries

The Criteria API is a type-safe and dynamic way to create queries in JPA. It is useful for building queries programmatically.

  • Creating a Query :
    • CriteriaBuilder: Used to construct query expressions.
    • CriteriaQuery<T>: Represents a query object.

Example
  java
  CriteriaBuilder cb = entityManager.getCriteriaBuilder();
  CriteriaQuery<Student> cq = cb.createQuery(Student.class);
  Root<Student> student = cq.from(Student.class);
  cq.select(student).where(cb.greaterThan(student.get("age"), 18));
  TypedQuery<Student> query = entityManager.createQuery(cq);
  List<Student> students = query.getResultList();

3.  Named Queries

Named queries are pre-defined, statically defined queries with a specific name. They can be defined using annotations or XML.

Example
java
  @Entity
  @NamedQuery(name = "Student.findByAge", query = "SELECT s FROM Student s WHERE s.age = :age")
  public class Student {
      // Fields, getters, setters
  }
  
  TypedQuery<Student> query = entityManager.createNamedQuery("Student.findByAge", Student.class);
  query.setParameter("age", 18);
  List<Student> students = query.getResultList();

4.  Native SQL Queries

Native queries use raw SQL statements to interact with the database. They are useful when you need to leverage database-specific features.

  • Creating a Query :
    • createNativeQuery(String sqlString): Used to create a native SQL query.

Example
java
  String sql = "SELECT * FROM students WHERE age > ?";
  Query query = entityManager.createNativeQuery(sql, Student.class);
  query.setParameter(1, 18);
  List<Student> students = query.getResultList();

Example Application

Here is an example demonstrating how to use these different query methods in a simple JPA application.

Step 1: Define the Entity
java
package com.example.demo.model;

import javax.persistence.*;

@Entity
@NamedQuery(name = "Student.findByAge", query = "SELECT s FROM Student s WHERE s.age = :age")
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private int age;

    // Constructors, getters, and setters

    public Student() {}

    public Student(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

Step 2: Define the Service
java
package com.example.demo.service;

import com.example.demo.model.Student;

import javax.persistence.*;
import javax.transaction.Transactional;
import java.util.List;

public class StudentService {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Student> findStudentsUsingJPQL(int age) {
        String jpql = "SELECT s FROM Student s WHERE s.age > :age";
        TypedQuery<Student> query = entityManager.createQuery(jpql, Student.class);
        query.setParameter("age", age);
        return query.getResultList();
    }

    public List<Student> findStudentsUsingCriteriaAPI(int age) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Student> cq = cb.createQuery(Student.class);
        Root<Student> student = cq.from(Student.class);
        cq.select(student).where(cb.greaterThan(student.get("age"), age));
        TypedQuery<Student> query = entityManager.createQuery(cq);
        return query.getResultList();
    }

    public List<Student> findStudentsUsingNamedQuery(int age) {
        TypedQuery<Student> query = entityManager.createNamedQuery("Student.findByAge", Student.class);
        query.setParameter("age", age);
        return query.getResultList();
    }

    public List<Student> findStudentsUsingNativeQuery(int age) {
        String sql = "SELECT * FROM students WHERE age > ?";
        Query query = entityManager.createNativeQuery(sql, Student.class);
        query.setParameter(1, age);
        return query.getResultList();
    }
}

Step 3: Define the Controller
java
package com.example.demo.controller;

import com.example.demo.model.Student;
import com.example.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/students")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping("/jpql")
    public List<Student> findStudentsUsingJPQL(@RequestParam int age) {
        return studentService.findStudentsUsingJPQL(age);
    }

    @GetMapping("/criteria")
    public List<Student> findStudentsUsingCriteriaAPI(@RequestParam int age) {
        return studentService.findStudentsUsingCriteriaAPI(age);
    }

    @GetMapping("/named")
    public List<Student> findStudentsUsingNamedQuery(@RequestParam int age) {
        return studentService.findStudentsUsingNamedQuery(age);
    }

    @GetMapping("/native")
    public List<Student> findStudentsUsingNativeQuery(@RequestParam int age) {
        return studentService.findStudentsUsingNativeQuery(age);
    }
}

Summary

JPA provides various query methods to retrieve data from the database:

  • JPQL Queries : Platform-independent, object-oriented queries.
  • Criteria API Queries : Type-safe, programmatic way to build queries.
  • Named Queries : Predefined, statically defined queries.
  • Native SQL Queries : Raw SQL queries for database-specific features.

Homepage

Readmore