20220907 [Spring Boot, Oracle]

Yeoonnii·2022년 9월 7일
Spring bootTILoraclesql
0

TIL

목록 보기
22/52
post-thumbnail

👨‍🏫 쿼리문 연습 뿐만이 아니라
SQL문을 이용하여 스프링에 연결하는것까지 연습해야한다! 화면구현도 많이 해보기
최종적으로 SQL을 Spring에 연동해서 자유자재로 사용가능한 정도가 되어야 한다

⚙️ SPRING과 SQL

SQL Mapper와 JPA(Java Persistence API)는 비슷한 일 다른 이름으로 일한다

SQL Mapper

SQL <—매핑—> Object 필드
SQL Mapper는 SQL 문장으로 직접 데이터베이스 데이터를 다룬다
즉, SQL Mapper는 SQL을 명시해줘야 한다

  • Object와 SQL의 필드를 매핑하여 데이터를 객체화
  • 객체와 테이블 간의 관계를 매핑하는 것이 아님
  • SQL문을 직접 작성하고 쿼리 수행 결과를 어떠한 객체에 매핑할지 바인딩 하는 방법
  • DBMS에 종속적인 문제
    EX) JdbcTemplate, MyBatis

JPA(ORM)

개발자가 JPA를 사용하면, JPA 내부에서 JDBC API를 사용하여 SQL을 호출하여 DB와 통신

  • JAVA ORM(Object Relational Mapping)의 기술 표준
  • 대표적인 오픈소스로 Hibernate가 있다
  • CRUD 메소드 기본 제공
  • 쿼리를 만들지 않아도 됨
  • MyBatis는 쿼리가 수정되어 데이터 정보가 바뀌면 그에 사용 되고 있던 DTO와 함께 수정해주어야 하는 반면에, JPA 는 객체만 바꾸면 된다 => 객체 중심으로 개발 가능
  • but 복잡한 쿼리는 해결이 어려움

🌎참고 : JPA와 MyBatis 차이

💻 Spring에서 SQL사용

Oracle MEMBERTBL 데이터 스프링으로 가져오기
JAP와 SERVICE가 일하듯이 MAPPER와 SERVICE가 일한다

MYBATIS 라이브러리 추가

<!-- ORACLE DB -->
		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc8</artifactId>
		</dependency>

		<!-- mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.2.0</version>
		</dependency>

📁 application.properties

📁 resources/templates/application.properties 에서 DB연결에 대한 설정

  • mongodb와 같이 실행한다
  • 💡 DB연결 후 일정시간이 지났는데 사용하지 않고있으면 연결이 끊긴다
    연결에 대한 부분을 미리 설정해서 주기적으로 연결이 끊어지지 않게 관리하도록 한다
# oracle
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@1.234.5.158:11521/xe
spring.datasource.username=ds207
spring.datasource.password=pw207

# mysql, mariadb
# spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
# spring.datasource.url=jdbc:mysql://localhost:3306/DB
# spring.datasource.username=아이디
# spring.datasource.password=암호

#연결에 대한 부분 설정 => 주기적으로 연결 끊어지지 않게 관리
spring.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL
spring.datasource.hikari.connection-timeout=600000
spring.datasource.hikari.maximum-pool-size=500
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.minimum-idle=20
spring.datasource.hikari.validation-timeout=3000
spring.datasource.hikari.idle-timeout=60000

📁 MemberVO.java

JPA = repository = 데이터 일치해야함
mybatis = 쿼리문(네이티브)사용하여 데이터를 가져온다

  • MemberVO는 DB랑 연관이 없고 데이터가 들어갈 공간만 있으면 된다
    ➡️컨트롤러에서 서비스 사용 위한 용도
  • 여기서 데이터 조작은 불가하며 mybatis의 데이터 조작은 네이티브 쿼리문으로만 진행된다
  • 단지 전송을 위한 용도이기 때문에 프로젝션 상관없고 클래스, 변수명도 DB 데이터와 일치하지 않아도 됨
package com.example.vo;

import java.sql.Date;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
@NoArgsConstructor
public class MemberVO {
    private String userid;    
    private String userpw;    
    private int age;    
    private String phone;    
    private String gender;    
    private Date regdate;    
}

📁 MemberMapper.java

회원 추가/조회 서비스 생성

  • Mapper에 쿼리문 생성
  • 쿼리문 알아야 DB에서 데이터 조작할 수 있다
  • MemberMapper의 value에 들어가는 값이 SELECT M.* FROM MEMBERTBL M;에 들어가는 값과 같다
  • JPA에서는 @Repository 사용했듯이 MYBATIS에서는 @Mapper를 사용해준다

💡 주의사항

  1. 쿼리문 마지막에 ;(세미콜론) 사용하지 않는다
  2. autocommit으로 설정되어 있기 때문에 Commit할 필요가 없다!
    ➡️ Commit도 불가하듯이 ROLLBACK도 불가하다
// @Repository => JPA
@Mapper // => MYBATIS
public interface MemberMapper {

        // 회원 등록
        @Insert(value = {
                        "<script>",
                        " INSERT INTO MEMBERTBL(USERID, USERPW, AGE, PHONE, GENDER, REGDATE) ",
                        " VALUES(#{obj.userid}, #{obj.userpw}, #{obj.age}, #{obj.phone}, #{obj.gender}, CURRENT_DATE ) ",
                        "</script>"
        })
        public int insertMember(@Param(value = "obj") MemberVO member);

        // 회원 조회
        // @Select(value = {"SELECT * FROM MEMBERTBL "})
        @Select(value = {
                        "<script>",
                        "SELECT M.* FROM MEMBERTBL M ORDER BY M.USERID DESC ",
                        "</script>"
        })
        public List<MemberVO> selectMemberList();

}

회원등록 작성 후 SERVICE로 돌아간다

📁 MyBatisMemberService.java

MyBatisMemberService.java 생성

  • @Autowired 로 MemberMapper mMapper; 이용하여 서비스 생성 후 컨트롤러에서 MemberController 생성 후 서비스 호출
@Service
public class MyBatisMemberService {

    @Autowired
    MemberMapper mMapper;
    
    // 회원 추가
    // Oracle => INSERT INTO .. 와 같다
    public int insertMember (MemberVO member) {
        try {
            return mMapper.insertMember(member);
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }

    }

    // 회원 조회
    // Oracle => SELECT * FROM... 과 같다
    public List<MemberVO> selectMemberList() {
        try {
            return mMapper.selectMemberList();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

📁 MemberController.java

@Autowired
    MyBatisMemberService mmService;
// mybatis이용한 회원 조회
// 127.0.0.1:8080/ROOT/member/memberlist.do
    @GetMapping(value = "/memberlist.do")
    public String memberListGET(Model model){
        List<MemberVO> list = mmService.selectMemberList();
        model.addAttribute("list", list);
        return "memberlist";
    }

📁 Application.java

Boot20220824Application.java에 mapper 등록!
➡️ mapper scan 되어야 사용가능하다

// mybatis mapper의 위치
@MapperScan(basePackages = {"com.example.mapper"})

SQL

📌 DCL (데이터 제어어)

  • 데이터베이스에 접근하고 객체들을 사용하도록 생성한 사용자에게 권한을 주거나 회수하는 명령어
  • 사용자를 만들고 권한을 부여하는게 DCL
  • 대부분 웹개발자는 DBA(최고 권한자가 지정해준 권한만 사용한다)
-- 최고권한자가 사용자 생성시 아래 코드를 사용한다 (아이디 aaa, 암호 bbb)    
CREATE USER 'aaa' IDENTIFIED BY 'bbb';
    
-- 최고권한자가 생성한 사용자의 권한 지정시
-- 사용자별로 생성/수정/삭제/등 세밀하게 지정가능 
-- SELECT만 가능한사람, DELETE만 가능한 사람 등...
GRANT CONNECT, RESOURCE, DBA TO aaa;

-- 최고권한자가 생성한 사용자 삭제시
DROP USER aaa CASCADE;

📌 DDL(데이터 정의어)

DDL(Data Definition Language)은 데이터 베이스에 데이터를 보관하기 위한 객체를 생성, 변경, 삭제등의 기능을 수행하는 명령어를 말한다

  • CREATE : 테이블 생성
  • DROP : 테이블 삭제
  • ALTER : 테이블 수정(구조 변경)
  • RENAME : 테이블 이름 변경
  • TRUNCATE : 테이블에 있는 모든 데이터 삭제

📌 DML(데이터 조작어)

INSERT, UPDATE, DELETE, SELECT

  • SELECT : 데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 한다
  • INSERT, UPDATE, DELETE
    ➡️ 데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류(데이터 삽입, 수정, 삭제)의 명령어들을 말함

📌 TCL(트렌젝션 제어어)

논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업단위(트랜잭션)별로 제어하는 명령어를 말한다 ➡️ COMMIT, ROLLBACK, SAVEPOINT

  • COMMIT : 모든 작업들을 정상 처리하겠다고 확정하는 명령어(데이터 변경)
  • ROLLBACK : 트랜잭션의 처리 과정에서 발생한 변경사항을 취소하는 명령어(데이터 변경)
  • SAVEPOINT : COMMIT전 특정 시점까지만 반영하거나 Rollback하겠다는 명령어

📃 회원 테이블

📙 클래스(반) 테이블 생성

클래스(반) 테이블 생성 + 기본키 제약조건

TIMESTAMP를 DEFAULT로 넣으면 ORACLE에서 현재 날짜를 자동으로 입력해준다!

CREATE TABLE CLASSTBL(
    CLSCODE CHAR(1), -- PK
    CLSNAME VARCHAR2(20) NOT NULL,
    CLSREGDATE TIMESTAMP DEFAULT CURRENT_DATE,
    
    CONSTRAINT PK_CLASSTBL_CODE PRIMARY KEY(CLSCODE)
);
잘못만들어서 테이블 삭제후 재생성함
--DROP TABLE CATSSTBL;

📕 클래스(반) 데이터 입력

INSERT INTO CLASSTBL(CLSCODE, CLSNAME) VALUES('A', '601호');
INSERT INTO CLASSTBL(CLSCODE, CLSNAME) VALUES('B', '602호');
INSERT INTO CLASSTBL(CLSCODE, CLSNAME) VALUES('C', '603호');
COMMIT;

SELECT C.* FROM CLASSTBL C;

📃 학생 테이블

📙 학생 테이블 생성

외래키로 CLASSTBL(클래스(반) 테이블)의 CLSCODE를 참조한다

학생 테이블 생성 + 기본키 제약조건 + 외래키 제약조건

TIMESTAMP를 DEFAULT로 넣으면 ORACLE에서 현재 날짜를 자동으로 입력해준다!

-- 학생테이블 생성
CREATE TABLE STUDENTTBL(
    STDNO NUMBER, -- PK
    STDNAME VARCHAR2(30) NOT NULL,
    STDCLASS CHAR(1),
    STDSCOREKOR NUMBER,
    STDSCOREENG NUMBER,
    STDSCOREMATH NUMBER,
    STDREGDATE TIMESTAMP DEFAULT CURRENT_DATE,
    
    CONSTRAINT PK_STUDENT_NO PRIMARY KEY(STDNO),
    CONSTRAINT FK_CLASS_CODE FOREIGN KEY(STDCLASS) REFERENCES CLASSTBL(CLSCODE)
);
COMMIT;

📕 학생클래스 데이터 입력

-- 학생클래스 데이터 입력
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREMATH, STDSCOREENG) 
    VALUES(1006, '학생6', 'A', 50, 85, 40);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREMATH, STDSCOREENG) 
    VALUES(1007, '학생7', 'B', 30, 75, 80);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREMATH, STDSCOREENG) 
    VALUES(1008, '학생8', 'C', 80, 95, 100);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREMATH, STDSCOREENG) 
    VALUES(1009, '학생9', 'A', 60, 55, 82);
INSERT INTO STUDENTTBL(STDNO, STDNAME, STDCLASS, STDSCOREKOR, STDSCOREMATH, STDSCOREENG) 
    VALUES(1010, '학생10', 'C', 95, 80, 75);

COMMIT;

SELECT S.* FROM STUDENTTBL S;

📌 CHAR(고정길이)와 VARCHAR(가변길이)

🌎참고 : CHAR 와 VARCHAR 차이

  • CHAR(s) : 고정 길이 문자열 정보
  • VARCHAR(s) : s만큼의 최대 길이를 갖지만 가변 길이로 조정이 되기 때문에 할당된 변수값의 바이트만 적용된다

CHAR

예를 들어 CHAR(10) 인데 ‘test’라는 4자짜리 문자열을 insert 하게 되면 남는 공간은 6개의 공백으로 채우게 된다
이때 남는 공간은 공백(스페이스)으로 채워지므로 공간의 낭비가 발생한다.
값을 받아 올 때 이 공백은 자동으로 제거되지만 무조건 처음 선언된 10byte가 소요되게 된다
따라서 반드시 고정길이에 해당하는 데이터만 CHAR로 선언하는 것이 좋다

VARCHAR

데이터를 삽입 시 데이터값 이외에 삽입된 문자열의 길이도 함께 저장되는데,
255글자 이하에는 1바이트 그 이상은 2바이트의 추가 공간을 필요로 한다

즉 실질적인 데이터와 함께 길이 정보도 같이 저장된다!

예를 들어 VARCHAR(10)에 ‘test’라는 4자짜리 문자열을 삽입하면
4byte + 1byte(길이를 저장하기 위한 메모리) = 5byte가 소모된다

VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐이다

그렇기 때문에 길이가 다양한 컬럼과 정의된 길이와 실제 데이터 길이에 차이가 있는 컬럼에 사용하기 적합하다
저장 측면에서도 VARCHAR 유형이 CHAR 유형보다 작은 영역에 저장할 수 있는 장점이 있다

🕹️ TO_CHAR 내장함수 사용하기

날짜 DATE ➡️ String 으로 변경하기
--초 설정시 FF 사용한다! FF3사용시 초 3자리까지 출력된다

SELECT
    S.*, TO_CHAR(S.STDREGDATE, 'YYYY-MM-DD HH24:MI:SS.FF') REGDATE1

    -- S.*, TO_CHAR(S.STDREGDATE, 'YYYY-MM-DD HH24:MI:SS') 여기 변수지정안하면 테이블 이름이 주어진 값으로 지정된다
FROM
    STUDENTTBL S;

📋 실습문제

[문제1] 학생번호 1001, 1005, 1007 번의 국어점수를 98점으로 변경

UPDATE 테이블명 SET 컬럼명 = 바꿀 변경값들..

UPDATE studenttbl SET STDSCOREKOR = 98 WHERE STDNO IN(1001, 1005, 1007);

COMMIT;

SELECT S.* FROM STUDENTTBL S;

[문제2] 국어점수가 40미만을 60점으로 변경

UPDATE 테이블명 SET 컬럼명 = 바꿀 변경값들.. WHERE = 조건

UPDATE studenttbl SET STDSCOREKOR = 60 WHERE STDSCOREKOR < 40;

COMMIT;

SELECT S.* FROM STUDENTTBL S;

[문제3] 학번이 1007인 학생을 삭제

DELETE FROM 테이블명 WHERE 조건;

DELETE FROM studenttbl WHERE STDNO = 1007;

COMMIT;
SELECT S.* FROM STUDENTTBL S;

📌 Oracle 그룹함수 (SUM, AVG, MAX, MIN, COUNT)

🕹️ 합과 평균 구하기(SUM, AVG)

-- STUDENTTBL을 S로 사용할것이다
SELECT S.* FROM STUDENTTBL S; 
-- S안의 다른 데이터만 꺼내올 수도 있고 전체 테이블과 같이 사용할 수 있다
SELECT S.STDNO, S.STDNAME, S.* FROM STUDENTTBL S; 

SELECT 
    SUM(S.STDSCOREKOR) TOT,
    AVG(S.STDSCOREKOR) AVG
FROM
    studenttbl S
WHERE
    STDCLASS = 'A';

📋 실습문제

[문제1] B반의 국어,영어,수학 점수의 합 구하기

SELECT 
    SUM(S.STDSCOREKOR), SUM(S.STDSCOREENG), SUM(S.STDSCOREMATH)
FROM
    studenttbl S
WHERE
    STDCLASS = 'B';

[문제2] 국어점수가 10점 이상인 학생을 학번순으로 내림차순 정렬

SELECT 
    S.* 
FROM 
    STUDENTTBL S 
WHERE 
    S.STDSCOREKOR >= 10 
ORDER BY STDNO DESC;

[문제3] 학생별 성적 평균 구하기

ROUND(숫자, 자릿수) ➡️ 반올림해주는 함수(반올림할 숫자, 출력할 자릿수)

SELECT S.STDNO, S.STDNAME,
    ROUND((S.STDSCOREKOR + S.STDSCOREENG + S.STDSCOREMATH) / 3 , 2) AVG
FROM
    STUDENTTBL S;

📌 GROUP BY, HAVING

WHERE - GROUP BY - HAVING의 순서

 SELECT SUM(컬럼명)
           , AVG(컬럼명)
           , MAX(컬럼명)
           , MIN(컬럼명)
           , COUNT(컬럼명)
  FROM 테이블명
 WHERE 조건
 GROUP BY 그룹화할 컬럼
 HAVING 그룹에 대한 조건

그룹화한 항목과 조회할 갯수가 같게 바뀌어야한다 ➡️ 1:1 또는 N:N 이어야 함

SELECT S.* FROM STUDENTTBL S;
SELECT S.* FROM STUDENTTBL S GROUP BY STDCLASS; -- 이렇게 하면 오류난다
SELECT S.STDCLASS FROM STUDENTTBL S GROUP BY STDCLASS;  -- S.STDCLASS와 STDCLASS는 항목의 개수가 같으니 그룹화해서 출력가능

🕹️ GROUP BY에서 변형된 값/형태 사용

GROUP BY 식에서 사용한 컬럼이 TO_CHAR() 함수에 의해 값이 변경되었다면 변형된 형태로 SELECT 절에 사용되어야 한다.
🌎참고 : GROUP BY

[문제] "시간대"을 기준으로 조회수의 평균과 개수 조회

SELECT 
    SUBSTR(TO_CHAR(B.REGDATE, 'YYYY-MM-DD HH24:MM'),12 , 2),
    ROUND(AVG(B.HIT), 2) AGVHIT,
    COUNT(*)
FROM
    BOARDTBL B
GROUP BY 
    SUBSTR(TO_CHAR(B.REGDATE, 'YYYY-MM-DD HH24:MM'),12 , 2);

📋 실습문제

[문제1] 반별로 학생 그룹화 하기

SELECT
    S.STDCLASS,
    SUM(S.STDSCOREKOR),
    COUNT(*)
FROM
    STUDENTTBL S
GROUP BY
    STDCLASS;

[문제2] 국어점수가 50점 이상인 학생만 반별 수학점수의 합 구하기

WHERE에서 먼저 필터링 된 후 그룹화 된다
1. SELECT~WHERE 먼저 실행 후
2. GROUP BY 실행

SELECT  
    S.STDCLASS,
    SUM(S.STDSCOREMATH),
    COUNT(*)
FROM
    STUDENTTBL S
WHERE
    S.STDSCOREKOR >= 50
GROUP BY
    STDCLASS;

[문제3] 반 별 국어점수 합을 구해서 합이 300이상인 것만 조회

그룹화 먼저 진행하고 그룹화 결과에 따라 조건 필터링한다
그룹화 이후에 진행되는 조건은 WHERE 이 아니라 HAVING 사용한다
1. GROUP BY 먼저 진행 후
2. SELECT~HAVING 실행

SELECT
    S.STDCLASS,
    SUM(S.STDSCOREKOR),
    COUNT(*)
FROM
    STUDENTTBL S
GROUP BY
    STDCLASS
HAVING
    SUM(S.STDSCOREKOR) >= 100;

📌 Dual 테이블

오라클 자체에서 제공되는 테이블이며,
간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용하는 테이블이다.

SELECT B.* FROM BOARDTBL B;
--문제 풀기전 확인시 
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24') FROM DUAL;

📌 SUBSTR 함수

SUBSTR 함수는 문자단위로 시작위치와 자를 길이를 지정하여 문자열을 자른다.
함수 : SUBSTR("문자열", "시작위치", "길이")

[문제] 시간에서 원하는 숫자만을 꺼내쓰기

오라클 내장함수 SUBSTR 사용한다
SUBSTR('원하는 날짜 또는 시간의 자리', 가져오기시작할자릿수, 선택한자릿수부터 몇개가져올지)

SELECT SUBSTR('2022-09-17 14', 12, 2);

📋 실습문제

[문제1] "날짜"를 기준으로 조회수의 합과 개수 (TO_CHAR()사용)

SELECT 
    TO_CHAR(B.REGDATE, 'YYYY-MM-DD'),
    SUM(B.HIT),
    COUNT(*)
FROM
    BOARDTBL B
GROUP BY 
    TO_CHAR(B.REGDATE, 'YYYY-MM-DD');

💻 Spring에서 SQL사용

vscode에서 StudentTest 실습하기
컨트롤러 서비스 화면 안만들어도 되니 데이터 가져오는 연습만 해보기
=> 단위테스트(apptest에서 실행)로 연습 ⇒ 디버그 콘솔로 확인
서비스 만들지 말고 mapper 로 바로 전송하여 사용

최종적으로 sql을 spring에 연동해서 사용가능한 정도가 되어야 함
@@ 최종 완성 후 다시한번 코드수정하여 올리기!@@

📚 Oracle

@@ 최종 완성 후 다시한번 코드수정하여 올리기!@@

  1. 학생 전체목록 가져오기
  2. 학생이름에서 검색한 데이터가 포함되는 데이터만 가져오기
  3. 학생목록에서 학번, 학생, 합, 평균 가져오기
    =>vo에 쿼리 만들어도 되고 map으로 반환시키면 vo를 안만들어도 된다
  4. 반별 국어, 영어, 수학 평균 가져오기
    --1. 반별로 학생 그룹화 하기
    --2. 그룹의 평균 구하기
  5. ? 학생목록 5개씩 표시하는 페이지네이션(날짜는 년월일만 가져오기)
    --ROWN 지정
-- 학생 전체목록 가져오기
SELECT S.* FROM STUDENTTBL S;

-- 학생이름에서 검색한 데이터가 포함되는 데이터만 가져오기
SELECT S.* FROM STUDENTTBL S WHERE S.STDNAME LIKE '%' || '김' || '%';

--  학생목록에서 학번, 학생, 합, 평균 가져오기 
=>vo에 쿼리 만들어도 되고 map으로 반환시키면 vo를 안만들어도 된다
SELECT 
    S.STDNO, S.STDNAME, (S.STDSCOREKOR + S.STDSCOREENG + S.STDSCOREMATH) SUM, ROUND((S.STDSCOREKOR + S.STDSCOREENG + S.STDSCOREMATH) / 3 , 2) AVG  
FROM 
    STUDENTTBL S;

-- ? 반별 국어, 영어, 수학 평균 가져오기
--1. 반별로 학생 그룹화 하기
--2. 그룹의 평균 구하기
SELECT
    S.STDCLASS,
    COUNT(*),
    ROUND((AVG(STDSCOREKOR) + AVG(STDSCOREENG) + AVG(STDSCOREMATH))/3 , 2) AVG,
    ROUND(SUM(STDSCOREKOR)/COUNT(*), 2) KORAVG,
    SUM(STDSCOREKOR),
    ROUND(SUM(STDSCOREENG)/COUNT(*), 2) ENGAVG,
    SUM(STDSCOREENG),
    ROUND(SUM(STDSCOREMATH)/COUNT(*), 2) MATHAVG,
    SUM(STDSCOREMATH)
    
FROM 
    STUDENTTBL S
GROUP BY
    STDCLASS;

--HAVING

-- ? 학생목록 5개씩 표시하는 페이지네이션(날짜는 년월일만 가져오기)
--ROWN 지정
SELECT S.*, ROW_NUMBER() OVER (ORDER BY STDNO ASC) ROWN FROM STUDENTTBL S;

SELECT * FROM (SELECT S.*, ROW_NUMBER() OVER (ORDER BY STDNO ASC) ROWN FROM STUDENTTBL S) WHERE ROWN BETWEEN 1 AND 5;

📁 StudentVO.java

package com.example.vo;

import java.sql.Date;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
@NoArgsConstructor
public class StudentVO {
    private int stdno;
    private String stdname;
    private char stdclass;
    private int stdscorekor;
    private int stdscoreeng;
    private int stdscoremath;
    private Date stdregdate;    

}

📁 StudentMapper.java

@@ 최종 완성 후 다시한번 코드수정하여 올리기!@@

package com.example.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface StudentMapper {
    
    // 학생 전체목록
    @Select(value = {
        "<script>",
        " SELECT S.* FROM STUDENTTBL S ",
        "</script>"
    })
    // public List<StudentVO> selectStudentList(); 
    // StudentVO 대신에 Map<String, Object> 키는 컬럼명, value 실제값
    public List<Map<String, Object>> allStudentList();

    // 학생이름에 검색되는 데이터만 가져오기
    @Select(value = {
        "<script>",
        " SELECT S.* FROM STUDENTTBL S ",
        " WHERE S.STDNAME LIKE '%' || #{text} || '%' ",
        "</script>"
    })
    public List<Map<String, Object>> searchStudentList(@Param("text") String text);

    // 학생목록에서 학번, 학생, 합, 평균 가져오기
    @Select(value = {
        "<script>",
        " SELECT S.STDNO, S.STDNAME, (S.STDSCOREKOR + S.STDSCOREENG + S.STDSCOREMATH) SUM, ROUND((S.STDSCOREKOR + S.STDSCOREENG + S.STDSCOREMATH) / 3 , 2) AVG FROM STUDENTTBL S ",
        "</script>"
    })
    public List<Map<String, Object>> studentData();
}
profile
Yeoonnii
이전 포스트

20220906 [Oracle]

다음 포스트

20220908 [Spring Boot, Oracle]

0개의 댓글