×
☰ See All Chapters

Spring Data JPA Query Annotation

Method naming convention and strategies for query generation is valid approach and works fine for smaller queries.  If the query is complex involving multiple conditions, then method naming convention and strategies for query generation has the following drawbacks.

  • We have to use only the supported keywords for method name. Also there are chances of coding wrong method names. 

  • If the conditions become more, method name length becomes more and looks ugly. 

  • This will not provide us the flexibility for having the named parameters. This method names query generation strategy is like a static query. 

The best solution is using @NamedQuery and @NamedQueries. This would be best solution, but the actual query will be residing in Entity class not in Repository class.

As an alternative for @NamedQuery, we have @Query which allows us to write our queries in Repository class.

Using spring sata JPA Query annotation we can build more sophisticated and complex queries, which would not be possible by using method naming conventions and strategies. But we can build same complex queries using @NamedQuery and @NamedQueries, but as these queries reside in entity classes and not in repository classes.

public interface  StudentRepository extends CrudRepository<Student, Long>{

        @Query("SELECT S FROM Student S WHERE S.studentId = :studentId")

        List<Student> query1(@Param("studentId")Long studentId);

       

        @Query("SELECT S FROM Student S WHERE S.studentId < :studentId AND S.marks > :marks")

        List<Student> query2(@Param("studentId")Long studentId, @Param("marks")Long marks);

}

Spring Data JPA Query Annotation Example

Below example shows how we can build queries using @Query annotation inside repositories.

Database script (MySQL)

CREATE TABLE STUDENT (

       STUDENTID INT NOT NULL AUTO_INCREMENT,

       FIRSTNAME VARCHAR(20) DEFAULT NULL,

       LASTNAME VARCHAR(20) DEFAULT NULL,

       MARKS INT(20) DEFAULT NULL,

       PRIMARY KEY (STUDENTID)

);

 

INSERT INTO STUDENT (FIRSTNAME,LASTNAME,MARKS) VALUES ('Manu','Manjunatha', 100);

INSERT INTO STUDENT (FIRSTNAME,LASTNAME,MARKS) VALUES('Advith','Tyagraj', 100);

INSERT INTO STUDENT (FIRSTNAME,LASTNAME,MARKS) VALUES('Likitha','Tyagraj', 98);

 

COMMIT;

pom.xml

<project xmlns="https://maven.apache.org/POM/4.0.0"

        xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

        <modelVersion>4.0.0</modelVersion>

        <groupId>com.java4coding</groupId>

        <artifactId>SpringDataJPA_@Query</artifactId>

        <version>0.0.1-SNAPSHOT</version>

 

        <dependencies>

                <dependency>

                        <groupId>org.springframework</groupId>

                        <artifactId>spring-context</artifactId>

                        <version>5.1.4.RELEASE</version>

                </dependency>

                <dependency>

                        <groupId>org.springframework</groupId>

                        <artifactId>spring-orm</artifactId>

                        <version>5.1.4.RELEASE</version>

                </dependency>

                <dependency>

                        <groupId>org.hibernate</groupId>

                        <artifactId>hibernate-core</artifactId>

                        <version>5.4.1.Final</version>

                </dependency>

                <dependency>

                        <groupId>org.springframework.data</groupId>

                        <artifactId>spring-data-jpa</artifactId>

                        <version>2.1.4.RELEASE</version>

                </dependency>

                <dependency>

                        <groupId>mysql</groupId>

                        <artifactId>mysql-connector-java</artifactId>

                        <version>8.0.14</version>

                </dependency>

        </dependencies>

 

        <build>

                <plugins>

                        <plugin>

                                <groupId>org.apache.maven.plugins</groupId>

                                <artifactId>maven-compiler-plugin</artifactId>

                                <version>3.8.1</version>

                                <configuration>

                                        <source>1.8</source>

                                        <target>1.8</target>

                                </configuration>

                        </plugin>

                </plugins>

        </build>

</project>

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="https://java.sun.com/xml/ns/persistence"

        xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="https://java.sun.com/xml/ns/persistence

             https://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

        version="2.0">

 

        <persistence-unit name="StudentPU">

                <provider>org.hibernate.ejb.HibernatePersistence</provider>

                <properties>

                        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/study" />

                        <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver" />

                        <property name="hibernate.connection.username" value="root" />

                        <property name="hibernate.connection.password" value="root" />

                        <property name="hibernate.archive.autodetection" value="class" />

                        <property name="hibernate.show_sql" value="true" />

                        <property name="hibernate.format_sql" value="true" />

                        <property name="hbm2ddl.auto" value="update" />

                </properties>

        </persistence-unit>

</persistence>

 

Student.java

package com.java4coding;

 

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

 

@Entity

public class Student {

 

        @Id

        @GeneratedValue(strategy = GenerationType.IDENTITY)

        private Long studentId;

        private String firstName;

        private String lastName;

        private Long marks;

 

        public Student() {

                super();

        }

       

        public Student(String firstName, String lastName, Long marks) {

                super();

                this.firstName = firstName;

                this.lastName = lastName;

                this.marks = marks;

        }

       

        //Setters and getters

        public Long getId() {

                return studentId;

        }

 

        public void setId(Long id) {

                this.studentId = 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 Long getMarks() {

                return marks;

        }

 

        public void setMarks(Long marks) {

                this.marks = marks;

        }

 

        @Override

        public String toString() {

                return "Student [firstName=" + firstName + ", lastName=" + lastName + ", Marks= " + marks + "]";

        }

}

AppConfig.java

package com.java4coding;

 

import javax.persistence.EntityManagerFactory;

 

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import org.springframework.orm.jpa.JpaTransactionManager;

import org.springframework.orm.jpa.LocalEntityManagerFactoryBean;

 

@Configuration

@EnableJpaRepositories(basePackages = {"com.java4coding"})

public class AppConfig {

        @Bean

        public LocalEntityManagerFactoryBean entityManagerFactory() {

                LocalEntityManagerFactoryBean factoryBean = new LocalEntityManagerFactoryBean();

                factoryBean.setPersistenceUnitName("StudentPU");

               

                return factoryBean;

        }

       

        @Bean

        public JpaTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {

                JpaTransactionManager transactionManager = new JpaTransactionManager();

                transactionManager.setEntityManagerFactory(entityManagerFactory);

               

                return transactionManager;

        }       

}

StudentRepository.java

package com.java4coding;

 

import java.util.List;

 

import org.springframework.data.jpa.repository.Query;

import org.springframework.data.repository.CrudRepository;

import org.springframework.data.repository.query.Param;

 

public interface  StudentRepository extends CrudRepository<Student, Long>{

        @Query("SELECT S FROM Student S WHERE S.studentId = :studentId")

        List<Student> query1(@Param("studentId")Long studentId);

       

        @Query("SELECT S FROM Student S WHERE S.studentId < :studentId AND S.marks > :marks")

        List<Student> query2(@Param("studentId")Long studentId, @Param("marks")Long marks);

}

StudentService.java

package com.java4coding;

 

import java.util.List;

 

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

import org.springframework.stereotype.Service;

 

@Service("studentService")

public class StudentService {

        @Autowired

        private StudentRepository repository;

       

        public void test() {

               

                List<Student> students1 = repository.query1(1L);

                students1.forEach(student1 -> System.out.println(student1));

               

                List<Student> students2 = repository.query2(3L, 99L);

                students2.forEach(student2 -> System.out.println(student2));

               

        }

}

Test.java

package com.java4coding;

 

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

 

public class Test {

        public static void main(String[] args) {

                AnnotationConfigApplicationContext appContext = new AnnotationConfigApplicationContext();

                appContext.scan("com.java4coding");

                appContext.refresh();

 

                StudentService studentService = (StudentService) appContext.getBean("studentService");

                studentService.test();

 

                appContext.close();

        }

}

Project directory structure

spring-data-jpa-query-annotation-0
 

Output

Hibernate:

    select

        student0_.studentId as studentI1_0_,

        student0_.firstName as firstNam2_0_,

        student0_.lastName as lastName3_0_,

        student0_.marks as marks4_0_

    from

        Student student0_

    where

        student0_.studentId=?

Student [firstName=Manu, lastName=Manjunatha, Marks= 100]

Hibernate:

    select

        student0_.studentId as studentI1_0_,

        student0_.firstName as firstNam2_0_,

        student0_.lastName as lastName3_0_,

        student0_.marks as marks4_0_

    from

        Student student0_

    where

        student0_.studentId<?

        and student0_.marks>?

Student [firstName=Manu, lastName=Manjunatha, Marks= 100]

Student [firstName=Advith, lastName=Tyagraj, Marks= 100]

 

All Chapters
Author