Tag

JPQL Spring Boot

Browsing

The JPA (Java Persistence API) – a part of Spring Data – is an Object Relational Mapping (ORM) framework – a part of the Java EE platform. JPA simplifies the implementation of the data access layer by letting developers work with an object oriented API instead of writing SQL queries by hand. The most popular JPA implementations are Hibernate, EclipseLink, and OpenJPA.

Spring Data integrates with most of the popular data access technologies, including JPA, MongoDB, Redis, Cassandra, Solr, ElasticSearch, etc.

Using Spring Data JPA with Spring Boot – Install JPA Spring

Create a Spring Boot Maven project and add the following dependencies to pom.xml, in this example I will use H2 database, if you haven’t installed H2 database yet, you can download it at https://www.h2database.com/

<dependencies>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
		</dependency>

	</dependencies>

Coreconcept

The central interface in Spring Data repository abstraction is Repository . It takes the domain class to manage as well as the id type of the domain class as type arguments. This interface acts primarily as a marker interface to capture the types to work with and to help you to discover interfaces that extend this one. The CrudRepository provides sophisticated CRUD functionality for the entity class that is being managed.

public interface CrudRepository<T, ID extends Serializable>
    extends Repository<T, ID> {
                                                                                                                       (1)
    <S extends T> S save(S entity);
                                                                                                                       (2)
    T findOne(ID primaryKey);
                                                                                                                       (3)
    Iterable<T> findAll();

    Long count();
                                                                                                                       (4)
    void delete(T entity);
                                                                                                                       (5)
    boolean exists(ID primaryKey);
                                                                                                                       (6)
    // … more functionality omitted.
}
  1. Saves the given entity.
  2. Returns the entity identified by the given id.
  3. Returns all entities.
  4. Returns the number of entities.
  5. Deletes the given entity.
  6. Indicates whether an entity with the given id exists.

Query data in Spring JPA with JPQL

In order to execute query data in Spring with JPA, we can annotate the method with the @Query annotation.

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import stackjava.com.sbdataquery.entities.Customer;
@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {
  @Query("SELECT e FROM Customer e ORDER BY e.name DESC")
  List<Customer> findAllOrderByNameDesc();
  @Query(value = "SELECT e.* FROM customer e ORDER BY e.name DESC", nativeQuery = true)
  List<Customer> findAllOrderByNameDescNative();
  @Query("SELECT e FROM Customer e WHERE e.name = ?1")
  List<Customer> findByName(String name);
  @Query("SELECT e FROM Customer e WHERE e.name = :name AND e.address = :address")
  List<Customer> findByNameAndAddress(@Param("name") String name, @Param("address") String address);
  @Query("SELECT e FROM Customer e WHERE e.name like ?1")
  List<Customer> findByNameLike(String name);
}

@Repository marks this class use JPA for query data. When we using ?1, ?2 or :name, :address in SQL statement, Spring Boot will bind method parameters to place holders in SQL statement automatically. Besides, JPA also supply another way to interact with data via method naming.

Derived Query Methods

For simple queries, it’s easy to derive what the query should be just by looking at the corresponding method name in our code.

In Spring JPA, the mechanism for constructing the query through the name of the method is specified by the following prefixes: find…By, read…By, query…By, count…By, and get…By. The rest will be parsed by the attribute’s name (first capital capitalization). If we have multiple conditions, the properties can be combined with the letters And or Or.

interface PersonRepository extends JpaRepository<User, Long> {
      // short version
    Person findByLastname(String lastname);
    // full version
    Person findPersonByLastname(String lastname);

    Person findAllByLastname(String lastname);

    List<Person> findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname);

    // findDistinctPeople and remove duplicate record (distinct query)
    List<Person> findDistinctPeopleByLastnameOrFirstname(String lastname, String firstname);
    List<Person> findPeopleDistinctByLastnameOrFirstname(String lastname, String firstname);

    //Search LastName in-cases-sensitive
    List<Person> findByLastnameIgnoreCase(String lastname);

    // Search Lastname and First name in-case-sensitive
    List<Person> findByLastnameAndFirstnameAllIgnoreCase(String lastname, String firstname);

    // OrderBy Firstname ASC
    List<Person> findByLastnameOrderByFirstnameAsc(String lastname);
    // OrderBy Firstname Desc
    List<Person> findByLastnameOrderByFirstnameDesc(String lastname);
    // same as WHERE name LIKE '%value'
    List<User> findByNameEndingWith(String suffix);
    //same as WHERE name LIKE '%value%'.
    List<User> findByNameLike(String likePattern);
}