import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class MemberDAO {

    private String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private String uid = "c##davidyoon";
    private String upw = "DBPASS";

    public MemberDAO() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }


    public ArrayList<MemberDTO> memberSelect() {
        ArrayList<MemberDTO> dtos = new ArrayList<MemberDTO>();

        Connection connection = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(url, uid, upw);
            stmt = connection.createStatement();
            resultSet = stmt.executeQuery("select * from member");

            while(resultSet.next()) {
                String name = resultSet.getString("name");
                String id = resultSet.getString("id");
                String pw = resultSet.getString("pw");
                String phone1 = resultSet.getString("phone1");
                String phone2 = resultSet.getString("phone2");
                String phone3 = resultSet.getString("phone3");
                String gender = resultSet.getString("gender");

                MemberDTO dto = new MemberDTO(name, id, pw, phone1, phone2, phone3, gender);

                dtos.add(dto);
            }

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

        return dtos;

    }
}

public class MemberDTO {
    private String name;
    private String id;
    private String pw;
    private String phone1;
    private String phone2;
    private String phone3;
    private String gender;




    public MemberDTO(String name, String id, String pw, String phone1, String phone2, String phone3, String gender) {
        super();
        this.name = name;
        this.id = id;
        this.pw = pw;
        this.phone1 = phone1;
        this.phone2 = phone2;
        this.phone3 = phone3;
        this.gender = gender;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPw() {
        return pw;
    }
    public void setPw(String pw) {
        this.pw = pw;
    }
    public String getPhone1() {
        return phone1;
    }
    public void setPhone1(String phone1) {
        this.phone1 = phone1;
    }
    public String getPhone2() {
        return phone2;
    }
    public void setPhone2(String phone2) {
        this.phone2 = phone2;
    }
    public String getPhone3() {
        return phone3;
    }
    public void setPhone3(String phone3) {
        this.phone3 = phone3;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
}

    public void memberUpdate(Member member) {
        try {
            connection = DriverManager.getConnection(url, uid, upw);
            String query = "insert into memberforpre (id, pw, name, phone) values(?, ?, ?, ?)";
            int n;
            pstmt = connection.prepareStatement(query);

            pstmt.setString(1, member.getId());
            pstmt.setString(2, member.getPw());
            pstmt.setString(3, member.getName());
            pstmt.setString(4, member.getPhone());
            n = pstmt.executeUpdate();

            if (n == 1) {
                System.out.println("insert success");
            }else {
                System.out.println("insert fail");
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if(connection != null) connection.close();
                if(pstmt != null) pstmt.close();
            } catch (Exception e) {
                // TODO: handle exception
            }
        }

    }

    <Resource
        auth="Container"
        driverClassName="oracle.jdbc.driver.OracleDriver"
        url="jdbc:oracle:thin:@localhost:1532:xe"
        username="c##davidyoon"
        password="xxxx"
        name="jdbc/Oracle18g"
        type="javax.sql.DataSource"
        maxActive="50"  //풀 갯수
        maxWait="1000" // 다음 풀(51번째) 만들때 대기 시간
    />