Spring Boot 13. JPA를 이용한 커스텀 쿼리 만들기

 

방법 1. @Query

UserApiController

@GetMapping("/users")
List<User> all(@RequestParam(required = false) String method,
               @RequestParam(required = false) String text){
    List<User> users = null;
    if("query".equals(method)) {
        users = repository.findByUsernameQuery(text);
    }else{
        users = repository.findAll();
    }
    return users;
}

 

UserRepository

public interface UserRepository extends JpaRepository<User, Long> {
    @EntityGraph(attributePaths = { "boards" })
    List<User> findAll();

    User findByUsername(String username);

    // ?1 : 첫번째 파라미터라는 의미
    @Query("select u from User u where u.username like %?1%")
    List<User> findByUsernameQuery(String username);
}

 

api/users 로 get mapping을 보내면 findAll() 함수에 의해 전체 User 데이터가 조회되고

api/users/method=query&text=dong2 와같이 파라미터를 보내면 일치하는 데이터가 조회된다.

 

@Query안의 현재 쿼리문은 JPQL이지만

두번째 파라미터로 NativeQuery=true를 주게 되면 순수 SQL문을 입력할 수 있다.

 

방법 2. QueryDsl

<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-jpa</artifactId>
  <version>${querydsl.version}</version>
</dependency>

 

<plugin>
    <groupId>com.mysema.maven</groupId>
    <artifactId>apt-maven-plugin</artifactId>
    <version>1.1.3</version>
    <executions>
      <execution>
        <goals>
          <goal>process</goal>
        </goals>
        <configuration>
          <outputDirectory>target/generated-sources/java</outputDirectory>
          <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
        </configuration>
      </execution>
    </executions>
    <dependencies>
      <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-apt</artifactId>
        <version>${querydsl.version}</version>
      </dependency>
    </dependencies>
  </plugin>

 

Q클래스 추가하는 과정에서 target/generated-sources/java경로에 추가가 죽어도 안돼서 3시간동안 붙잡다 패스

 

-- 결국 강사님 pom.xml에 맞춰 버전 전부 수정 후 다시 compile하니 해결됐다

성공한 pom.xml ⬇️

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.7.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.javalab</groupId>
	<artifactId>myhome</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>myhome</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
		<querydsl.version>5.0.0</querydsl.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mariadb.jdbc</groupId>
			<artifactId>mariadb-java-client</artifactId>
			<version>2.6.0</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-security</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.thymeleaf.extras</groupId>
			<artifactId>thymeleaf-extras-springsecurity5</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
			<version>1.18.24</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>com.querydsl</groupId>
			<artifactId>querydsl-jpa</artifactId>
			<version>4.4.0</version>
		</dependency>
<!--		<dependency>-->
<!--			<groupId>org.springframework.boot</groupId>-->
<!--			<artifactId>spring-boot-starter-validation</artifactId>-->
<!--			<version>3.2.2</version>-->
<!--		</dependency>-->
<!--		<dependency>-->
<!--			<groupId>javax.persistence</groupId>-->
<!--			<artifactId>javax.persistence-api</artifactId>-->
<!--			<version>2.2</version>-->
<!--		</dependency>-->
<!--		<dependency>-->
<!--			<groupId>org.junit.jupiter</groupId>-->
<!--			<artifactId>junit-jupiter-api</artifactId>-->
<!--			<version>5.4.0</version>-->
<!--		</dependency>-->
<!--		<dependency>-->
<!--			<groupId>com.querydsl</groupId>-->
<!--			<artifactId>querydsl-apt</artifactId>-->
<!--			<version>${querydsl.version}</version>-->
<!--			<scope>provided</scope>-->
<!--		</dependency>-->
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
			<plugin>
				<groupId>com.mysema.maven</groupId>
				<artifactId>apt-maven-plugin</artifactId>
				<version>1.1.3</version>
				<executions>
					<execution>
						<goals>
							<goal>process</goal>
						</goals>
						<configuration>
							<outputDirectory>target/generated-sources/java</outputDirectory>
							<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
						</configuration>
					</execution>
				</executions>
				<dependencies>
					<dependency>
						<groupId>com.querydsl</groupId>
						<artifactId>querydsl-apt</artifactId>
						<version>${querydsl.version}</version>
					</dependency>
				</dependencies>
			</plugin>
		</plugins>
	</build>
</project>

이자식이 내 4시간을...

 

-- 

이후에.. 계속 SpringBootTest dependency가 안돼서(org.springframework.boot.test. context does not exist) 강의 듣다 포기하고 방치하다가 오늘 다시 켜서 구글링하다 <test> 써진 부분 있으면 지워보래서 지웠더니 드디어...!!!! 해결돼서 강의 진행 ㅎ 후..

 

방법 3. EntityManager

CustomizedUserRepositoryImpl

public class CustomizedUserRepositoryImpl implements CustomizedUserRepository {
    @PersistenceContext
    private EntityManager em;

    @Override
    public List<User> findByCustomUsername(String username) {
        QUser qUser = QUser.user;
        JPAQuery<?> query = new JPAQuery<Void>(em);
        List<User> users = query.select(qUser)
                .from(qUser)
                .where(qUser.username.contains(username))
                .fetch();
        return users;
    }
}

 

 

방법 4. JdbcTemplate

@Override
public List<User> findByUsernameJdbc(String username) {
    String sql = "SELECT * FROM USER WHERE username like ?";
    List<User> users = jdbcTemplate.query(
            sql,
            new Object[]{"%"+username+"%"},
            new BeanPropertyRowMapper(User.class));
    return users;
}

 

username을 두번째 파라미터 값으로 넘겨줘야 조회가 가능하다.

 

UserApiController

@RestController
@RequestMapping("/api")
@Slf4j
class UserApiController {
    @Autowired
    private UserRepository repository;

    @GetMapping("/users")
    Iterable<User> all(@RequestParam(required = false) String method,
                   @RequestParam(required = false) String text){
        Iterable <User> users = null;
        if("query".equals(method)) {
            users = repository.findByUsernameQuery(text);
        }else if("query".equals(method)) {
            users = repository.findByUsernameNativeQuery(text);
        }else if("querydsl".equals(method)) {
            QUser user = QUser.user;
            Predicate predicate = user.username.contains(text);
            users = repository.findAll(predicate);
        }else if("querydslCustom".equals(method)) {
            users = repository.findByCustomUsername(text);
        }else if("querydslJdbc".equals(method)) {
            users = repository.findByUsernameJdbc(text);
        } else {
            users = repository.findAll();
        }
        return users;
    }

    @PostMapping("/users")
    User newUser(@RequestBody User newUser) {
        return repository.save(newUser);
    }

    @GetMapping("/users/{id}")
    User one(@PathVariable Long id) {

        return repository.findById(id).orElse(null);
    }
    @PutMapping("/users/{id}")
    User replaceUser(@RequestBody User newUser, @PathVariable Long id) {

        return repository.findById(id)
                .map(user -> {
                    user.getBoards().clear(); // 기존의 데이터 삭제
                    user.getBoards().addAll(newUser.getBoards());  // 지금 받은 데이터로 바꿈
                    for(Board board : user.getBoards()){
                        board.setUser(user);
                    }
                    return repository.save(user);
                })
                .orElseGet(() -> {
                    newUser.setId(id);
                    return repository.save(newUser);
                });
    }
    @DeleteMapping("/users/{id}")
    void deleteUser(@PathVariable Long id) {
        repository.deleteById(id);
    }
}