In this article, let us see a sample maven project in Spring Boot JPA with Query methods. Spring Boot + JPA removes the boilerplate code and it will be enhanced much if we use query methods as well. Let us discuss this project with MySQL Connectivity for geeksforgeeks database and table name as “Contest”.
Sample Project
MySQL scripts:
-- if we want to drop the database, we can use this DROP DATABASE IF EXISTS geeksforgeeks; --creation of database CREATE DATABASE geeksforgeeks; --Make the database active USE geeksforgeeks; --Create the table Contest CREATE TABLE `Contest` ( `id` int(11) NOT NULL AUTO_INCREMENT, `contestName` varchar(45) NOT NULL, `contestDescription` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Project Structure:
Maven Project. All dependencies are specified here
pom.xml
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
< modelVersion >4.0.0</ modelVersion >
< groupId >com.gfg</ groupId >
< artifactId >SpringDataJPAQueryMethods</ artifactId >
< version >0.0.1-SNAPSHOT</ version >
< properties >
< maven.compiler.source >1.8</ maven.compiler.source >
< maven.compiler.target >1.8</ maven.compiler.target >
</ properties >
< dependencies >
<!-- Spring framework with support for Spring Data JPA -->
< 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 >
<!-- Spring framework with
support for Spring Data JPA -->
<!-- MySQL dependency -->
< dependency >
< groupId >mysql</ groupId >
< artifactId >mysql-connector-java</ artifactId >
< version >8.0.14</ version >
</ dependency >
</ dependencies >
</ project >
|
Let’s start with the Model class
Contest.java
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity public class Contest {
@Id
@GeneratedValue (strategy = GenerationType.IDENTITY)
private Long id;
private String contestName;
private String contestDescription;
protected Contest() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this .id = id;
}
public String getContestName() {
return contestName;
}
public void setContestName(String contestName) {
this .contestName = contestName;
}
public String getContestDescription() {
return contestDescription;
}
public void setContestDescription(String contestDescription) {
this .contestDescription = contestDescription;
}
@Override
public String toString() {
return "Contest [contestName=" + contestName + ", contestDescription=" + contestDescription + "]" ;
}
} |
ContestAppConfiguration.java
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 // Entire package contents of // com.gfg.jpaquerymethods need to be looked @EnableJpaRepositories (basePackages = { "com.gfg.jpaquerymethods" })
public class ContestAppConfiguration {
@Bean
public LocalEntityManagerFactoryBean entityManagerFactory() {
LocalEntityManagerFactoryBean factoryBean = new LocalEntityManagerFactoryBean();
factoryBean.setPersistenceUnitName( "GeeksDB" );
return factoryBean;
}
@Bean
public JpaTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(entityManagerFactory);
return transactionManager;
}
} |
Now let us define the methods in ContestRepositoryWithQuery for the query methods
// We can write the query as our needs, that will help to make clear what is happening inside @Query("SELECT contest.contestName FROM Contest contest where contest.id = :id") String findContestByIdString(@Param("id") Long id); // In case if we have multiple arguments, we can write // queries based on position as well as name // Position based queries // We cannot change the order of the method parameters // We cannot change the order of the placeholders without breaking our database query @Query("SELECT contest FROM Contest contest where contest.contestName = ?1 AND contest.id = ?2") public Optional<Contest> findByContestNameAndId(String contestName, Long id);
// Named Parameter // The @Param annotation configures the name of the named parameter that is replaced with the value of the method parameter. // This will be more helpful than positional based @Query("SELECT contest FROM Contest contest where contest.contestName = :contestName AND contest.id = :id") public Optional<Contest> findByNamedParameter(@Param("contestName") String contestName, @Param("id") Long id);
@Async annotation and Future<T> There will be situations where the query method need to execute asynchronously and in those cases, annotation has to be done with @Async annotation and return a Future<T> object. @Async Future<Contest> findContest1ById(Long id); @Async Future<Optional<Contest>> findContest2ById(Long id); @Async Future<Contest> findContestAsyncByContestName(String contestName); @Async Future<Stream<Contest>> findContestAsyncStreamByContestName(String contestName);
With the Async, we can even have our Query too
@Async @Query("SELECT contest.contestName FROM Contest contest where contest.id = :id") Future<Optional<String>> findContestById(@Param("id") Long id); @Async @Query("SELECT contest.contestName FROM Contest contest where contest.id = :id") Future<String> findContestAsyncById(@Param("id") Long id);
By combining all, lets code the below Java
ContestRepositoryWithQuery.java
import java.util.List;
import java.util.Optional;
import java.util.concurrent.Future;
import java.util.stream.Stream;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.scheduling.annotation.Async;
public interface ContestRepositoryWithQuery extends CrudRepository<Contest, Long> {
@Query ( "SELECT contest.contestName FROM Contest contest where contest.id = :id" )
String findContestByIdString( @Param ( "id" ) Long id);
@Query ( "SELECT contest.contestName FROM Contest contest where contest.contestName = :contestName" )
String findContestByContestName( @Param ( "contestName" ) String contestName);
List<Contest> findByContestName(String contestName);
// Position based parameter binding
// We cannot change the order of the method parameters
// We cannot change the order of the placeholders
// without breaking our database query
@Query ( "SELECT contest FROM Contest contest where contest.contestName = ?1 AND contest.id = ?2" )
public Optional<Contest> findByContestNameAndId(String contestName, Long id);
// Named Parameter
// The @Param annotation configures the name of the
// named parameter that is replaced with the value
// of the method parameter.
// This will be more helpful than positional based
@Query ( "SELECT contest FROM Contest contest where contest.contestName = :contestName AND contest.id = :id" )
public Optional<Contest> findByNamedParameter( @Param ( "contestName" ) String contestName,
@Param ( "id" ) Long id);
@Async
Future<Contest> findContest1ById(Long id);
@Async
Future<Optional<Contest>> findContest2ById(Long id);
@Async
Future<Contest> findContestAsyncByContestName(String contestName);
@Async
Future<Stream<Contest>> findContestAsyncStreamByContestName(String contestName);
@Async
@Query ( "SELECT contest.contestName FROM Contest contest where contest.id = :id" )
Future<Optional<String>> findContestById( @Param ( "id" ) Long id);
@Async
@Query ( "SELECT contest.contestName FROM Contest contest where contest.id = :id" )
Future<String> findContestAsyncById( @Param ( "id" ) Long id);
} |
Let’s consume the repository in a service file and as a test method, let’s test everything
ContestQueryService.java
import java.util.List;
import java.util.Optional;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service ( "contestQueryService" )
public class ContestQueryService {
@Autowired
private ContestRepositoryWithQuery contestRepositoryWithQuery;
public void test() {
// Save a new contest
Contest geekContest = new Contest();
geekContest.setContestName( "PremierLeague" );
geekContest.setContestDescription( "Inviting Geeks To submit articles in plenty" );
contestRepositoryWithQuery.save(geekContest);
Contest hackthoContest = new Contest();
hackthoContest.setContestName( "Hackathon" );
hackthoContest.setContestDescription( "Coding Round Challenge" );
contestRepositoryWithQuery.save(hackthoContest);
Optional<Contest> result = contestRepositoryWithQuery.findById(1L);
result.ifPresent(contest -> System.out.println(contest));
// Positional based test
Optional<Contest> result1 = contestRepositoryWithQuery.findByContestNameAndId( "PremierLeague" ,6L);
result1.ifPresent(contest -> System.out.println( "Searched for PremierLeague.." + contest));
// Named query test
Optional<Contest> namedQueryResult = contestRepositoryWithQuery.findByNamedParameter( "PremierLeague" ,6L);
namedQueryResult.ifPresent(contest -> System.out.println( "Searched for PremierLeague.." + contest));
// Find contest by contest name
List<Contest> contests = contestRepositoryWithQuery.findByContestName( "Hackathon" );
contests.forEach(contest -> System.out.println( "Searched for Hackathon.." + contest));
// List all contests
Iterable<Contest> iterator = contestRepositoryWithQuery.findAll();
iterator.forEach(contest -> System.out.println(contest));
// Count number of contest
long countOfContest = contestRepositoryWithQuery.count();
System.out.println( "Number of contest held: " + countOfContest);
// Async way of testing
Future<Contest> resultAsync = contestRepositoryWithQuery.findContest1ById(6L);
try {
System.out.println( "Async way of getting contestname.." + resultAsync.get().getContestName());
} catch (InterruptedException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (ExecutionException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Future<Optional<Contest>> hackathonAsyncResult = contestRepositoryWithQuery.findContest2ById(7L);
result.ifPresent(hackathonContest -> System.out.println(hackathonContest));
Future<Contest> asyncContest = contestRepositoryWithQuery.findContestAsyncByContestName( "Hackathon" );
try {
System.out.println( "contestname retrieval in async way .." + asyncContest.get().getContestName());
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ExecutionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Future<Optional<String>> contestDetails = contestRepositoryWithQuery.findContestById(10L);
//contestDetails.ifPresent(hackathonContest -> System.out.println(hackathonContest));
Future<String> contestString = contestRepositoryWithQuery.findContestAsyncById(10L);
System.out.println(contestString);
}
} |
We can call the service file via
ContestTestIncludingQueryMethods.java
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
public class ContestTestIncludingQueryMethods {
public static void main(String[] args) {
AnnotationConfigApplicationContext appContext = new AnnotationConfigApplicationContext();
appContext.scan( "com.gfg.jpaquerymethods" );
appContext.refresh();
ContestQueryService contestService = (ContestQueryService) appContext.getBean( "contestQueryService" );
contestService.test();
appContext.close();
}
} |
We can run the test file as a normal Java application and can find the observations. First, let us have DB data display
Output:
Positional Query Output:
Named Query Output:
Async Way of Output:
As we have different options available with Query methods, we can choose them as per our needs.