[37일차] 국비 교육

snooop 2022. 9. 8. 17:40

<커밋 롤백 추가>

package com.kh.jdbc.dao;

import com.kh.jdbc.util.Common;
import com.kh.jdbc.vo.EmpVO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

// Query 문으로 DB의 정보를 가져옴
public class EmpDAO {
    Connection conn = null;
    Statement stmt = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Scanner sc = new Scanner(System.in);
    public List<EmpVO> empSelect(){
        List<EmpVO> list = new ArrayList<>();
        // arraylist가 조회할때는 유리하므로 linkedlist는 삽입/삭제 시 유리
        try{
            conn = Common.getConnection();
            // DB에 SQL문을 전달하여 실행 시키고 결과값을 반환 받기 위해 사용
            stmt = conn.createStatement();
            String sql = "SELECT * FROM EMP";
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                int empNO = rs.getInt("EMPNO");
                String name = rs.getString("ENAME");
                String job = rs.getString("JOB");
                int mgr = rs. getInt("MGR");
                Date date = rs.getDate("HIREDATE");
                double sal = rs.getDouble("SAL");
                double comm = rs. getInt("COMM");
                int dept = rs.getInt("DEPTNO");
                EmpVO vo = new EmpVO(empNO, name, job, mgr, date, sal, comm, dept);
                list.add(vo); // 생성된 객체를 리스트에 저장
            }
            Common.close(rs);
            Common.close(stmt);
            Common.close(conn);

        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
    public void empInsert() {
        System.out.println("사원정보를 입력 하세요");
        System.out.print("사원번호(4자리) : ");
        int no = sc. nextInt();
        System.out.print("이름 : ");
        String name = sc.next();
        System.out.print("직책 : ");
        String job = sc.next();
        System.out.print("상관 사원번호(4자리) : ");
        int mgr = sc.nextInt();
        System.out.print("입사일 : ");
        String date = sc.next();
        System.out.print("급여 : ");
        int sal = sc.nextInt();
        System.out.print("성과급 : ");
        int comm = sc.nextInt();
        System.out.print("부서번호 : ");
        int dept = sc. nextInt();

        String sql = "INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) " +
                "VALUES(?,?,?,?,?,?,?,?)";
        try{
            conn = Common.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, no);
            pstmt.setString(2, name);
            pstmt.setString(3,job);
            pstmt.setInt(4,mgr);
            pstmt.setString(5,date);
            pstmt.setInt(6,sal);
            pstmt.setInt(7,comm);
            pstmt.setInt(8,dept);
            pstmt.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }
        Common.commit(conn);
        Common.close(pstmt);
        Common.close(conn);
    }
        public void empUpdate() {
         System.out.println("변경할 사원 정보 입력");
         System.out.print("이름 : ");
         String name = sc.next();
         System.out.print("직책  : ");
         String job = sc.next();
         System.out.print("급여 : ");
         int sal = sc.nextInt();
         System.out.print("성과급 : ");
         int comm = sc.nextInt();

         String sql = "UPDATE EMP "
                 +"SET JOB = ?,SAL = ?,COMM = ? WHERE ENAME = ? ";
         try{
             conn = Common.getConnection();
             pstmt = conn.prepareStatement(sql);
             pstmt.setString(1,job);
             pstmt.setInt(2, sal);
             pstmt.setInt(3, comm);
             pstmt.setString(4, name);
             int ret = pstmt.executeUpdate();
             System.out.println("Return" + ret);
         }catch(Exception e){
             e.printStackTrace();
         }
         Common.close(pstmt);
         Common.close(conn);
        }

        public void empDelete() {
        System.out.print("삭제할 이름을 입력하세요 : ");
        String name = sc.next();
        String sql = "DELETE FROM EMP WHERE ENAME = ?";
        try {
            conn = Common.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            int ret = pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        Common.commit(conn);
        Common.close(pstmt);
        Common.close(conn);
        }


    public void empSelectRst(List<EmpVO> list){
        for(EmpVO e : list){
            System.out.print(e.getEmpNO()+ " ");
            System.out.print(e.getName()+ " ");
            System.out.print(e.getJob()+ " ");
            System.out.print(e.getMgr()+" ");
            System.out.print(e.getDate()+ " ");
            System.out.print(e.getSal()+ " ");
            System.out.print(e.getComm()+ " ");
            System.out.print(e.getDepthNO()+" ");
            System.out.println();

        }
    }
}

package com.kh.jdbc.util;

import java.sql.*;

public class Common {
    final static String ORACLE_URL = "jdbc:oracle:thin:@localhost:1521:xe";
    final static String ORACLE_ID = "scott";
    final static String ORACLE_PW = "1234";
    final static String ORACLE_DRV = "oracle.jdbc.OracleDriver";

    public static Connection getConnection()  {
        Connection conn = null;
        // null이면 접속 해제를 하지 말라는 조건을 넣을 수 있다

        try{
            Class.forName(ORACLE_DRV); // 드라이버 로딩
            // 연결 얻기
            conn = DriverManager.getConnection
                    (ORACLE_URL, ORACLE_ID, ORACLE_PW);
            conn.setAutoCommit(false); // 원래 자동 커밋되는데 끄기 위해 FALSE
            System.out.println("오라클 DB 연결 성공");
        }catch (Exception e){
            e.printStackTrace();
            // 에러 리스트를 호출
        }
        return conn;
    }
    //해제 시 고려 해야할 사항
    public static void close(Connection conn) {
        try{
           if(conn != null && !conn.isClosed()){
               conn.close();
               System.out.println("연결 해제 성공");
           }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static void close(Statement stmt) {
        try{
            if(stmt != null && !stmt.isClosed()){
                stmt.close();
                System.out.println("Statement 해제 성공");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static void close(ResultSet rset) {
        try{
            if(rset != null && !rset.isClosed()){
                rset.close();
                System.out.println("ResultSet 해제 성공");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static void commit(Connection conn){
        try{
            if(conn != null && !conn.isClosed()){
                conn.commit();
                System.out.println("커밋 완료");
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    public static void rollback(Connection conn){
        try{
            if(conn != null && !conn.isClosed()){
                conn.rollback();
                System.out.println("롤백 완료");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

자바에서 INSERT하면 오토커밋하기 때문에 디벨로퍼에서 확인이 됨(따로 COMMIT안해줘도 됨)

디벨로퍼에서 COMMIT을 해야 자바에서 보임(안하면 확인 불가)

COMMIT 하기 전에 롤백해야함

<깃허브 공동작업>

먼저 레파지토리 깃허브 만들기

<깃 배쉬>

https://velog.io/@debut12/Github-%EC%97%AC%EB%9F%AC-%EB%AA%85%EA%B3%BC-%ED%94%84%EB%A1%9C%EC%A0%9D%ED%8A%B8-%EA%B3%B5%EC%9C%A0%ED%95%98%EA%B8%B0

package com.kh;

import com.kh.util.Common;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcMain {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            conn = Common.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM EMP";
            rs = stmt.executeQuery(sql);

            System.out.println(rs);
            while(rs.next()){
                System.out.print(rs.getInt("EMPNO")+" ");
                System.out.print(rs.getString("ENAME")+" ");
                System.out.print(rs.getString("JOB")+" ");
                System.out.print(rs. getInt("MGR")+" ");
                System.out.print(rs.getDate("HIREDATE")+" ");
                System.out.print(rs.getDouble("SAL")+" ");
                System.out.print(rs. getInt("COMM")+" ");
                System.out.print(rs.getInt("DEPTNO")+" ");
                System.out.println();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}