[Java] DB연동

oracleDB에 로그인 : class DBUtil

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

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;
    }
}

로그인한 DB에 query문으로 emp테이블의 값들을 호출

class A {
    public static void main(String args[]) throws Exception {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select empno, ename from emp");
        rs.next(); // next() : 테이블에 다음 줄이 있으면 true를 리턴, 없으면 false를 리턴
        System.out.println(rs.getString(1)); //emp 테이블의 empno의 1번을 출력
        System.out.println(rs.getString(2)); // ""         ename의 1번을 출력
    }
}

호출문장을 while문으로 바꾸기

class A {
    public static void main(String args[]) throws Exception {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select empno, ename from emp");
        while(rs.next()) {
            System.out.println(rs.getString("empno")); //**칼럼명을 입력하는 편이 더 가독성 좋다.**
            System.out.println(rs.getString(2));
        }//while문안에서 다음 값이 있으면 true 없으면 false를 리턴하기 때문에 
    }  //다음값이 없을때까지 Sysout문을 실행한다.
}

args[0]을 이용해서 테이블값 출력. varchar타입 출력하기

class A {
    public static void main(String args[]) throws Exception {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select empno, ename from emp where ename='"+args[0]+"'");
        while(rs.next()) {System.out.println(rs.getString(1));

        }  **//""쌍따옴표 안에 ''외따옴표 넣기**
    }   **// 테이블 값이 varchar타입 일경우 ''(외따옴표) 필수!**
}

‘ ’외따옴표 스킵 : 쿼리문 담당하는 클래스 → PreparedStatement이용

PreparedStatement 변수1 = new Connection().preparedStatement(”쿼리문=?”)

변수1.setString(1, 테이블열 이름);

ResultSet 변수2 = 변수1.executeQuery(); —> 호출

class A {
    public static void main(String args[]) throws Exception {
        Connection conn = DBUtil.getConnection();
        **PreparedStatement pstmt = conn.prepareStatement("select empno from emp where ename=?");
        pstmt.setString(1, args[0]);**  **//1번 물음표가 args[0]이다 라고 세팅**
        ResultSet rs = pstmt.executeQuery();
        while (rs.next())
            System.out.println(rs.getString(1));
    }
}

VO(ValueObject), TO(TransperObject), Bean : 값을 가지고 있는 클래스

• 테이블의 값들을 객체화 해야하기때문에 그것을 CRUD클래스에 담는다
• set() : 쓰기 메서드
• get() : 읽기 메서드

class MemberBean {
    private String id, pw, addr, tel;  //테이블의 칼럼을 멤버변수로서 선언
// set() : 쓰기 메서드
    public void setId(String id) {this.id = id;}
    public void setPw(String pw) {this.pw = pw;}
    public void setAddr(String id) {this.addr = addr;}
    public void setTel(String tel) {this.tel = tel;}
//get() : 읽기 메서드
    public String getId() {return id;}
    public String getPw() {return pw;}
    public String getAddr() {return addr;}
    public String getTel() {return tel;}
}

CRUD 예시

import java.sql.*;
import java.util.*;

public 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 query = new StringBuffer();
            query.append("delete from member where id=?");

            con = DBUtil.getConnection();
            pstmt = con.prepareStatement(query.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 void updateMember(Member member) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            StringBuffer query = new StringBuffer();
            query.append("update member set ");
            query.append("pw=?,addr=?,tel=? where id=?");

            con = DBUtil.getConnection();
            pstmt = con.prepareStatement(query.toString());
            pstmt.setString(1, member.getPw());
            pstmt.setString(2, member.getAddr());
            pstmt.setString(3, member.getTel());
            pstmt.setString(4, member.getId());
            pstmt.executeUpdate();
        } catch (SQLException sqle) {
            System.out.println("MemberDAO:updateMember: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 void insertMember(Member member) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            StringBuffer insertQuery = new StringBuffer();
            insertQuery.append("insert into member values");
            insertQuery.append("(?, ?, ?, ?)");
            con = DBUtil.getConnection();
            pstmt = con.prepareStatement(insertQuery.toString());
            pstmt.setString(1, member.getId());
            pstmt.setString(2, member.getPw());
            pstmt.setString(3, member.getAddr());
            pstmt.setString(4, member.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());
            }
        }
    }

    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());
            }
        }
    }

    public Member selectMember(String id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {

            StringBuffer selectQuery = new StringBuffer();
            selectQuery.append("select * from member where id=?");
            con = DBUtil.getConnection();
            pstmt = con.prepareStatement(selectQuery.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) {
            throw new RuntimeException(sqle.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                throw new RuntimeException(e.getMessage());
            }
        }
    }

}