February 28, 2022

Spring Data JPA Auditing Example

In this post we’ll see how to configure Spring Data JPA to automatically store auditing information like created by, created date, modified by, modified date for any entity.

Spring Data auditing support

Spring Data provides support to keep track of who created or changed an entity and the time when this happened. To use this audit functionality you need to equip your entity classes with auditing metadata that can be defined either using annotations or by implementing an interface.

Annotations that are used are as following-

  • @CreatedBy- To capture the user who created the entity.
  • @LastModifiedBy- To capture the user who modified the entity.
  • @CreatedDate- To capture the point in time when entity is created.
  • @LastModifiedDate- To capture the point in time when entity was last modified.

If you want to see how to create Maven project, please check this post- Create Java Project Using Maven in Eclipse

For setting up a Spring Data JPA project and the required maven dependencies please check this post- Spring Data JPA Example

Apart from the dependencies mentioned in that post you also need spring-aspects.jar on the classpath for auditing features, for that you need to add following dependency.

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-aspects</artifactId>
  <version>${spring.version}</version>
</dependency>

Spring Data annotations @CreatedBy, @LastModifiedBy, @CreatedDate, @LastModifiedDate

In the example we’ll see how to configure Spring Data JPA to automatically store the auditing information like created by, created date, modified by, modified date for any entity.

For the example entity used is post and the table has columns post_title and post_content to store title and content. Apart from that information about the user who is creating or modifying any record and the time of creation and modification is also persisted.

DB Table

MySQL DB table used for this Spring data JPA auditing example can be created using the following query.

CREATE TABLE `post` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_title` varchar(100) NOT NULL,
  `post_content` varchar(250) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

JPA Entity class

There should be an entity class which maps to the post table in DB. Better practice is to keep audit related fields in a separate class and the entities that need audit information can extend that class. That way other entities can also reuse the super class.

So there is a class Audit which is annotated as @MappedSuperclass. A mapped superclass has no separate table defined for it. It's mapping information is applied to the entities that inherit from it.

Spring Data JPA provides an entity listener called AuditingEntityListener that can be used to trigger capturing auditing information. AuditingEntityListener class has callback methods annotated with @PrePersist and @PreUpdate annotations that are triggered for persist event and update event respectively.

You can enable the AuditingEntityListener per entity using the @EntityListeners annotation. You can also specify your own custom listener class with @EntityListeners annotation.

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.EntityListeners;
import javax.persistence.MappedSuperclass;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class Audit<T> {
  @CreatedBy
  @Column(name="created_by")
  protected T createdBy;
  @LastModifiedBy
  @Column(name="modified_by")
  protected T modifiedBy;
  @CreatedDate
  @Temporal(TemporalType.TIMESTAMP)
  @Column(name="created_date")
  protected Date createdDate;
  @LastModifiedDate
  @Temporal(TemporalType.TIMESTAMP)
  @Column(name="modified_date")
  protected Date modifiedDate;
  public T getCreatedBy() {
    return createdBy;
  }
  public void setCreatedBy(T createdBy) {
    this.createdBy = createdBy;
  }
  public T getModifiedBy() {
    return modifiedBy;
  }
  public void setModifiedBy(T modifiedBy) {
    this.modifiedBy = modifiedBy;
  }
  public Date getCreatedDate() {
    return createdDate;
  }
  public void setCreatedDate(Date createdDate) {
    this.createdDate = createdDate;
  }
  public Date getModifiedDate() {
    return modifiedDate;
  }
  public void setModifiedDate(Date modifiedDate) {
    this.modifiedDate = modifiedDate;
  }	
}

As you can see above super class has fields to capture creation and modification data which is mapped to appropriate columns.

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="post")
public class Post extends Audit<String> {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name="post_id")
  private int id;
  @Column(name="post_title")
  private String postTitle;
  @Column(name="post_content")
  private String content;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getPostTitle() {
    return postTitle;
  }
  public void setPostTitle(String postTitle) {
    this.postTitle = postTitle;
  }
  public String getContent() {
    return content;
  }
  public void setContent(String content) {
    this.content = content;
  }

  @Override
  public String toString() {
    
    return "Id= "+ getId() + " Title= " + getPostTitle() + " Content= "+ getContent()
    + " createdBy= " + getCreatedBy() + " modifiedBy= " + getModifiedBy() 
    + " createdDate= " + getCreatedDate() + " modifiedDate= " + getModifiedDate() ;
  }
}

AuditorAware interface

In case you use either @CreatedBy or @LastModifiedBy annotations, the auditing infrastructure needs to capture information about the current user. To do so, you have to implement an AuditorAware<T> interface infrastructure to tell who the current user or system interacting with the application is.

For our example we are taking the user who is currently logged in using the system property.

import java.util.Optional;
import org.springframework.data.domain.AuditorAware;

public class LoggedInUserAuditorAwareImpl implements AuditorAware<String>{

  @Override
  public Optional<String> getCurrentAuditor() {
    return Optional.of(System.getProperty("user.name"));
  }
}

Spring Data JPA Repository

import org.springframework.data.repository.CrudRepository;
import com.knpcode.springproject.model.Post;

public interface PostRepository extends CrudRepository<Post, Integer>{

}

You can see that PostRepository interface extends CrudRepository which takes the domain class to manage (Post in this case) as well as the id type of the domain class as type arguments.

Service class

From the service layer we’ll call the repository methods. Notice that repository instance has to be injected in the service class.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.knpcode.springproject.dao.PostRepository;
import com.knpcode.springproject.model.Post;

@Service
public class PostService {
  @Autowired
  private PostRepository repository;
  public Post addPost(Post post) {
    return repository.save(post);
  }

  public Post getPostById(int id) {
    return repository.findById(id).get();
  }
}

Java Config class

In this Spring data JPA auditing example Java configuration is used so class is annotated with @Configuration annotation.

@ComponentScan annotation configures component scanning.

@EnableJpaRepositories annotation enables the JPA repositories. Package to scan for the repositories is provided as a value with this annotation.

@EnableTransactionManagement annotation enables Spring's annotation-driven transaction management capability.

@EnableJpaAuditing annotation enables auditing in JPA.

For setting up DataSource DB properties are read from a properties file, path for the properties file is configured using @PropertySource annotation.

With in this Java config class we set up a EntityManagerFactory and use Hibernate as persistence provider. There is also a bean definition for AuditorAware implementation.

@Configuration
@ComponentScan(basePackages = "com.knpcode.springproject")
@EnableJpaRepositories(basePackages = "com.knpcode.springproject.dao")
@EnableTransactionManagement
@EnableJpaAuditing
@PropertySource("classpath:config/db.properties")
public class JPAConfig {
  @Autowired
  private Environment env;
  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.knpcode.springproject.model");
    factory.setDataSource(dataSource());
    factory.setJpaProperties(hibernateProperties());
    return factory;
  }

  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName(env.getProperty("db.driverClassName"));
    ds.setUrl(env.getProperty("db.url"));
    ds.setUsername(env.getProperty("db.username"));
    ds.setPassword(env.getProperty("db.password"));
    return ds;
  }

  Properties hibernateProperties() {
    Properties properties = new Properties();
    properties.setProperty("hibernate.dialect", env.getProperty("hibernate.sqldialect"));
    properties.setProperty("hibernate.show_sql", env.getProperty("hibernate.showsql"));
    return properties;
  }

  @Bean
  public PlatformTransactionManager transactionManager() {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory().getObject());
    return txManager;
  }

  @Bean
  public AuditorAware<String> auditorProvider() {
    return new LoggedInUserAuditorAwareImpl();
  }
}
db.properties file
db.driverClassName=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/knpcode
db.username=
db.password=
hibernate.sqldialect=org.hibernate.dialect.MySQLDialect
hibernate.showsql=true

Testing the example

Following class with main method is used to test the example. Initially a post instance is persisted. As you can see from the query audit information (created_by, created_date, modified_by, modified_date) is also inserted.

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import com.knpcode.springproject.model.Post;
import com.knpcode.springproject.service.PostService;

public class App {
  public static void main(String[] args) {
    AbstractApplicationContext context = new AnnotationConfigApplicationContext(JPAConfig.class);
    PostService postService =  context.getBean("postService", PostService.class);
    Post post = new Post();
    post.setPostTitle("A Post on Spring Data JPA");
    post.setContent("Spring Data JPA reduces boiler plate code");
    postService.addPost(post);
    //postService.updatePost(1);
    
    context.close();
  }
}

Generated query from the logs-

Hibernate: insert into post (created_by, created_date, modified_by, modified_date, post_content, post_title) values (?, ?, ?, ?, ?, ?)

Fetching record

Fetching the record that is persisted.

public class App {
  public static void main(String[] args) {
    AbstractApplicationContext context = new AnnotationConfigApplicationContext(JPAConfig.class);
    PostService postService =  context.getBean("postService", PostService.class);

    Post post = postService.getPostById(1);
    System.out.println("Post- "+ post);
    context.close();
  }
}

Generated Query and fetched record

Hibernate: select post0_.post_id as post_id1_1_0_, post0_.created_by as created_2_1_0_, post0_.created_date as created_3_1_0_, post0_.modified_by as modified4_1_0_, post0_.modified_date as modified5_1_0_, post0_.post_content as post_con6_1_0_, post0_.post_title as post_tit7_1_0_ from post post0_ where post0_.post_id=?

Post- Id= 1 Title= A Post on Spring Data JPA Content= Spring Data JPA reduces boiler plate code createdBy= knpcode modifiedBy= knpcode createdDate= 2019-09-30 11:16:47.0 modifiedDate= 2019-09-30 11:16:47.0
Updating the record
public class App {
  public static void main(String[] args) {
    AbstractApplicationContext context = new AnnotationConfigApplicationContext(JPAConfig.class);
    PostService postService =  context.getBean("postService", PostService.class);
    
    Post post = postService.getPostById(1);
    post.setPostTitle("New Title");
    postService.addPost(post);
    post = postService.getPostById(1);
    System.out.println("Post- " + post);
    context.close();
  }
}

As you can see modified date is changed when the record is fetched now.

Post- Id= 1 Title= New Title Content= Spring Data JPA reduces boiler plate code createdBy= knpcode modifiedBy= knpcode createdDate= 2019-09-30 11:16:47.0 modifiedDate= 2019-09-30 12:08:50.0

That's all for the topic Spring Data JPA Auditing Example. If something is missing or you have something to share about the topic please write a comment.


You may also like

February 27, 2022

Spring Data JPA Pagination and Sorting Example

In this Spring Data JPA pagination and sorting example we’ll see how to use PagingAndSortingRepository to paginate access by providing number of records per page and page number. You can sort records too by passing the field (or group of fields) on which sorting is done along with page properties or separately.

We’ll create a rest web service using Spring Web MVC, JPA implementation used is Hibernate and DB is MySQL.

For maven dependencies required for the example and configuration classes for setting up EntityManagerFactory and web application you can refer this post- Spring Data JPA @Query Annotation Example

DB table Query

MySQL DB table used for this Spring data JPA can be created using the following query.
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `department` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

JPA Entity class

This is the entity class which maps to the employee table in DB.

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="employee")
public class Employee {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;
  @Column(name="first_name")
  private String firstName;
  @Column(name="last_name")
  private String lastName;
  @Column(name="department")
  private String dept;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  public String getDept() {
    return dept;
  }
  public void setDept(String dept) {
    this.dept = dept;
  }

  @Override
  public String toString() {
    return "Id= " + getId() + " First Name= " + 
             getFirstName() + " Last Name= " + getLastName() + 
             " Dept= "+ getDept();
  }
}

@Entity annotation specifies that this model class is an entity.

@Table annotation specifies the primary table for the entity.

@Id annotation specifies the primary key of the entity.

@GeneratedValue specifies the primary key generation strategy which is autoincrement in this case.

@Column annotation specifies the mapped table column name for the field.

Spring Data JPA Repository

import java.util.List;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import com.knpcode.springproject.model.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
	
  List<Employee> findByLastName(String lastName, Pageable pageable);

  List<Employee> findByDept(String dept, Sort sort);
}

EmployeeRepository interface has two methods-

  • findByLastName – Where Pageable object is passed to provide pagination properties.
  • findByDept- In this method Sort object is passed to provide options for sorting.

You can see that EmployeeRepository interface extends JpaRepository which takes the domain class to manage (Employee in this case) as well as the id type of the domain class as type arguments. Since JpaRepository extends PagingAndSortingRepository so your interface indirectly extends PagingAndSortingRepository.

PagingAndSortingRepository extends CrudRepository interface and defines two methods of its own.

public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
  /**
   * Returns all entities sorted by the given options.
   */
  Iterable<T> findAll(Sort sort);

  /**
   * Returns a Page of entities meeting the paging restriction provided in the Pageable object.
   */
  Page<T> findAll(Pageable pageable);
}

Rest Controller

Using a Rest controller class we’ll map the path to the methods that are to be called for the requests. Parameters for passing in Pageable and Sort objects creation are also received as @RequestParam in the methods in Controller class.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
import com.knpcode.springproject.model.Employee;
import com.knpcode.springproject.service.EmployeeService;

@RestController
@RequestMapping("/employee")
public class EmployeeController {
  @Autowired
  EmployeeService empService;
  @GetMapping("/{id}")
  public Employee getEmployeeById(@PathVariable int id) {
    return empService.getEmployeeById(id);
  }
  @GetMapping
  public List<Employee> getAllEmployees(@RequestParam(value="pageNo", defaultValue="0") Integer pageNo,
      @RequestParam(value="sortKey", defaultValue="lastName") String sortKey)
  {
    return empService.getAllEmployees(pageNo, sortKey);
  }
  @DeleteMapping("/{id}")
  @ResponseStatus(HttpStatus.OK)
  public void deleteEmployeeById(@PathVariable int id){
    empService.deleteEmployeeById(id);
  }
  @PostMapping
  @ResponseStatus(HttpStatus.CREATED)
  public Employee addEmployee(@RequestBody Employee emp) {
    return empService.addEmployee(emp);
  }
  @GetMapping("/lastname/{lastName}")
  public List<Employee> getEmployeeByLastName(@PathVariable String lastName, 
          @RequestParam(value="pageNo", defaultValue="0") Integer pageNo) 
  {
    return empService.getEmployeeByLastName(lastName, pageNo);
  }
  @GetMapping("/dept/{department}")
  public List<Employee> getEmployeeByDepartment(@PathVariable String department) {
    return empService.getEmployeeByDepartment(department);
  }
}

Spring Data JPA example – Service class

From the service layer we’ll call the repository methods. Notice that repository instance has to be injected in the service class.

In the Service class we’ll create Pageable and Sort object that are passed in the methods.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import com.knpcode.springproject.dao.EmployeeRepository;
import com.knpcode.springproject.model.Employee;

@Service
public class EmployeeService {
  @Autowired
  private EmployeeRepository repository;

  public Employee getEmployeeById(int id) {
    return repository.findById(id).get();
  }

  public List<Employee> getAllEmployees(Integer pageNo, String sortKey)
  {
    // Setting no. of records in each page, page no., sort field
    int noOfRecords = 2;
    Pageable page = PageRequest.of(pageNo, noOfRecords, Sort.by(sortKey));
    Page<Employee> pagedResult = repository.findAll(page);
    // changing to List
    return pagedResult.getContent();
  }
	
  public void deleteEmployeeById(int id){
    repository.deleteById(id);
  }

  public Employee addEmployee(Employee emp) {
    return repository.save(emp);
  }

  public List<Employee> getEmployeeByLastName(String lastName, Integer pageNo) {
    // Setting no. of records in each page, page no., sort field
    int noOfRecords = 2;
    Pageable page = PageRequest.of(pageNo, noOfRecords, Sort.by("firstName"));
    return repository.findByLastName(lastName, page);
  }
	
  public List<Employee> getEmployeeByDepartment(String department) {
    // sort field
    Sort sortKey = Sort.by("lastName");
    return repository.findByDept(department, sortKey);
  }
}

In getAllEmployees() and getEmployeeByLastName() methods a org.springframework.data.domain.Pageable object is created where page number, number of records per page and sort key are passed as parameters.

In getEmployeeByDepartment() method a org.springframework.data.domain.Sort object is created by passing the field on which the sorting is done.

Paging and Sorting options

If you want records to be sorted with in the paginated records then you can pass Sort instance while creating Pageable object.

Pageable page = PageRequest.of(pageNo, noOfRecords, Sort.by(sortKey));

When creating Pageable object passing sort field is optional so you can have a Pageable instance created as following too.

Pageable page = PageRequest.of(pageNo, noOfRecords);

If you want records to be displayed in the sorted order only without any pagination then pass only Sort object.

Sort sortKey = Sort.by("lastName");

If you want to sort on more than one fields then you combine sorting fields using and method. For example if you want to sort on both lastName and firstName fields.

Sort sortKey = Sort.by("lastName").and(Sort.by("firstName"));

To specify ascending (default order) or descending order for sorting there are ascending() and descending() methods.

Sort sortKey = Sort.by("lastName").ascending().and(Sort.by("firstName"));

Page Vs Slice

If you have noticed in the getAllEmployees() method of the Service class repository.findAll(page) call returns a Page instance. Other options for returning data with paginated access are Slice and List.

org.springframework.data.domain.Page<T>- It is an interface that represents a sublist of a list of objects. It has methods to get information about the contained entire list like getTotalElements() and getTotalPages()

org.springframework.data.domain.Slice<T>- A slice of data that indicates whether there's a next or previous slice available.

Deploying the Spring Data JPA application

Right clicking the project and select Run As – Maven build, provide goal as clean install. If the build is successful you will have your application packaged as a war which you can deploy on web container like Tomcat and then test the application.

For testing the RESTful webservice, Postman rest client is used.

Get all employees

Note that the request selected is GET and the URL is- http://localhost:8080/SpringJPAProject/employee/?pageNo=0&sortKey=lastName where pageNo and sortKey are request parameters.

Spring data paging

For another page i.e. page number 1 you can send the URL as http://localhost:8080/SpringJPAProject/employee/?pageNo=1&sortKey=lastName

Note that page number starts from 0.

From the console you can see that query sent for page number 1 is as following-

Hibernate: select employee0_.id as id1_0_, employee0_.department as departme2_0_, employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ from employee employee0_ order by employee0_.last_name asc limit ?, ?
Hibernate: select count(employee0_.id) as col_0_0_ from employee employee0_

As you can see a separate count query is also sent. That is one drawback of using Page instance which has the overhead of having this count query for getting total number of pages.

Get employees by department

Spring Data JPA sorting

That's all for the topic Spring Data JPA Pagination and Sorting Example. If something is missing or you have something to share about the topic please write a comment.


You may also like

February 26, 2022

Spring Data JPA @Query Annotation Example

In the Spring Data JPA Example we have already seen an integrated example of Spring Rest Controller + JPA (Hibernate) + MySQL. In that example query look up strategy for user defined query was automatic where Spring framework did the work of deriving query by parsing the method name. Though getting a query derived from the method name is quite convenient but you may face the situation where method name parser does not support the keyword you used or the method name would get unnecessarily ugly. In that case Spring Data JPA provides two options-

  • You can use JPA named queries using @NamedQuery annotation.
  • Annotate your query method with @Query.

In this article we'll see Spring data JPA example with @Query annotation.

For Spring Data JPA example using @NamedQuery annotation check this post- Spring Data JPA @NamedQuery Annotation Example

Spring Data JPA with @Query annotation

we’ll create a rest web service using Spring Web MVC, JPA implementation used is Hibernate and DB is MySQL.

The example shows the use of the @Query annotation. Advantage of using Spring Data JPA @Query annotation is that it is applied on the query method in the repository interface rather than annotating them to the domain class. This will free the domain class from persistence specific information and place the query in the repository interface itself.

Note that queries annotated to the query method using @Query will take precedence over queries defined using @NamedQuery or named queries defined in XML using <named-query /> element.

Maven dependencies

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.knpcode</groupId>
  <artifactId>SpringJPAProject</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <name>SpringJPA</name>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 
    <spring.version>5.1.8.RELEASE</spring.version>
    <spring.data>2.1.10.RELEASE</spring.data>
    <hibernate.jpa>5.4.3.Final</hibernate.jpa>
    <mysql.version>8.0.17</mysql.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <!-- Spring data JPA -->
    <dependency>
      <groupId>org.springframework.data</groupId>
      <artifactId>spring-data-jpa</artifactId>
      <version>${spring.data}</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.9.6</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.0</version>
      <scope>provided</scope>
    </dependency>
    <!-- Hibernate -->
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
      <version>${hibernate.jpa}</version>
    </dependency>
    <!-- MySQL Driver -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql.version}</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.25</version>
    </dependency>
  </dependencies>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.0</version>
        <configuration>
          <release>11</release>
        </configuration>
      </plugin>
      <plugin>
        <artifactId>maven-war-plugin</artifactId>
        <version>3.2.1</version>
        <configuration>
          <warSourceDirectory>WebContent</warSourceDirectory>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

Dependencies are added for Spring core, Spring context as well as for Spring Web and Spring data JPA.

Dependency for Hibernate is added as Hibernate JPA implementation is used.

MySQL connector is used for connecting to MySQL DB from Java application.

Jackson databind is needed for webservice responses which are sent as JSON.

DB table Query

MySQL DB table used for this Spring data JPA can be created using the following query.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `department` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Spring data JPA example – Entity class

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="employee")
public class Employee {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;
  @Column(name="first_name")
  private String firstName;
  @Column(name="last_name")
  private String lastName;
  @Column(name="department")
  private String dept;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  public String getDept() {
    return dept;
  }
  public void setDept(String dept) {
    this.dept = dept;
  }

  @Override
  public String toString() {
    return "Id= " + getId() + " First Name= " + 
             getFirstName() + " Last Name= " + getLastName() + 
             " Dept= "+ getDept();
  }
}

This is the entity class which maps to the employee table in DB.

@Entity annotation specifies that this model class is an entity.

@Table annotation specifies the primary table for the entity.

@Id annotation specifies the primary key of the entity.

@GeneratedValue specifies the primary key generation strategy which is autoincrement in this case.

@Column annotation specifies the mapped table column name for the field.

Spring Data JPA Repository

import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import com.knpcode.springproject.model.Employee;

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {
  List<Employee> findByLastName(String lastName);

  @Query("select e from Employee e where e.dept = ?1")
  List<Employee> findByDepartment(String department);
}

EmployeeRepository interface extends CrudRepository which takes the domain class to manage (Employee in this case) as well as the id type of the domain class as type arguments.

Apart from the methods inherited from CrudRepository there are two methods defined in EmployeeRepository inerface-

Method findByDepartment() has a query annotated with the method using @Query annotation so that query is used for the method rather than deriving query.

For method findByLastName Spring data derives a query using the method name.

Spring Data JPA example – Service class

From the service layer we’ll call the repository methods. Notice that repository instance has to be injected in the service class.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.knpcode.springproject.dao.EmployeeRepository;
import com.knpcode.springproject.model.Employee;

@Service
public class EmployeeService {
  @Autowired
  private EmployeeRepository repository;

  public Employee getEmployeeById(int id) {
    return repository.findById(id).get();
  }

  public List<Employee> getAllEmployees(){
    return (List<Employee>) repository.findAll();
  }

  public void deleteEmployeeById(int id){
    repository.deleteById(id);
  }

  public Employee addEmployee(Employee emp) {
    return repository.save(emp);
  }

  public List<Employee> getEmployeeByLastName(String lastName) {
    return repository.findByLastName(lastName);
  }

  public List<Employee> getEmployeeByDepartment(String department) {
    return repository.findByDepartment(department);
  }
}

Rest Controller

Using a Rest controller class we’ll map the path to the methods that are to be called for the requests.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
import com.knpcode.springproject.model.Employee;
import com.knpcode.springproject.service.EmployeeService;

@RestController
@RequestMapping("/employee")
public class EmployeeController {
  @Autowired
  EmployeeService empService;
  @GetMapping("/{id}")
  public Employee getEmployeeById(@PathVariable int id) {
    return empService.getEmployeeById(id);
  }
  @GetMapping
  public List<Employee> getAllEmployees(){
    return empService.getAllEmployees();
  }
  @DeleteMapping("/{id}")
  @ResponseStatus(HttpStatus.OK)
  public void deleteEmployeeById(@PathVariable int id){
    empService.deleteEmployeeById(id);
  }
  @PostMapping
  @ResponseStatus(HttpStatus.CREATED)
  public Employee addEmployee(@RequestBody Employee emp) {
    return empService.addEmployee(emp);
  }
  @GetMapping("/lastname/{lastName}")
  public List<Employee> getEmployeeByLastName(@PathVariable String lastName) {
    return empService.getEmployeeByLastName(lastName);
  }
  @GetMapping("/dept/{department}")
  public List<Employee> getEmployeeByDepartment(@PathVariable String department) {
    return empService.getEmployeeByDepartment(department);
  }
}

Spring Data JPA – configuration classes

In this Spring data JPA example Java configuration is used so class is annotated with @Configuration annotation.

For setting up DataSource DB properties are read from a properties file, path for the properties file is configured using @PropertySource annotation.

@EnableJpaRepositories annotation enables the JPA repositories. Package to scan for the repositories is provided as a value with this annotation.

@EnableTransactionManagement annotation enables Spring's annotation-driven transaction management capability.

With in this Java config class we set up a EntityManagerFactory and use Hibernate as persistence provider.

import java.util.Properties;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.data.repository.query.QueryLookupStrategy.Key;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableJpaRepositories(basePackages = "com.knpcode.springproject.dao", queryLookupStrategy=Key.CREATE_IF_NOT_FOUND)
@EnableTransactionManagement
@PropertySource("classpath:config/db.properties")
public class JPAConfig {
  @Autowired
  private Environment env;
  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.knpcode.springproject.model");
    factory.setDataSource(dataSource());
    factory.setJpaProperties(hibernateProperties());
    return factory;
  }

  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName(env.getProperty("db.driverClassName"));
    ds.setUrl(env.getProperty("db.url"));
    ds.setUsername(env.getProperty("db.username"));
    ds.setPassword(env.getProperty("db.password"));
    return ds;
  }

  Properties hibernateProperties() {
    Properties properties = new Properties();
    properties.setProperty("hibernate.dialect", env.getProperty("hibernate.sqldialect"));
    properties.setProperty("hibernate.show_sql", env.getProperty("hibernate.showsql"));
    return properties;
  }

  @Bean
  public PlatformTransactionManager transactionManager() {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory().getObject());
    return txManager;
  }
}

If you are using XML configuration then the configuration for enabling JPA repositories is-

<jpa:repositories base-package="com.knpcode.springproject.dao"/>
db.properties file
db.driverClassName=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/knpcode
db.username=
db.password=
hibernate.sqldialect=org.hibernate.dialect.MySQLDialect
hibernate.showsql=true

To set up the web application using Java config rather than using the web.xml we’ll need the following classes.

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class WebConfigInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {

  @Override
  protected Class<?>[] getRootConfigClasses() {
    // TODO Auto-generated method stub
    return null;
  }

  @Override
  protected Class<?>[] getServletConfigClasses() {
    return new Class<?>[] {WebConfig.class};
  }

  @Override
  protected String[] getServletMappings() {
    return new String[] {"/"};
  }
}
@Configuration
@EnableWebMvc
@ComponentScan(basePackages = "com.knpcode.springproject")
public class WebConfig implements WebMvcConfigurer{

}

Deploying the Spring Data JPA application

Right clicking the project and select Run As – Maven build, provide goal as clean install. If the build is successful you will have your application packaged as a war which you can deploy on web container like Tomcat and then test the application.

For testing the RESTful webservice, Postman rest client is used.

Adding employee

Spring Data JPA Example

Note that the request selected is POST and the URL is http://localhost:8080/SpringJPAProject/employee

Data is sent as request body in JSON format. In the response added Employee data is sent back.

Find by department (@Query annotated query method)

Spring data @Query

Get Employee by last name

You can also also send requests directly from browser as done for this request.

spring data

That's all for the topic Spring Data JPA @Query Annotation Example. If something is missing or you have something to share about the topic please write a comment.


You may also like

February 25, 2022

Spring Data JPA @NamedQuery Annotation Example

In the Spring Data JPA Example we have already seen an integrated example of Spring MVC + JPA (Hibernate) + MySQL. In that example, query look up strategy for user defined query was automatic where Spring framework did the work of deriving query by parsing the method name. Though getting a query derived from the method name is quite convenient but you may face the situation where method name parser does not support the keyword you used or the method name would get unnecessarily ugly. In that case you can use JPA named queries using @NamedQuery annotation or annotate your query method with @Query. In this article we'll see Spring data JPA example with @NamedQuery annotation.

For Spring Data JPA example using Query annotation check this post- @Spring Data JPA @Query Annotation Example

Spring Data JPA with named query

we’ll create a rest web service using Spring Web MVC, JPA implementation used is Hibernate and DB is MySQL.

Example shows the use of the <named-query /> element (in case of XML configuration) and @NamedQuery annotation. The queries for these configuration elements have to be defined in the JPA query language. If you want to define queries in native SQL you can use <named-native-query /> or @NamedNativeQuery too. But drawback with native SQL is that you lose the database platform independence.

Maven dependencies

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.knpcode</groupId>
  <artifactId>SpringJPAProject</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <name>SpringJPA</name>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 
    <spring.version>5.1.8.RELEASE</spring.version>
    <spring.data>2.1.10.RELEASE</spring.data>
    <hibernate.jpa>5.4.3.Final</hibernate.jpa>
    <mysql.version>8.0.17</mysql.version>
  </properties>
    <dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>${spring.version}</version>
    </dependency>
        <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${spring.version}</version>
    </dependency>
     <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-web</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <!-- Spring data JPA -->
    <dependency>
    	<groupId>org.springframework.data</groupId>
    	<artifactId>spring-data-jpa</artifactId>
    	<version>${spring.data}</version>
	</dependency>
    <dependency>
    	<groupId>com.fasterxml.jackson.core</groupId>
    	<artifactId>jackson-databind</artifactId>
    	<version>2.9.6</version>
	</dependency>
	<dependency>
		<groupId>javax.servlet</groupId>
		<artifactId>javax.servlet-api</artifactId>
		<version>4.0.0</version>
		<scope>provided</scope>
	</dependency>
    <!-- Hibernate -->
	<dependency>
    	<groupId>org.hibernate</groupId>
    	<artifactId>hibernate-entitymanager</artifactId>
    	<version>${hibernate.jpa}</version>
	</dependency>
	<!-- MySQL Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.version}</version>
    </dependency>
        <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.25</version>
    </dependency>
  </dependencies>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.0</version>
        <configuration>
          <release>11</release>
        </configuration>
      </plugin>
      <plugin>
        <artifactId>maven-war-plugin</artifactId>
        <version>3.2.1</version>
        <configuration>
          <warSourceDirectory>WebContent</warSourceDirectory>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

Dependencies are added for Spring core, Spring context as well as for Spring Web and Spring data JPA.

Dependency for Hibernate is added as Hibernate JPA implementation is used.

MySQL connector is used for connecting to MySQL DB from Java application.

Jackson databind is needed for webservice responses which are sent as JSON.

DB table Query

MySQL DB table used for this Spring data JPA can be created using the following query.

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `department` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

JPA Entity – Spring data JPA

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table(name="emp")
@NamedQuery(name = "Employee.findByDepartment", query = "select e from Employee e where e.dept = ?1")
public class Employee {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;
  @Column(name="first_name")
  private String firstName;
  @Column(name="last_name")
  private String lastName;
  @Column(name="department")
  private String dept;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  public String getDept() {
    return dept;
  }
  public void setDept(String dept) {
    this.dept = dept;
  }

  @Override
  public String toString() {
    return "Id= " + getId() + " First Name= " + 
             getFirstName() + " Last Name= " + getLastName() + 
             " Dept= "+ getDept();
  }
}

This is the entity class which corresponds to the emp table in DB.

@Entity annotation specifies that this model class is an entity.

@Table annotation specifies the primary table for the entity.

@NamedQuery annotation specifies the named query. If you have more than one query you can use @NamedQueries annotation. For example-

@NamedQueries({
    @NamedQuery(name = "Employee.findByDepartment",
query = "Select e from emp e where e.department = ?1"),
    @NamedQuery(name="Employee.findByLastName",
                query = "Select e from emp e where e.lastName = ?1""),
})

@Id annotation specifies the primary key of the entity.

@GeneratedValue specifies the primary key generation strategy which is autoincrement in this case.

@Column annotation specifies the mapped table column name for the field.

In case of XML configuration <named-query /> element is used for defining named query.

<named-query name="Employee.findByDepartment">
  <query>Select e from emp e where e.department = ?1</query>
</named-query>

Spring Data JPA Repository

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import com.knpcode.springproject.model.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
  List<Employee> findByLastName(String lastName);

  List<Employee> findByDepartment(String department);
}

EmployeeRepository interface extends JpaRepository which takes the domain class to manage (Employee in this case) as well as the id type of the domain class as type arguments.

Apart from the methods inherited from JPARepository there are two methods defined in EmployeeRepository inerface.

Spring Data tries to resolve a call to these methods to a named query, starting with the simple name of the configured domain class, followed by the method name separated by a dot. So, for the method findByDepartment named query (Employee.findByDepartment) is used whereas for findByLastName Spring data creates a query from the method name.

Spring Data JPA example – Service class

From the service layer we’ll call the repository methods. Notice that repository instance has to be injected in the service class.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.knpcode.springproject.dao.EmployeeRepository;
import com.knpcode.springproject.model.Employee;

@Service
public class EmployeeService {
  @Autowired
  private EmployeeRepository repository;

  public Employee getEmployeeById(int id) {
    return repository.findById(id).get();
  }

  public List<Employee> getAllEmployees(){
    return (List<Employee>) repository.findAll();
  }

  public void deleteEmployeeById(int id){
    repository.deleteById(id);
  }

  public Employee addEmployee(Employee emp) {
    return repository.save(emp);
  }

  public List<Employee> getEmployeeByLastName(String lastName) {
    return repository.findByLastName(lastName);
  }

  public List<Employee> getEmployeeByDepartment(String department) {
    return repository.findByDepartment(department);
  }
}

Rest Controller

Using a Rest controller class we’ll map the path to the methods that are to be called for the requests.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;

import com.knpcode.springproject.model.Employee;
import com.knpcode.springproject.service.EmployeeService;

@RestController
@RequestMapping("/employee")
public class EmployeeController {
  @Autowired
  EmployeeService empService;
  @GetMapping("/{id}")
  public Employee getEmployeeById(@PathVariable int id) {
    return empService.getEmployeeById(id);
  }
  @GetMapping
  public List<Employee> getAllEmployees(){
    return empService.getAllEmployees();
  }
  @DeleteMapping("/{id}")
  @ResponseStatus(HttpStatus.OK)
  public void deleteEmployeeById(@PathVariable int id){
    empService.deleteEmployeeById(id);
  }
  @PostMapping
  @ResponseStatus(HttpStatus.CREATED)
  public Employee addEmployee(@RequestBody Employee emp) {
    return empService.addEmployee(emp);
  }
  @GetMapping("/lastname/{lastName}")
  public List<Employee> getEmployeeByLastName(@PathVariable String lastName) {
    return empService.getEmployeeByLastName(lastName);
  }
  @GetMapping("/dept/{department}")
  public List<Employee> getEmployeeByDepartment(@PathVariable String department) {
    return empService.getEmployeeByDepartment(department);
  }
}

Spring Data JPA – configuration classes

In this Spring data JPA example Java configuration is used so class is annotated with @Configuration annotation.

For setting up DataSource DB properties are read from a properties file, path for the properties file is configured using @PropertySource annotation.

@EnableJpaRepositories annotation enables the JPA repositories. Package to scan for the repositories is provided as a value with this annotation.

@EnableTransactionManagement annotation enables Spring's annotation-driven transaction management capability.

With in this Java config class we set up a EntityManagerFactory and use Hibernate as persistence provider.

import java.util.Properties;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableJpaRepositories("com.knpcode.springproject.dao")
@EnableTransactionManagement
@PropertySource("classpath:config/db.properties")
public class JPAConfig {
  @Autowired
  private Environment env;
  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.knpcode.springproject.model");
    factory.setDataSource(dataSource());
    factory.setJpaProperties(hibernateProperties());
    return factory;
  }
	
  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName(env.getProperty("db.driverClassName"));
    ds.setUrl(env.getProperty("db.url"));
    ds.setUsername(env.getProperty("db.username"));
    ds.setPassword(env.getProperty("db.password"));
    return ds;
  }
	
  Properties hibernateProperties() {
    Properties properties = new Properties();
    properties.setProperty("hibernate.dialect", env.getProperty("hibernate.sqldialect"));
    properties.setProperty("hibernate.show_sql", env.getProperty("hibernate.showsql"));
    return properties;
  }
	
  @Bean
  public PlatformTransactionManager transactionManager() {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory().getObject());
    return txManager;
  }
}

If you are using XML configuration then the configuration for enabling JPA repositories is-

<jpa:repositories base-package="com.knpcode.springproject.dao"/>
db.properties file
db.driverClassName=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/knpcode
db.username=
db.password=
hibernate.sqldialect=org.hibernate.dialect.MySQLDialect
hibernate.showsql=true

To set up the web application using Java config rather than using the web.xml we’ll need the following classes.

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class WebConfigInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {

  @Override
  protected Class<?>[] getRootConfigClasses() {
    // TODO Auto-generated method stub
    return null;
  }

  @Override
  protected Class<?>[] getServletConfigClasses() {
    return new Class<?>[] {WebConfig.class};
  }

  @Override
  protected String[] getServletMappings() {
    return new String[] {"/"};
  }
}
@Configuration
@EnableWebMvc
@ComponentScan(basePackages = "com.knpcode.springproject")
public class WebConfig  implements WebMvcConfigurer{

}

Deploying the Spring Data JPA application

Right clicking the project and select Run As – Maven build, provide goal as clean install. If the build is successful you will have your application packaged as a war which you can deploy on web container like Tomcat and then test the application.

For testing the RESTful webservice, Postman rest client is used.

Adding employee

add employee

Note that the request selected is POST and the URL is http://localhost:8080/SpringJPAProject/employee

Data is sent as request body in JSON format. In the response added Employee data is sent back.

Find by departement (named Query)

You can also also send requests directly from browser as done for this request.

Spring Data JPA @NamedQuery

Get Employee by last name

Spring data JPA

That's all for the topic Spring Data JPA @NamedQuery Annotation Example. If something is missing or you have something to share about the topic please write a comment.


You may also like

February 24, 2022

Spring Data JPA Example

In this Spring Data JPA example we’ll create a rest web service using Spring Web MVC, JPA implementation used is Hibernate and DB is MySQL.

Spring Data JPA

Spring data JPA is the Spring Data repository support for JPA.

By using Spring Data repository abstraction on top of the persistence store you are using (JPA, NoSQL, JDBC etc.) you can significantly reduce the amount of boilerplate code required to implement data access layers for those persistence stores.

As a developer you just need to write your repository interfaces, including custom finder methods (if any) and Spring will provide the implementation for those data access methods automatically.

Maven dependencies

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.knpcode</groupId>
  <artifactId>SpringJPAProject</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <name>SpringJPA</name>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 
    <spring.version>5.1.8.RELEASE</spring.version>
    <spring.data>2.1.10.RELEASE</spring.data>
    <hibernate.jpa>5.4.3.Final</hibernate.jpa>
    <mysql.version>8.0.17</mysql.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <!-- Spring data JPA -->
    <dependency>
      <groupId>org.springframework.data</groupId>
      <artifactId>spring-data-jpa</artifactId>
      <version>${spring.data}</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.9.6</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.0</version>
      <scope>provided</scope>
    </dependency>
    <!-- Hibernate -->
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
      <version>${hibernate.jpa}</version>
    </dependency>
    <!-- MySQL Driver -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql.version}</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.25</version>
    </dependency>
  </dependencies>
  <build>
    <sourceDirectory>src</sourceDirectory>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.0</version>
        <configuration>
          <release>11</release>
        </configuration>
      </plugin>
      <plugin>
        <artifactId>maven-war-plugin</artifactId>
        <version>3.2.1</version>
        <configuration>
          <warSourceDirectory>WebContent</warSourceDirectory>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

Dependencies are added for Spring core, Spring context as well as for Spring Web and Spring data JPA.

Dependency for Hibernate is added as Hibernate JPA implementation is used.

MySQL connector is used for connecting to MySQL DB from Java application.

Jackson databind is needed for webservice responses which are sent as JSON.

Project structure

Project structure with full implementation of this Spring Data JPA example is as follows-

Spring data jpa project

DB table Query

MySQL DB table used for this Spring data JPA can be created using the following query.
CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `department` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Spring Data JPA example – Model class

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="emp")
public class Employee {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;
  @Column(name="first_name")
  private String firstName;
  @Column(name="last_name")
  private String lastName;
  @Column(name="department")
  private String dept;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  public String getDept() {
    return dept;
  }
  public void setDept(String dept) {
    this.dept = dept;
  }

  @Override
  public String toString() {
    return "Id= " + getId() + " First Name= " + 
             getFirstName() + " Last Name= " + getLastName() + 
             " Dept= "+ getDept();
  }
}

This is the model class which corresponds to the emp table in DB.

@Entity annotation specifies that this model class is an entity.

@Table annotation specifies the primary table for the entity.

@Id annotation specifies the primary key of the entity.

@GeneratedValue specifies the primary key generation strategy which is autoincrement in this case.

@Column annotation specifies the mapped table column name for the field.

Spring Data JPA example – Repository

import java.util.List;
import org.springframework.data.repository.CrudRepository;
import com.knpcode.springproject.model.Employee;

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {
	List<Employee> findByLastName(String lastName);
}

EmployeeRepository interface extends CrudRepository which takes the domain class to manage (Employee in this case) as well as the id type of the domain class as type arguments.

That is all the data access code you need for your CRUD functionality, no need to write a class that implements this interface.

The CrudRepository which is inherited by your custom repository provides sophisticated CRUD functionality for the entity class that is being managed. Some of the methods that are in the CrudRepository interface.

  • <S extends T> S save(S entity)- Saves the given entity.
  • T findOne(ID primaryKey)- Returns the entity identified by the given id.
  • Iterable<T> findAll()- Returns all entities.
  • Long count()- Returns the number of entities.
  • void delete(T entity)- Deletes the given entity.
  • boolean exists(ID primaryKey)- Indicates whether an entity with the given id exists.

You can also write custom queries which can be generated automatically or you can write the query as Named query or by using @Query annotation with in the repository. As you can see in our Repository interface there is a findByLastName() method. For methods starting with such names as “find”, “count”, “remove”, “delete”, Spring framework, by parsing the method name and matching it with a property in the entity class can automatically generate the correct query.

See example of using @NamedQuery annotation in Spring Data JPA in this post- Spring Data JPA @NamedQuery Annotation Example

See example of using @Query annotation in Spring Data JPA in this post- Spring Data JPA @Query Annotation Example

Spring Data JPA example – Service class

From the service layer we’ll call the DAO layer methods. Since all we need is a repository in case of spring data so we’ll call methods of repository from the service class. Notice that repository instance has to be injected in the service class.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.knpcode.springproject.dao.EmployeeRepository;
import com.knpcode.springproject.model.Employee;

@Service
public class EmployeeService {
  @Autowired
  private EmployeeRepository repository;

  public Employee getEmployeeById(int id) {
    return repository.findById(id).get();
  }

  public List<Employee> getAllEmployees(){
    return (List<Employee>) repository.findAll();
  }

  public void deleteEmployeeById(int id){
    repository.deleteById(id);
  }

  public Employee addEmployee(Employee emp) {
    return repository.save(emp);
  }

  public List<Employee> getEmployeeByLastName(String lastName) {
    return repository.findByLastName(lastName);
  }
}

Spring Data JPA example – Controller class

Using a Rest controller class we’ll map the path to the methods that are to be called for the requests.

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
import com.knpcode.springproject.model.Employee;
import com.knpcode.springproject.service.EmployeeService;

@RestController
@RequestMapping("/employee")
public class EmployeeController {
  @Autowired
  EmployeeService empService;
  @GetMapping("/{id}")
  public Employee getEmployeeById(@PathVariable int id) {
    return empService.getEmployeeById(id);
  }
  @GetMapping
  public List<Employee> getAllEmployees(){
    return empService.getAllEmployees();
  }
  @DeleteMapping("/{id}")
  @ResponseStatus(HttpStatus.OK)
  public void deleteEmployeeById(@PathVariable int id){
    empService.deleteEmployeeById(id);
  }
  @PostMapping
  @ResponseStatus(HttpStatus.CREATED)
  public Employee addEmployee(@RequestBody Employee emp) {
    return empService.addEmployee(emp);
  }
  @GetMapping("/lastname/{lastName}")
  public List<Employee> getEmployeeByLastName(@PathVariable String lastName) {
    return empService.getEmployeeByLastName(lastName);
  }
}

Spring Data JPA example – Configuration

In this Spring data JPA example Java configuration is used so class is annotated with @Configuration annotation.

For setting up DataSource, DB properties are read from a properties file. Path for the properties file is configured using @PropertySource annotation.

@EnableJpaRepositories annotation enables the JPA repositories. Package to scan for the repositories is provided as a value with this annotation.

@EnableTransactionManagement annotation enables Spring's annotation-driven transaction management capability.

With in this Java config class we set up a EntityManagerFactory and use Hibernate as persistence provider.

import java.util.Properties;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableJpaRepositories("com.knpcode.springproject.dao")
@EnableTransactionManagement
@PropertySource("classpath:config/db.properties")
public class JPAConfig {
  @Autowired
  private Environment env;
  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.knpcode.springproject.model");
    factory.setDataSource(dataSource());
    factory.setJpaProperties(hibernateProperties());
    return factory;
  }

  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName(env.getProperty("db.driverClassName"));
    ds.setUrl(env.getProperty("db.url"));
    ds.setUsername(env.getProperty("db.username"));
    ds.setPassword(env.getProperty("db.password"));
    return ds;
  }

  Properties hibernateProperties() {
    Properties properties = new Properties();
    properties.setProperty("hibernate.dialect", env.getProperty("hibernate.sqldialect"));
    properties.setProperty("hibernate.show_sql", env.getProperty("hibernate.showsql"));
    return properties;
  }

  @Bean
  public PlatformTransactionManager transactionManager() {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory().getObject());
    return txManager;
  }
}

If you are using XML configuration then the configuration for enabling JPA repositories is-

<jpa:repositories base-package="com.knpcode.springproject.dao"/>
db.properties file
db.driverClassName=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/knpcode
db.username=
db.password=
hibernate.sqldialect=org.hibernate.dialect.MySQLDialect
hibernate.showsql=true

To set up the web application using Java config rather than using the web.xml we’ll need the following classes.

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class WebConfigInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
  @Override
  protected Class<?>[] getRootConfigClasses() {
    // TODO Auto-generated method stub
    return null;
  }

  @Override
  protected Class<?>[] getServletConfigClasses() {
    return new Class<?>[] {WebConfig.class};
  }

  @Override
  protected String[] getServletMappings() {
    return new String[] {"/"};
  }
}
@Configuration
@EnableWebMvc
@ComponentScan(basePackages = "com.knpcode.springproject")
public class WebConfig implements WebMvcConfigurer{

}

Deploying the Spring Data JPA application

Right clicking the project and select Run As – Maven build, provide goal as clean install. If the build is successful you will have your application packaged as a war which you can deploy on web container like Tomcat and then test the application.

For testing the RESTful webservice, Postman rest client is used.

Adding employee

add employee

Note that the request selected is POST and the URL is http://localhost:8080/SpringJPAProject/employee

Data is sent as request body in JSON format. In the response added Employee data is sent back.

Get All employees

All Employees

Delete Employee by ID

Delete Employee

Get Employee by last name

You can also also send requests directly from browser as done for this request.
Spring data JPA

That's all for the topic Spring Data JPA Example. If something is missing or you have something to share about the topic please write a comment.


You may also like