[20121101] 2일차 (JDBC 사용하여 JAVA와 Oracle 연동하기, 클래스 분산작업)

** 별도로 공부할 내용

예외처리(exception)

쿼리문(인서트, 셀렉트) 실행하는 자바 구문.

executeUpdate

executeQuery -- > 결과집합인 ResultSet 을 리턴받는다.

테이블 불러와서 출력할때는

while (ResultSet의 변수명.next()) 를 사용하고 row단위로 먼저 접근하고

실행문에서는 컬럼 단위로 접근한다.

http://devyongsik.tistory.com/294

Class.forName() 내용 확인 url

---

문제) 여러명의 국,영,수 점수를 입력 받아서 총점, 평균, 판정 결과 출력.

JDBC 프로그램 작성. 오라클 이용. 총점 기준 정렬 출력(내림차순 desc)

판정 기준은 

합격 -> 과목별로 40점 이상이면서, 평균이 60점 이상.

과락 -> 과목중 40점 미만이 있고, 평균이 60이상.

불합격 -> 평균이 60점 미만

평균이 60점 이상 -> 합격 또는 과락 -> 국영수 점수가 모두 40점 이상인지 확인.

평균이 60점 이하 -> 불합격

정렬 기준은 총점 기준으로 내림차순 정렬.

실행 예)

성적 입력(이름 국어 영어 수학)?hong 100 100 100

성적 입력(이름 국어 영어 수학)?kim 80 90 80

성적 입력(이름 국어 영어 수학)?-1 -> 입력끝 지정 필요

------------------------------------------

    이름  국어  영어  수학  총점   평균   판정

------------------------------------------

1등 hong   100   100   100   300  100.0   합격

2등 kim     80    90    80    ..    ..    합격

------------------------------------------

** 오라클 별도작업

--오라클에 성적저장용 테이블 생성

CREATE TABLE score(

sid NUMBER --PK, 자동번호 기능 부여.

,name VARCHAR2(10)

,kor NUMBER(3) --CK (0~100)

,end NUMBER(3) --CK (0~100)

,mat NUMBER(3) --CK (0~100)

);

-- 제약조건 추가

ALTER TABLE score

ADD CONSTRAINT score_sid_pk PRIMARY KEY (sid)

ALTER TABLE score

ADD CONSTRAINT score_kor_ck CHECK (kor BETWEEN 0 AND 100);

ALTER TABLE score

ADD CONSTRAINT score_eng_ck CHECK (eng BETWEEN 0 AND 100);

ALTER TABLE score

ADD CONSTRAINT score_mat_ck CHECK (mat BETWEEN 0 AND 100);

--자동번호부여

CREATE SEQUENCE scoreSeq;

--INSERT 쿼리 샘플 (자바 소스에 복사할 목적으로 미리 만든 샘플이므로 구문 끝에 ; 표시를 제외함.)

INSERT INTO score (sid, name, kor, eng, mat) VALUES (scoreSeq.nextVal, 'hong', 100, 100, 100)

--SELECT 쿼리 샘플

  SELECT sid, name, kor, eng, mat

  , (kor+eng+mat) as tot

  , (kor+eng+mat)/3 as ave

  , case

           when ((kor+eng+mat)/3 >= 60) and (kor<40 or eng<40 or mat<40) then '과락'

   when ((kor+eng+mat)/3 >= 60) then '합격'

   else '불합격'

    end as "grade"

FROM score

ORDER BY (kor+eng+mat) DESC

------------------------------------------------------------

//DBconn.java

package com.test;

import java.sql.*;

public class DBConn {

//Singleton pattern

private static Connection dbConn;

public static Connection getConnection() throws SQLException, ClassNotFoundException {

if (dbConn == null) {

String url = "jdbc:oracle:thin:@localhost:1521:xe";

String user = "scott";

String pw = "tiger";

Class.forName("oracle.jdbc.driver.OracleDriver");

dbConn = DriverManager.getConnection(url, user, pw);

}

return dbConn;

}

public static void close() throws SQLException {

if (dbConn != null) {

if (!dbConn.isClosed()) {

dbConn.close();

}

}

dbConn = null;

}

}

//Oracle07.java

package com.test;

import java.sql.*;

import java.util.*;

public class Oracle07 {

public static void main(String[] args) {

//1단계. 연결 (예외처리)

try {

Connection conn = DBConn.getConnection();

//System.out.println("오라클 연결됨!");

Scanner sc = new Scanner(System.in);

//무한 실행 ------------------------

do {

//외부입력단계

System.out.print("성적 입력(이름 국어 영어 수학)?");

String name = sc.next();

if (name.equals("-1")) {

break;

}

int kor = sc.nextInt();

int eng = sc.nextInt();

int mat = sc.nextInt();

//insert 쿼리 실행단계 -------------

//2단계. 쿼리준비

//insert 쿼리를 문자열로 만든다

String sql = String.format("INSERT INTO score (sid, name, kor, eng, mat) VALUES (scoreSeq.nextVal, '%s', %d, %d, %d)", name, kor, eng, mat); 

//3단계. 쿼리실행

//스테이트먼트는 execute부르기 위해 호출

Statement stmt = conn.createStatement(); // Statement 객체 생성. conn 은 Connection conn = DBConn.getConnection(); 의 변수임을 기억

int rowCount = stmt.executeUpdate(sql); // DML 문인경우 

//4단계. 결과출력

System.out.printf("%d개의 행이 입력됨. %n", rowCount);

} while (true);

//무한 실행 ------------------------

//select 쿼리 실행단계 -------------

//2단계. 쿼리준비

String sql = String.format("SELECT sid, name, kor, eng, mat, (kor+eng+mat) as tot, (kor+eng+mat)/3 as ave FROM score ORDER BY (kor+eng+mat) DESC");

//3단계. 쿼리실행

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql); //DQL 문인경우. 메모리에 쿼리문을 날린 결과를 저장시킴.

//4단계. 결과출력

int cnt = 0; //등수 출력용

while (rs.next()) {  // ROW 단위 접근 .. next()는 true/false를 리턴함.

String name = rs.getString("name");  // COLUMN 단위 접근

int kor = rs.getInt("kor"); // get으로 메모리에 올라간 rs데이터를 읽어온다.

int eng = rs.getInt("eng");

int mat = rs.getInt("mat");

int tot = rs.getInt("tot");

double ave = rs.getDouble("ave");

System.out.printf("%d등 %s %d %d %d %d %.1f %n", ++cnt, name, kor, eng, mat, tot, ave);

}

rs.close(); //ResultSet rs = stmt.executeQuery(sql) 은 재실행때를 대비해서 반드시!! close 해야함.

} catch(Exception e) {

System.out.println(e.toString());

}

}

}

---

문제) 이름과 전화번호를 저장하는 JDBC 프로그램 작성. 오라클 이용.

MemberDTO, MemberDAO, Oracle08 클래스 작성

실행 예)

이름 전화번호(1)?홍길동 010-123-1234

이름 전화번호(2)?김길동 010-222-3333

이름 전화번호(3)?-1

----------------------------

전체 회원수 : 2명

----------------------------

회원번호 이름   전화번호

----------------------------

1        홍길동 010-123-1234

2        김길동 010-222-3333

----------------------------

-- 회원수 출력쿼리

SELECT COUNT(*) AS count FROM member 

-- 회원정보 출력

SELECT mid, name, tel FROM member ORDER BY mid

-- 회원정보 입력

INSERT INTO member (mid, name, tel) VALUES (memberSeq.nextval, '%s', '%s')

//회원 테이블 생성. 회원번호, 이름, 전화번호. 시퀀스 객체 생성.

CREATE TABLE member (

mid NUMBER --PK

,name VARCHAR2(10)

,tel VARCHAR2(20)

);

ALTER TABLE member

ADD CONSTRAINT member_mid_pk PRIMARY KEY(mid);

CREATE SEQUENCE memberSeq;

----

//DBconn.java -> 오라클 연결용 클래스

//연결 전용 클래스 작성

//DBConn.java

package com.test;

import java.sql.*;

public class DBConn {

//Singleton pattern

private static Connection dbConn;

public static Connection getConnection() throws SQLException, ClassNotFoundException {

if (dbConn == null) {  // 이미 getConnection 을 호출하여 성공한적이 있으면 dbConn 을 그냥 리턴.

   // Singleton pattern은 이것을 의미함.

String url = "jdbc:oracle:thin:@localhost:1521:xe";

String user = "scott";

String pw = "tiger";

Class.forName("oracle.jdbc.driver.OracleDriver");

dbConn = DriverManager.getConnection(url, user, pw);

}

return dbConn;

}

public static void close() throws SQLException {

if (dbConn != null) {

if (!dbConn.isClosed()) {

dbConn.close();

}

}

dbConn = null;

}

}

//MemberDTO.java -> 자료처리 전용 클래스. 멤버변수, getter, setter

package com.test;

public class MemberDTO {

private int mid;

private String name, tel;

public int getMid() {

return mid;

}

public void setMid(int mid) {

this.mid = mid;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getTel() {

return tel;

}

public void setTel(String tel) {

this.tel = tel;

}

}

//MemberDAO.java -> 데이터베이스 엑세스 담당 클래스. 입력, 출력, 연결용 메소드.

package com.test;

import java.util.*;

import java.sql.*;

public class MemberDAO {

private Connection conn;

//연결 전용 메소드

public void connect() throws SQLException, ClassNotFoundException{

conn  = DBConn.getConnection();

}

//연결 해제 전용 메소드

public void close() throws SQLException{

DBConn.close();

conn = null;

}

//입력 메소드 즉, INSERT 쿼리 실행

public int add(MemberDTO dto) throws SQLException{

int result = 0;

String sql = String.format("INSERT INTO member (mid, name, tel) VALUES (memberSeq.nextval, '%s', '%s')", dto.getName(), dto.getTel());

//쿼리 실행

//Java에서 쿼리를 실행하면 

//내부적으로 COMMIT은 자동 실행됨.

//executeUpdate() 메소드는 DML문인 경우만 실행.

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

//회원수 출력 전용 메소드

public int count() throws SQLException {

int result = 0;

String sql = String.format("SELECT COUNT(*) AS count FROM member");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql); //결과집합

while(rs.next()) { //row 단위 접근

int count = rs.getInt("count"); //column 단위 접근

result = count;

}

rs.count();

return result;

}

//출력 메소드 즉, SELECT 쿼리 실행

public ArrayList<MemberDTO> lists() throws SQLException{

ArrayList<MemberDTO> arrayList = new ArrayList<MemberDTO>();

//SELECT 쿼리 준비

String sql = String.format("SELECT mid, name, tel FROM member ORDER BY mid");

//쿼리 실행

//executeQuery() 메소드는 DQL문인 경우만 실행.

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql); //결과집합

//결과 집합(ResultSet)을 리턴해야 된다.

//결과 집합은 특성상 이 메소드를 벗어날 수 없다. 왜냐하면 ResultSet은 메소드 빠져나가기 전에 반드시 클로즈하기 때문.

//결과 집합을 -> 결렉션 저장

//컬렉션을 리턴.

while(rs.next()) { //row 단위 접근

int mid = rs.getInt("mid"); //column 단위 접근

String name = rs.getString("name");

String tel = rs.getString("tel");

MemberDTO dto = new MemberDTO();

dto.setMid(mid);

dto.setName(name);

dto.setTel(tel);

arrayList.add(dto);

}

rs.close();

return arrayList;

}

}

//Oracle08.java -> main() 메소드. 인터페이스 담당.

package com.test;

import java.util.*;

public class Oracle08 {

public static void main(String[] args) {

MemberDAO dao = new MemberDAO();

Scanner sc = new Scanner(System.in);

try {

//연결

dao.connect();

//입력 - 무한실행

do {

System.out.printf("이름 전화번호(%d)?", (dao.count()+1));

String name = sc.next(); //이름

if (name.equals("-1")) {

break;

}

String tel = sc.next();  //전화번호

MemberDTO dto = new MemberDTO();

dto.setName(name);

dto.setTel(tel);

int rowCount = dao.add(dto);

System.out.printf("%d개의 행이 입력됨. %n", rowCount);

} while (true);

//출력

System.out.println("----------------------");

System.out.printf("전체 회원수 : %d명 %n", dao.count());

System.out.println("----------------------");

System.out.println("회원번호 이름  전화번호");

for(MemberDTO dto : dao.lists()) {

System.out.printf("%8d %4s %9s %n", dto.getMid(), dto.getName(), dto.getTel());

}

System.out.println("----------------------");

} catch (Exception e) {

System.out.println(e.toString());

}

}

}

---

문제) 여러명의 국,영,수 점수를 입력 받아서 총점, 평균, 판정 결과 출력.

JDBC 프로그램 작성. 오라클 이용. 총점 기준 정렬 출력(내림차순 desc)

DBConn, ScoreDTO, ScoreDAO, Oracle09

판정 기준은 

합격 -> 과목별로 40점 이상이면서, 평균이 60점 이상.

과락 -> 과목중 40점 미만이 있고, 평균이 60이상.

불합격 -> 평균이 60점 미만

평균이 60점 이상 -> 합격 또는 과락 -> 국영수 점수가 모두 40점 이상인지 확인.

평균이 60점 이하 -> 불합격

정렬 기준은 총점 기준으로 내림차순 정렬.

실행 예)

성적 입력(이름 국어 영어 수학)?hong 100 100 100

성적 입력(이름 국어 영어 수학)?kim 80 90 80

성적 입력(이름 국어 영어 수학)?-1 -> 입력끝 지정 필요

------------------------------------------

    이름  국어  영어  수학  총점   평균   판정

------------------------------------------

1등 hong   100   100   100   300  100.0   합격

2등 kim     80    90    80    ..    ..    합격

------------------------------------------

//DBConn.java 오라클 접속을 위한 클래스

//ScoreDTO.java 자료처리전용 클래스 - 멤버변수, getter, setter

package com.test;

public class ScoreDTO {

private int sid, kor, eng, mat, tot;

private String name;

private String grade;

private double avg;

public int getSid() {

return sid;

}

public void setSid(int sid) {

this.sid = sid;

}

public int getKor() {

return kor;

}

public void setKor(int kor) {

this.kor = kor;

}

public int getEng() {

return eng;

}

public void setEng(int eng) {

this.eng = eng;

}

public int getMat() {

return mat;

}

public void setMat(int mat) {

this.mat = mat;

}

public int getTot() {

return tot;

}

public void setTot(int tot) {

this.tot = tot;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getGrade() {

return grade;

}

public void setGrade(String grade) {

this.grade = grade;

}

public double getAvg() {

return avg;

}

public void setAvg(double avg) {

this.avg = avg;

}

}

//ScoreDAO.java 데이터베이스 엑세스 전용 클래스.

package com.test;

import java.sql.*;

import java.util.*;

public class ScoreDAO {

private Connection conn;

public void connect () throws SQLException, ClassNotFoundException{

conn = DBConn.getConnection();

}

public void close() throws SQLException{

DBConn.close();

conn = null;

}

public int add(ScoreDTO dto) throws SQLException{

int result = 0; // 1행이 입력되었습니다. 에서 1을 반환받기 위해 사용.

String sql = String.format("INSERT INTO score (sid, name, kor, eng, mat) VALUES (scoreSeq.nextval, '%s', %d, %d, %d)", dto.getName(), dto.getKor(), dto.getEng(), dto.getMat());

Statement stmt = conn.createStatement();

result = stmt.executeUpdate(sql);

return result;

}

public ArrayList<ScoreDTO> lists() throws SQLException {

ArrayList<ScoreDTO> arrayList = new ArrayList<ScoreDTO>();

//쿼리 준비

String sql = String.format("SELECT sid, name, kor, eng, mat, (kor+eng+mat) as tot, (kor+eng+mat)/3 as ave, case when ((kor+eng+mat)/3 >= 60) and (kor<40 or eng<40 or mat<40) then '과락' when ((kor+eng+mat)/3 >= 60) then '합격' else '불합격' end as grade FROM score ORDER BY (kor+eng+mat) DESC");

//쿼리 실행

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) { //row 단위 접근

int sid = rs.getInt("sid"); //column 단위 접근

String name = rs.getString("name");

int kor = rs.getInt("kor");

int eng = rs.getInt("eng");

int mat = rs.getInt("mat");

int tot = rs.getInt("tot");

double avg = rs.getDouble("ave");

String grade = rs.getString("grade");

ScoreDTO dto = new ScoreDTO();

dto.setSid(sid);

dto.setName(name);

dto.setKor(kor);

dto.setEng(eng);

dto.setMat(mat);

dto.setTot(tot);

dto.setAvg(avg);

dto.setGrade(grade);

arrayList.add(dto);

}

rs.close();

return arrayList;

}

}

//Oracle09.java main() 메소드 구현. 인터페이스 출력 클래스.

package com.test;

import java.util.*;

public class Oracle09 {

public static void main(String[] args) {

ScoreDAO dao = new ScoreDAO();

Scanner sc = new Scanner(System.in);

try {

//연결

dao.connect();

//입력

do{

System.out.print("성적 입력(이름 국어 영어 수학)?");

String name = sc.next();

if(name.equals("-1")) {

break;

}

int kor = sc.nextInt();

int eng = sc.nextInt();

int mat = sc.nextInt();

ScoreDTO dto = new ScoreDTO();

dto.setName(name);

dto.setKor(kor);

dto.setEng(eng);

dto.setMat(mat);

int rowcount = dao.add(dto);

System.out.printf("%d개의 행이 입력됨. %n", rowcount);

}while(true);

//출력

int cnt = 0; //등수 출력용

System.out.println("-----------------------------------------------");

System.out.println("    이름  국어  영어  수학  총점   평균   판정");

System.out.println("-----------------------------------------------");

for (ScoreDTO dto : dao.lists()) {

System.out.printf("%d등 %5s %4d  %4d  %4d  %4d  %3.1f %5s %n", ++cnt, dto.getName(), dto.getKor(), dto.getEng(), dto.getMat(), dto.getTot(), dto.getAvg(), dto.getGrade());

} 

System.out.println("-----------------------------------------------");

} catch (Exception e) {

System.out.println(e.toString());

}

}

}

---

문제) 성적 처리. 메뉴 구성 추가. 새 프로젝트에서 구성할 것.

DBConn.java, ScoreDTO, ScoreDAO, Process, Oralce10 클래스 이용.

여러명의 이름, 국어, 영어, 수학 입력 받아서 총점, 평균, 판정, 등수 계산해서 출력. 출력시 이름 오름차순 정렬해서 출력.

실행 예)

======== 성적 처리 ==============

1. 성적 입력

2. 성적 전체 출력

3. 이름 검색 출력

4. 성적 수정

5. 성적 삭제

선택(1~5, -1 종료)->1

성적 입력(이름 국어 영어 수학)?park 80 75 60

성적 입력(이름 국어 영어 수학)?hong 100 90 80

성적 입력(이름 국어 영어 수학)?kim 80 85 80

성적 입력(이름 국어 영어 수학)?-1

======== 성적 처리 ==============

1. 성적 입력

2. 성적 전체 출력

3. 이름 검색 출력

4. 성적 수정

5. 성적 삭제

선택(1~5, -1 종료)->2

 이름   국어  영어  수학  총점  평균   판정  등수

 hong    100   90    80    XXX  XXX.X  합격   1

 kim      80   85    80    XXX  XXX.X  합격   2

 park     80   75    60    XXX  XXX.X  합격   3

======== 성적 처리 ==============

1. 성적 입력

2. 성적 전체 출력

3. 이름 검색 출력

4. 성적 수정

5. 성적 삭제

선택(1~5, -1 종료)->3

검색하려는 이름?kim

 이름   국어  영어  수학  총점  평균   판정  등수

 kim      80   85    80    XXX  XXX.X  합격   2

======== 성적 처리 ==============

1. 성적 입력

2. 성적 전체 출력

3. 이름 검색 출력

4. 성적 수정

5. 성적 삭제

선택(1~5, -1 종료)->3

검색하려는 이름?choi

검색 결과가 없습니다.

======== 성적 처리 ==============

1. 성적 입력

2. 성적 전체 출력

3. 이름 검색 출력

4. 성적 수정

5. 성적 삭제

선택(1~5, -1 종료)->-1

프로그램이 종료되었습니다.

-------------------------------------------------------------

//새 프로젝트 생성(Score_Console)

//DBConn.java    -> 오라클 데이터베이스 접속 클래스.

//ScoreDTO.java  -> 멤버변수, getter, setter. 등수 관련 멤버 추가.

//ScoreDAO.java  -> 연결, 입력, 출력, 검색, 수정, 삭제 메소드.

//Process.java   -> 입력, 출력, 검색, 수정, 삭제 관련 인터페이스

//Oralce10.java  -> main() 메소드. 주메뉴 무한실행