Query문을 잘만 쓰면 개꿀, 기본문제풀이 4개

 

#####  문제 1

Q. 주민번호 입력하고 성별과 나이 구하기

1. Interface

public interface InterInterface {
    String getName();
    String getTel();
    String getJumin();
    String getAddress();
    //나이와 성별 추가사항
    int getAge();
    String getSb();

 

2. Repository

@Query(value =  "select id, name, tel, address, hobby, " +
            "TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))-TO_NUMBER( " +
               "CASE WHEN SUBSTR(jumin,8,1) IN('1','2') THEN '19' " +
            "WHEN SUBSTR(jumin,8,1) IN('3','4') THEN '20' " +
            "END || SUBSTR(jumin,1,2))+1 as age, " +
            "case when SUBSTR(jumin,8,1) in ('1','3') then '남자' " +
            "when SUBSTR(jumin,8,1) in ('2','4') then '여자' " +
            "end as sb " +
            "from member1113 ", nativeQuery = true)
     List<MemberEntity> result();
   public  MemberEntity findOneById(String id);
}

 

3. OutTemplate

<div class="container">
    <table class="table">
        <thead>
        <tr>
            <th>아이디</th>
            <th>이름</th>
            <th>전화번호</th>
            <th>성별</th>
            <th>나이</th>
            <th>거주지</th>
            <th>취미</th>
        </tr>
        </thead>
        <tbody>
        <tr th:each="emp:${list}">
            <td th:text="${emp.id}"></td>
            <td th:text="${emp.name}"></td>
            <td th:text="${emp.tel}"></td>
            <td th:text="${emp.sb}"></td>
            <td th:text="${emp.age}"></td>
            <td th:text="${emp.address}"></td>
            <td th:text="${emp.hobby}"></td>
        </tr>

        </tbody>
    </table>
</div>

 

 

#####    문제 2

Q. 주차요금 구하기; 기본 30분은 1000원, 30분씩 500원 추가 

 

1. Interface

public interface ParkInterface {
    String getCarnumber();
    String getIntime();
    String getOuttime();
    int getParktime();
    int getFee();//주차시간 분

 

2. Repository

@Query(value = """
SELECT
    carnumber,
    intime,
    outtime,
    ROUND((TO_DATE(outtime,'HH24:MI') - TO_DATE(intime,'HH24:MI')) * 24 * 60, 0) AS parktime,
    CASE
        WHEN ROUND((TO_DATE(outtime,'HH24:MI') - TO_DATE(intime,'HH24:MI')) * 24 * 60, 0) <= 30
            THEN 1000
        ELSE
            1000
            + CEIL(
                (ROUND((TO_DATE(outtime,'HH24:MI') - TO_DATE(intime,'HH24:MI')) * 24 * 60, 0) - 30) / 10
              ) * 500
    END AS fee
FROM parktime
""", nativeQuery = true)
List<ParkInterface> allout();

 

 

##### 문제 3

Q. 학점구하기

 

1. Interface

public interface ScoreInterface {
    int getBan();
    int getBun();
    String getName();
    int getKor();
    int getEng();
    int getMat();
    int getTot();
    double getAvg();
    String getHak();
    int getBanrank();
    int getTotrank();

 

2. Repository

@Query(value = """
    select id,ban,bun,name,kor,eng,mat,
       (kor+eng+mat) as tot ,round((kor+eng+mat)/3,1) as avg ,
       case 
            when (kor+eng+mat)/3 >=90 then 'A'
            when (kor+eng+mat)/3 >=80 then 'B'
            when (kor+eng+mat)/3 >=70 then 'C'
            when (kor+eng+mat)/3 >=60 then 'D'
            else  'F' end as hak ,
        rank() over(partition by ban  order by (kor+eng+mat) desc) as totrank,
        rank() over(order by (kor+eng+mat) desc) as banrank 
        
          from score1230
          order by ban asc,bun asc      
    """,nativeQuery = true)
Page<ScoreInterface> alloutpage2(Pageable pageable);

 

 

##### 문제 4

Q. 근태관리하기 9시 이후출근 지각, 18시 이전퇴근 조퇴

 

1. Interface

public interface WorkInterface {
    long getId();
    String getName();
    String getIntime();//입실시간
    String getInresult();//입실시간 결과
    String getOuttime();//퇴실시간
    String getOutresult();//퇴실시간 결과

 

2. Repository

@Query(value = """
    select id,name,intime,outtime,    
    case when intime > '09:40' then '지각' else '정상' end as inresult,
    case when outtime > '18:00' then '정상' else '조퇴' end as outresult
    from worktime
    """,nativeQuery = true)
List<WorkInterface> resuluout();