DB연동(oracle DB, Eclipse)

0. download oracle Databse11g Release 2 Express Edition

-cmd창에 sqlplus 입력하고 system account로 login

-유저생성: create user (ID) identified by (PW);

-권한부여: grant connect, reosurce to (ID);

-exit로 나간뒤 sqlplus (ID)/(PW) 로 login 

-create table member( id varchar2(20), pw varchar2(20), name varchar2(20), email varchar2(80));

-insert into member (id, pw, name, email) values('jusung','1234','jusung','jusung@gmail.com');

-select * from member;

-commit; //data가 실제로 반영되도록하기위해

1. C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib

//위 link에서 ojdbc6_g를 복사한다

2. C:\Program Files\Java\jre1.8.0_221\lib\ext

//복사한 ojdbc6_g를 위 link에 붙여넣기한다

 

DB연동 순서

1. JDBC driver loading: Class.forName(oracle.jdbc.driver.OracleDriver);

2. DB 연결 (Connection 객체 생성): DriverManager.getConnection()

3. SQL문 실행(Statement 개게 생성): Connection.createStatement();

Statement 관련 method

-executeQuery() : 쿼리문을 사용할때 결과값이(ResultSet) 생긴다(select).

-executeUpdate() : 테이블의 내용을 수정,삭제,삽입할때 사용(insert,update,delete)

4. DB 열결해제: ResultSet객체로부터 데이터 추출, Resultset Close

 

eclipse에서 New\Dynamic Web Project\jsp file생성

 

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*"%>
<%
    Connection conn = null;
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String uid = "semo";
    String upw = "1025";
    Boolean connect = false;
    
    try {
        Class.forName(driver);
        System.out.println("drivier loading success");
        conn = DriverManager.getConnection(url, uid, upw);
        System.out.println("successfuly connected");
        connect = true;
        conn.close();
    } catch (Exception e) {
        connect = false;
        e.printStackTrace();
    }
%>
<%
if(connect==true){%>
    connected
<%}else{ %>
    connetion failed
<%}%>