조건식

조건식 - CASE 식

---

 

학생요금 / 경로요금 예제 (기본 CASE 식)

JpqlMain

쿼리 식을 따로 떼서 보면 이렇다

String query =
    "select " +
        "case when m.age <= 10 then '학생요금' " +
        "     when m.age >= 60 then '경로요금' " +
        "     else '일반요금' " +
        "end " +
    "from Member m";

 

전체 코드는

public class JpqlMain {

  public static void main(String[] args) {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
    EntityManager em = emf.createEntityManager();

    EntityTransaction tx = em.getTransaction();
    tx.begin();

    try {
      // 팀 먼저 저장
      Team team = new Team();
      team.setName("TeamA");
      em.persist(team);

      Member member = new Member();
      member.setUsername("member1");
      member.setAge(10);
      member.setType(MemberType.ADMIN); // 여기 추가
      em.persist(member);

      member.setTeam(team); // 연관관계 편의 메서드 만들러 Member 다녀옴.

      em.flush();
      em.clear(); // 비움

      String query =
          "select " +
              "case when m.age <= 10 then '학생요금' " +
              "     when m.age >= 60 then '경로요금' " +
              "     else '일반요금' " +
              "end " +
          "from Member m";
      List<String> result = em.createQuery(query, String.class)
          .getResultList();
      for (String s : result) {
        System.out.println("s = " + s);
      }

      tx.commit();
    } catch (Exception e) {
      tx.rollback();
      e.printStackTrace();
    } finally {
      em.close();
    }
    emf.close();
  }
}

 

실행해보면

왜? setAge를 10으로 했기 때문에!

 

---

단순 CASE 식 - 인센티브 예제

JpaMain

public class JpqlMain {

  public static void main(String[] args) {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
    EntityManager em = emf.createEntityManager();

    EntityTransaction tx = em.getTransaction();
    tx.begin();

    try {
      // 팀 먼저 저장
      Team team = new Team();
      team.setName("TeamA");
      em.persist(team);

      Member member = new Member();
      member.setUsername("member1");
      member.setAge(10);
      member.setType(MemberType.ADMIN); // 여기 추가
      em.persist(member);

      member.setTeam(team); // 연관관계 편의 메서드 만들러 Member 다녀옴.

      em.flush();
      em.clear(); // 비움

      String query =
          "select " + "case t.name "
              + "when 'TeamA' then '인센티브 110%' "
              + "when 'TeamB' then '인센티브 120%' "
              + "else '인센티브 105%' "
              + "end "
              + "from Team t";

      List<String> result = em.createQuery(query, String.class)
          .getResultList();
      for (String s : result) {
        System.out.println("s = " + s);
      }

      tx.commit();
    } catch (Exception e) {
      tx.rollback();
      e.printStackTrace();
    } finally {
      em.close();
    }
    emf.close();
  }
}

 

 

쿼리식만 빼서 다시한번 적으면

      String query =
          "select " + "case t.name "
              + "when 'TeamA' then '인센티브 110%' "
              + "when 'TeamB' then '인센티브 120%' "
              + "else '인센티브 105%' "
              + "end "
              + "from Team t";

 

실행해보면

위에서 TeamA로 적었으므로.

 

---

조건식 - CASE 식 (2)

 

바로 예시

이름 없는 회원을 위해 .setUsername() 주석처리

public class JpqlMain {

  public static void main(String[] args) {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
    EntityManager em = emf.createEntityManager();

    EntityTransaction tx = em.getTransaction();
    tx.begin();

    try {
      // 팀 먼저 저장
      Team team = new Team();
      team.setName("TeamA");
      em.persist(team);

      Member member = new Member();
//      member.setUsername("member1"); 빼버림 요기
      member.setAge(10);
      member.setType(MemberType.ADMIN); 
      em.persist(member);

      member.setTeam(team); 

      em.flush();
      em.clear(); // 비움


      String query = "select coalesce(m.username, '이름 없는 회원') from Member m";
      List<String> result = em.createQuery(query, String.class)
          .getResultList();
      for (String s : result) {
        System.out.println("s = " + s);
      }

      tx.commit();
    } catch (Exception e) {
      tx.rollback();
      e.printStackTrace();
    } finally {
      em.close();
    }
    emf.close();
  }
}

 

실행 결과

---

사용자 이름이 관리자면 예시

public class JpqlMain {

  public static void main(String[] args) {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
    EntityManager em = emf.createEntityManager();

    EntityTransaction tx = em.getTransaction();
    tx.begin();

    try {
      // 팀 먼저 저장
      Team team = new Team();
      team.setName("TeamA");
      em.persist(team);

      Member member = new Member();
//      member.setUsername("member1"); 빼버림
      member.setUsername("관리자");
      member.setAge(10);
      member.setType(MemberType.ADMIN); // 여기 추가
      em.persist(member);

      member.setTeam(team); // 연관관계 편의 메서드 만들러 Member 다녀옴.

      em.flush();
      em.clear(); // 비움


//      String query = "select coalesce(m.username, '이름 없는 회원') from Member m";
      String query = "select NULLIF(m.username, '관리자') from Member m";
      List<String> result = em.createQuery(query, String.class)
          .getResultList();
      for (String s : result) {
        System.out.println("s = " + s);
      }

      tx.commit();
    } catch (Exception e) {
      tx.rollback();
      e.printStackTrace();
    } finally {
      em.close();
    }
    emf.close();
  }
}

 

실행하면

null 반환

 

이름 세팅을

member.setUsername("뉴진스"); 로 

바꾸면

 

실행 결과

자기 이름ㅇ 출력됨을 확인 가능