Native Sql

Backend/Spring

Native Sql

brian110326 2024. 4. 12. 21:09

이전 글에서 JPQL에 관하여 객체를 검색하는 객체 지향 쿼리에 대해 포스팅했다

2번째 방법으로는 Native Sql이 있다. JPA 에서 JPQL 대신 직접 SQL 사용하는 방법이다.

package com.example.jpa.repository;

import com.example.jpa.entity.Member2;

import com.example.jpa.entity.Team2;

import java.util.List;

import org.springframework.data.domain.Sort;

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

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

import org.springframework.data.querydsl.QuerydslPredicateExecutor;

public interface Member2Repository

extends JpaRepository<Member2, Long>, QuerydslPredicateExecutor<Member2> {

// jpql 사용 시

// 1. entity 타입 결과 => List<Entity명>

// 2. 개별 타입 결과 => List<Object[]>

@Query("select m from Member2 m")

List<Member2> findByMembers(Sort sort);

@Query("select m.userName, m.age from Member2 m")

// String, Integer 등 개별로 가져오면 Member2타입으로 불가능

List<Object[]> findByMembers2();

// 특정 나이보다 많은 회원 조회

@Query("select m from Member2 m where m.age > ?1")

List<Member2> findByAgeList(int id);

// 특정 팀의 회원 조회

@Query("select m from Member2 m where m.team2 = ?1")

List<Member2> findByTeamEqual(Team2 team2);

@Query("select m from Member2 m where m.team2.id = ?1")

List<Member2> findByTeamIdEqual(Long id);

// 집계함수

@Query(

"select count(m), sum(m.age), avg(m.age), max(m.age), min(m.age) from Member2 m"

)

List<Object[]> aggregate();

// Join(on 쓰지 않음)

@Query("select m from Member2 m join m.team2 t where t.name = :teamName")

List<Member2> findByTeamMember(String teamName);

@Query("select m, t from Member2 m join m.team2 t where t.name = :teamName")

List<Object[]> findByTeamMember2(String teamName);

// 외부 join시 on 사용

@Query("select m, t from Member2 m left join m.team2 t on t.name = :teamName")

List<Object[]> findByTeamMember3(String teamName);

}

1. @Query 안에 있는 sql문이 먼저 실행됨

2. 변수를 부여할 때 where t.name = :teamName(메소드의 매개변수로 온것, :을 꼭 붙인다.) or where t.name = ?1(물음표 뒤 몇번째 물음표인지 표시를 해준다.)

3. innerJoin시 on을 쓰지 않는다. 하지만 outerJoin시 on을 사용한다.

RepositoryTest

package cohttp://m.example.jpa.repository;

import java.util.Arrays;
import java.util.List;
import java.util.stream.IntStream;

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.io.TempDir;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Sort;

import cohttp://m.example.jpa.entity.Address;
import cohttp://m.example.jpa.entity.Member2;
import cohttp://m.example.jpa.entity.Order;
import cohttp://m.example.jpa.entity.Product;
import cohttp://m.example.jpa.entity.Team2;

import oracle.net.aso.b;

@SpringBootTest
public class JpqlRepositoryTest {

    @Autowired
    private Member2Repository member2Repository;

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private ProductRepository productRepository;

    @Autowired
    private Team2Repository team2Repository;

    @Test
    public void insertTest() {
        IntStream.rangeClosed(1, 10).forEach(i -> {
            Team2 team2 = Team2.builder().name("Team" + i).build();
            team2Repository.save(team2);

            Member2 member2 = Member2.builder().userName("User" + i).age(i).team2(team2).build();
            member2Repository.save(member2);

            Product product = Product.builder().name("Product" + i).price(i * 1000).stockAmount(i * 5).build();
            productRepository.save(product);
        });
    }

    @Test
    public void orderInsertTest() {
        Address address = new Address();
        address.setCity("Brooklyn NY");
        address.setStreet("PowerStreet50");
        address.setZipcode("78249");

        IntStream.rangeClosed(1, 3).forEach(i -> {
            Member2 member2 = Member2.builder().id(1L).build();
            Product product = Product.builder().id(2L).build();

            Order order = Order.builder().member2(member2).product(product).homeAddress(address).build();
            orderRepository.save(order);
        });
    }

    @Test
    public void findMembersTest() {
        // List<Member2> list = member2Repository.findByMembers();
        // list.forEach(member -> {
        // System.out.println(member);
        // });

        System.out.println(member2Repository.findByMembers(Sort.by("age")));

        List<Object[]> list = member2Repository.findByMembers2();

        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));
            Systehttp://m.out.printf("userName : %s, age : %d\n", objects[0], objects[1]);
        }
    }

    @Test
    public void findAddressTest() {
        List<Address> list = orderRepository.findByHomeAddress();
        System.out.println(list);

    }

    @Test
    public void findOrdersTest() {
        List<Object[]> list = orderRepository.findByOrders();
        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));

            Member2 member2 = (Member2) objects[0];
            Product product = (Product) objects[1];
            Long id = (Long) objects[2];

            System.out.println(member2);
            System.out.println(product);
            System.out.println(id);
        }

        // [Member2(id=1, userName=User1, age=1), Product(id=2, name=Product2,
        // price=2000, stockAmount=10), 1]

    }

    @Test
    public void ageTest() {
        List<Member2> list = member2Repository.findByAgeList(5);
        System.out.println(list);
    }

    @Test
    public void teamMemberTest() {
        List<Member2> list = member2Repository.findByTeamEqual(new Team2(1L, "Team1"));
        System.out.println(list);

        List<Member2> list2 = member2Repository.findByTeamIdEqual(2L);
        System.out.println(list2);
    }

    @Test
    public void aggregateTest() {
        List<Object[]> list = member2Repository.aggregate();

        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));
            System.out.println("회원수 = " + objects[0]);
            System.out.println("나이합계 = " + objects[1]);
            System.out.println("나이평균 = " + objects[2]);
            System.out.println("최대나이 = " + objects[3]);
            System.out.println("최소나이 = " + objects[4]);
        }
    }

    @Test
    public void joinTest() {
        System.out.println(member2Repository.findByTeamMember("Team1"));

        List<Object[]> list = member2Repository.findByTeamMember2("Team2");
        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));
            Member2 member2 = (Member2) objects[0];
            Team2 team2 = (Team2) objects[1];

            System.out.println(member2);
            System.out.println(team2);
        }
    }
}

★ ★ ★ ★ ★ List<object[]></object[]> 형태 ★ ★ ★ ★ ★

결과값이 List 안에 List가 나온다. 예를 들어 [ [Member2(memberName,age,address)], [Team2(teamName, teamId] ] 이런 형태로 나온다면

Member2 member2 = (Member2) objects[0];

Team2 team2 = (Team2) objects[1];

objects list를 통해 형변환과 동시에 같이 새로운 객체를 만들 수 있다.