국비 26일차 JDBC(delete, update, 클래스 연동)

2021.02.09 국비 교육 26일차

UPDATE

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Statement_update {

    public static void main(String[] args) {

        String driver ="oracle.jdbc.driver.OracleDriver"; 
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String userid = "scott"; 
        String passwd = "tiger";

        Connection con = null;

        Statement stmt = null;

        try {
            Class.forName(driver);

            con = DriverManager.getConnection(url, userid, passwd);
            String dname = "제조";
            String deptno = "경기";


            String sql = "update dept set dname = '" + dname + "', loc= '" + deptno +"' where deptno =99";
            System.out.println(sql);
            stmt = con.createStatement();

            int num= stmt.executeUpdate(sql);
            System.out.println(num);



        } catch (SQLException e) {
            e.printStackTrace();
        } catch(ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {

                if(stmt !=null)stmt.close();
                if(con != null)con.close();
            } catch (SQLException e2) {
                e2.printStackTrace();
            }
        }



    }

}

DELETE

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Statement_delete {

    public static void main(String[] args) {

        String driver ="oracle.jdbc.driver.OracleDriver"; 
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String userid = "scott"; 
        String passwd = "tiger";

        Connection con = null;

        Statement stmt = null;

        try {
            Class.forName(driver);

            con = DriverManager.getConnection(url, userid, passwd);

            String sql = "delete from dept where deptno =99";
            System.out.println(sql);
            stmt = con.createStatement();

            int num= stmt.executeUpdate(sql);
            System.out.println(num);



        } catch (SQLException e) {
            e.printStackTrace();
        } catch(ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {

                if(stmt !=null)stmt.close();
                if(con != null)con.close();
            } catch (SQLException e2) {
                e2.printStackTrace();
            }
        }



    }

}

JDBC 클래스 연동

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class DeptDAO { // DB연동 역할 - select, insert, delete, update - 함수별로 구현
    // 1. 4가지 정보 - 멤버변수로 선언
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String userid = "scott";
    String passwd = "tiger";

    public DeptDAO() {
        super();

        try {
            Class.forName(driver);
            System.out.println("드라이버 로딩 성공");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public void selectAllDept() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(url, userid, passwd);
            System.out.println(con);
            String sql = "select deptno x, dname, loc from dept";

            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                int deptno = rs.getInt("x");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                System.out.println(deptno + " " + dname + " " + loc);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } // try-catch 끝

    } // method 끝

    public int insert(DeptDTO dto) {
        int num = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DriverManager.getConnection(url, userid, passwd);
            String sql = "insert into dept(deptno, dname, loc) values(?,?,?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, dto.getDeptno());
            pstmt.setString(2, dto.getDname());
            pstmt.setString(3, dto.getLoc());

            num = pstmt.executeUpdate();

            System.out.println(num);
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return num;
    }

    public DeptDTO selectByDeptno(int deptno) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        DeptDTO DTO = null;
        try {
            con = DriverManager.getConnection(url, userid, passwd);
            System.out.println(con);
            String sql = "select * from dept where deptno = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, deptno);
            rs = pstmt.executeQuery();

            while(rs.next()) {    
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                DTO = new DeptDTO(deptno, dname, loc);
            }



        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } // try-catch 끝

        return DTO;
    }

    //main에 전체 데이터를 전달 해 주는 함수
    public ArrayList<DeptDTO> getAllData(){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<DeptDTO> list = new ArrayList<DeptDTO>();
        try {
            con = DriverManager.getConnection(url, userid, passwd);

            DeptDTO DTO = null;
            String sql = "select * from dept";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while(rs.next()) {    
                int deptno = rs.getInt("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                DTO = new DeptDTO(deptno, dname, loc);
                list.add(DTO);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } // try-catch 끝

        return list;
    }


}

package com.test;

public class DeptDTO { //데이터 수신

    int deptno;
    String dname;
    String loc;
    public DeptDTO() {
        super();
    }

    public DeptDTO(int deptno, String dname, String loc) {
        super();
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }

    @Override
    public String toString() {
        return "DeptDTO [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }



}

package com.test;

import java.sql.SQLException;
import java.util.ArrayList;

public class JDBCDAOTest {

    public static void main(String[] args) throws SQLException {
        //dao클래스의 필요한 함수 호출 db연동
        DeptDAO dao = new DeptDAO();
//        dao.selectAllDept();


//        DeptDTO dto = new DeptDTO(99, "개발", "서울");
//        System.out.println(dto);
//        dao.insert(dto);

//        System.out.println(dao.selectByDeptno(99));

        ArrayList<DeptDTO> list = dao.getAllData();

        for(DeptDTO a : list) {
            System.out.println(a);
        }
    }

}