[Java] CRUD(Create Read Update Delete)Test

• CRUD소스코드가 있는 클래스를 DAO라고 합니다

[ex1] "회원리스트"버튼을 누르면 cmd창에 회원목록을 출력하세요
import java.awt.*;
import java.awt.event.*;
class A{
    public static void main(String args[]){
        Button b=new Button("회원리스트");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(b);
        f.setBounds(200,200,300,300);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler());   
    }
}
class ActionHandler implements ActionListener{
    public void actionPerformed(ActionEvent e){
        // cmd창에 회원목록 출력
    }
}
[an]    
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import lombok.*;
class A{
    public static void main(String args[]){
        Button b=new Button("회원리스트");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(b);
        f.setBounds(200,200,300,300);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler());   
    }
}
class ActionHandler implements ActionListener{
    public void actionPerformed(ActionEvent e){
        MemberDAO dao=MemberDAO.getInstance();
        java.util.List<Member> list=dao.selectMemberList();
        for(Member m : list){ 
            System.out.print(m.getId()+"\t");
            System.out.print(m.getPw()+"\t");
            System.out.print(m.getAddr()+"\t");
            System.out.println(m.getTel());
        }
    }
}
/* Member */
@Data
class Member{
    private String id;
    private String pw;
    private String addr;
    private String tel;
}
/* DBUtil */
class DBUtil{
    public static Connection getConnection(){
    Connection conn=null;
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String id = "scott"; String pass = "tiger";
        conn = DriverManager.getConnection(url,id,pass);        
    }catch(SQLException sqle){
        System.out.println("DBConnection:SQLException");
        throw new RuntimeException(sqle.getMessage());
    }catch(ClassNotFoundException cnfe){
        System.out.println("DBConnection:ClassNotFoundException");
        throw new RuntimeException(cnfe.getMessage());
    }

        return conn; 
    }
}
/* MemberDAO */
class MemberDAO {
    private static MemberDAO instance;
    private MemberDAO(){}
    public static MemberDAO getInstance(){
        if(instance==null)
            instance=new MemberDAO();
        return instance;
    }
    public ArrayList<Member> selectMemberList(){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            StringBuffer listQuery = new StringBuffer();
            listQuery.append("select * from member");    
            con = DBUtil.getConnection();

            pstmt = con.prepareStatement(listQuery.toString());
            rs = pstmt.executeQuery();
            ArrayList<Member> v=new ArrayList<Member>();

            while(rs.next()){
                Member member=new Member();
                member.setId(rs.getString("id"));
                member.setPw(rs.getString("pw"));
                member.setAddr(rs.getString("addr"));
                member.setTel(rs.getString("tel"));
                v.add(member);
            }
            return v;
        } catch(SQLException sqle) {
            System.out.println("MemberDAO:selectMemberList:SQLException");
            throw new RuntimeException(sqle.getMessage());            
        } finally {
            try{
            if ( rs != null ){rs.close(); rs=null; }
            if ( pstmt != null ){ pstmt.close(); pstmt=null; }
            if ( con != null ){ con.close(); con=null;    }
            }catch(Exception e){
            throw new RuntimeException(e.getMessage());
            }
        }
    }
}

[ex2] 텍스트에 회원ID를 입력한 뒤 "회원정보보기"버튼을 
누르면 cmd창에 회원정보를 출력하세요

import java.awt.*;
import java.awt.event.*;
class A{
    public static void main(String args[]){
        Label la=new Label("ID : ");
        TextField tf=new TextField(10);
        Button b=new Button("회원정보보기");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(la);f.add(tf);f.add(b);
        f.setBounds(200,200,300,300);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler());   
    }
}
class ActionHandler implements ActionListener{
    public void actionPerformed(ActionEvent e){
        // cmd창에 회원정보 출력
    }
}    
[an]
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import lombok.*;
class A{
    public static void main(String args[]){
        Label la=new Label("ID : ");
        TextField tf=new TextField(10);
        Button b=new Button("회원정보보기");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(la);f.add(tf);f.add(b);
        f.setBounds(200,200,300,300);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler( tf ));   
    }
}
class ActionHandler implements ActionListener{
    TextField tf;
    ActionHandler(TextField tf){ this.tf=tf; }
    public void actionPerformed(ActionEvent e){
        MemberDAO dao=MemberDAO.getInstance();
        Member m=dao.selectMember( tf.getText() );
        System.out.println(m.getId()); 
        System.out.println(m.getPw());
        System.out.println(m.getAddr());
        System.out.println(m.getTel());
    }
}
/* Member */
@Data
class Member{
    private String id;
    private String pw;
    private String addr;
    private String tel;
}
/* DBUtil */
class DBUtil{
    public static Connection getConnection(){
    Connection conn=null;
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String id = "scott"; String pass = "tiger";
        conn = DriverManager.getConnection(url,id,pass);        
    }catch(SQLException sqle){
        System.out.println("DBConnection:SQLException");
        throw new RuntimeException(sqle.getMessage());
    }catch(ClassNotFoundException cnfe){
        System.out.println("DBConnection:ClassNotFoundException");
        throw new RuntimeException(cnfe.getMessage());
    }

        return conn; 
    }
}
/* MemberDAO */
class MemberDAO {
    private static MemberDAO instance;
    private MemberDAO(){}
    public static MemberDAO getInstance(){
        if(instance==null)
            instance=new MemberDAO();
        return instance;
    }
    public Member selectMember(String id){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            StringBuffer listQuery = new StringBuffer();
            listQuery.append("select * from member where id=?");    
            con = DBUtil.getConnection();

            pstmt = con.prepareStatement(listQuery.toString());
            pstmt.setString(1,id);
            rs = pstmt.executeQuery();

            Member member=null;
            if(rs.next()){
                member=new Member();
                member.setId(rs.getString("id"));
                member.setPw(rs.getString("pw"));
                member.setAddr(rs.getString("addr"));
                member.setTel(rs.getString("tel"));
            }
            return member;
        } catch(SQLException sqle) {
            System.out.println("MemberDAO:selectMember:SQLException");
            throw new RuntimeException(sqle.getMessage());            
        } finally {
            try{
            if ( rs != null ){rs.close(); rs=null; }
            if ( pstmt != null ){ pstmt.close(); pstmt=null; }
            if ( con != null ){ con.close(); con=null;    }
            }catch(Exception e){
            throw new RuntimeException(e.getMessage());
            }
        }
    }
}    

[ex3] 텍스트에 회원정보를 입력한 뒤 "가입"버튼을 
누르면 DB에 회원한명을 추가하세요
import java.awt.*;
import java.awt.event.*;
class A{
    public static void main(String args[]){
        TextField tf1=new TextField(10);
        TextField tf2=new TextField(10);
        TextField tf3=new TextField(10);
        TextField tf4=new TextField(10);
        Button b=new Button("가입");
        Frame f=new Frame();
        f.setLayout(new GridLayout(5,1));
        f.add(tf1);f.add(tf2);f.add(tf3);f.add(tf4);f.add(b);
        f.setBounds(200,200,300,200);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler());   
    }
}
class ActionHandler implements ActionListener{
    public void actionPerformed(ActionEvent e){
        // 회원한명 가입
    }
}
[an]
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import lombok.*;
class A{
    public static void main(String args[]){
        TextField tf1=new TextField(10);
        TextField tf2=new TextField(10);
        TextField tf3=new TextField(10);
        TextField tf4=new TextField(10);
        Button b=new Button("가입");
        Frame f=new Frame();
        f.setLayout(new GridLayout(5,1));
        f.add(tf1);f.add(tf2);f.add(tf3);f.add(tf4);f.add(b);
        f.setBounds(200,200,300,200);
        f.setVisible(true);
        // 이벤트등록
        TextField[] array={tf1,tf2,tf3,tf4};
        b.addActionListener(new ActionHandler( array,f ));   
    }
}
class ActionHandler implements ActionListener{
    TextField[] array;  Frame frame;
    ActionHandler(TextField[] array, Frame frame){ this.array=array; this.frame=frame;}
    public void actionPerformed(ActionEvent e){
        frame.setVisible(false); frame.dispose();
        MemberDAO dao=MemberDAO.getInstance();
        Member member=new Member();
        member.setId(array[0].getText());
        member.setPw(array[1].getText());
        member.setAddr(array[2].getText());
        member.setTel(array[3].getText());
        dao.insertMember(  member  );    
    }
}
/* Member */
@Data
class Member{
    private String id;
    private String pw;
    private String addr;
    private String tel;
}
/* DBUtil */
class DBUtil{
    public static Connection getConnection(){
    Connection conn=null;
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String id = "scott"; String pass = "tiger";
        conn = DriverManager.getConnection(url,id,pass);        
    }catch(SQLException sqle){
        System.out.println("DBConnection:SQLException");
        throw new RuntimeException(sqle.getMessage());
    }catch(ClassNotFoundException cnfe){
        System.out.println("DBConnection:ClassNotFoundException");
        throw new RuntimeException(cnfe.getMessage());
    }

        return conn; 
    }
}
/* MemberDAO */
class MemberDAO {
    private static MemberDAO instance;
    private MemberDAO(){}
    public static MemberDAO getInstance(){
        if(instance==null)
            instance=new MemberDAO();
        return instance;
    }
    public void insertMember(Member bean){
        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            StringBuffer listQuery = new StringBuffer();
            listQuery.append("insert into member values(?,?,?,?)");    
            con = DBUtil.getConnection();

            pstmt = con.prepareStatement(listQuery.toString());
            pstmt.setString(1,bean.getId());
            pstmt.setString(2,bean.getPw());
            pstmt.setString(3,bean.getAddr());
            pstmt.setString(4,bean.getTel());
            pstmt.executeUpdate();
        } catch(SQLException sqle) {
            System.out.println("MemberDAO:insertMember:SQLException");
            throw new RuntimeException(sqle.getMessage());            
        } finally {
            try{
            if ( pstmt != null ){ pstmt.close(); pstmt=null; }
            if ( con != null ){ con.close(); con=null;    }
            }catch(Exception e){
            throw new RuntimeException(e.getMessage());
            }
        }
    }
}

[ex4] 회원ID를 선택한뒤 "삭제"버튼을 
누르면 DB에 회원한명을 삭제하세요

import java.awt.*;
import java.awt.event.*;
class A{
    public static void main(String args[]){
        List lst = new List(3);
        // 아래 문장은 Member테이블에 존재하는 ID목록으로 item을 수정합니다.
         lst.add("Mercury");
         lst.add("Venus");
         lst.add("Earth");
         lst.add("JavaSoft");
         lst.add("Mars");

        Button b=new Button("삭제");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(lst);f.add(b);
        f.setBounds(200,200,300,200);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler());   
    }
}
class ActionHandler implements ActionListener{
    public void actionPerformed(ActionEvent e){
        // 회원한명 삭제
    }
}    
[an]

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import lombok.*;
class A{
    public static void main(String args[]){
        List lst = new List(3);
        // 아래 문장은 Member테이블에 존재하는 ID목록으로 item을 수정합니다.
         lst.add("aa");
         lst.add("1");
         lst.add("11");
         lst.add("7");

        Button b=new Button("삭제");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(lst);f.add(b);
        f.setBounds(200,200,300,200);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler(lst));   
    }
}
class ActionHandler implements ActionListener{
    List lst;
    ActionHandler(List lst){ this.lst=lst; }
    public void actionPerformed(ActionEvent e){
        MemberDAO dao=MemberDAO.getInstance();
        dao.deleteMember( lst.getSelectedItem() );
    }
}    
/* Member */
@Data
class Member{
    private String id;
    private String pw;
    private String addr;
    private String tel;
}
/* DBUtil */
class DBUtil{
    public static Connection getConnection(){
    Connection conn=null;
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String id = "scott"; String pass = "tiger";
        conn = DriverManager.getConnection(url,id,pass);        
    }catch(SQLException sqle){
        System.out.println("DBConnection:SQLException");
        throw new RuntimeException(sqle.getMessage());
    }catch(ClassNotFoundException cnfe){
        System.out.println("DBConnection:ClassNotFoundException");
        throw new RuntimeException(cnfe.getMessage());
    }

        return conn; 
    }
}
/* MemberDAO */
class MemberDAO {
    private static MemberDAO instance;
    private MemberDAO(){}
    public static MemberDAO getInstance(){
        if(instance==null)
            instance=new MemberDAO();
        return instance;
    }
    public void deleteMember(String id){
        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            StringBuffer listQuery = new StringBuffer();
            listQuery.append("delete from member where id=?");    
            con = DBUtil.getConnection();

            pstmt = con.prepareStatement(listQuery.toString());
            pstmt.setString(1,id);
            pstmt.executeUpdate();
        } catch(SQLException sqle) {
            System.out.println("MemberDAO:deleteMember:SQLException");
            throw new RuntimeException(sqle.getMessage());            
        } finally {
            try{
            if ( pstmt != null ){ pstmt.close(); pstmt=null; }
            if ( con != null ){ con.close(); con=null;    }
            }catch(Exception e){
            throw new RuntimeException(e.getMessage());
            }
        }
    }
}

[ex5] 위 소스코드에서 시작하자마자 DB연동해서 회원리스트를 List컴포넌트에 출력하세요.
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import lombok.*;
class A{
    public static void main(String args[]){
        List lst = new List(3);
        /*
        DB연동해서 회원ID 모두를 List컴포넌트에 출력합니다.
         lst.add("aa");
         lst.add("1");
         lst.add("11");
         lst.add("7");
        */

        Button b=new Button("삭제");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(lst);f.add(b);
        f.setBounds(200,200,300,200);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler(lst));   
    }
}
class ActionHandler implements ActionListener{
    List lst;
    ActionHandler(List lst){ this.lst=lst; }
    public void actionPerformed(ActionEvent e){
        MemberDAO dao=MemberDAO.getInstance();
        dao.deleteMember( lst.getSelectedItem() );
    }
}    
/* Member */
@Data
class Member{
    private String id;
    private String pw;
    private String addr;
    private String tel;
}
/* DBUtil */
class DBUtil{
    public static Connection getConnection(){
    Connection conn=null;
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String id = "scott"; String pass = "tiger";
        conn = DriverManager.getConnection(url,id,pass);        
    }catch(SQLException sqle){
        System.out.println("DBConnection:SQLException");
        throw new RuntimeException(sqle.getMessage());
    }catch(ClassNotFoundException cnfe){
        System.out.println("DBConnection:ClassNotFoundException");
        throw new RuntimeException(cnfe.getMessage());
    }

        return conn; 
    }
}
/* MemberDAO */
class MemberDAO {
    private static MemberDAO instance;
    private MemberDAO(){}
    public static MemberDAO getInstance(){
        if(instance==null)
            instance=new MemberDAO();
        return instance;
    }
    public void deleteMember(String id){
        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            StringBuffer listQuery = new StringBuffer();
            listQuery.append("delete from member where id=?");    
            con = DBUtil.getConnection();

            pstmt = con.prepareStatement(listQuery.toString());
            pstmt.setString(1,id);
            pstmt.executeUpdate();
        } catch(SQLException sqle) {
            System.out.println("MemberDAO:deleteMember:SQLException");
            throw new RuntimeException(sqle.getMessage());            
        } finally {
            try{
            if ( pstmt != null ){ pstmt.close(); pstmt=null; }
            if ( con != null ){ con.close(); con=null;    }
            }catch(Exception e){
            throw new RuntimeException(e.getMessage());
            }
        }
    }
}
[an]
import java.awt.*;
import java.util.*;
import java.awt.event.*;
import java.sql.*;
import lombok.*;
class A{
    public static void main(String args[]){
        java.awt.List lst = new java.awt.List(15);

         MemberDAO dao=MemberDAO.getInstance();
        java.util.List<Member> dblist=dao.selectMemberList();
        for(Member m : dblist) lst.add(m.getId());

        Button b=new Button("삭제");
        Frame f=new Frame();
        f.setLayout(new FlowLayout());
        f.add(lst);f.add(b);
        f.setBounds(200,200,300,800);
        f.setVisible(true);
        // 이벤트등록
        b.addActionListener(new ActionHandler(lst));   
    }
}
class ActionHandler implements ActionListener{
    java.awt.List lst;
    ActionHandler(java.awt.List lst){ this.lst=lst; }
    public void actionPerformed(ActionEvent e){
        MemberDAO dao=MemberDAO.getInstance();
        dao.deleteMember( lst.getSelectedItem() );
        java.util.List<Member> dblist=dao.selectMemberList();
        lst.removeAll();
        for(Member m : dblist) lst.add(m.getId());
    }
}    
/* Member */
@Data
class Member{
    private String id;
    private String pw;
    private String addr;
    private String tel;
}
/* DBUtil */
class DBUtil{
    public static Connection getConnection(){
    Connection conn=null;
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String id = "scott"; String pass = "tiger";
        conn = DriverManager.getConnection(url,id,pass);        
    }catch(SQLException sqle){
        System.out.println("DBConnection:SQLException");
        throw new RuntimeException(sqle.getMessage());
    }catch(ClassNotFoundException cnfe){
        System.out.println("DBConnection:ClassNotFoundException");
        throw new RuntimeException(cnfe.getMessage());
    }

        return conn; 
    }
}
/* MemberDAO */
class MemberDAO {
    private static MemberDAO instance;
    private MemberDAO(){}
    public static MemberDAO getInstance(){
        if(instance==null)
            instance=new MemberDAO();
        return instance;
    }
    public void deleteMember(String id){
        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            StringBuffer listQuery = new StringBuffer();
            listQuery.append("delete from member where id=?");    
            con = DBUtil.getConnection();

            pstmt = con.prepareStatement(listQuery.toString());
            pstmt.setString(1,id);
            pstmt.executeUpdate();
        } catch(SQLException sqle) {
            System.out.println("MemberDAO:deleteMember:SQLException");
            throw new RuntimeException(sqle.getMessage());            
        } finally {
            try{
            if ( pstmt != null ){ pstmt.close(); pstmt=null; }
            if ( con != null ){ con.close(); con=null;    }
            }catch(Exception e){
            throw new RuntimeException(e.getMessage());
            }
        }
    }
    public java.util.List<Member> selectMemberList(){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            StringBuffer listQuery = new StringBuffer();
            listQuery.append("select * from member");    
            con = DBUtil.getConnection();

            pstmt = con.prepareStatement(listQuery.toString());
            rs = pstmt.executeQuery();
            java.util.List<Member> v=new ArrayList<Member>();

            while(rs.next()){
                Member member=new Member();
                member.setId(rs.getString("id"));
                member.setPw(rs.getString("pw"));
                member.setAddr(rs.getString("addr"));
                member.setTel(rs.getString("tel"));
                v.add(member);
            }
            return v;
        } catch(SQLException sqle) {
            System.out.println("MemberDAO:selectMemberList:SQLException");
            throw new RuntimeException(sqle.getMessage());            
        } finally {
            try{
            if ( rs != null ){rs.close(); rs=null; }
            if ( pstmt != null ){ pstmt.close(); pstmt=null; }
            if ( con != null ){ con.close(); con=null;    }
            }catch(Exception e){
            throw new RuntimeException(e.getMessage());
            }
        }
    }
}