자바 오라클 연동 데이터 삽입,수정,조회,삭제-2

안녕하세요 엘체프 GG 입니다.

데이터를 수정,조건조회,전체조회,삭제 해보겠습니다.

1에서 구조가 잡혀 있어서 MemberDAOServiceImpl 만 추가 적으로 소스 만들고 test페이지만 만들면 됩니다.

-MemberDAOServiceImpl 

package kr.co.js.jdbc.service;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import kr.co.js.jdbc.domain.MemberVO;
 
/**
 * DAO 구현 클래스(concrete class)
 * 
 * @author Administrator
 *
 */
public class MemberDAOServiceImpl implements MemberDAOService {
    // DB 연결정보
    private static final String oracleDriver = "oracle.jdbc.OracleDriver";
    private static final String oracleURL = "jdbc:oracle:thin:@localhost:1521:xe";
    private static final String oracleID = "greenjava";
    private static final String oraclePW = "greenjava";
 
    // 싱글턴(singleton) 패턴
 
    private static MemberDAOServiceImpl instance = null;
 
    private MemberDAOServiceImpl() {
 
    }
 
    public static final MemberDAOServiceImpl getInstance() {
 
        if (instance == null) {
            instance = new MemberDAOServiceImpl();
        }
 
        return instance;
    }
 
    /**
     * DB 연결(connect)
     * 
     * @return DB연결 객체
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(oracleDriver);
            try {
                conn = DriverManager.getConnection(oracleURL, oracleID, oraclePW);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            System.out.println("DAO getConnection : " + oracleDriver);
            e.printStackTrace();
        }
        return conn;
 
    }
 
    /**
     * DB연결 자원 반납
     * 
     * @param conn DB연결 객체
     * @param pstmt SQL 해석 객체
     * @param rs 결과셋 객체
     */
    public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();// 유효성검사 후 자원 반납
            if (pstmt != null)
                pstmt.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            System.out.println("DB close");
            e.printStackTrace();
        }
 
    }
 
    //회원정보 삽입(가입,생성)
    @Override
    public void insertMember(MemberVO memberVo) {
        Connection conn = null;// 연결 객체
        PreparedStatement pstmt = null;// SQL 해석 객체
        String sql = "INSERT INTO MEMBER VALUES (?,?,?,?,sysdate)";
 
        conn = this.getConnection(); // DB연결
        try {
            pstmt = conn.prepareStatement(sql); // SQL 해석
            pstmt.setString(1, memberVo.getMemberId());
            pstmt.setString(2, memberVo.getMemberName());
            pstmt.setString(3, memberVo.getMemberPw());
            pstmt.setString(4, memberVo.getMemberAddr());
            // pstmt.setDate(5, memberVo.getMemberJoindate());
 
            if (pstmt.executeUpdate() == 1) {
                System.out.println("성공적으로 회원 정보를 저장하였습니다.");
            } else {
                System.out.println("회원정보 저장에 실패 했습니다.");
            }
 
        } catch (SQLException e) {
            System.out.println("insertMember SQLE : ");
            e.printStackTrace();
        } finally {
 
            this.closeAll(conn, pstmt, null);
        }
    }
 
    //전체회원 정보 조회(검색)
    @Override
    public List<MemberVO> getAllMembers() throws Exception {
        
        List<MemberVO> members = new ArrayList<MemberVO>();//리턴값을 만들어줘야지~!
        
        Connection conn = this.getConnection();// 연결 객체
        PreparedStatement pstmt = null;// SQL 해석 객체
        String sql = "SELECT * FROM member";
        ResultSet rs = null;// row(결과) 집합(set)
        
//        MemberVO member = new MemberVO();//주의 같은 레코드 반복 인쇄! =>오류!
        MemberVO member =null; 
        try {
 
            pstmt = conn.prepareStatement(sql); // SQL 해석
            rs = pstmt.executeQuery();
 
            while (rs.next()) {
 
                member = new MemberVO();//대처법 : 레코드 (record,row)
                member.setMemberId(rs.getString("member_id"));
                member.setMemberName(rs.getString("member_name"));
                member.setMemberPw(rs.getString("member_pw"));
                member.setMemberAddr(rs.getString("member_addr"));
                member.setMemberJoindate(rs.getDate("member_joindate"));
                
                members.add(member);
 
            }
 
            // SQL 실행
 
        } catch (Exception e) {
            System.out.println("get All Member E : ");
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return members;
        
 
    }
 
    //개별 회원 정보 조회(검색)
    @Override
    public MemberVO getMember(String memberId) throws Exception {
        Connection conn = this.getConnection();// 연결 객체
        PreparedStatement pstmt = null;// SQL 해석 객체
        String sql = "SELECT * FROM member WHERE member_id=?";
        ResultSet rs = null;// row(결과) 집합(set)
        MemberVO member = new MemberVO();
 
        try {
 
            pstmt = conn.prepareStatement(sql); // SQL 해석
            pstmt.setString(1, memberId);
 
            rs = pstmt.executeQuery();
 
            while (rs.next()) {
 
                member.setMemberId(rs.getString("member_id"));
                member.setMemberName(rs.getString("member_name"));
                member.setMemberPw(rs.getString("member_pw"));
                member.setMemberAddr(rs.getString("member_addr"));
                member.setMemberJoindate(rs.getDate("member_joindate"));
 
            }
 
        } catch (Exception e) {
            System.out.println("select Member E : ");
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return member;
 
    }
    
    //개별 회원 정보 수정(갱신,변경)
    @Override
    public void updateMember(MemberVO memberVo) throws Exception {
 
        Connection conn = this.getConnection();// 연결 객체
        PreparedStatement pstmt = null;// SQL 해석 객체
        // Statement 사용시 SQL
        /*
         * String sql = "UPDATE member SET member_pw = '"+
         * memberVO.getMemberPw() +"'," +
         * "member_addr = '"+memberVO.getMemberAddr()+"' " +
         * "WHERE member_id = '"+memberVO.getMemberId()+"'";
         */
 
        // PreparedStatement 사용시 SQL
        /*
         * String sql = "UPDATE member SET member_pw=?," +
         * "                  member_addr=? " + "      WHERE member_id=?";
         */
 
        StringBuilder sql = new StringBuilder();
        /*
         * sql.append("UPDATE member SET "); sql.append("      member_pw=?,");
         * sql.append("      member_addr=? "); sql.append("WHERE member_id=?");
         */
 
        sql.append("UPDATE member SET ")
            .append("       member_pw=?,")
            .append("       member_addr=?")
            .append(" WHERE member_id=?");
 
        try {
            pstmt = conn.prepareStatement(sql.toString());
 
            // SQL 인자처리
            pstmt.setString(1, memberVo.getMemberPw());
            pstmt.setString(2, memberVo.getMemberAddr());
            pstmt.setString(3, memberVo.getMemberId());
 
            // SQL 실행
 
            if (pstmt.executeUpdate() == 1) {
                System.out.println("회원정보 수정에 성공하였습니다.");
 
            } else {
                System.out.println("회원정보 수정에 실패하였습니다.");
            }
        } catch (Exception e) {
            System.out.println("uptateMember E : ");
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, null);
        }
 
    }
 
    //개별 회원 정보 삭제
    @Override
    public void deleteMember(String memberId) throws Exception {
        
        Connection conn = this.getConnection();// 연결 객체
        PreparedStatement pstmt = null;// SQL 해석 객체
        String sql = "DELETE member WHERE member_id=?";
 
        try {
 
            pstmt = conn.prepareStatement(sql); // SQL 해석
            pstmt.setString(1, memberId);
            if(pstmt.executeUpdate()==1){
                System.out.println("회원정보를 성공적으로 삭제했습니다.");
            }else{
                System.out.println("회원정보 삭제에 실패했습니다.");
            }
            
        } catch (Exception e) {
            System.out.println("delete Member E : ");
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, null);
        }
        return;
    }
 
}

-selectTestPage

/**
 * 
 */
package kr.co.js.jdbc.test;
 
import kr.co.js.jdbc.domain.MemberVO;
import kr.co.js.jdbc.service.MemberDAOService;
import kr.co.js.jdbc.service.MemberDAOServiceImpl;
 
/**
 * select Unit test
 * @author Administrator
 *
 */
public class selectTest {
 
    /**
     * @param args
     * @throws Exception 
     */
    public static void main(String[] args) throws Exception {
        //싱글톤 객체 호출
        MemberDAOService dao = MemberDAOServiceImpl.getInstance();
        MemberVO member = dao.getMember("n123");
        
        System.out.println("member : " + member);        
 
    }
}

-selectAllTestPage

/**
 * 
 */
package kr.co.js.jdbc.test;
 
import java.util.List;
 
import kr.co.js.jdbc.domain.MemberVO;
import kr.co.js.jdbc.service.MemberDAOService;
import kr.co.js.jdbc.service.MemberDAOServiceImpl;
 
/**
 * selectAll Unit test
 * @author Administrator
 *
 */
public class allSelectTest {
 
    /**
     * @param args
     * @throws Exception 
     */
    public static void main(String[] args) throws Exception {
        //싱글톤 객체 호출
        MemberDAOService dao = MemberDAOServiceImpl.getInstance();
        List<MemberVO> members = dao.getAllMembers();
        
        for(MemberVO member : members){
        System.out.println("member : " + member);
        }        
 
    }
}

-updateTestPage

/**
 * 
 */
package kr.co.js.jdbc.test;
 
import kr.co.js.jdbc.domain.MemberVO;
import kr.co.js.jdbc.service.MemberDAOService;
import kr.co.js.jdbc.service.MemberDAOServiceImpl;
 
/**
 * update Unit test
 * @author Administrator
 *
 */
public class updateTest {
 
    /**
     * @param args
     * @throws Exception 
     */
    public static void main(String[] args) throws Exception {
        //싱글톤 객체 호출
        MemberDAOService dao = MemberDAOServiceImpl.getInstance();
        MemberVO member = new MemberVO();
        
        member.setMemberId("n123");
        member.setMemberPw("1");
        member.setMemberAddr("1");
        
 
        dao.updateMember(member);
    }
 
}

-deleteTestPage

package kr.co.js.jdbc.test;
 
import kr.co.js.jdbc.service.MemberDAOService;
import kr.co.js.jdbc.service.MemberDAOServiceImpl;
 
/**
 * delete Unit test
 * @author Administrator
 *
 */
public class deleteTest {
 
    /**
     * @param args
     * @throws Exception 
     */
    public static void main(String[] args) throws Exception {
        //싱글톤 객체 호출
        MemberDAOService dao = MemberDAOServiceImpl.getInstance();    
        
        dao.deleteMember("n123");        
 
    }
}

오늘 하루 수고 하셧습니다.

감사합니다.