Spring Boot Query

Introduction to Spring Boot Query

In Spring boot, we have a query provided by the JPA to create the sql query above the method declaration only. This annotation can be used inside the interface, unlike named query in JPA. Named query we have written in the class itself, in order to use both these annotations to deal with the SQL, we have to have JAP dependency in place in the project setup; else, it will give a compile-time error for not finding it. By the use of this query annotation, we can write the custom sql query as well; the value attribute for this query will contain the SQL or JPQL to execute it properly. Here we will see both the annotations in detail because they both use to write a query.

Syntax of Spring Boot Query

As we have seen, query annotations are used to write the sql queries in the spring boot application in order to get the desired result from the database.

Syntax of the of this annotations in order to use it:

@Query("your query ")
public return_type method _name();

As you can see in the above line of syntax, we have used @Query annotation to make the query, which will retrieve the data from the database.

@Query("SELECT e FROM EMP e WHERE e.city = 2")
List<EMP> getALl();

As you can see in the above line of code, we have written one query using @Query annotation, and also it has given you the proper idea of how we can use this in our application. But we have to make some changes to our code while writing it.

How does Spring Boot Query work?

As we already know, that query is used to write the sql query in spring boot, which help us to retrieve the data from the database. But this annotation comes under the JPA so that we can use this inside the JPARepository interface only. We have some standard definition to use this in the application, which needs to follow in order to make it work. This annotation always returns us the specified entity object from the database; while creating the interface, we mentioned all these necessary details inside it. To use this annotation, we should have JPA dependency in the build file, which will enable us to make the database calls.

Here we will see how we can define this using the @Query annotations in spring-boot:

1. @Query

We can use this annotation inside the interface and above the method of the interface. This method will return the desired result of the sql statement. We can also pass our own parameters to the methods in order to pass them into the sql query itself. First, we will have a look at the simple syntax then we can have a look at the param based query.

2. Simple Query

@Query("SELECT e FROM EMP e WHERE e.city = 2")
List<EMP> get();

Here we are trying to get the list of all employees which falls under the city 2 parameter. But as you can see, we have hard code the value for the city param here. get() is the method name here which will return the list of the employee to the calling service, and when we try to call the repository method, this query will always get executed automatically.

3. Param Based

What if we want to pass any param inside it, we have to use the @Param annotations from jap itself; this annotation will help us to bind the value to the query we have created. But in order to use it, we have to make use of the standard defined by the spring boot framework.

@Query("SELECT e FROM EMP e WHERE e.id =:id ")
EMP getById(@Param("id") String id);

As you can see in the above syntax, we have used the param annotation here to get the parameter from the service and bind it to the query we have. Here we have passed the parameter inside the method using the @Param annotation, which needs the param name to be defined in the small brackets; if we miss this, it will give us the compile-time error. Also, we have one syntax to bind it, ‘=:’ this. This will bind the param with the query value and return the result to us.

4. Use Native Query

We can also write the sql native query by the use of the native keyword inside the @Query syntax only. By the use of it, we can write the sql based on the sql syntax only, which will jpa convert to its untestable form.

@Query(
value = "SELECT e FROM EMP e WHERE e.city = 2",
nativeQuery = true)
List<EMP> get();

Required steps which are needed to perform this:

1. We have to create the spring boot project from scratch; we can use the spring initializer to create it fast.

Follow the below link and fill up the required details.

URL: https://start.spring.io/

2. Now, we have added the required dependency into the build file to make use of the given annotation in the application.

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

3. Create the interface which can contain the query inside it.

import com.scania.coc.core.entity.Co2Contents;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;
public interface EMPRepo extends JpaRepository<EMP, Integer> {
@Query("SELECT e FROM EMP e WHERE e.id =:id ")
public EMP findByid(@Param("id") String id);
}

It can contain multiple methods depend upon the requirement.

4. Our main application file should look like below.

import org.springframework.boot.SpringApplication;
@SpringBootApplication
@EnableJpaRepositories("base path ")
public class CocCoreApplication {
public static void main(String[] args) throws IOException {
SpringApplication.run(CocCoreApplication.class, args);
}
}

Here we have to use one more annotation, which is @EnableJpaRepositories this will enable the JPA in the application, and when we run our project, it will identify the repository in the application . Also, we have to mention the base package name of our application inside the bracket; otherwise, the application will not be able to scan the package for a repository, and the code will fail.

5. Run the application, and you will see the below trace of logs on the console of your application.

Output:

Conclusion

A query is easy to use and write, as we have already seen. Then, we need to make the necessary change, or we can say configuration in order to use this while coding. Follow the above steps to make it work.

Recommended Articles

This is a guide to Spring Boot Query. Here we discuss the introduction and how the spring boot query works? For better understanding. You may also have a look at the following articles to learn more –

1. Maven Repository Spring
2. Spring Boot DevTools
3. Spring AOP
4. Spring Cloud Components