[JAVA] JDBC

DB 테이블 작성 및 데이터 넣기

CREATE TABLE PRODUCT(
    PID        INT                PRIMARY KEY,
    NAME    VARCHAR(15)        NOT NULL,
    PRICE    INT,
    CNT        INT                DEFAULT 10
);

INSERT INTO PRODUCT VALUES(1001,'콜라',1100,1);
INSERT INTO PRODUCT VALUES(1002,'사이다',1200,3);
INSERT INTO PRODUCT (PID,NAME,PRICE) VALUES(1003,'환타',1500);
INSERT INTO PRODUCT VALUES(1004,'환타포도',1500,0);

DB 데이터 처리순서

• driver를 메모리에 로드
• conn 객체를 확보

public class JDBCUtil {

    static final String driverName="oracle.jdbc.driver.OracleDriver";
    static final String url="jdbc:oracle:thin:@localhost:1521:xe";
    static final String user="사용자 명";
    static final String passwd="비밀번호";

    public static Connection connect() {

        Connection conn=null;

        try {

            // 1. driver를 메모리에 로드 == 적재
            Class.forName(driverName);

            // 2. conn 객체를 확보
            conn=DriverManager.getConnection(url,user,passwd);

        } catch (ClassNotFoundException | SQLException e) {

            e.printStackTrace();

        }

        return conn;

    }

}

 

• pstmt 로 CRUD 수행

public ArrayList<ProductDTO> selectAll(ProductDTO pDTO){

    ArrayList<ProductDTO> datas=new ArrayList<ProductDTO>();

    conn=JDBCUtil.connect();

    try {

        pstmt=conn.prepareStatement("SELECT * FROM PRODUCT");

        ResultSet rs=pstmt.executeQuery();

        while(rs.next()) {

            ProductDTO data=new ProductDTO();

            data.setPid(rs.getInt("PID"));
            data.setName(rs.getString("NAME"));
            data.setPrice(rs.getInt("PRICE"));
            data.setCnt(rs.getInt("CNT"));

            datas.add(data);

        }

        rs.close();

    } catch (SQLException e) {

        e.printStackTrace();

    } finally {

        JDBCUtil.disconnect(pstmt, conn);

    }

    return datas;

}

 

• 연결 해제

public class JDBCUtil {

    public static void disconnect(PreparedStatement pstmt, Connection conn) {

        try {

            // 4. 연결 해제
            pstmt.close();
            conn.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}