Spring Boot JPA Sample Maven Project With Query Methods

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 neveropen 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 neveropen;
--creation of database
CREATE DATABASE neveropen;
--Make the database active
USE neveropen;
--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

Let’s start with the Model class

Contest.java

ContestAppConfiguration.java

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

Let’s consume the repository in a service file and as a test method, let’s test everything

ContestQueryService.java

We can call the service file via 

ContestTestIncludingQueryMethods.java

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.