Converting to Spring Data JPA

In this log, we will implement Spring Data JPA into the "In-Memory Database Practice" project.

UserEntity

• UserEntity used to extend Entity class which implements PrimaryKey interface. The role of setting id is handled by the MySQL now so we no longer need PrimaryKey interface.

public abstract class Entity implements PrimaryKey{
    @Getter
    @Setter
    private Long id;
}

public interface PrimaryKey {

    void setId(Long id);

    Long getId();
}

@EqualsAndHashCode(callSuper = true)
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class UserEntity extends Entity {
    private String name;
    private int score;
}

• We no longer need PrimaryKey and Entity, and we modify UserEntity as following:

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity(name = "user")
public class UserEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private int score;
}

UserRepository

• UserRepository used to extend SimpleDataRepository as following:

@Slf4j
@Repository
public class UserRepository extends SimpleDataRepository<UserEntity, Long> {

    public List<UserEntity> findAllScoreGreaterThan(int score){

        return this.findAll().stream()
                .filter(
                        it -> {
                            return it.getScore() >= score;
                        }
                ).collect(Collectors.toList());
    }
}

• We now make it such that UserRepository extends JpaRepository:

public interface UserRepository extends JpaRepository<UserEntity, Long>{}

Query Methods in JPA

1. Derived Query Methods (Method Name Derivation)

• These are methods in repository interfaces whose names are parsed by Spring Data JPA to automatically generate SQL queries based on conventions.
• Spring Data JPA analyzes the method name, starting with prefixes likefindBy, getBy, readBy. It extracts the property names (e.g. Name, Age) and conditions (e.g. GreaterThan, And)
• Simple Queries:

List<UserEntity> findByName(String name);
//Generated SQL: SELECT * FROM user WHERE name = ?

• Conditional Queries:

List<UserEntity> findByAgeGreaterThan(int age);
//Generated SQL: SELECT * FROM user WHERE age > ?

• Multiple Conditions:

List<UserEntity> findByNameAndAge(String name, int age);
//Generates SQL: SELECT * FROM user WHERE name = ? AND age = ?

• Using Or:

List<UserEntity> findByNameOrAge(String name, int age);
//Generates SQL: SELECT * FROM user WHERE name = ? OR age = ?

• Sorting and Limiting:

List<UserEntity> findTop5ByOrderByAgeDesc();
//Generates SQL: SELECT * FROM user ORDER BY age DESC LIMIT 5

2. Custom Queries (Using @Query)

• When the derived method names become complex or when we need more control, we can define queries using @Query annotation.
• JPQL (Java Persistence Query Language) queries are object-oriented and work with entity fields instead of database columns.
• Basic JPQL Query:
  - The syntax of JPQL is close to SQL but uses entity names and field names instead of table and column names:

  SELECT <entity/field> FROM <EntityName> [AS <alias>] [WHERE <condition>] [ORDER BY <field> ASC|DESC]

  	- `u.name` refers to the `name` field in the `UserEntity` class, not the column.

@Query("SELECT u FROM UserEntity u WHERE u.name = :name")
List<UserEntity> findByNameCustom(@Param("name") String name);

• Named Parameters:

@Query("SELECT u FROM UserEntity u WHERE u.age > :age")
List<UserEntity> findByAgeGreaterThanCustom(@Param("age") int age);

• Positional Paramters:

@Query("SELECT u FROM UserEntity u WHERE u.name = ?1 AND u.age = ?2")
List<UserEntity> findByNameAndAgeCustom(String name, int age);

Application

• UserRepository used to look like the following:

public class UserRepository extends SimpleDataRepository<UserEntity, Long> {

    public List<UserEntity> findAllScoreGreaterThan(int score){

        return this.findAll().stream()
                .filter(
                        it -> {
                            return it.getScore() >= score;
                        }
                ).collect(Collectors.toList());
    }
}

• We add a method to the UserRepository interface as following:

public interface UserRepository extends JpaRepository<UserEntity, Long>{
    public List<UserEntity> findAllByScoreGreaterThanEqual(int sc);
}

• From the above derived query method, the generated SQL query would be: SELECT * FROM user WHERE score >= ??.
• We modify the filterScore(int score) in the UserService accordingly, to reflect the modified method name:

public List<UserEntity> filterScore(int score){
    return userRepository.findAllByScoreGreaterThanEqual(score);
}

• From the GET request http://localhost:8080/api/user/score?score=90, we get the following response, which includes all UserEntity with score greater than or equal to 90:

[
    {
        "id": 1,
        "name": "User1",
        "score": 100
    },
    {
        "id": 2,
        "name": "User2",
        "score": 200
    },
    {
        "id": 3,
        "name": "User3",
        "score": 90
    }
]

• Now we create a query method that fetches UserEntity with score greaterThanEqual and lessThanEqual, as well as corresponding methods in UserService and UserApiController, with endpoint /minmax

public interface UserRepository extends JpaRepository<UserEntity, Long>{

    public List<UserEntity> findAllByScoreGreaterThanEqual(int sc);

    public List<UserEntity> findAllByScoreGreaterThanEqualAndScoreLessThanEqual(int min, int max);
}

• With a GET request sent to http://localhost:8080/api/user/minmax?min=90&max=100 (min = 90, max = 100), we get the following response:

[
    {
        "id": 1,
        "name": "User1",
        "score": 100
    },
    {
        "id": 3,
        "name": "User3",
        "score": 90
    }
]

• Detailed rules available at: https://docs.spring.io/spring-data/jpa/docs/current-SNAPSHOT/reference/html/#jpa.query-methods

• Now using JPQL query:

public interface UserRepository extends JpaRepository<UserEntity, Long>{

    // select * from user where score > ??
    public List<UserEntity> findAllByScoreGreaterThanEqual(int sc);

    public List<UserEntity> findAllByScoreGreaterThanEqualAndScoreLessThanEqual(int min, int max);

    @Query(
            value = "select u from user u where u.score >= ?1 AND score <= ?2"
    )
    public List<UserEntity> score(int min, int max);
}

//and in userService:
public List<UserEntity> filterScore(int min, int max){
    return userRepository.score(min, max);
}

• Or if we want native SQL queries instead of JPQL queries:

@Query(
	value = "select * from user as u where u.score >= ?1 AND u.score <= ?2",
    nativeQuery = true //default value is false
)
public List<UserEntity> score(int min, int max);

• Or with parameter binding:

@Query(
	value = "select * from user as u where u.score >= :min AND u.score <= :max",
    nativeQuery = true //default value is false
)
public List<UserEntity> score(
	@Param(value = "min") int min, 
    @Param(value = "max") int max
);