Skip to main content

[JPA] write your own query in Java JPA Repository @Query annotation

In this post, I would like to take the time to write about @Query, which allows you to write queries directly in Java JPA Respository.

JPQL

Although JPA automatically generates queries for you, you may need to write your own queries in some circumstances.
There are two ways to write queries directly in JPA:
 
  • Written in JPQL
  • written in plain SQL 
JPQL is a platform-independent, object-oriented query language defined as part of JPA .
As a query language that can be used in JPA, JPQL needs to look at and write entity classes, while plain SQL looks at databases and writes them.
The way to write queries directly in both JPQL and SQL is to use the @Query annotation in the same way. And you can distinguish whether it is written in JPQL or SQL by using the property called nativeQuery in the @Query annotation.

  • nativeQuery = true → SQL
  • nativeQuery = false (default) → JPQL
Also, the method name can be written freely unlike the existing automatic generation method .
I'll generate the relevant code to show you how it can be written.

Let's Getting Started

First, let's set up basic settings such as creating tables and entities.

[ 1. Creating tables/data in MySQL ]
create table snack (
    snack_id int auto_increment,
    name varchar(50),
    price int,
    primary key(snack_id)
);

insert into snack(name, price) values ('Honey Butter Chips', 1500);
insert into snack(name, price) values ('Swing Chip', 2000);
insert into snack(name, price) values ('Roly-Poly', 1200);
insert into snack(name, price) values ('Postick', 1600);
insert into snack(name, price) values ('potato cracker', 1600);


[ 2. Snack Entity Class ]

package com.spring.jpa.dto;

import java.io.Serializable;

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

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "snack")
public class Snack implements Serializable {
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Id
	@Column(name = "snack_id")
	private int id;
	
	private String name;
	private int price;
}

 

[ 3. SnackRepository class ]

package com.spring.jpa.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.spring.jpa.dto.Snack;

public interface SnackRepository extends JpaRepository<Snack, Integer>{
// generic type: <entity class, primary key of entity class>

}

[ 4. Unit testing ]

package com.spring.jpa;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import com.spring.jpa.config.RootContext;
import com.spring.jpa.dto.Snack;
import com.spring.jpa.repository.SnackRepository;

import lombok.extern.slf4j.Slf4j;

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = RootContext.class)
@Slf4j
public class JPATest {
	
	@Autowired
	SnackRepository snackRepository;
	
	@Test
	public void snackTest() {
		
	}
}

How to use @Query (general query)

[ Repository ]

package com.spring.jpa.repository;

import java.util.List;

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

import com.spring.jpa.dto.Snack;

public interface SnackRepository extends JpaRepository<Snack, Integer>{ 
	@Query(value = "select sn from Snack sn")
	public List<Snack> selectAllJPQL();
	
	@Query(value = "select snack_id, name, price from snack", nativeQuery = true)
	public List<Snack> selectAllSQL();
}


[ Unit Test ]

package com.spring.jpa;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import com.spring.jpa.config.RootContext;
import com.spring.jpa.dto.Snack;
import com.spring.jpa.repository.SnackRepository;

import lombok.extern.slf4j.Slf4j;

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = RootContext.class)
@Slf4j
public class JPATest {
	
	@Autowired
	SnackRepository snackRepository;
	
	@Test
	public void snackTest() {
		log.info(snackRepository.selectAllJPQL().toString());
		log.info(snackRepository.selectAllSQL().toString());
	}
}

 

How to use @Query (using parameters)

[ Repository ]

 
package com.spring.jpa.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.spring.jpa.dto.Snack;

public interface SnackRepository extends JpaRepository<Snack, Integer>{ 
	// In JPQL, the variable name of entity is id, so sn.id is included in the condition clause.
       // JPQL general parameter query, using @Param X
	@Query(value = "select sn from Snack sn where sn.id > ?1")
	public List<Snack> selectJPQLById1(int id);
	
	// JPQL general parameter query, using @Param O
	@Query(value = "select sn from Snack sn where sn.id > :id")
	public List<Snack> selectJPQLById2(@Param(value = "id") int id);
	
	// Query JPQL object parameters
	@Query(value = "select sn from Snack sn where sn.id > :#{#paramSnack.id}")
	public List<Snack> selectJPQLById3(@Param(value = "paramSnack") Snack snack);
	
	// Since the column name of the table is snack_id in SQL, snack_id in the condition clause
        // SQL general parameter query, using @Param X
	@Query(value = "select snack_id, name, price from snack where snack_id > ?1", nativeQuery = true)
	public List<Snack> selectSQLById1(int id);
	
	// SQL general parameter query, using @Param O
	@Query(value = "select snack_id, name, price from snack where snack_id > :id", nativeQuery = true)
	public List<Snack> selectSQLById2(@Param(value = "id") int id);
	
	// Query SQL object parameters
	@Query(value = "select snack_id, name, price from snack where snack_id > :#{#paramSnack.id}", nativeQuery = true)
	public List<Snack> selectSQLById3(@Param(value = "paramSnack") Snack snack);
}

[ Unit Test ]

package com.spring.jpa;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import com.spring.jpa.config.RootContext;
import com.spring.jpa.dto.Snack;
import com.spring.jpa.repository.SnackRepository;

import lombok.extern.slf4j.Slf4j;

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = RootContext.class)
@Slf4j
public class JPATest {
	
	@Autowired
	SnackRepository snackRepository;
	
	@Test
	public void snackTest() {
		log.info(snackRepository.selectJPQLById1(2).toString());
		log.info(snackRepository.selectJPQLById2(2).toString());
		log.info(snackRepository.selectJPQLById3(new Snack(2, null, 0)).toString());
		log.info(snackRepository.selectSQLById1(2).toString());
		log.info(snackRepository.selectSQLById2(2).toString());
		log.info(snackRepository.selectSQLById3(new Snack(2, null, 0)).toString());
	}
}


Thank for reading. Hope it could help you to learn more. :)

Comments

Post a Comment

Popular posts from this blog

Ubuntu/Linux: How to use the JsonCpp Lib in C++

JsonCpp is a lightweight data-interchange format. It can represent numbers, strings, ordered sequences of values, and collections of name/value pairs. Step 1: Install using apt-get You can easily install JsonCpp library on Ubuntu or another flavor of Debian Linux simply by running the following command: sudo apt-get install libjsoncpp-dev It will install the compatible JsonCpp library on your system. Step 2: Example program and compiler flag To compile a C++ program that uses JsonCpp library use the compiler flag: -ljsoncpp Create a Json file called profile.json with the following content: { "firstname":"Amritpal", "lastname": "Singh", "ss": 12345678910 } Within the same directory create a userdata.cpp file with the following source code: #include <iostream> #include <fstream> #include <jsoncpp/json/json.h> using namespace std; int main() { ifstream ifs("profile.json"); Json::Reader read...
Post a Comment
Read more

5 Simple Daily Habits for Better Health

5 Simple Daily Habits for Better Health Small changes can lead to big improvements in your overall wellbeing. Here are five simple habits you can start today: 1. Drink Water First Thing Start your day with a glass of water to kickstart your metabolism and rehydrate after sleep. 2. Take the Stairs Choose stairs over elevators whenever possible to add extra movement to your day. 3. Practice Deep Breathing Take 5 minutes daily for deep breathing exercises to reduce stress and improve focus. 4. Pack Healthy Snacks Prepare nutritious snacks in advance to avoid unhealthy impulse choices. 5. Set a Sleep Schedule Go to bed and wake up at consistent times to improve your sleep quality. Remember, consistency is key. Start with one habit and gradually add others for lasting change!
Post a Comment
Read more

Syntax Highlighter - Beautify source code in Blogger

For my tastes, the source code ought to be colorful.  As the site is about development, I frequently had to enter the source code, however Syntax Highlighter consistently let me down. Although Wordpress provides a ton of plug-in possibilities, since this is Google Blogger, you'll have to build it yourself. The Syntax Highlighter feature in Google Blogger also offers a variety of choices. The ensuing standards were set up in order to choose one of them. 99% of geeks' tastes can be characterized by this criterion. So, What kind of SyntaxHighlighters could we use? The most used Syntax Highlighter is by Alex Gorbatchev. It is old and powerful functions, but... Crucially, I was dissatisfied with having to load a separate Javascript file called a brush for each required language. So I put this aside for now. 1. SHJS was also excluded because separate .js files had to be loaded for each language. 2. H ighlight.js supports a wide variety of languages and configuration file formats, a...
4 comments
Read more