DAO

package org.comstudy21.saram.model;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
 
import javax.swing.plaf.synth.SynthRadioButtonMenuItemUI;
 
import org.comstudy21.dbcp.JdbcUtill;
 
 
public class SaramDAO {
    static String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
    static String user = "comstudy21";
    static String password = "comstudy21";
    
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    PreparedStatement pstmt = null;
    
    String SELECT_ALL = "SELECT * FROM SARAM";
    String SELECT_ID = "SELECT * FROM SARAM WHERE ID=?";
    // "SELECT * FROM SARAM WHERE ID='"+ id +"'";
    String SELECT_NAME = "SELECT * FROM SARAM WHERE NAME=?";
    String INSERT = "INSERT INTO SARAM(ID, NAME, AGE)VALUES(?,?,?)";
    String UPDATE = "UPDATE SARAM SET NAME=?, AGE=? WHERE ID=?";
    String DELETE = "DELETE FROM SARAM WHERE ID=?";
    
    public List<SaramDTO> selectAll() {
        List<SaramDTO> list = new ArrayList();  // 업캐스팅
        
        System.out.println(":::: selectAll() ::::");
        
        conn = JdbcUtill.getConnection(url, user, password);
 
        // 1.Connection -> 2.Statement(쿼리문) 실행 -> 3.ResultSet 반복 -> List에 DTO 저장
        try {
            pstmt = conn.prepareStatement(SELECT_ALL);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                list.add(new SaramDTO(id, name, age));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(conn, pstmt, rs);
        }
        
        return list;
    }
 
    
    public List<SaramDTO> selectOne(SaramDTO dto) {
 
        List<SaramDTO> list = new ArrayList<SaramDTO>();
        
        conn = JdbcUtill.getConnection(url, user, password);
        try {
            pstmt = conn.prepareStatement(SELECT_NAME);
            pstmt.setString(1, dto.getName());
            rs = pstmt.executeQuery();
            while(rs.next()) {
                String id = rs.getString("id");
                int age = rs.getInt("age");
                list.add(new SaramDTO(id, dto.getName(), age));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(conn, pstmt, rs);
        }
        
        return list;
    }
    
    public List<SaramDTO> findById(String id) {
 
        
        List<SaramDTO> list = new ArrayList<SaramDTO>();
        
        conn = JdbcUtill.getConnection(url, user, password);
        try {
            pstmt = conn.prepareStatement(SELECT_ID);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                String name = rs.getString("name");
                int age = rs.getInt("age");
                list.add(new SaramDTO(id, name, age));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(conn, pstmt, rs);
        }
        
        return list;
    }
    
    public void insert(SaramDTO dto) {
    
        
        conn = JdbcUtill.getConnection(url, user, password);
 
        try {
            pstmt = conn.prepareStatement(INSERT);
            pstmt.setString(1, dto.getId());
            pstmt.setString(2, dto.getName());
            pstmt.setInt(3, dto.getAge());
            int cnt = pstmt.executeUpdate();
            if(cnt > 0) {
                System.out.println(">>>>> 입력 성공!");
                conn.commit();
            } else {
                System.out.println(">>>>> 입력 실패!");
                conn.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(conn, pstmt, rs);
        }
        
    }
    
    public void update(SaramDTO dto) {
 
        conn = JdbcUtill.getConnection(url, user, password);
 
        try {
            pstmt = conn.prepareStatement(UPDATE);
            pstmt.setString(1, dto.getName());
            pstmt.setInt(2, dto.getAge());
            pstmt.setString(3, dto.getId());
            int cnt = pstmt.executeUpdate();
            if(cnt > 0) {
                System.out.println(">>>>> 수정 성공!");
                conn.commit();
            } else {
                System.out.println(">>>>> 수정 실패!");
                conn.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(conn, pstmt, rs);
        }
    }
    
    public boolean delete(SaramDTO dto) {
 
        boolean isOk = false;
        conn = JdbcUtill.getConnection(url, user, password);
        try {
            pstmt = conn.prepareStatement(DELETE);
            pstmt.setString(1, dto.getId());
            int cnt = pstmt.executeUpdate();
            if(cnt > 0) {
                System.out.println(">>>>> 삭제 성공!");
                conn.commit();
                isOk = true;
            } else {
                System.out.println(">>>>> 삭제 실패!");
                conn.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtill.close(conn, pstmt, rs);
        }
        
        return isOk;
    }
}