How to write custom queries in JPA

In Spring Boot applications, you can write custom queries using JPQL (Java Persistence Query Language) or native SQL queries. Here's how you can write custom queries using both approaches:

1. Using JPQL:

You can write custom queries using JPQL, which is a platform-independent query language similar to SQL but operates on entity objects rather than database tables.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.age > ?1")
    List<User> findByAgeGreaterThan(int age);
}

In the above example, @Query annotation is used to specify a JPQL query. The ?1 is a 
positional parameter indicating the first method parameter (int age).

2. Using Native SQL:

You can also write custom queries using native SQL queries, which are SQL queries written in the database's native dialect.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT * FROM users WHERE age > ?1", nativeQuery = true)
    List<User> findByAgeGreaterThan(int age);
}

In this example, @Query annotation is used with nativeQuery = true attribute to specify a native SQL query.

3. Named Queries:

You can also define named queries in your entity class or XML mapping file and refer to them in your repository interface.

import javax.persistence.NamedQuery;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    List<User> findByAgeGreaterThan(int age);

import javax.persistence.Entity;
import javax.persistence.NamedQuery;

@Entity
@NamedQuery(name = "User.findByAgeGreaterThan", query = "SELECT u FROM User u WHERE u.age > ?1")
public class User {
    // Entity definition
}

4. Dynamic Queries:

You can also create dynamic queries using Querydsl or Specifications, which allow you to build queries programmatically based on runtime conditions.

Here's an example using Querydsl:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.querydsl.core.types.Predicate;
import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long>, QuerydslPredicateExecutor<User> {

    List<User> findAll(Predicate predicate);
}

How to call named query

import javax.persistence.Entity;
import javax.persistence.NamedQuery;

@Entity
@NamedQuery(name = "User.findByAgeGreaterThan", query = "SELECT u FROM User u WHERE u.age > ?1")
public class User {
    // Entity definition
}

To call this named query in your repository interface, you would define a method with the same name as the named query:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    List<User> findByAgeGreaterThan(int age);
}

import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    public List<User> getUsersByAgeGreaterThan(int age) {
        return userRepository.findByAgeGreaterThan(age);
    }
}

In this example, the getUsersByAgeGreaterThan method in the UserService class calls the 
findByAgeGreaterThan method of the UserRepository interface to retrieve users whose age 
is greater than the specified value.

When you invoke userService.getUsersByAgeGreaterThan(30) (assuming userService is an instance of UserService), it will execute the named query and return the list of users with an age greater than 30.

Update Query:

The @Query annotation also allows you to define custom JPQL (Java Persistence Query Language) or native SQL queries, including update and delete operations. Here's how you can write an update query using @Query.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import javax.transaction.Transactional;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Transactional
    @Modifying
    @Query("UPDATE User u SET u.age = :age WHERE u.id = :id")
    void updateAgeById(Long id, int age);
}

In the above example:

@Query annotation is used to define the custom JPQL update query.

@Modifying annotation is used to indicate that this query modifies the database.

@Transactional annotation is used to mark the transaction boundary for the method.

:id and :age are named parameters that will be replaced with the actual values passed to the method.

The method updateAgeById takes the id and new age as parameters and updates the age of the user with the specified id.

Now, you can call the updateAgeById method in your service or controller to perform the update operation:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    public void updateUserAge(Long id, int newAge) {
        userRepository.updateAgeById(id, newAge);
    }
}